Mohamed Houri’s Oracle Notes

December 25, 2013

Partition pruning with hash partitioned tables

Filed under: Partitioning — hourim @ 9:27 am

This is a simple reminder on how partition pruning with hash partitioned table could be different from range partitioned ones. If you have a range partitioned table, a query using a partition key in the where clause can eliminate partitions either dynamically (dynamic pruning) or at hard parse time. This partition pruning might occur even if the predicate on the partition key is not an equality (>= or <=). This is, however, not the case for a hash partitioned table. For this partioning method you have to apply an equality or an in list predicate on the partitioned key to see the CBO pruning your hash partitions.

drop table t1;
create table t1
(n1             number not null,
creation_date  date   not null,
comments       varchar2(500))
partition by hash (n1)
partitions 16;

insert into t1 select rownum, sysdate + (rownum-1), lpad('x',10)
from dual
connect by level <= 1000;

commit;

I am going to execute 3 queries against the above engineered hash partitioned table. The first one will use an equality predicate against the partition key (n1) while in the second one I will be using an in list operator on the same partition key. And finally in the last query I will compare the partition key to a value using a greater than predicate operator.


select * from t1
where n1 = 42;

select * from t1
where n1 in(42,100);

select * from t1
where n1 > 42;

The corresponding execution plans are given below respectively

----------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Pstart| Pstop |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |  1024 |       |       |
|   1 |  PARTITION HASH SINGLE|      |     1 |  1024 |    14 |    14 |
|*  2 |   TABLE ACCESS FULL   | T1   |     1 |  1024 |    14 |    14 |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("N1"=42)

----------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Pstart| Pstop |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     2 |  2048 |       |       |
|   1 |  PARTITION HASH INLIST|      |     2 |  2048 |KEY(I) |KEY(I) |
|*  2 |   TABLE ACCESS FULL   | T1   |     2 |  2048 |KEY(I) |KEY(I) |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("N1"=42 OR "N1"=100)

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Pstart| Pstop |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   737 |   737K|       |       |
|   1 |  PARTITION HASH ALL|      |   737 |   737K|     1 |    16 |
|*  2 |   TABLE ACCESS FULL| T1   |   737 |   737K|     1 |    16 |
-------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("N1">42)

A partition pruning occurs, at hard parse time, for the first query (equality predicate against the hash partitioned key). A dynamic partition pruning occurs for the second query (in list predicate against the partition key) and there was no partition pruning for the third query (an inequality predicate on the partition key).

Simply put, when selecting from a hash partitioned table, if you want to prune your partitions and accelerate your query response time, you need to apply an equality predicate (or in list) on the partition key.

Spot the resemblance (or coincidence) with the hash join method that works only when the join condition is an equality.

Up to now we are using a single hash partition key. What if I hash partition by a composite key?

create table t1
(n1             number not null,
 n2             number not null,
 creation_date  date   not null,
 comments       varchar2(500))
partition by hash (n1,n2) --> composite partition key
partitions 16;

insert into t1 select rownum,rownum*2, sysdate + (rownum-1), lpad('x',10)
from dual
connect by level <= 1000;

commit;

In the following query I will use a single equality predicate on the first column of the composed partition key as shown below:

explain plan for
select * from t1
where n1 = 42;

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Pstart| Pstop |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     8 |  8296 |       |       |
|   1 |  PARTITION HASH ALL|      |     8 |  8296 |     1 |    16 |
|*  2 |   TABLE ACCESS FULL| T1   |     8 |  8296 |     1 |    16 |
-------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("N1"=42)

Despite the existence of an equality predicate on the first partition key, partition pruning didn’t occurred. However if I add to the above query an extra predicate on the second partition key, partition pruning will this time occur as shown below:

explain plan for
select * from t1
where n1 = 42
and   n2 = 84;

----------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Pstart| Pstop |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |  1037 |       |       |
|   1 |  PARTITION HASH SINGLE|      |     1 |  1037 |    13 |    13 |
|*  2 |   TABLE ACCESS FULL   | T1   |     1 |  1037 |    13 |    13 |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("N1"=42 AND "N2"=84)

Put is simply, when you have a hash partitioned table, partitioned via a composite partition key, to eliminate partitions, you need to have an equality (or an in list) predicate on the complete set of the partition keys( in any order).

I will not finish this hash partition pruning visit without extending the above cases to a composite range hash partition

drop table t1;

create table t1
(n1             number not null,
creation_date  date   not null,
comments       varchar2(500)
 )
partition by range (creation_date)
subpartition by hash(n1)
subpartitions 16
(
partition p1 values less than (to_date('01/01/2011','DD/MM/YYYY'))
,partition p2 values less than (to_date('01/01/2012','DD/MM/YYYY'))
,partition p3 values less than (to_date('01/01/2013','DD/MM/YYYY'))
,partition p4 values less than (to_date('31/12/2014','DD/MM/YYYY'))
);

insert into t1 values (1, to_date('29032010','ddmmyyyy'), '2010_Part');
insert into t1 values (42, to_date('17022011','ddmmyyyy'), '2011_Part');
insert into t1 values (17, to_date('15022012','ddmmyyyy'), '2012_Part');
insert into t1 values (25, to_date('13/02/2013','dd/mm/yyyy'), '2013_Part');

commit;

And the usual selects with their corresponding execution plans

explain plan for
select * from t1
here creation_date = to_date('29032010','ddmmyyyy')
and  n1 = 42;

-----------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Pstart| Pstop |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |   274 |       |       |
|   1 |  PARTITION RANGE SINGLE|      |     1 |   274 |     1 |     1 | --> range partition pruning
|   2 |   PARTITION HASH SINGLE|      |     1 |   274 |    14 |    14 | --> hash partition pruning
|*  3 |    TABLE ACCESS FULL   | T1   |     1 |   274 |    14 |    14 |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("CREATION_DATE"=TO_DATE(' 2010-03-29 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "N1"=42)

explain plan for
select * from t1
where creation_date = to_date('29032010','ddmmyyyy')
and  n1 >= 42;

-----------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Pstart| Pstop |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |   274 |       |       |
|   1 |  PARTITION RANGE SINGLE|      |     1 |   274 |     1 |     1 | --> range partition pruning
|   2 |   PARTITION HASH ALL   |      |     1 |   274 |     1 |    16 | --> no hash partition pruning due to n1>42
|*  3 |    TABLE ACCESS FULL   | T1   |     1 |   274 |     1 |    16 |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("CREATION_DATE"=TO_DATE(' 2010-03-29 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "N1">=42)

Bottom line : was it a simple hash partitioned table or a composite range-hash one, to have a partition pruning within the hash partitioned bit of the table you need to apply an equality (or an in list) predicate on the complete partition key (single or composite key).

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: