Re: Why partition pruning is not happening?

  • From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
  • To: <nigel_cl_thomas@xxxxxxxxx>, <exriscer@xxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>, <Bernard.Polarski@xxxxxxxxxxxxxx>, <AbhKulkarni@xxxxxxxx>
  • Date: Mon, 8 Jan 2007 23:01:40 +0100

Hi,

> Try replacing the join to PRM_FEDIA with a scalar subquery - then the 
> optimiser should be able to tell that only one value for FE_DIA can be 
> referenced.

If you are satisfied with the performance of NL you may additionally consider 
to change the order of the access path. While looping first from the A12 table 
(containing the partition key as foreign key) to the partitioned table (A11 in 
my example) you get the KEY-KEY pruning (the inner rowset of the nested loop 
access only the partition defined by the current row of the outer table).

The execution plan looks something like this

---------------------------------------------------------------------
| Id  | Operation                         | Name    | Pstart| Pstop |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |       |       |
|   1 |  TABLE ACCESS BY LOCAL INDEX ROWID| A11     |       |       |
|   2 |   NESTED LOOPS                    |         |       |       |
|*  3 |    TABLE ACCESS FULL              | A12     |       |       |
|   4 |    PARTITION RANGE ITERATOR       |         |   KEY |   KEY |
|*  5 |     INDEX RANGE SCAN              | A11_IX1 |   KEY |   KEY |
---------------------------------------------------------------------

 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter("A12"."ATT"=1)
   5 - access("A11"."A12_FK"="A12"."ID")

On the plus side is that this works even if the filter on A12 returns more than 
one row.
Two thing should be considered carefully:
As the pruning is performed on runtime (KEY-KEY) the optimizer uses the table 
statistics (not the partition level).
If the join method is changed to hash join the no pruning will be performed.


Regards

Jaromir
  ----- Original Message ----- 
  From: Nigel Thomas 
  To: exriscer@xxxxxxxxx ; oracle-l 
  Sent: Friday, January 05, 2007 1:51 PM
  Subject: Re: Why partition pruning is not happening?

Other related posts: