Verify That Partition Pruning Works

If you have to deal with large amounts of data, it is often favourable to partition your tables. When accessing the data, you can take advantage of partition pruning, i.e. Oracle reads only the partitions that are affected by your query.

We will build an easy example to see how we can figure out what Oracle is doing. Let's create a partitioned table with six partitions, each containing one row:

drop table part_prune
/
create table part_prune
(part_key number(1)
)
partition by list (part_key)
(partition p1 values (1)
,partition p2 values (2)
,partition p3 values (3)
,partition p4 values (4)
,partition p5 values (5)
,partition p6 values (6)
)
/
insert into part_prune values (1);
insert into part_prune values (2);
insert into part_prune values (3);
insert into part_prune values (4);
insert into part_prune values (5);
insert into part_prune values (6);

When we query the table using a where condition which constrains the partition key, Oracle should only access the partitions we use in the constraint. Let's verify this by looking at the execution plan:

explain plan for
select *
from   part_prune
where  part_key in (1,3)
/
select * 
from   table(dbms_xplan.display())
/

----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |     5 |    65 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST INLIST|            |     5 |    65 |     2   (0)| 00:00:01 |KEY(I) |KEY(I) |
|   2 |   TABLE ACCESS FULL   | PART_PRUNE |     5 |    65 |     2   (0)| 00:00:01 |KEY(I) |KEY(I) |
----------------------------------------------------------------------------------------------------

In the columns "Pstart" and "Pstop" we can see "KEY(I)". That tells us that Oracle actually reads the requested partitions only. The "PARTITION LIST INLIST" entry denotes the elimination strategy, our IN list.

But what if we do not provide the values as literals, but query them from another table?

drop table part_keys;
create table part_keys(n number(1), sel varchar2(1));
insert into part_keys values (1, 'Y');
insert into part_keys values (2, 'Y');
insert into part_keys values (3, 'Y');

explain plan for
select *
from   part_prune
where  part_key in (select n from part_keys where sel = 'Y')
/
select * 
from   table(dbms_xplan.display())
/
-------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |            |    15 |   390 |     4  (25)| 00:00:01 |       |       |
|   1 |  NESTED LOOPS            |            |    15 |   390 |     4  (25)| 00:00:01 |       |       |
|   2 |   SORT UNIQUE            |            |   164 |  2132 |     2   (0)| 00:00:01 |       |       |
|   3 |    TABLE ACCESS FULL     | PART_KEYS  |   164 |  2132 |     2   (0)| 00:00:01 |       |       |
|   4 |   PARTITION LIST ITERATOR|            |     3 |    39 |     0   (0)| 00:00:01 |   KEY |   KEY |
|*  5 |    TABLE ACCESS FULL     | PART_PRUNE |     3 |    39 |     0   (0)| 00:00:01 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------

"Pstart" and "Pstop" only reference "KEY" now. This means that Oracle retrieves the actual key values at runtime and decides whether to use partition pruning then.

In order to prune the partitions, Oracle must know the values of the partition keys requested. Before 8.1.6 this was only possible when using a nested loop. Since 8.1.6 Oracle uses recursive SQL to determine the values before the actual statement is being executed.

Here is an example with a hash join:

select /*+ use_hash(pp) */
       *
from   part_prune pp
  join part_keys pk
    on pp.part_key = pk.n
       and pk.sel = 'Y'
/
select * 
from   table(dbms_xplan.display())
/

-------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |            |    36 |  1008 |     7  (15)| 00:00:01 |       |       |
|*  1 |  HASH JOIN               |            |    36 |  1008 |     7  (15)| 00:00:01 |       |       |
|*  2 |   TABLE ACCESS FULL      | PART_KEYS  |     8 |   120 |     2   (0)| 00:00:01 |       |       |
|   3 |   PARTITION LIST ITERATOR|            |  3600 | 46800 |     4   (0)| 00:00:01 |   KEY |   KEY |
|   4 |    TABLE ACCESS FULL     | PART_PRUNE |  3600 | 46800 |     4   (0)| 00:00:01 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------

The recursive statement that Oracle uses to determine the values of the partition key can be seen in the column other in the plan_table:

SELECT distinct TBL$OR$IDX$PART$NUM("PART_PRUNE", 0, d#, p#, "PART_KEY") 
FROM (SELECT "PK"."N" "PART_KEY" FROM "PART_KEYS" "PK" WHERE "PK"."SEL"='Y') 
ORDER BY 1

The following undocumented init.ora parameters influence this behaviour:

Parameter Name	Default Value	Use To Force Partition Pruning	Description
_subquery_pruning_cost_factor	20	1	Cost restriction.
_subquery_pruning_reduction	50	100	Selectivity restriction.

Use the following alter session statements to force partition pruning:

alter session set "_subquery_pruning_cost_factor" =   1; -- overrides cost restrictions
alter session set "_subquery_pruning_reduction"   = 100; -- overrides selectivity restrictions

I have observed that this does not necessarily always force partition pruning. In our example, pruning is only done if I use the predicate pk.sel = 'Y', which is true for all records... Can anyone explain this?

How can we now check if partition pruning is being used at runtime? Event 10128 tells us exactly this. In order to use the event, we need a special table.

drop table kkpap_pruning;
create table kkpap_pruning 
(partition_count  NUMBER
,iterator         VARCHAR2(32)
,partition_level  VARCHAR2(32)
,order_pt         VARCHAR2(12)
,call_time        VARCHAR2(12)
,part#            NUMBER
,subp#            NUMBER
,abs#             NUMBER
);
-- set the event
alter session set events '10128 trace name context forever, level 2';
-- perform the query
select /*+ use_hash(pp) */
       *
from   part_prune pp
  join part_keys pk
    on pp.part_key = pk.n
       and pk.sel = 'Y'
/
-- disable the event
alter session set events '10128 trace name context off';

Oracle writes the information about partition pruning to a trace file in the user_dump_dest. Let's see what we got:

*** 2006-10-31 16:37:41.234
Partition Iterator Information:
  partition level = PARTITION
  call time = RUN
  order = ASCENDING
  Partition iterator for level 1:
   iterator = ARRAY [count= 3, max = 6] = { 0 1 2 }
SQL text = text = SELECT distinct TBL$OR$IDX$PART$NUM("PART_PRUNE", 0,  "PART_KEY") FROM (SELECT "PK"."N" "PART_KEY" FROM "PART_KEYS" "PK" WHERE "PK"."SEL"='Y') ORDER BY 1}
   index = 0
  current partition: part# = 0, subp# = 65535, abs# = 0
  current partition: part# = 1, subp# = 65535, abs# = 1
  current partition: part# = 2, subp# = 65535, abs# = 2

It has worked! Oracle has determined the partition key values and accesses the partitions { 0, 1, 2 } only.