分区方法的分区消除

troubleshooting中的一个图：

SQL> SELECT * FROM v$version WHERE rownum=1; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod SQL> --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 1 SALES_RANGE SALES_FEB2000 2 SALES_RANGE SALES_MAR2000 3 SALES_RANGE SALES_APR2000 4 SALES_RANGE SALES_ALL_OTHER2000 5 SQL> set autot traceonly explain SQL> ed tmp.buf SQL> 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也可以看出扫描了所有的分区。