Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Why partition pruning is not happening?

Re: Why partition pruning is not happening?

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Mon, 8 Jan 2007 23:01:40 +0100
Message-ID: <022b01c73370$9b007280$3c02a8c0@JARAWIN>


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

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jan 08 2007 - 16:01:40 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US