局部分区索引的分区消除

局部分区索引的分区消除[@more@]分区消除行为：如果查询首先访问索引，是否能消除分区完全取决于查询中的谓词。

SQL> create table test

2 (a int,

3 b int,

4 data char(20)

5 )

6 partition by range(a)

7 (partition part_1 values less than(2),

8 partition part_2 values less than(3)

9 )

10 /

表已创建。

SQL> create index local_pre on test(a,b) local;

索引已创建。

SQL> create index local_nopre on test(b) local;

索引已创建。

SQL> insert into test

2 select mod(rownum -1 ,2) +1, rownum, 'x'

3 from all_objects;

已创建11201行。

进行对象统计信息收集：

SQL> edit

已写入 file afiedt.buf

1 BEGIN

2 dbms_stats.gather_table_stats(

3 ownname => user,

4 tabname => 'TEST',

5 estimate_percent => 100,

6 method_opt => 'for all columns size skewonly',

7 cascade => TRUE

8 );

9* END;

SQL> /

PL/SQL 过程已成功完成。

SQL> set autot traceonly explain

SQL> select * from test where a=1 and b=1;

执行计划

----------------------------------------------------------

Plan hash value: 3724286461

--------------------------------------------------------------------------------

--------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%

CPU)| Time | Pstart| Pstop |

--------------------------------------------------------------------------------

--------------------------------

| 0 | SELECT STATEMENT | | 1 | 28 | 2

(0)| 00:00:01 | | |

| 1 | PARTITION RANGE SINGLE | | 1 | 28 | 2

(0)| 00:00:01 | 1 | 1 |

| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST | 1 | 28 | 2

(0)| 00:00:01 | 1 | 1 |

|* 3 | INDEX RANGE SCAN | LOCAL_PRE | 1 | | 1

(0)| 00:00:01 | 1 | 1 |

--------------------------------------------------------------------------------

--------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("A"=1 AND "B"=1)

通过查看执行计划，此查询通过索引local_pre做了分区消除。

SQL> select * from test where b =1;

执行计划

----------------------------------------------------------

Plan hash value: 3332543978

--------------------------------------------------------------------------------

----------------------------------

| Id | Operation | Name | Rows | Bytes | Cost

(%CPU)| Time | Pstart| Pstop |

--------------------------------------------------------------------------------

----------------------------------

| 0 | SELECT STATEMENT | | 1 | 28 | 4

(0)| 00:00:01 | | |

| 1 | PARTITION RANGE ALL | | 1 | 28 | 4

(0)| 00:00:01 | 1 | 2 |

| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST | 1 | 28 | 4

(0)| 00:00:01 | 1 | 2 |

|* 3 | INDEX RANGE SCAN | LOCAL_NOPRE | 1 | | 3

(0)| 00:00:01 | 1 | 2 |

--------------------------------------------------------------------------------

----------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("B"=1)

通过local_nopre，oracle不能排除分区，两个分区都必须考虑，此查询未做分区消除。使用非前缀索引，要做到分区消除，必须使用一个允许分区消除的查询。

SQL> drop index local_pre;

索引已删除。

SQL> select * from test where a= 1 and b=1;

执行计划

----------------------------------------------------------

Plan hash value: 2334989887

--------------------------------------------------------------------------------

----------------------------------

| Id | Operation | Name | Rows | Bytes | Cost

(%CPU)| Time | Pstart| Pstop |

--------------------------------------------------------------------------------

----------------------------------

| 0 | SELECT STATEMENT | | 1 | 28 | 2

(0)| 00:00:01 | | |

| 1 | PARTITION RANGE SINGLE | | 1 | 28 | 2

(0)| 00:00:01 | 1 | 1 |

|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST | 1 | 28 | 2

(0)| 00:00:01 | 1 | 1 |

|* 3 | INDEX RANGE SCAN | LOCAL_NOPRE | 1 | | 1

(0)| 00:00:01 | 1 | 1 |

--------------------------------------------------------------------------------

----------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("A"=1)

3 - access("B"=1)

通过查看执行计划，Oracle利用了分区消除。因为使用了条件a = 1,Oracle拥有了足够的信息，从而使用非前缀索引也能做到分区消除。

根据这个例子中创建的分区表，如果经常使用如下查询：

select * from test where a = 1 and b = 1;

select * from test where b = 1;

可以测试创建(b,a)索引的效果，此索引以上两个查询都可以使用。如果创建局部前缀索引（a ,b)，很可能只对第一个查询有用，对第二个查询不能作分区消除，但能否用得上索引，需要测试，看执行计划是否走index skip scan。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9523925/viewspace-1031875/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9523925/viewspace-1031875/