Re: Non-atomic mview refresh and unique index.

  • From: Mohamed Houri <mohamed.houri@xxxxxxxxx>
  • To: Yakov Vasilchenko <dexatro@xxxxxxxxx>
  • Date: Tue, 22 Oct 2013 15:10:47 +0200

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


Other related posts: