Re: PARTITION HASH INLIST -- QUERY

From: Ind-dba <oraclearora_at_googlemail.com>
Date: Thu, 24 Dec 2009 03:05:04 -0800 (PST)
Message-ID: <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) Received on Thu Dec 24 2009 - 05:05:04 CST

Original text of this message