Re: Subquery partition pruning

Hello

I tested with

alter session set "_subquery_pruning_enabled" = TRUE;
alter session set "_subquery_pruning_cost_factor" = 1;
alter session set "_subquery_pruning_reduction" = 100;

still got partiton range all, 751 where each has around 9 million of rows.

I did a test changing the query to

SELECT /*+ USE_NL (A B) */
       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.DATE_WID = 812
----------------------------------------------------> leading column of
partition key
GROUP  BY TO_CHAR(A.DATE_NAME,'YYYY-MM-DD'),
          B.MSISDN,
          B.IMSI

T_MOBILE_EVENT is partitioned (and subpartitioned) in two columns where
leading column is DATE_WID (so pruning is still possible).

So it has now part of the partition key (DATE_WID), pruning happens now and
it reads 25 partitions.

The CBO is choosing reading 751 partitions against 25, really bizarre. The
statistics are up to date (only up to partition level, no global stats may
be that is a problem?)

Doesnt makes sense because A.DATE_NAME = TO_DATE('20100322', 'yyyymmdd')
returns one row which maps to 25 partitions of 751


I will try a 10053 and see if I can find anything useful

Thanks



2010/4/1 Christian Antognini <Christian.Antognini@xxxxxxxxxxxx>

> Hi
>
> > I dont understand why the pruning is not happening with
> > TBL$OR$IDX$PART$NUM() function call (subquery pruning) when hash join
> is
> > involved.
>
> Most of the time it is because of the estimations of the query
> optimizer. When I want to demonstrate that feature, I usually set the
> environment in the following way:
>
> ALTER SESSION SET "_subquery_pruning_enabled"=TRUE;
> ALTER SESSION SET "_subquery_pruning_cost_factor"=1;
> ALTER SESSION SET "_subquery_pruning_reduction"=100;
>
> So, what I suggest you is to give it a try by setting this parameters.
> If it works, the "problem" is due to the estimations. If it is the case,
> you have to judge if the estimations are good or not. It might be a good
> thing that subquery pruning is not used... Remember, part of the query
> is executed twice and, therefore, the overhead cannot be underestimated.
>
> HTH
> Chris
>
> Troubleshooting Oracle Performance (Apress, 2008)
> http://antognini.ch/top
>
>
>

Other related posts: