Re: PARTITION HASH INLIST -- QUERY

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 24 Dec 2009 12:01:29 -0000
Message-ID: <YKCdnTnRYeGKxa7WnZ2dnUVZ8iKdnZ2d_at_bt.com>



"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news: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)
>
>
>

The clue, by the way is in the KEY-KEY pstart/pstop compared to the KEY(I)/KEY(I), which confirms the oddity with the change between partition hash iterator and partition hash inlist

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com
Received on Thu Dec 24 2009 - 06:01:29 CST

Original text of this message