数据技术 - 16. page

在12.2之前，如果你想获得实时的数据，那么在利用query rewrite前，你必须得用on commit的刷新方式刷新物化视图。但是on commit的刷新方式有众多限制。所以，以往的方式中需要采用on command的方式来进行刷新（不管是全量刷新还是增量刷新）。

在使用on command刷新的时候，通过设置job来定时的刷新物化视图，一次job运行之后，下一次job到来之前，如果基表有数据变化是不会体现到物化视图的查询结果里的，实时物化视图就是解决这种问题的，实时获取数据而且免去频繁刷新mv。

下面的测试仅供参考：

SQL> create table t1 (x not null primary key, y not null) as
2 select rownum x, mod(rownum, 10) y from dual connect by level <= 1000000;

Table created.

SQL> create materialized view log on t1 with rowid (x, y) including new values;

Materialized view log created.

SQL>
SQL> create materialized view mv_old
2 refresh fast on demand
3 enable on query computation
4 enable query rewrite
5 as
6 select y , count(*) c1
7 from t1
8 group by y;

Materialized view created.

create mv时的关键字：enable on query computation

SQL> create table t2 (x not null primary key, y not null) as
2 select rownum x, mod(rownum, 10) y from dual connect by level <= 1000000;

Table created.

SQL> create materialized view log on t2 with rowid (x, y) including new values;

Materialized view log created.

SQL>
SQL> create materialized view mv_new
2 refresh fast on demand
3 enable on query computation
4 enable query rewrite
5 as
6 select y , count(*) c1
7 from t2
8 group by y;

Materialized view created.

SQL> show parameter rewrite

NAME TYPE VALUE
———————————— ———– ——————————
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced

SQL> set autotrace on explain stat

SQL> select mview_name,staleness,on_query_computation from user_mviews;

MVIEW_NAME STALENESS O
—————————————- ——————- –
MV_OLD FRESH N
MV_NEW FRESH Y

SQL>
SQL> select count(*) from MLOG$_T1;

COUNT(*)
———-
0

SQL> select count(*) from MLOG$_T2;

COUNT(*)
———-
0

SQL>

SQL> insert into t1
2 select 1000000+rownum, mod(rownum, 3) from dual connect by level <= 999;

999 rows created.

SQL>
SQL> insert into t2
2 select 1000000+rownum, mod(rownum, 3) from dual connect by level <= 999;

999 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select mview_name,staleness,on_query_computation from user_mviews;

MVIEW_NAME STALENESS O
—————————————- ——————- –
MV_OLD NEEDS_COMPILE N
MV_NEW NEEDS_COMPILE Y

SQL>
SQL> select count(*) from MLOG$_T1;

COUNT(*)
———-
999

SQL> select count(*) from MLOG$_T2;

COUNT(*)
———-
999

SQL>

由于数据stale，且没有set query_rewrite_integrity=stale_tolerated，传统mv没有进行query write。
SQL> select y as y_new_parse1, count(*) from t1
2 group by y;

Y_NEW_PARSE1 COUNT(*)
———— ———-
1 100333
6 100000
2 100333
4 100000
5 100000
8 100000
3 100000
7 100000
9 100000
0 100333

10 rows selected.

Execution Plan
———————————————————-
Plan hash value: 136660032

—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 10 | 30 | 515 (4)| 00:00:01 |
| 1 | HASH GROUP BY | | 10 | 30 | 515 (4)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 1000K| 2929K| 498 (1)| 00:00:01 |
—————————————————————————

Statistics
———————————————————-
1975 recursive calls
30 db block gets
4167 consistent gets
1786 physical reads
5440 redo size
754 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
131 sorts (memory)
0 sorts (disk)
10 rows processed

SQL>

real time进行了query rewrite，且查到的数据是最新实时数据

SQL> select y as y_new_parse1, count(*) from t2
2 group by y;

Y_NEW_PARSE1 COUNT(*)
———— ———-
6 100000
4 100000
5 100000
8 100000
3 100000
7 100000
9 100000
1 100333
2 100333
0 100333

10 rows selected.

Execution Plan
———————————————————-
Plan hash value: 542978159

——————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————————
| 0 | SELECT STATEMENT | | 12 | 312 | 22 (14)| 00:00:01 |
| 1 | VIEW | | 12 | 312 | 22 (14)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | VIEW | VW_FOJ_0 | 10 | 290 | 9 (12)| 00:00:01 |
|* 4 | HASH JOIN FULL OUTER | | 10 | 240 | 9 (12)| 00:00:01 |
| 5 | VIEW | | 1 | 7 | 6 (17)| 00:00:01 |
| 6 | HASH GROUP BY | | 1 | 22 | 6 (17)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | MLOG$_T2 | 999 | 21978 | 5 (0)| 00:00:01 |
| 8 | VIEW | | 10 | 170 | 3 (0)| 00:00:01 |
| 9 | MAT_VIEW ACCESS FULL | MV_NEW | 10 | 60 | 3 (0)| 00:00:01 |
| 10 | VIEW | | 2 | 52 | 13 (16)| 00:00:01 |
| 11 | UNION-ALL | | | | | |
|* 12 | FILTER | | | | | |
| 13 | NESTED LOOPS OUTER | | 1 | 32 | 6 (17)| 00:00:01 |
| 14 | VIEW | | 1 | 26 | 6 (17)| 00:00:01 |
|* 15 | FILTER | | | | | |
| 16 | HASH GROUP BY | | 1 | 22 | 6 (17)| 00:00:01 |
|* 17 | TABLE ACCESS FULL | MLOG$_T2 | 999 | 21978 | 5 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | I_SNAP$_MV_NEW | 1 | 6 | 0 (0)| 00:00:01 |
| 19 | NESTED LOOPS | | 1 | 35 | 7 (15)| 00:00:01 |
| 20 | VIEW | | 1 | 29 | 6 (17)| 00:00:01 |
| 21 | HASH GROUP BY | | 1 | 22 | 6 (17)| 00:00:01 |
|* 22 | TABLE ACCESS FULL | MLOG$_T2 | 999 | 21978 | 5 (0)| 00:00:01 |
|* 23 | MAT_VIEW ACCESS BY INDEX ROWID| MV_NEW | 1 | 6 | 1 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | I_SNAP$_MV_NEW | 1 | | 0 (0)| 00:00:01 |
——————————————————————————————————

Predicate Information (identified by operation id):
—————————————————

3 – filter(“AV$0″.”OJ_MARK” IS NULL)
4 – access(SYS_OP_MAP_NONNULL(“SNA$0″.”Y”)=SYS_OP_MAP_NONNULL(“AV$0”.”GB0″))
7 – filter(“MAS$”.”SNAPTIME$$”>TO_DATE(‘ 2017-07-12 14:35:01’, ‘syyyy-mm-dd hh24:mi:ss’))
12 – filter(CASE WHEN ROWID IS NOT NULL THEN 1 ELSE NULL END IS NULL)
15 – filter(SUM(1)>0)
17 – filter(“MAS$”.”SNAPTIME$$”>TO_DATE(‘ 2017-07-12 14:35:01’, ‘syyyy-mm-dd hh24:mi:ss’))
18 – access(“MV_NEW”.”SYS_NC00003$”(+)=SYS_OP_MAP_NONNULL(“AV$0”.”GB0″))
22 – filter(“MAS$”.”SNAPTIME$$”>TO_DATE(‘ 2017-07-12 14:35:01’, ‘syyyy-mm-dd hh24:mi:ss’))
23 – filter(“MV_NEW”.”C1″+”AV$0″.”D0″>0)
24 – access(SYS_OP_MAP_NONNULL(“Y”)=SYS_OP_MAP_NONNULL(“AV$0”.”GB0″))

Note
—–
– dynamic statistics used: dynamic sampling (level=2)
– this is an adaptive plan

Statistics
———————————————————-
906 recursive calls
64 db block gets
1232 consistent gets
14 physical reads
10548 redo size
744 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
64 sorts (memory)
0 sorts (disk)
10 rows processed

对比结果还是比较清楚的。

Real time mv利用原来的已经stale的物化视图，结合mv log，通过计算后，帮你获取实时的数据。即能获得实时数据，又避免频繁的刷新mv。