Partition Pruning and Joins
A number of examples are presented to illustrate these scenarios.
Partition Pruning and Joins
---------------------------
Note that the operation is PARTITION RANGE ITERATOR and the Pstart and Pstop columns show KEY. This indicates that the elimination of partitions will be determined at runtime dependant on the values returned from the dept table.
3. Elimination is not occurring due to the join order
=====================================================
If the partition is accessed prior to the tables that must be read to provide data values required to allow elimination to take place then elimination will not occur.
DETAILS
Things to think about:
----------------------
----------------------
- If elimination does not occur automatically, then can elimination be forced with hints? If so then this means that the optimizer is choosing a less costly plan that just happens not to require the elimination of partitions.
- Is the partition key value in place when the partition is accessed? In other words is the join order correct? If the partition key value is dependant on a join to another table, then that table MUST be accessed PRIOR to the partitioned table in order for partition elimination to be able to take place.
- Is elimination possible with the join type that is being used? Only nested loop joins (and certain types of hash joins) allow predicates from the outer table to seed a lookup on the inner table. If the query in question uses a normal hash join or a sort merge join then join predicates cannot be used for partition elimination.
- If bind variables are used in the query then at parse time it may only be possible to tell that elimination can occur as opposed to which partitions can be eliminated. This DOES NOT mean that elimination cannot occur with bind variables.
A number of examples are presented to illustrate these scenarios.
Partition Pruning and Joins
---------------------------
At parse time the cost based optimizer determines (among others) 2 key things about partitions:
Can partitions be eliminated at runtime? If so, exactly which partitions these are.
Often it may only be able to determine that elimination is possible. When dealing with partition tables, there is often confusion as to whether partition elimination has occurred or not. This is because it is not particularly easy, to the uninitiated, to tell if elimination has actually occurred or not.
The key to determining if rows can and have been eliminated is as follows:
PLAN TABLE
==========
Can partitions be eliminated at runtime? If so, exactly which partitions these are.
Often it may only be able to determine that elimination is possible. When dealing with partition tables, there is often confusion as to whether partition elimination has occurred or not. This is because it is not particularly easy, to the uninitiated, to tell if elimination has actually occurred or not.
The key to determining if rows can and have been eliminated is as follows:
PLAN TABLE
==========
Remember that the plan table CAN ONLY tell you what is PREDICTED to happen at run time. Partitions that will be accessed are recorded in the PARTITION_START and PARTITION_STOP columns of the plan table. These columns have 4 potential values:
a number - the partition number for the start or stop partition
KEY/ROW LOCATION - partition elimination is possible but the partition number
will be identified at run time
INVALID - partition elimination
is possible but the range of partitions is invalid
Refer to Table 9-1 in 'Oracle9i Database Performance Guide and Reference' for further details regarding plan table columns. It is suggested that utlxpls.sql is used to view partition details in the plan table. This script is found in $ORACLE_HOME/rdbms/admin.
=============
This event shows which partitions are actually accessed at runtime.
See Note:166118.1 for a simple example of this.
level 8
=====================
This is probably the most long winded and complex way of finding if partitions have been accessed or not. It involves recording exactly which files and blocks are spanned by each individual partitions and then looking at the actual blocks read to determine is any of these are outside the expected partitions. This can be time consuming but can be speeded up by sensible use of sorting and unix tools such as grep and awk (or equivalents) to strip out exactly the data that is required. This should be regarded as a last resort.
Grep example to gather all the normal file io waits:
grep 'WAIT' TRACE.TRC | grep 'db file s' > out.txt
awk and sort example to pick out the file and block numbers and perform a numeric sort:
awk '{print $9" "$10}' out.txt |sort -n > final_out.txt
Common Scenarios:
=================
a number - the partition number for the start or stop partition
KEY/ROW LOCATION - partition elimination is possible but the partition number
will be identified at run time
INVALID - partition elimination
is possible but the range of partitions is invalid
Refer to Table 9-1 in 'Oracle9i Database Performance Guide and Reference' for further details regarding plan table columns. It is suggested that utlxpls.sql is used to view partition details in the plan table. This script is found in $ORACLE_HOME/rdbms/admin.
=============
This event shows which partitions are actually accessed at runtime.
See Note:166118.1 for a simple example of this.
=====================
This is probably the most long winded and complex way of finding if partitions have been accessed or not. It involves recording exactly which files and blocks are spanned by each individual partitions and then looking at the actual blocks read to determine is any of these are outside the expected partitions. This can be time consuming but can be speeded up by sensible use of sorting and unix tools such as grep and awk (or equivalents) to strip out exactly the data that is required. This should be regarded as a last resort.
Grep example to gather all the normal file io waits:
grep 'WAIT' TRACE.TRC | grep 'db file s' > out.txt
awk and sort example to pick out the file and block numbers and perform a numeric sort:
awk '{print $9" "$10}' out.txt |sort -n > final_out.txt
Common Scenarios:
=================
The Examples below use 2 tables: dept and range_part See Note:166118.1.
For reference, range_part is created as follows:
For reference, range_part is created as follows:
create table range_part (col1 number(9))
partition by range (col1)
(partition p1 values less than (10) tablespace system,
partition p2 values less than (20) tablespace system,
partition p3 values less than(30) tablespace users,
partition p4 values less than (MAXVALUE) tablespace users);
insert into range_part values (1);
insert into range_part values (11);
insert into range_part values (21);
insert into range_part values (31);
commit;
partition by range (col1)
(partition p1 values less than (10) tablespace system,
partition p2 values less than (20) tablespace system,
partition p3 values less than(30) tablespace users,
partition p4 values less than (MAXVALUE) tablespace users);
insert into range_part values (1);
insert into range_part values (11);
insert into range_part values (21);
insert into range_part values (31);
commit;
1. Eliminated partitions can be determined at parse time
========================================================
In this case the optimizer can tell, at parse time, from information in the query combined with dictionary information, exactly which partitions need to be accessed.
The PARTITION_START and PARTITION_STOP columns in the plan table will contain the partition numbers for the start and stop partitions in the range of partitions to be accessed.
Example: The following example selects where col1 is equal to 15. this should only access partition 2.
========================================================
In this case the optimizer can tell, at parse time, from information in the query combined with dictionary information, exactly which partitions need to be accessed.
The PARTITION_START and PARTITION_STOP columns in the plan table will contain the partition numbers for the start and stop partitions in the range of partitions to be accessed.
Example: The following example selects where col1 is equal to 15. this should only access partition 2.
explain plan for select * from range_part where col1=15;
start $ORACLE_HOME/rdbms/admin/utlxpls.sql
Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes | Cost | Pstart | Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 1 | 13 | 1 | | |
| TABLE ACCESS FULL | RANGE_PAR | 1 | 13 | 1 | 2 | 2 |
--------------------------------------------------------------------------------
start $ORACLE_HOME/rdbms/admin/utlxpls.sql
Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes | Cost | Pstart | Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 1 | 13 | 1 | | |
| TABLE ACCESS FULL | RANGE_PAR | 1 | 13 | 1 | 2 | 2 |
--------------------------------------------------------------------------------
The Pstart and Pstop columns clearly show that the optimizer believes that only partition 2 is required to satisfy this query.
There are more examples of this in Note:166118.1
2. Eliminated partitions can only be determined at run time
===========================================================
In this example the actual partition numbers cannot be determined, only that there is a possibility that partitions can be eliminated at runtime. The PARTITION_START and PARTITION_STOP columns in the plan table will contain either KEY or ROW LOCATION depending on the nature of the query and the partition itself.
There are more examples of this in Note:166118.1
2. Eliminated partitions can only be determined at run time
===========================================================
In this example the actual partition numbers cannot be determined, only that there is a possibility that partitions can be eliminated at runtime. The PARTITION_START and PARTITION_STOP columns in the plan table will contain either KEY or ROW LOCATION depending on the nature of the query and the partition itself.
explain plan for
select /*+ ordered use_nl(p) */ col1
from dept d, range_part p
where d.deptno = p.col1
and d.loc = 'DALLAS';
start /oracle2/app/oracle/product/8.1.7/rdbms/admin/utlxpls.sql
Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes | Cost | Pstart | Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 3 | 102 | 2 | | |
| NESTED LOOPS | | 3 | 102 | 2 | | |
| TABLE ACCESS FULL | DEPT | 1 | 21 | 1 | | |
| PARTITION RANGE ITERATOR| | | | | KEY | KEY |
| TABLE ACCESS FULL | RANGE_PAR| 328 | 4K | 1 | KEY | KEY |
--------------------------------------------------------------------------------
select /*+ ordered use_nl(p) */ col1
from dept d, range_part p
where d.deptno = p.col1
and d.loc = 'DALLAS';
start /oracle2/app/oracle/product/8.1.7/rdbms/admin/utlxpls.sql
Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes | Cost | Pstart | Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 3 | 102 | 2 | | |
| NESTED LOOPS | | 3 | 102 | 2 | | |
| TABLE ACCESS FULL | DEPT | 1 | 21 | 1 | | |
| PARTITION RANGE ITERATOR| | | | | KEY | KEY |
| TABLE ACCESS FULL | RANGE_PAR| 328 | 4K | 1 | KEY | KEY |
--------------------------------------------------------------------------------
Note that the operation is PARTITION RANGE ITERATOR and the Pstart and Pstop columns show KEY. This indicates that the elimination of partitions will be determined at runtime dependant on the values returned from the dept table.
3. Elimination is not occurring due to the join order
=====================================================
If the partition is accessed prior to the tables that must be read to provide data values required to allow elimination to take place then elimination will not occur.
explain plan for
select /*+ ordered use_nl(d) */ col1
from range_part p, dept d
where d.deptno = p.col1
and d.loc = 'DALLAS';
start /oracle2/app/oracle/product/8.1.7/rdbms/admin/utlxpls.sql
Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes | Cost | Pstart | Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 3 | 102 | 329 | | |
| NESTED LOOPS | | 3 | 102 | 329 | | |
| PARTITION RANGE ALL | | | | | 1 | 4 |
| TABLE ACCESS FULL | RANGE_PAR| 328 | 4K | 1 | 1 | 4 |
| TABLE ACCESS FULL | DEPT | 1 | 21 | 1 | | |
--------------------------------------------------------------------------------
select /*+ ordered use_nl(d) */ col1
from range_part p, dept d
where d.deptno = p.col1
and d.loc = 'DALLAS';
start /oracle2/app/oracle/product/8.1.7/rdbms/admin/utlxpls.sql
Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes | Cost | Pstart | Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 3 | 102 | 329 | | |
| NESTED LOOPS | | 3 | 102 | 329 | | |
| PARTITION RANGE ALL | | | | | 1 | 4 |
| TABLE ACCESS FULL | RANGE_PAR| 328 | 4K | 1 | 1 | 4 |
| TABLE ACCESS FULL | DEPT | 1 | 21 | 1 | | |
--------------------------------------------------------------------------------
Note that the operation is PARTITION RANGE ALL and the Pstart and Pstop columns show the first and last partitions in the table. This indicates that all partitions will be accessed. Runtime elimination CANNOT occur.
4. Elimination is not occurring due to the join method
======================================================
Before 8.1.6
For elimination to occur on a join based predicate, the join method must allow values from the non-partitioned side of the join to be passed to the partition side. Only Nested loops and certain types of hash joins where a subquery is dynamically built can allow elimination to occur.
In this example a Hash Join is forced using a hint. Because Hash Joins do not normally allow join key values to drive lookups on the inner table, all partitions must be accessed.
4. Elimination is not occurring due to the join method
======================================================
Before 8.1.6
For elimination to occur on a join based predicate, the join method must allow values from the non-partitioned side of the join to be passed to the partition side. Only Nested loops and certain types of hash joins where a subquery is dynamically built can allow elimination to occur.
In this example a Hash Join is forced using a hint. Because Hash Joins do not normally allow join key values to drive lookups on the inner table, all partitions must be accessed.
explain plan for
select /*+ ordered use_hash(p) */ col1
from dept d, range_part p
where d.deptno = p.col1
and d.loc = 'DALLAS';
start /oracle2/app/oracle/product/8.1.7/rdbms/admin/utlxpls.sql
Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes | Cost | Pstart | Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 3 | 102 | 3 | | |
| HASH JOIN | | 3 | 102 | 3 | | |
| TABLE ACCESS FULL | DEPT | 1 | 21 | 1 | | |
| PARTITION RANGE ALL | | | | | 1 | 4 |
| TABLE ACCESS FULL | RANGE_PAR| 328 | 4K | 1 | 1 | 4 |
--------------------------------------------------------------------------------
select /*+ ordered use_hash(p) */ col1
from dept d, range_part p
where d.deptno = p.col1
and d.loc = 'DALLAS';
start /oracle2/app/oracle/product/8.1.7/rdbms/admin/utlxpls.sql
Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes | Cost | Pstart | Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 3 | 102 | 3 | | |
| HASH JOIN | | 3 | 102 | 3 | | |
| TABLE ACCESS FULL | DEPT | 1 | 21 | 1 | | |
| PARTITION RANGE ALL | | | | | 1 | 4 |
| TABLE ACCESS FULL | RANGE_PAR| 328 | 4K | 1 | 1 | 4 |
--------------------------------------------------------------------------------
Note that the operation is PARTITION RANGE ALL and the Pstart and Pstop columns show the first and last partitions in the table. This indicates that all partitions will be accessed. In this case runtime elimination CANNOT occur.
8.1.6 and above
In 8.1.6 the code was enhanced to allow elimination to occur with a hash join. It was recognized that the non-partitioned table contains the information to eliminate partitions. If this information is compared with the dictionary information regarding partition ranges, then a list of partitions to be accessed can be generated. This activity only occurs if certain cost and selectivity criteria are met. The elimination IS NOT done in the join step itself, rather it is performed as recursive SQL independently of the join itself and therefore elimination is not join order dependant. In this case the following plan can be seen:
Note that this plan was forced using 2 parameters to override the cost and selectivity criteria mentioned above. The relationship between these parameters and the costing algorithms is complex and is beyond the scope of this article:
8.1.6 and above
In 8.1.6 the code was enhanced to allow elimination to occur with a hash join. It was recognized that the non-partitioned table contains the information to eliminate partitions. If this information is compared with the dictionary information regarding partition ranges, then a list of partitions to be accessed can be generated. This activity only occurs if certain cost and selectivity criteria are met. The elimination IS NOT done in the join step itself, rather it is performed as recursive SQL independently of the join itself and therefore elimination is not join order dependant. In this case the following plan can be seen:
Note that this plan was forced using 2 parameters to override the cost and selectivity criteria mentioned above. The relationship between these parameters and the costing algorithms is complex and is beyond the scope of this article:
alter session set "_subquery_pruning_cost_factor"=1; -- overrides cost restrictions
alter session set "_subquery_pruning_reduction"=100; -- overrides selectivity restrictions
explain plan for
select /*+ ordered use_hash(p) */ col1
from dept d, range_part p
where d.deptno = p.col1
and d.loc = 'DALLAS';
start /oracle2/app/oracle/product/8.1.7/rdbms/admin/utlxpls.sql
Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes | Cost | Pstart | Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 3 | 102 | 3 | | |
| HASH JOIN | | 3 | 102 | 3 | | |
| TABLE ACCESS FULL | DEPT | 1 | 21 | 1 | | |
| PARTITION RANGE ITERATOR| | | | | KEY | KEY |
| TABLE ACCESS FULL | RANGE_PAR | 328 | 4K | 1 | KEY | KEY |
--------------------------------------------------------------------------------
alter session set "_subquery_pruning_reduction"=100; -- overrides selectivity restrictions
explain plan for
select /*+ ordered use_hash(p) */ col1
from dept d, range_part p
where d.deptno = p.col1
and d.loc = 'DALLAS';
start /oracle2/app/oracle/product/8.1.7/rdbms/admin/utlxpls.sql
Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes | Cost | Pstart | Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 3 | 102 | 3 | | |
| HASH JOIN | | 3 | 102 | 3 | | |
| TABLE ACCESS FULL | DEPT | 1 | 21 | 1 | | |
| PARTITION RANGE ITERATOR| | | | | KEY | KEY |
| TABLE ACCESS FULL | RANGE_PAR | 328 | 4K | 1 | KEY | KEY |
--------------------------------------------------------------------------------
Note that the operation is PARTITION RANGE ITERATOR and the Pstart and Pstop columns show KEY. This indicates the elimination of partitions will be determined at runtime dependant on the values returned from a recursive lookup of the dept table during the PARTITION RANGE ITERATOR step.
For more information on Dimension table based partition pruning see Note:209070.1
5. Elimination is potentially possible with joins and bind variables
====================================================================
The fact that there is a bind variable on the query DOES NOT prevent the optimizer from spotting the potential for elimination at runtime.
For more information on Dimension table based partition pruning see Note:209070.1
5. Elimination is potentially possible with joins and bind variables
====================================================================
The fact that there is a bind variable on the query DOES NOT prevent the optimizer from spotting the potential for elimination at runtime.
variable b1 varchar2(10);
explain plan for
select /*+ ordered use_nl(p) */ col1
from dept d, range_part p
where d.deptno = p.col1
and d.loc =:b1;
start /oracle2/app/oracle/product/8.1.7/rdbms/admin/utlxpls.sql
Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes | Cost | Pstart | Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 3 | 102 | 2 | | |
| NESTED LOOPS | | 3 | 102 | 2 | | |
| TABLE ACCESS FULL | DEPT | 1 | 21 | 1 | | |
| PARTITION RANGE ITERATOR| | | | | KEY | KEY |
| TABLE ACCESS FULL | RANGE_PAR| 328 | 4K | 1 | KEY | KEY |
--------------------------------------------------------------------------------
explain plan for
select /*+ ordered use_nl(p) */ col1
from dept d, range_part p
where d.deptno = p.col1
and d.loc =:b1;
start /oracle2/app/oracle/product/8.1.7/rdbms/admin/utlxpls.sql
Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes | Cost | Pstart | Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 3 | 102 | 2 | | |
| NESTED LOOPS | | 3 | 102 | 2 | | |
| TABLE ACCESS FULL | DEPT | 1 | 21 | 1 | | |
| PARTITION RANGE ITERATOR| | | | | KEY | KEY |
| TABLE ACCESS FULL | RANGE_PAR| 328 | 4K | 1 | KEY | KEY |
--------------------------------------------------------------------------------
Note that the operation is PARTITION RANGE ITERATOR and the Pstart and Pstop columns show KEY. This indicates the elimination of partitions will be determined at runtime dependant on the values returned from the dept table.
Comments
Post a Comment
Oracle DBA Information