Re: PARTITION HASH INLIST -- QUERY

From: ddf <oratune_at_msn.com>
Date: Thu, 24 Dec 2009 12:41:11 -0800 (PST)
Message-ID: <32d6b736-62b5-4040-beea-e756e37dd440_at_m7g2000prd.googlegroups.com>

On Dec 24, 2:38 pm, Ind-dba <oraclear..._at_googlemail.com> wrote:
> On Dec 24, 5:01 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk>
> wrote:
>
>
>
>
>
> > "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote in message
>
> >news:X4WdnWZNdY-Byq7WnZ2dnUVZ8ludnZ2d_at_bt.com...
>
> > > "Ind-dba" <oraclear..._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 Lewishttp://jonathanlewis.wordpress.com
>
> Thanks a bunch Jonathan for the deep-dive analysis!
>
> Could you please shed some more light (or probably redirect) on term:
> "Transitive closure"
>
> Another point that you mentioned that "The excessive selection of
> partitions looks
> like the bug which use_concat has accidentally blocked." -- Have you
> come across such bug? Or you recommend me filing a new one with Oracle
> Support.
>
> Another point .. how do we confirm that the relevant data in TE_Y
> table for this SQL is in 791 partitions. Is there a SQL that can help
> me do so.
>
> Thanks much for your inputs so far .. appreciate your help.
>
> Regards,
> Sachin- Hide quoted text -
>
> - Show quoted text -

Jonathan covers your first question in his book 'Cost-Based Oracle Fundamentals'; basically if you write a query like this:

select count(*)
from t1, t2
where t1.x = 5
and t2.x = t1.x;

Oracle can produce a query plan that looks like this:

   0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=303 Card=1 Bytes=6)

   1 0 SORT (AGGREGATE)

   2    1     MERGE JOIN (CARTESIAN) (Cost=303 Card=10000 Bytes=60000)
   3    2       TABLE ACCESS (FULL) OF 'T1' (Cost=3 Card=100
Bytes=300)
   4    2       SORT (JOIN) (Cost=300 Card=100 Bytes=300)
   5    4         TABLE ACCESS (FULL) OF 'T2' (Cost=3 Card=100

Bytes=300)

The merge join cartesian appears and in the predicate information you see:

3 - filter("T1.X" = 5)
5 - filter("T2.X" = 5)

but no predicate joining the two tables. Oracle has deduced that since t1.x = 5 and t2.x = t1.x that (rightly so) t2.x = 5 and has rewritten the query to reflect that revelation (which eliminates the join condition). The act of making the leap from this:

where t1.x = 5 and t2.x = t1.x

to

where t1.x = 5 and t2.x = 5

is called transitive closure. Jonathan discusses the sometimes 'interesting' side effects of this behaviour in this same book. His comment about the in-lists proves that transitive closure does not happen because, if it did, you'd see two in-lists, not one, in the predicate section.

Jonathan will have to respond to the rest of your questions and I await his responses.

David Fitzjarrell Received on Thu Dec 24 2009 - 14:41:11 CST