分区方法的分区消除

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也可以看出扫描了所有的分区。

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