分割槽剪除 (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條件才能使用到分割槽剪除。