Re: Non-atomic mview refresh and unique index.
- From: Mohamed Houri <mohamed.houri@xxxxxxxxx>
- To: dexatro@xxxxxxxxx
- Date: Tue, 22 Oct 2013 11:38:56 +0200
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@xxxxxxxxx>
> 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@oracle>
> create table a(
> id int,
> val number);
>
> Table created.
>
> yakov@oracle>
> insert into a
> select 1, 1
> from dual
> ;
>
> 1 row created.
>
> yakov@oracle>
> create table b(
> id int
> );
>
> Table created.
>
> yakov@oracle>
> create unique index uq_b on b(id);
>
> Index created.
>
> yakov@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@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@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@oracle>
> begin
> dbms_stats.gather_table_stats(user, 'a');
> dbms_stats.gather_table_stats(user, 'b');
> end;
> /
>
> PL/SQL procedure successfully completed.
>
> yakov@oracle>
> set autotrace traceonly explain;
> yakov@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@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@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?
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Bien Respectueusement
Mohamed Houri
--
//www.freelists.org/webpage/oracle-l
Other related posts:
