Re: Subquery partition pruning

> Thanks for the tip, I modified the query and added TIME_EID (it contains 24
> hours time dimension) as follows but no luck even it looks like it is doing
> pruning it filters first the second column of partition key so it is still
> reading the whole table! Statistics is refreshed as well. (Global and
> partition level statistics)
>
>
> SELECT
>        TO_CHAR(A.DATE_NAME,'YYYY-MM-DD'),
>        B.MSISDN,
>        B.IMSI,
>        SUM(B.EVENT_AMOUNT)
> FROM   DW.D_DATE A,
>        DWH_OWN.T_MOBILE_EVENT B
> WHERE  A.DATE_EID  = B.DATE_EID
>   AND  A.DATE_NAME = TO_DATE('20100322', 'yyyymmdd')
>   AND  B.TIME_EID between 1 and 86400
> GROUP  BY TO_CHAR(A.DATE_NAME,'YYYY-MM-DD'),
>           B.MSISDN,
>           B.IMSI

 

If you think about how subquery pruning is supposed to work which is to run a recursive subquery on the driving row sources being joined (the D_DATE dimension in your case) it might become obvious that adding a predicate on the T_MOBILE_EVENT alone probably won't help in that matter.

 

Since the subquery pruning obviously requires all range partition keys to be specified in the join condition to work you would need to add the TIME_EID as join predicate. Now since your date dimension obviously doesn't cover a TIME, you probably can't simply add something like that:

 

...

FROM   DW.D_DATE A,
       DWH_OWN.T_MOBILE_EVENT B
WHERE  A.DATE_EID  = B.DATE_EID

  AND  A.TIME_EID = B.TIME_EID
  AND  A.TIME_EID between 1 and 86400

  AND  A.DATE_NAME = TO_DATE('20100322', 'yyyymmdd')
...

 

because there is no TIME_EID in D_DATE I suppose.

 

Since you probably have a suitable TIME dimension, you could try something like this:

 

...

FROM   DW.D_DATE A,

       DW.D_TIME T
       DWH_OWN.T_MOBILE_EVENT B
WHERE  A.DATE_EID  = B.DATE_EID

  AND  T.TIME_EID = B.TIME_EID
  AND  T.TIME_EID between 1 and 86400

  AND  A.DATE_NAME = TO_DATE('20100322', 'yyyymmdd')
...

 

But even with the underscore parameters altered as suggested by Christian ("_subquery_pruning_cost_factor" = 1 and "_subquery_pruning_reduction" = 100) I didn't get the subquery pruning to work in a simple example setup, since obviously the cost calculation underneath decided that the subquery pruning was not worth it - restricting the second table to less rows switched to subquery pruning at a certain threshold. Unfortunately I don't think that even the 10053 optimizer trace reveals the underlying cost calculations for the subquery pruning.

 

Your mileage might vary so you might want to give this setup a try - if this is a reasonable approach is another question.

 

If your example of a single row (or only few rows) being returned by the predicate on the dimension is representative for the kind of query you try to tune, then I would be more encouraged to look into why the optimizer doesn't choose the NESTED LOOP approach on its own. It looks like in your posted examples that the cost of the PARTITION RANGE ITERATOR operation is equal to the PARTITION RANGE ALL which is very likely caused by the fact that you join only on one of the two partition keys. In my simple test with a range-hash composite partitioned table the cost of the PARTITION RANGE ITERATOR operation was significantly lower when using the NESTED LOOP plan compared to the other options when joining on both columns - in fact the optimizer chose this plan automatically, but again only if the number of iterations of the loop was low, which will not be the case in your setup if you add the unrestricted TIME dimension holding 86400 rows.

 

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

Co-author of the "OakTable Expert Oracle Practices" book:
http://www.apress.com/book/view/1430226684
http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684