Re: PARTITION HASH INLIST -- QUERY

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 24 Dec 2009 11:57:18 -0000
Message-ID: <X4WdnWZNdY-Byq7WnZ2dnUVZ8ludnZ2d_at_bt.com>


"Ind-dba" <oraclearora_at_googlemail.com> wrote in message news:6db294bb-0f4b-446c-9c75-8e2ddb86b957_at_x25g2000prf.googlegroups.com... On Dec 24, 12:41 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "Ind-dba" <oraclear..._at_googlemail.com> wrote in message
>
> news:378b724d-ca44-46f2-b3b8-37ea58c33945_at_x5g2000prf.googlegroups.com...
>
>
>
>
>
> >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.
>
> --
> Regards
>
> 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 that
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.com
Received on Thu Dec 24 2009 - 05:57:18 CST

Original text of this message