Slide 43
Slide 43 text
部分索引
実⾏例
パーティションp99を除外する部分グローバル索引
• 索引でパーティション・パルーニングは⾏わないので、
TBL$OR$IDX$PART$NUMファンクションでフィルターを
⾏っている
43 Copyright © 2024, Oracle and/or its affiliates
SQL> CREATE TABLE tab1 (c1 NUMBER, c2 NUMBER, c3 CHAR(1000))
2 PARTITION BY RANGE (c1) (
3 PARTITION p01 VALUES LESS THAN (100),
4 PARTITION p02 VALUES LESS THAN (200),
5 PARTITION p03 VALUES LESS THAN (300),
6 PARTITION p99 VALUES LESS THAN (MAXVALUE) INDEXING OFF);
SQL> CREATE INDEX tab1_ix1 ON tab1 (c2) GLOBAL INDEXING PARTIAL;
SQL> SELECT partition_name,indexing
2 FROM user_tab_partitions
3 WHERE table_name = 'TAB1';
PARTITION_NAME INDEXING
-------------- --------
P01 ON
P02 ON
P03 ON
P99 OFF
SQL> SELECT index_name,indexing FROM user_indexes
2 WHERE index_name = 'TAB1_IX1';
INDEX_NAME INDEXING
------------ --------
TAB1_IX1 PARTIAL
SQL> SELECT * FROM tab1 WHERE c1 > 200 AND c2 = 1;
-----------------------------------------------------------------------------------
| Id | Operation | Name | | Pstart| Pstop |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | VIEW | VW_TE_2 | | | |
| 2 | UNION-ALL | | | | |
|* 3 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| TAB1 | | 3 | 3 |
|* 4 | INDEX RANGE SCAN | TAB1_IX1 | | | |
| 5 | PARTITION RANGE SINGLE | | | 4 | 4 |
|* 6 | TABLE ACCESS FULL | TAB1 | | 4 | 4 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("TAB1"."C1">200 AND "TAB1"."C1"<300)
4 - access("C2"=1)
filter(TBL$OR$IDX$PART$NUM("TAB1",0,0,65535,ROWID)>=3 AND
TBL$OR$IDX$PART$NUM("TAB1",0,0,65535,ROWID)<=3)
6 - filter("C2"=1 AND "TAB1"."C1" IS NOT NULL)