分区方法的分区消除

troubleshooting中的一个图：

SQL> SELECT * FROM v$version WHERE rownum=1;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProdSQL>--range分区表CREATE TABLE sales_range (salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_amount NUMBER(10), sales_date DATE)PARTITION BY RANGE(sales_date) (PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','MM/DD/YYYY')),PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','MM/DD/YYYY')),PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','MM/DD/YYYY')),PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','MM/DD/YYYY')),PARTITION sales_all_other2000 VALUES LESS THAN(MAXVALUE));insert into sales_range values(00001,'tom',10,TO_DATE('01/01/2000','MM/DD/YYYY'));insert into sales_range values(00002,'jerry',20,TO_DATE('01/25/2000','MM/DD/YYYY'));insert into sales_range values(00003,'evan',10,TO_DATE('02/05/2000','MM/DD/YYYY'));insert into sales_range values(00004,'smith',10,TO_DATE('03/05/2000','MM/DD/YYYY'));insert into sales_range values(00005,'lucy',10,TO_DATE('04/05/2000','MM/DD/YYYY'));insert into sales_range values(00006,'mary',10,TO_DATE('05/05/2000','MM/DD/YYYY'));SQL> SELECT table_name,partition_name,partition_position 2 FROM dba_tab_partitions 3 WHERE table_name='SALES_RANGE';TABLE_NAME PARTITION_NAME PARTITION_POSITION------------------------------ ------------------------------ ------------------SALES_RANGE SALES_JAN2000 1SALES_RANGE SALES_FEB2000 2SALES_RANGE SALES_MAR2000 3SALES_RANGE SALES_APR2000 4SALES_RANGE SALES_ALL_OTHER2000 5SQL> set autot traceonly explainSQL> ed tmp.bufSQL> select * from sales_range s 2 where s.sales_date 3 between TO_DATE('02/01/2000','MM/DD/YYYY') and TO_DATE('04/01/2000','MM/DD/YYYY');执行计划----------------------------------------------------------Plan hash value: 3978535265--------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |--------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 104 | 5 (0)| 00:00:01 | | || 1 | PARTITION RANGE ITERATOR| | 2 | 104 | 5 (0)| 00:00:01 | 2 | 4 ||* 2 | TABLE ACCESS FULL | SALES_RANGE | 2 | 104 | 5 (0)| 00:00:01 | 2 | 4 |--------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter("S"."SALES_DATE"<=TO_DATE('2000-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))Note----- - dynamic sampling used for this statement 从 Pstart，Pstop可以看出只扫描了2，3，4分区。

以下是not in:SQL> select * from sales_range s 2 where s.sales_date 3 not in (TO_DATE('01/01/2000','MM/DD/YYYY') ,TO_DATE('01/25/2000','MM/DD/YYYY') ,TO_DATE('04/05/2000','MM/DD/YYYY'), 4 TO_DATE('02/05/2000','MM/DD/YYYY') ,TO_DATE('03/05/2000','MM/DD/YYYY') );执行计划----------------------------------------------------------Plan hash value: 1773267025---------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |---------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 52 | 7 (0)| 00:00:01 | | || 1 | PARTITION RANGE ALL| | 1 | 52 | 7 (0)| 00:00:01 | 1 | 5 ||* 2 | TABLE ACCESS FULL | SALES_RANGE | 1 | 52 | 7 (0)| 00:00:01 | 1 | 5 |---------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter("S"."SALES_DATE"<>TO_DATE('2000-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "S"."SALES_DATE"<>TO_DATE('2000-01-25 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "S"."SALES_DATE"<>TO_DATE('2000-04-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "S"."SALES_DATE"<>TO_DATE('2000-02-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "S"."SALES_DATE"<>TO_DATE('2000-03-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))Note----- - dynamic sampling used for this statement 优化器对所有的分区进行扫描PARTITION RANGE ALL，从Pstart| Pstop也可以看出扫描了所有的分区。

要注意，==,<>,not in,not null 不会进行分区消除。