Re: PARTITION HASH INLIST -- QUERY
From: Ind-dba <oraclearora_at_googlemail.com>
Date: Fri, 25 Dec 2009 10:59:46 -0800 (PST)
Message-ID: <294c5b5f-a27a-4649-91b0-46ba83f45630_at_d9g2000prh.googlegroups.com>
On Dec 25, 4:57 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "Ind-dba" <oraclear..._at_googlemail.com> wrote in message
>
> news:c18c2018-d228-40f4-a7ba-9e332387f390_at_15g2000prz.googlegroups.com...> Hi Jonathan,
>
> .> I just read your article on Transitive closure - so no need to explain
> .> that.
>
>
>
> >> Yes - if you could shed more light on why this bug occur in first
> >> place. Could this be due to STATS on table?
>
> >> Or due to constraints on the 2 tables in picture?
> > I just happen to see a way at metalink to disable transitive closures
> > by setting event 10195 level 1(I tried level 3, 10 also - though they
> > were not documented anywhere).
> > Still I got the same plan (bad one) without USE_CONCAT HINT.
> > I have set the event at session level by :
> > ALTER SESSION SET EVENTS '10195 trace name context forever, level 1';
>
> > Can we have any other way to stop optimizer make unwanred transitive
> > closures?
>
> I am assuming that it is a bug because the behaviour is clearly
> unreasonable. Really I shouldn't call it a bug until it's been
> acknowledged as such by Oracle. Raise an SR
>
> In another post you asked how to count the number of partitions
> identified by the IN-list. There's a clue in another of my blog posts
>
> http://jonathanlewis.wordpress.com/2009/11/25/counting/
>
> select count(distinct dbms_mview.pmarker(rowid)
> from {your_table}
> where {youe_column} in ({your list of values})
> ;
>
> Event 10195 is about generating predicates from constraints
> using transitive closure (at least in the 10.2.0.1 oraus.msg,
> so ity won't help.
>
> I think if you could block transitive closure by changing the
> join predicate to
> table2.colx = table1.coly + 0
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com
Date: Fri, 25 Dec 2009 10:59:46 -0800 (PST)
Message-ID: <294c5b5f-a27a-4649-91b0-46ba83f45630_at_d9g2000prh.googlegroups.com>
On Dec 25, 4:57 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "Ind-dba" <oraclear..._at_googlemail.com> wrote in message
>
> news:c18c2018-d228-40f4-a7ba-9e332387f390_at_15g2000prz.googlegroups.com...> Hi Jonathan,
>
> .> I just read your article on Transitive closure - so no need to explain
> .> that.
>
>
>
> >> Yes - if you could shed more light on why this bug occur in first
> >> place. Could this be due to STATS on table?
>
> >> Or due to constraints on the 2 tables in picture?
> > I just happen to see a way at metalink to disable transitive closures
> > by setting event 10195 level 1(I tried level 3, 10 also - though they
> > were not documented anywhere).
> > Still I got the same plan (bad one) without USE_CONCAT HINT.
> > I have set the event at session level by :
> > ALTER SESSION SET EVENTS '10195 trace name context forever, level 1';
>
> > Can we have any other way to stop optimizer make unwanred transitive
> > closures?
>
> I am assuming that it is a bug because the behaviour is clearly
> unreasonable. Really I shouldn't call it a bug until it's been
> acknowledged as such by Oracle. Raise an SR
>
> In another post you asked how to count the number of partitions
> identified by the IN-list. There's a clue in another of my blog posts
>
> http://jonathanlewis.wordpress.com/2009/11/25/counting/
>
> select count(distinct dbms_mview.pmarker(rowid)
> from {your_table}
> where {youe_column} in ({your list of values})
> ;
>
> Event 10195 is about generating predicates from constraints
> using transitive closure (at least in the 10.2.0.1 oraus.msg,
> so ity won't help.
>
> I think if you could block transitive closure by changing the
> join predicate to
> table2.colx = table1.coly + 0
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com
Awesome results!
I'm able to block the transitive closures by the trick mentioned.
I'm yet to try the count of partition trick that you mentioned in your
blog.
But -- Its satisfying to have an answer of a complex issue :)
Many thanks to you !!
Merry X'mas and new year to all!!!
Regards,
Sachin
Received on Fri Dec 25 2009 - 12:59:46 CST