Re: Non-atomic mview refresh and unique index.

From: Mohamed Houri <>
Date: Tue, 22 Oct 2013 15:10:47 +0200
Message-ID: <>

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 =
  6      );



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

2013/10/22 Yakov Vasilchenko <>

> 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

Received on Tue Oct 22 2013 - 15:10:47 CEST

Original text of this message