Thanks for the question, Alan.

Asked: August 04, 2016 - 2:56 pm UTC

Last updated: January 06, 2021 - 5:03 pm UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

I am researching how to improve the availability of data in an MV. The doc says that while doing a complete refresh of MV using the out-of-place option that the data in the mv is still available, which I find is true. But it also says the mv is available for query rewrite, which does not seem to happen. Am I misunderstanding this option?
Thanks.

and Connor said...

I've replicated what you are seeing

SQL> create table t as
  2  select mod(rownum,10) x, rownum y, cast(rownum as char(100)) p
  3  from
  4   ( select 1 from dual connect by level <= 2000 ),
  5   ( select 1 from dual connect by level <= 5000 );

Table created.

SQL> create materialized view t_mv
  2  enable query rewrite
  3  as
  4  select x, sum(y) sal
  5    from t
  6   group by x;

Materialized view created.

SQL> set autotrace on
SQL> select x, sum(y) sal
  2    from t
  3   group by x
  4  /

         X        SAL
---------- ----------
         1 5.0000E+12
         6 5.0000E+12
         2 5.0000E+12
         4 5.0000E+12
         5 5.0000E+12
         8 5.0000E+12
         3 5.0000E+12
         7 5.0000E+12
         9 5.0000E+12
         0 5.0000E+12

10 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 139569370

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |    10 |    90 |     2   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| T_MV |    10 |    90 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        774  bytes sent via SQL*Net to client
        551  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

--
-- and now in session 2, i set the refresh going
--

SQL> exec dbms_mview.refresh('T_MV', out_of_place=>true, atomic_refresh=>false);

{still running]

--
-- back in Session 1
--

SQL> /

         X        SAL
---------- ----------
         1 5.0000E+12
         6 5.0000E+12
         2 5.0000E+12
         4 5.0000E+12
         5 5.0000E+12
         8 5.0000E+12
         3 5.0000E+12
         7 5.0000E+12
         9 5.0000E+12
         0 5.0000E+12

10 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 47235625

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    10 |    90 | 28348   (2)| 00:00:02 |
|   1 |  HASH GROUP BY     |      |    10 |    90 | 28348   (2)| 00:00:02 |
|   2 |   TABLE ACCESS FULL| T    |    10M|    85M| 28074   (1)| 00:00:02 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          2  recursive calls
          0  db block gets
     161040  consistent gets
     161032  physical reads
          0  redo size
        774  bytes sent via SQL*Net to client
        551  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

I think you'll need to take it up with Support, because I agree, the documentation suggests otherwise.

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

out of place refresh in 12c mview

sudheer godgeri, January 06, 2021 - 3:16 pm UTC

I think the issue was:
during out of place refresh -- the mview's internal object ID used to change i.e a new mview table was created, which was refreshed, the older table was dropped and mview switched to the new table --this had a bug n 12.1 -- i.e the new object id caused the existing grants to fail i.e we had to re-do the grants to roles/users who queried / used the mview object : This was fixed i believe in 12.2 and later where we did not have to re-do the grants.

January 06, 2021 - 5:03 pm UTC

Thanks for letting us know.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

out of place refresh in 12c mview

More to Explore

DBMS_MVIEW

out-of-place refresh of a materialized view

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

out of place refresh in 12c mview

More to Explore

DBMS_MVIEW