RE: Subquery partition pruning
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <ax.mount@xxxxxxxxx>, "'Christian Antognini'" <Christian.Antognini@xxxxxxxxxxxx>
- Date: Thu, 1 Apr 2010 15:21:36 -0400
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:
