关于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 编程艺术中有明确说明，如果允许包含非分区键的局部唯一索引，那么其分区特性
将被消耗殆尽，因为这样不得不去每次扫描全部分区来保证其唯一性，只有包含了分区键才能做到事先判断。