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 > > >