Partition Pruning is an optimization technique that the optimizer applies to eliminate certain partitions during query execution. In this article we will discuss about various methods of Partition Pruning and certain pruning restrictions that apply to Insert As Select ( IAS) statements.
Let's get started with a test case. We create two tables T1 and T2. T1 is non partitioned.
T2 is a partitioned table. Partitioned by HASH on the partition_id column.
CREATE TABLE T1 ( event_name VARCHAR2(254) , partition_id NUMBER);
CREATE TABLE T2
(
partition_id NUMBER,
partition_name VARCHAR2 (254)
)
PARTITION BY HASH (partition_id)(PARTITION p1,
PARTITION p2,
PARTITION p3,
PARTITION p4);
Now we run a SELECT statement with different hints and examine the execution plans and look at the different types of partition pruning Oracle uses.
EXPLAIN PLAN FOR
SELECT /*+ USE_HASH(T2) */ * FROM T1,T2 where
T1.event_name like 'str%'
and T1.partition_id=T2.partition_id
Plan hash value: 2384829105
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 976K| 11158 (1)| 00:03:33 | | |
|* 1 | HASH JOIN | | 5000 | 976K| 11158 (1)| 00:03:33 | | |
| 2 | JOIN FILTER CREATE | :BF0001 | 1 | 100 | 4 (0)| 00:00:01 | | |
| 3 | PART JOIN FILTER CREATE | :BF0000 | 1 | 100 | 4 (0)| 00:00:01 | | |
|* 4 | TABLE ACCESS STORAGE FULL| T1 | 1 | 100 | 4 (0)| 00:00:01 | | |
| 5 | JOIN FILTER USE | :BF0001 | 100K| 9765K| 11154 (1)| 00:03:32 | | |
| 6 | PARTITION HASH JOIN-FILTER| | 100K| 9765K| 11154 (1)| 00:03:32 |:BF0000|:BF0000|
|* 7 | TABLE ACCESS STORAGE FULL| T2 | 100K| 9765K| 11154 (1)| 00:03:32 |:BF0000|:BF0000|
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."PARTITION_ID"="T2"."PARTITION_ID")
4 - storage("T1"."EVENT_NAME" LIKE 'str%')
filter("T1"."EVENT_NAME" LIKE 'str%')
7 - storage(SYS_OP_BLOOM_FILTER(:BF0001,"T2"."PARTITION_ID"))
filter(SYS_OP_BLOOM_FILTER(:BF0001,"T2"."PARTITION_ID"))
Here a bloom filter is created at O/P 3. And at O/P 7, Pstart and Pstop is :BF0000.
Based on this bloom filter partition pruning occurs on T2 and therefore, only the partitions containing the relevant data are scanned.This is called Bloom Pruning.
This is controlled by the hidden parameter _bloom_pruning_enabled which defaults to TRUE
Let's get started with a test case. We create two tables T1 and T2. T1 is non partitioned.
T2 is a partitioned table. Partitioned by HASH on the partition_id column.
CREATE TABLE T1 ( event_name VARCHAR2(254) , partition_id NUMBER);
CREATE TABLE T2
(
partition_id NUMBER,
partition_name VARCHAR2 (254)
)
PARTITION BY HASH (partition_id)(PARTITION p1,
PARTITION p2,
PARTITION p3,
PARTITION p4);
Now we run a SELECT statement with different hints and examine the execution plans and look at the different types of partition pruning Oracle uses.
BLOOM PRUNING
SELECT /*+ USE_HASH(T2) */ * FROM T1,T2 where
T1.event_name like 'str%'
and T1.partition_id=T2.partition_id
Plan hash value: 2384829105
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 976K| 11158 (1)| 00:03:33 | | |
|* 1 | HASH JOIN | | 5000 | 976K| 11158 (1)| 00:03:33 | | |
| 2 | JOIN FILTER CREATE | :BF0001 | 1 | 100 | 4 (0)| 00:00:01 | | |
| 3 | PART JOIN FILTER CREATE | :BF0000 | 1 | 100 | 4 (0)| 00:00:01 | | |
|* 4 | TABLE ACCESS STORAGE FULL| T1 | 1 | 100 | 4 (0)| 00:00:01 | | |
| 5 | JOIN FILTER USE | :BF0001 | 100K| 9765K| 11154 (1)| 00:03:32 | | |
| 6 | PARTITION HASH JOIN-FILTER| | 100K| 9765K| 11154 (1)| 00:03:32 |:BF0000|:BF0000|
|* 7 | TABLE ACCESS STORAGE FULL| T2 | 100K| 9765K| 11154 (1)| 00:03:32 |:BF0000|:BF0000|
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."PARTITION_ID"="T2"."PARTITION_ID")
4 - storage("T1"."EVENT_NAME" LIKE 'str%')
filter("T1"."EVENT_NAME" LIKE 'str%')
7 - storage(SYS_OP_BLOOM_FILTER(:BF0001,"T2"."PARTITION_ID"))
filter(SYS_OP_BLOOM_FILTER(:BF0001,"T2"."PARTITION_ID"))
Here a bloom filter is created at O/P 3. And at O/P 7, Pstart and Pstop is :BF0000.
Based on this bloom filter partition pruning occurs on T2 and therefore, only the partitions containing the relevant data are scanned.This is called Bloom Pruning.
This is controlled by the hidden parameter _bloom_pruning_enabled which defaults to TRUE
At O/P 2 we see JOIN FILTER CREATE, this is a join filter which also relies on bloom filters but this does not eliminate partitions. This is another sort of optimization and not to be confused with bloom pruning. You can read about the bloom filter here.
EXPLAIN PLAN FOR
SELECT /*+ USE_NL(T1) */ * FROM T1,T2 where
T1.event_name like 'str%'
and T1.partition_id=T2.partition_id
Plan hash value: 2185522821
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 976K| 2792 (1)| 00:00:54 | | |
| 1 | NESTED LOOPS | | 5000 | 976K| 2792 (1)| 00:00:54 | | |
|* 2 | TABLE ACCESS STORAGE FULL | T1 | 1 | 100 | 4 (0)| 00:00:01 | | |
| 3 | PARTITION HASH ITERATOR | | 10000 | 976K| 2788 (1)| 00:00:53 | KEY | KEY |
|* 4 | TABLE ACCESS STORAGE FULL| T2 | 10000 | 976K| 2788 (1)| 00:00:53 | KEY | KEY |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("T1"."EVENT_NAME" LIKE 'str%')
filter("T1"."EVENT_NAME" LIKE 'str%')
4 - storage("T1"."PARTITION_ID"="T2"."PARTITION_ID")
filter("T1"."PARTITION_ID"="T2"."PARTITION_ID")
With Nested Loop joins, the execution plan has completely changed. There are no bloom filters here.
All rows of T1 are scanned. And for each of these partition_id's Oracle can now prune into a specific partition on T2. Pstart and Pstop shows as KEY-KEY. If T1 returns 10 records with same partition ids - then the same partition on T2 is scanned 10 times. This kind of pruning is bad if T1 returns lot of rows.
EXPLAIN PLAN FOR
SELECT /*+ USE_HASH(T2) SUBQUERY_PRUNING(T2 PARTITION) */ * FROM T1,T2 where
T1.event_name like 'str%'
and T1.partition_id=T2.partition_id
Plan hash value: 2405306729
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 976K| 11158 (1)| 00:03:33 | | |
|* 1 | HASH JOIN | | 5000 | 976K| 11158 (1)| 00:03:33 | | |
|* 2 | TABLE ACCESS STORAGE FULL | T1 | 1 | 100 | 4 (0)| 00:00:01 | | |
| 3 | PARTITION HASH SUBQUERY | | 100K| 9765K| 11154 (1)| 00:03:32 |KEY(SQ)|KEY(SQ)|
| 4 | TABLE ACCESS STORAGE FULL| T2 | 100K| 9765K| 11154 (1)| 00:03:32 |KEY(SQ)|KEY(SQ)|
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."PARTITION_ID"="T2"."PARTITION_ID")
2 - storage("T1"."EVENT_NAME" LIKE 'str%')
filter("T1"."EVENT_NAME" LIKE 'str%')
And BLOOM ....!
KEY KEY PRUNING
SELECT /*+ USE_NL(T1) */ * FROM T1,T2 where
T1.event_name like 'str%'
and T1.partition_id=T2.partition_id
Plan hash value: 2185522821
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 976K| 2792 (1)| 00:00:54 | | |
| 1 | NESTED LOOPS | | 5000 | 976K| 2792 (1)| 00:00:54 | | |
|* 2 | TABLE ACCESS STORAGE FULL | T1 | 1 | 100 | 4 (0)| 00:00:01 | | |
| 3 | PARTITION HASH ITERATOR | | 10000 | 976K| 2788 (1)| 00:00:53 | KEY | KEY |
|* 4 | TABLE ACCESS STORAGE FULL| T2 | 10000 | 976K| 2788 (1)| 00:00:53 | KEY | KEY |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("T1"."EVENT_NAME" LIKE 'str%')
filter("T1"."EVENT_NAME" LIKE 'str%')
4 - storage("T1"."PARTITION_ID"="T2"."PARTITION_ID")
filter("T1"."PARTITION_ID"="T2"."PARTITION_ID")
With Nested Loop joins, the execution plan has completely changed. There are no bloom filters here.
All rows of T1 are scanned. And for each of these partition_id's Oracle can now prune into a specific partition on T2. Pstart and Pstop shows as KEY-KEY. If T1 returns 10 records with same partition ids - then the same partition on T2 is scanned 10 times. This kind of pruning is bad if T1 returns lot of rows.
SUBQUERY PRUNING
ALTER SESSION SET "_bloom_pruning_enabled"=FALSE
ALTER SESSION SET "_bloom_filter_enabled"=FALSEEXPLAIN PLAN FOR
SELECT /*+ USE_HASH(T2) SUBQUERY_PRUNING(T2 PARTITION) */ * FROM T1,T2 where
T1.event_name like 'str%'
and T1.partition_id=T2.partition_id
Plan hash value: 2405306729
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 976K| 11158 (1)| 00:03:33 | | |
|* 1 | HASH JOIN | | 5000 | 976K| 11158 (1)| 00:03:33 | | |
|* 2 | TABLE ACCESS STORAGE FULL | T1 | 1 | 100 | 4 (0)| 00:00:01 | | |
| 3 | PARTITION HASH SUBQUERY | | 100K| 9765K| 11154 (1)| 00:03:32 |KEY(SQ)|KEY(SQ)|
| 4 | TABLE ACCESS STORAGE FULL| T2 | 100K| 9765K| 11154 (1)| 00:03:32 |KEY(SQ)|KEY(SQ)|
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."PARTITION_ID"="T2"."PARTITION_ID")
2 - storage("T1"."EVENT_NAME" LIKE 'str%')
filter("T1"."EVENT_NAME" LIKE 'str%')
We have disabled bloom pruning here. With Hash joins, Oracle now goes for Subquery Pruning. At O/P 3 you will see Partition Hash Subquery. T1 is fully scanned. Now Oracle runs a recursive query on TBL$OR$IDX$PART$NUM to discover what partitions it will need from T2. Based on the result of that recursive query, T2 is pruned. Pstart and Pstop is KEY(SQ) KEY( SQ)
This is controlled by the parameter _subquery_pruning_enabled.
INSERT AS SELECT and BLOOM PRUNING
EXPLAIN PLAN FOR
INSERT /*+ APPEND */ INTO T3
SELECT /*+ USE_HASH(T2) */ * FROM T1,T2 where
T1.event_name like 'str%'
and T1.partition_id=T2.partition_id
Plan hash value: 1934473477
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 5000 | 976K| 11158 (1)| 00:03:33 | | |
| 1 | LOAD AS SELECT | T3 | | | | | | |
|* 2 | HASH JOIN | | 5000 | 976K| 11158 (1)| 00:03:33 | | |
|* 3 | TABLE ACCESS STORAGE FULL | T1 | 1 | 100 | 4 (0)| 00:00:01 | | |
| 4 | PARTITION HASH ALL | | 100K| 9765K| 11154 (1)| 00:03:32 | 1 | 4 |
| 5 | TABLE ACCESS STORAGE FULL| T2 | 100K| 9765K| 11154 (1)| 00:03:32 | 1 | 4 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."PARTITION_ID"="T2"."PARTITION_ID")
3 - storage("T1"."EVENT_NAME" LIKE 'str%')
filter("T1"."EVENT_NAME" LIKE 'str%')
In 11.2.0.4 Oracle does not consider bloom pruning for Insert As Select statements.
Looking at the plan Pstart -> Pstop is 1 and 4. All partitions of T2 are being scanned. This could cause major performance issues when you have tens of thousands of partitions in T2.
This restriction has been lifted on 18.1. One workaround would be to use CTAS instead if IAS.
CTAS allows bloom pruning.
You can now request backport for fix 16609749 on 11.2.0.4. This enables bloom pruning for direct path insert as selects. Note that this fix is not enabled by default. You need to explicitly enable this
ALTER SESSION SET "_fix_control" = '16609749:ON';
EXPLAIN PLAN FOR
INSERT /*+ APPEND */ INTO T3
SELECT /*+ USE_HASH(T2) */ * FROM T1,T2 where
T1.event_name like 'str%'
and T1.partition_id=T2.partition_id
Plan hash value: 3019913043
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 5000 | 976K| 12379 (1)| 00:03:31 | | |
| 1 | LOAD AS SELECT | T3 | | | | | | |
|* 2 | HASH JOIN | | 5000 | 976K| 12379 (1)| 00:03:31 | | |
| 3 | JOIN FILTER CREATE | :BF0001 | 1 | 100 | 4 (0)| 00:00:01 | | |
| 4 | PART JOIN FILTER CREATE | :BF0000 | 1 | 100 | 4 (0)| 00:00:01 | | |
|* 5 | TABLE ACCESS STORAGE FULL| T1 | 1 | 100 | 4 (0)| 00:00:01 | | |
| 6 | JOIN FILTER USE | :BF0001 | 100K| 9765K| 12375 (1)| 00:03:31 | | |
| 7 | PARTITION HASH JOIN-FILTER| | 100K| 9765K| 12375 (1)| 00:03:31 |:BF0000|:BF0000|
|* 8 | TABLE ACCESS STORAGE FULL| T2 | 100K| 9765K| 12375 (1)| 00:03:31 |:BF0000|:BF0000|
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."PARTITION_ID"="T2"."PARTITION_ID")
5 - storage("T1"."EVENT_NAME" LIKE 'str%')
filter("T1"."EVENT_NAME" LIKE 'str%')
8 - storage(SYS_OP_BLOOM_FILTER(:BF0001,"T2"."PARTITION_ID"))
filter(SYS_OP_BLOOM_FILTER(:BF0001,"T2"."PARTITION_ID"))
No comments:
Post a Comment