Non-atomic mview refresh and unique index.
- From: Yakov Vasilchenko <dexatro@xxxxxxxxx>
- To: Oracle-L@xxxxxxxxxxxxx
- Date: Tue, 22 Oct 2013 12:01:02 +0300
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
Other related posts:
