Re: Subquery partition pruning
Date: Sat, 3 Apr 2010 09:11:02 +0100
Message-ID: <D818C30C79DD4000AC2077F3F9480F80_at_Primary>
>
> Date: Fri, 2 Apr 2010 10:24:35 +0200
> Subject: Re: Subquery partition pruning
> From: amonte <ax.mount_at_gmail.com>
>
> Hello Greg
>
> 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)
>
>
> AND B.TIME_EID between 1 and 86400
>
> 6 - filter(("B"."TIME_WID">=1 AND "B"."TIME_WID"<=86400)) --> *no good,
> should be filter(("B"."TIME_WID">=1 AND "B"."TIME_WID"<=86400 AND
> "A"."DATE_WID"="B"."DATE_WID"))*
>
>
> If I add NL hint as previous tests it runs fast in 25 minutes, expected
> time.
>
You need to know where the time difference went - so run the query with the hint /*+ gather_plan_statistics */ to see if the amount of work and the number of "starts" of each line vary significantly between the hash join and the nested loop.
I believe your interpretation of the line 6 filter is incorrect. Oracle doesn't
need the "date_wid" comparison at that point because it has already used
it in the "partition range multicolumn (key/key) in line 4". It's possible that
the excess work appears because every row is subject to this filter. YOU
know it's not necessary but ORACLE doesn't. How have you defined your
24 hourly partitions ? Are they defined in such a way that the values
HAVE to be in the range 1..86400; e.g. do you have a constraint that says
"time_eid is not null and time_eid between 1 and 86400" ? If you did, then
I think this filter would disappear and the CPU usage might drop quite
significantly.
.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Apr 03 2010 - 03:11:02 CDT