Re: Non-atomic mview refresh and unique index.

From: Mohamed Houri <mohamed.houri_at_gmail.com>
Date: Tue, 22 Oct 2013 11:38:56 +0200
Message-ID: <CAJu8R6hOpwVW91ZthrQhfgAYwoeW2rnbDxqhSVRnCE8bPVgB-w_at_mail.gmail.com>

I have tested your case in two database

*Case 1*
*
*

SQL> select * from v$version where rownum = 1;

BANNER

---

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

SQL> select index_name, status from user_indexes where table_name = 'B';

INDEX_NAME                     STATUS
------------------------------ --------
UQ_B                           VALID

SQL>  BEGIN
  2        DBMS_SNAPSHOT.REFRESH(
  3          LIST                 => 'B'
  4         ,METHOD               => 'C'
  5         ,PUSH_DEFERRED_RPC    => FALSE
  6         ,REFRESH_AFTER_ERRORS => FALSE
  7         ,PURGE_OPTION         => 1
  8         ,PARALLELISM          => 0
  9         ,ATOMIC_REFRESH       => FALSE
 10         ,NESTED               => FALSE);
 11      END;
 12      /

 BEGIN
*
ERROR at line 1:

ORA-12008: error in materialized view refresh path
ORA-00001: unique constraint (D102.UQ_B) violated
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2251
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2457
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2426
ORA-06512: at line 2

*Case 2*

*
*
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

  BEGIN

      DBMS_SNAPSHOT.REFRESH(
        LIST                 => 'B'
       ,METHOD               => 'C'
       ,PUSH_DEFERRED_RPC    => FALSE
       ,REFRESH_AFTER_ERRORS => FALSE
       ,PURGE_OPTION         => 1
       ,PARALLELISM          => 0
       ,ATOMIC_REFRESH       => FALSE
       ,NESTED               => FALSE);

    END;
    /

anonymous block completed

SQL> select index_name, status
  2 from user_indexes where table_name = 'B';

INDEX_NAME                     STATUS
------------------------------ --------
UQ_B                           UNUSABLE

Look how your index has been disabled. It is the effect of the atomic_refresh when set to false will truncate and direct path load( direct path load will disable the unique index).

Try with atomic_refresh => true and you will avoid this issue

Best regards
Mohamed Houri
www.hourim.wordpress.com

2013/10/22 Yakov Vasilchenko <dexatro_at_gmail.com>

> Hi folks,
> It seems like we've encountered some unexpected behavior of optimizer after
> complete non-atomic mview refresh. Please see the issue below:
>
> Oracle version 11.2.0.3
>
> yakov_at_oracle>
> create table a(
> id int,
> val number);
>
> Table created.
>
> yakov_at_oracle>
> insert into a
> select 1, 1
> from dual
> ;
>
> 1 row created.
>
> yakov_at_oracle>
> create table b(
> id int
> );
>
> Table created.
>
> yakov_at_oracle>
> create unique index uq_b on b(id);
>
> Index created.
>
> yakov_at_oracle>
> create materialized view b
> on prebuilt table
> with reduced precision
> as
> select 1 id --duplicate ids.
> from dual
> union all
> select 1 id
> from dual;
>
> Materialized view created.
>
> yakov_at_oracle>
> BEGIN
> DBMS_SNAPSHOT.REFRESH(
> LIST => 'B'
> ,METHOD => 'C'
> ,PUSH_DEFERRED_RPC => FALSE
> ,REFRESH_AFTER_ERRORS => FALSE
> ,PURGE_OPTION => 1
> ,PARALLELISM => 0
> ,ATOMIC_REFRESH => FALSE
> ,NESTED => FALSE);
> END;
> /
>
> PL/SQL procedure successfully completed.
> --Yet the refresh is done without any errors!
> yakov_at_oracle>
> alter index uq_b rebuild;
> alter index uq_b rebuild
> *
> ERROR at line 1:
> ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
>
> yakov_at_oracle>
> begin
> dbms_stats.gather_table_stats(user, 'a');
> dbms_stats.gather_table_stats(user, 'b');
> end;
> /
>
> PL/SQL procedure successfully completed.
>
> yakov_at_oracle>
> set autotrace traceonly explain;
> yakov_at_oracle>
> select *
> from b
> where id = 1;
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value: 1971462461
>
>
> -----------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
> |
>
> -----------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 2 | 6 | 15 (0)| 00:00:01
> |
> |* 1 | MAT_VIEW ACCESS FULL| B | 2 | 6 | 15 (0)| 00:00:01
> | *Fullscan as index is unusable.
>
> -----------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 1 - filter("ID"=1)
>
> yakov_at_oracle>
> select sum(val) -- this returns 1
> from (
> select sum(a.val) val
> from a, b
> where a.id = b.id(+)
> );
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value: 1361687250
>
>
> -----------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
> |
>
> -----------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | 13 | 144 (4)| 00:00:01
> |
> | 1 | SORT AGGREGATE | | 1 | 13 | |
> |
> | 2 | VIEW | | 1 | 13 | 144 (4)| 00:00:01
> |
> | 3 | SORT AGGREGATE | | 1 | 3 | |
> |
> | 4 | TABLE ACCESS FULL| A | 1 | 3 | 144 (4)| 00:00:01
> | *Left Join is eliminated(despite unique index is unusable!)
>
> -----------------------------------------------------------------------------
>
> yakov_at_oracle>
> select sum(val) --and this returns 2
> from (
> select sum(a.val) val
> from a, b
> where a.id = b.id(+)
> group by b.id
> );
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value: 569947948
>
>
> ---------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
> Time |
>
> ---------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | 13 | 161 (5)|
> 00:00:01 |
> | 1 | SORT AGGREGATE | | 1 | 13 |
> | |
> | 2 | VIEW | | 1 | 13 | 161 (5)|
> 00:00:01 |
> | 3 | HASH GROUP BY | | 1 | 9 | 161 (5)|
> 00:00:01 |
> |* 4 | HASH JOIN OUTER | | 2 | 18 | 160 (4)|
> 00:00:01 |
> | 5 | TABLE ACCESS FULL | A | 1 | 6 | 144 (4)|
> 00:00:01 |
> | 6 | MAT_VIEW ACCESS FULL| B | 2 | 6 | 15 (0)|
> 00:00:01 |
>
> ---------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 4 - access("A"."ID"="B"."ID"(+))
>
>
> So my questions are:
> 1.Is it expected behavior for non-atomic refresh to proceed even with
> errors?
> 2.What may cause strange behavior with left join elimination?
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Bien Respectueusement
Mohamed Houri


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

Received on Tue Oct 22 2013 - 11:38:56 CEST