Re: PARTITION HASH INLIST -- QUERY

"Ind-dba" <oraclearora_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?

> 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 Lewis
http://jonathanlewis.wordpress.com