Non-atomic mview refresh and unique index.

From: Yakov Vasilchenko <dexatro_at_gmail.com>
Date: Tue, 22 Oct 2013 12:01:02 +0300
Message-ID: <CAMEMMa1wnnjuOLHaKx3ZZewANSZkXAR8cG_Hus9hyT7QHzB67A_at_mail.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

Received on Tue Oct 22 2013 - 11:01:02 CEST