10.6 Partition Pruning

Even when you don't name a specific partition in a SQL statement, the fact that a table is partitioned might still influence the manner in which the statement accesses the table. When a SQL statement accesses one or more partitioned tables, the Oracle optimizer attempts to use the information in the WHERE clause to eliminate some of the partitions from consideration during statement execution. This process, called partition pruning, speeds statement execution by ignoring any partitions that cannot satisfy the statement's WHERE clause. To do so, the optimizer uses information from the table definition combined with information from the statement's WHERE clause. For example, given the following table definition:

CREATE TABLE tab1 (

  col1 NUMBER(5) NOT NULL,

  col2 DATE NOT NULL,

  col3 VARCHAR2(10) NOT NULL)

PARTITION BY RANGE (col2)

 (PARTITION tab1_1998 

    VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY'))

      TABLESPACE t1,

  PARTITION tab1_1999 

    VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY'))

      TABLESPACE t1,

  PARTITION tab1_2000 

    VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY'))

      TABLESPACE t3,

  PARTITION tab1_2001 

    VALUES LESS THAN (TO_DATE('01-JAN-2002','DD-MON-YYYY'))

      TABLESPACE t4);

and the following query:

SELECT col1, col2, col3

FROM tab1

WHERE col2 > TO_DATE('01-OCT-2000','DD-MON-YYYY');

the optimizer would eliminate partitions tab1_1998 and tab1_1999 from consideration, since neither partition could contain rows with a value for col2 greater than October 1, 2000.

Partition pruning is sometimes referred to as partition elimination.

For the optimizer to make these types of decisions, the WHERE clause must reference at least one column from the set of columns that comprise the partition key. Although this might seem fairly straightforward, not all queries against a partitioned table naturally include the partition key. If a unique index exists on the col1 column of the tab1 table from the previous example, for instance, the following query would generally offer the most efficient access:

SELECT col1, col2, col3

FROM tab1

WHERE col1 = 1578;

If the index on col1 had been defined as a local index, however, Oracle would need to visit each partition's local index to find the one that holds the value 1578. If you also have information about the partition key (col2 in this case), you might want to consider including it in the query so that the optimizer can eliminate partitions, as in the following:

SELECT col1, col2, col3

FROM tab1

WHERE col1 = 1578 

  AND col2 > TO_DATE('01-JAN-2001','DD-MON-YYYY');

With the additional condition, the optimizer can now eliminate the tab1_1998, tab1_1999, and tab1_2000 partitions from consideration. Oracle will now search a single unique index on the tab1_2001 partition instead of searching a unique index on each of the four table partitions. Of course, you would need to know that data pertaining to the value 1578 also had a value for col2 greater then January 1, 2001. If you can reliably provide additional information regarding the partition keys, than you should do so; otherwise, you'll just have to let the optimizer do its best. Running EXPLAIN PLAN on your DML statements against partitioned tables will allow you to see which partitions the optimizer decided to utilize.

When checking the results of EXPLAIN PLAN, there are a couple of partition specific columns that you should add to your query against plan_table to see which partitions are being considered by the optimizer. To demonstrate, we'll explain the following query against tab1:

EXPLAIN PLAN 

SET STATEMENT_ID = 'qry1' FOR

SELECT col1, col2, col3

FROM tab1

WHERE col2 BETWEEN TO_DATE('01-JUL-1999','DD-MON-YYYY')

  AND TO_DATE('01-JUL-2000','DD-MON-YYYY');

When querying the plan_table table, you should include the partition_start and partition_end columns whenever the operation field starts with 'PARTITION':

SELECT lpad(' ',2 * level) || operation || ' ' ||

  options || ' ' || object_name || 

  DECODE(SUBSTR(operation, 1, 9), 'PARTITION',

    ' FROM ' || partition_start || 

    ' TO ' || partition_stop, ' ') "exec plan"

FROM plan_table

CONNECT BY PRIOR id = parent_id

START WITH id = 0 AND statement_id = 'qry1';



exec plan

------------------------------------------------------

  SELECT STATEMENT

    PARTITION RANGE ITERATOR  FROM 2 TO 3

      TABLE ACCESS FULL TAB1

The value of PARTITION RANGE for the operation column along with the value of ITERATOR for the options column indicates that more than one partition will be involved in the execution plan.^[2] The values of the partition_start and partition_end columns (2 and 3, respectively) indicate that the optimizer has decided to prune partitions 1 and 4, which correlate to the tab1_1998 and tab1_2001 partitions.^[3] Given that the WHERE clause specifies a date range of July 1, 1999 to July 1, 2000, the optimizer has correctly pruned all partitions that cannot contribute to the result set.

^[2] If the optimizer had pruned all but one partition, the options column would contain the value 'SINGLE'. If no partitions were pruned, the options column would contain the value 'ALL'.

^[3] The number shown in the partition_start and partition_end columns correlates to the partition_position column in the user_tab_partitions table, so you can query this table to identify the names of the partitions that are included in the execution plan.