Re: Non-atomic mview refresh and unique index.

From: Mohamed Houri <mohamed.houri_at_gmail.com>
Date: Wed, 23 Oct 2013 08:57:10 +0200
Message-ID: <CAJu8R6irnivdFCNYHc_CfvXGqUoufmKeKfbaJ1EXR95504j9sw_at_mail.gmail.com>

Hi Yakov,
I have summarized what seems to be a CBO bug in the presence of *unusable unique indexes* here below

http://hourim.wordpress.com/2013/10/23/cbo-and-unusable-unique-index/

Best regards
Mohamed Houri
www.hourim.wordpress.com

2013/10/22 Mohamed Houri <mohamed.houri_at_gmail.com>

> 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_at_gmail.com>
>
>> 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
>

-- 
Bien Respectueusement
Mohamed Houri


--
http://www.freelists.org/webpage/oracle-l

Received on Wed Oct 23 2013 - 08:57:10 CEST