Tuesday, October 31, 2006

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 NameDefault ValueUse To Force Partition PruningDescription
_subquery_pruning_cost_factor201Cost restriction.
_subquery_pruning_reduction50100Selectivity 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.

3 comments:

Unknown said...

Great Article and very informative

Best Regards,
K. Hairopoulos

kk said...

I have been thinking how to know the KEY value.

Thanks a lot

kk said...

Thanks a lot, I have been searching for this and Randolf pointed me to this.