From: Jonathan Lewis <>
Date: Thu, 24 Dec 2009 23:57:25 -0000
Message-ID: <>

"Ind-dba" <> wrote in message
> 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

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 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


Jonathan Lewis
Received on Thu Dec 24 2009 - 17:57:25 CST

Original text of this message