關於ORACLE MYSQL在非字首分割槽索引上分割槽剪裁的比較

ORACLE:
CREATE TABLE testpur (i NUMBER, j NUMBER , f varchar2(20))
     PARTITION BY RANGE(i)
        (PARTITION p1 VALUES LESS THAN (10),
         PARTITION p2 VALUES LESS THAN (20),
         PARTITION p2 VALUES LESS THAN (30),
         PARTITION p2 VALUES LESS THAN (40),
         PARTITION p2 VALUES LESS THAN (50));


declare  
   i number(10);
begin 
  for i in 1..1000
  loop
  insert into purge
   values(mod(i,50),i,'gaopeng');
  end loop;
end;


MYSQL:
CREATE TABLE testpur (i int, j int , f varchar(20))
     PARTITION BY RANGE(i)
        (PARTITION p1 VALUES LESS THAN (10),
         PARTITION p2 VALUES LESS THAN (20),
         PARTITION p3 VALUES LESS THAN (30),
         PARTITION p4 VALUES LESS THAN (40),
         PARTITION p5 VALUES LESS THAN (50));
         
delimiter //
create procedure myproc() 
begin 
declare num int; 
set num=1; 
while num <= 1000 do 
  insert into testpur  values(mod(num,50),num,'test'); 
  set num=num+1;
end while;
 end//
         
這樣ORACLE和MYSQL同樣的建立了相同的分割槽表，在ORACLE中，即使查詢中使用的索引是本地非字首索引，也就是本LOCAL索引
不包含分割槽鍵本身，這種情況下即使使用本索引也不會觸發分割槽剪裁，但是如果謂詞中包含分割槽鍵，索引分割槽剪裁的特效能夠用到
如上，我們建立本地非字首分割槽索引
SQL> create index testpur_l_nopre on testpur(j) local;
Index created
然後檢視他的執行計劃


explain plan for select * from  testpur where j=10 and i=19;


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 717037044


----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                 |     1 |    15 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE            |                 |     1 |    15 |     2   (0)| 00:00:01 |     2 |     2 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TESTPUR         |     1 |    15 |     2   (0)| 00:00:01 |     2 |     2 |
|*  3 |    INDEX RANGE SCAN                | TESTPUR_L_NOPRE |     1 |       |     1   (0)| 00:00:01 |     2 |     2 |
----------------------------------------------------------------------------------------------------------------------




PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------


   2 - filter("I"=19)
   3 - access("J"=10)


可以看到及時如此索引任然是在分割槽2中進行的掃描，也就是說特定條件下非字首分割槽索引是可以起到字首索引效果的


然後我們看看MYSQL的表現，MYSQL沒有GLOBAL分割槽索引一說。只有本地分割槽索引
我們建立索引


create index testpur_l_nopre on testpur(j) ;
檢視執行計劃
mysql> explain partitions  select * from  testpur where j=10 and i=19;
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------+------+-------------+
| id | select_type | table   | partitions | type | possible_keys   | key             | key_len | ref   | rows | Extra       |
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------+------+-------------+
|  1 | SIMPLE      | testpur | p2         | ref  | testpur_l_nopre | testpur_l_nopre | 5       | const |    1 | Using where |


很顯然MYSQL也是用了同樣技術，這裡不僅用到分割槽剪裁而且使用到了分割槽索引 testpur_l_nopre。


另外題外話，MYSQL,ORACLE的主鍵唯一鍵必須是分割槽鍵的一部分，如果分割槽鍵是i,j,那麼主鍵唯一鍵必須是其中一個。
其原因很簡單，在ORACLE 9I 10G 程式設計藝術中有明確說明，如果允許包含非分割槽鍵的區域性唯一索引，那麼其分割槽特性
將被消耗殆盡，因為這樣不得不去每次掃描全部分割槽來保證其唯一性，只有包含了分割槽鍵才能做到事先判斷。