I dropped the index and created a unique index and put it into a disable no validate state SQL> alter table b drop constraint b_uk; Table altered. SQL> drop index uq_b; SQL> alter table b add constraint b_uk unique (id) disable novalidate; Table altered. SQL> select sum(val) 2 from ( 3 select sum(a.val) val 4 from a, b 5 where a.id = b.id(+) 6 ); SUM(VAL)--THISRETURNS1 ---------------------- 2 Spot how the CBO takes into account that the unique constraint is disabled and don't take into account that the unique index is in an unusable state. It seems that the CBO is doing a wrong assumption on the uniqueness when it uses a unusable unique index. but in the meantime the CBO is taking a good decision in the presence of a disabled unique constraint Best regards Mohamed Houri www.hourim.wordpress.com 2013/10/22 Yakov Vasilchenko <dexatro@xxxxxxxxx> > Hi Mohamed, > > This is the very valid points. > > However what bugs me is the fact that unusable index seems valid to > optimizer to do left-join elimination. > And I cannot find it to be written somewhere that non-atomic refresh makes > no promises in case of errors during refresh. > > > -- Bien Respectueusement Mohamed Houri -- //www.freelists.org/webpage/oracle-l