分区剪除 (partition pruning)

 

CREATE TABLE DFMS.TEST06
PARTITION BY RANGE (OBJECT_ID)
( 
  PARTITION P1 VALUES LESS THAN (2000),
  PARTITION P2 VALUES LESS THAN (8000),
  PARTITION P3 VALUES LESS THAN (20000),
  PARTITION P4 VALUES LESS THAN (80000),
  PARTITION P5 VALUES LESS THAN (120000),
  PARTITION PMAX VALUES LESS THAN (MAXVALUE)
)
AS SELECT * FROM DBA_OBJECTS; 

 

A. 未建立任何索引
SELECT * FROM TEST06 WHERE OBJECT_ID = 5421 ;

通过执行计划可以看到系统cost较普通表的full table scan要低很多，且执行
计划中有 partition range single 过程，而且可以看到只扫描了分区p2, 并
没有做实际的full table scan . 同样如果是hash表，如果where条件使用了
分区键，执行计划中也会是 partition hash single 。  

B. 在object_name上建立local索引：
create index idx_test06_name on test06(object_name) local ;

使用object_name作为查询条件：
SELECT * FROM TEST06 WHERE  OBJECT_NAME = 'DBA_TYPES' ;
通过执行计划可以看到虽然用到了object_name字段上的索引, 但是分区部分使用
了partition range all，没有使用到分区剪除。

C. 在last_ddl_time上建立global索引：
create index idx_test06_time on test06(LAST_DDL_TIME) ;

通过last_ddl_time作为查询条件：
SELECT * FROM TEST06  
WHERE  LAST_DDL_TIME  >= to_date('2008-02-17 01:50:57','yyyy-mm-dd hh24:mi:ss') AND
       LAST_DDL_TIME  <= to_date('2008-02-17 01:51:00','yyyy-mm-dd hh24:mi:ss') ;
通过执行计划可以看到，和普通表一样通过global index索引扫描进行查询,没有使用到分区剪除 。

D. 在object_id上建立local索引(注意，如果字段上有unique的约束[pk或unique]，
那么是不能在上面建立local partitin index的，不过包含有分区键值就可以)。

create index idx_test06_id on test06(object_id) local ;

同样查询 SELECT * FROM TEST06 WHERE OBJECT_ID = 5421 ; 执行计划，既做了
partition range single (使用了分区剪除), 也有index range scan. 这里在分
区P2中又进行了索引扫描，相对在object_id上没有local index来看，cost低。

E. 我们在object_id上建立global索引
create index idx_test06_id on test06(object_id);
查询 SELECT * FROM TEST06 WHERE  OBJECT_ID = 5421 ; 执行计划，同样只是做
了index range scan, 没有做分区剪除，cost与有local index时一样。

 

测试发现， 只有包含有分区键作为where条件才能使用到分区剪除。

 

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

转载于:http://blog.itpub.net/35489/viewspace-684640/