Re: Non-atomic mview refresh and unique index.
- From: Mohamed Houri <mohamed.houri@xxxxxxxxx>
- To: Yakov Vasilchenko <dexatro@xxxxxxxxx>
- Date: Wed, 23 Oct 2013 08:57:10 +0200
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@xxxxxxxxx>
> 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
>
--
Bien Respectueusement
Mohamed Houri
--
//www.freelists.org/webpage/oracle-l
Other related posts:
