Re: Subquery partition pruning
- From: amonte <ax.mount@xxxxxxxxx>
- To: "Mark W. Farnham" <mwf@xxxxxxxx>
- Date: Thu, 1 Apr 2010 21:51:58 +0200
I make a pardon, DATE_WID is really DATE_EID, typo
2010/4/1 Mark W. Farnham <mwf@xxxxxxxx>
> Did I misread the thread, or did you leave out DATE_WID before? Are you
> surprised it had to look at more partitions when it didn’t know only value
> 812 was a candidate? Is there some reason why you thought it would know 812
> was the only candidate? I thought you previously told us the table was
> partitioned on date_eid. I believe you are now stating that the table is
> partitioned on date_wid and subpartitioned on date_eid.
>
> Do I have that right?
>
>
>
> Is DATE_WID also a column in DW.D_DATE?
>
>
>
> What type is that and what do the values mean? Ditto on DATE_EID.
>
>
>
> Finally, if you leave out the NL hint but continue to include the DATE_WID
> filter, do you get partition pruning?
>
>
>
> Regards,
>
>
>
> mwf
> ------------------------------
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *amonte
> *Sent:* Thursday, April 01, 2010 10:28 AM
> *To:* Christian Antognini
> *Cc:* oracle-l@xxxxxxxxxxxxx
> *Subject:* 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:
