Re: PARTITION HASH INLIST -- QUERY
Date: Thu, 24 Dec 2009 11:57:18 -0000
"Ind-dba" <oraclearora_at_googlemail.com> wrote in message
On Dec 24, 12:41 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk>
> "Ind-dba" <oraclear..._at_googlemail.com> wrote in message
> >I need your help in a SQL tuning exercise:
> > SQL:
> > =======
> > select /*+ gather_plan_statistics index(ag,PK_AD_Y) */ count
> > (t.EDIT_STATUS)
> > , count(ag.cr_date)
> > from CMPGN.TE_Y t
> > join CMPGN.AD_Y ag on ag.ad_grp_id=t.ad_grp_id
> > and ag.ad_grp_id in
> > (
> > ..,
> > ..,
> > 997 values
> > )
> > order by t.acct_id, ag.cmpgn_id, t.ad_grp_id, t.term_id;
> What version of Oracle
> Can we see the execution plans without the 'allstats last' option so that
> we can see the predicted cost, and the pstart/pstop information.
> For each table - how many partitions, and how which column is
> it partitioned on.
> Jonathan Lewishttp://jonathanlewis.wordpress.com
What version of Oracle
10.2.0.4 on Linux
Can we see the execution plans without the 'allstats last' option so
we can see the predicted cost, and the pstart/pstop information.
Sure: I have posted the execution plans _at_ http://pastebin.com/d28ee5395
For each table - how many partitions, and how which column is it partitioned on.
TE_Y: no of partitions: 2048 PARTITION BY HASH (ad_grp_id) AD_Y: no of partitions: 2048 PARTITION BY HASH (ad_grp_id)
It looks like it might be an accidental side-effect (i.e. bug).
The use_concat hint isn't actually working - or you'd see a CONCATENATION operator - but it seems to have stopped transitive closure from taking place. (Your in-list shows up on only one of the tables).
With transitive closure in place I think Oracle has used the list of values to access each partition in turn in its "partition selection" phase on the second table, then found that the join predicate works for only one partition. The excessive selection of partitions looks like the bug which use_concat has accidentally blocked.
So, with transitive closure you get starts = number of rows in first table * number of partitions referenced by inlist.
With transitive closue block you get starts = rows selected from first table.
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.comReceived on Thu Dec 24 2009 - 05:57:18 CST