[20230225]12c Real-time materialized view 實時物化檢視的應用.txt
[20230225]12c Real-time materialized view 實時物化檢視的應用.txt
--//
在12.2之前，如果你想獲得實時的資料，那麼在利用query rewrite前，你必須得用on commit的重新整理方式重新整理物化檢視。但是on commit
的重新整理方式有眾多限制。所以，以往的方式中需要採用on command的方式來進行重新整理(不管是全量重新整理還是增量重新整理)。
在使用on command重新整理的時候，透過設定job來定時的重新整理物化檢視，一次job執行之後，下一次job到來之前，如果基表有資料變化是不
會體現到物化檢視的查詢結果裡的，實時物化檢視就是解決這種問題的，實時獲取資料而且免去頻繁重新整理mv。
1.環境:
SCOTT@test01p> @prxx_win.sql
==============================
PORT_STRING : IBMPC/WIN_NT64-9.1.0
VERSION : 12.2.0.1.0
BANNER : Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
CON_ID : 0
PL/SQL procedure successfully completed.
SCOTT@test01p> show parameter rewrite
PARAMETER_NAME TYPE VALUE
----------------------- ------ --------
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced
2.測試例子:
SCOTT@test01p> create table t2 (x not null primary key, y not null) tablespace users as select rownum x, mod(rownum, 10) y from dual connect by level <= 1e5 ;
Table created.
--//分析表.
@ gts lis.t2 '' '' ''
SCOTT@test01p> create materialized view log on t2 with rowid (x, y) including new values;
Materialized view log created.
create materialized view mv_new
refresh fast on demand
enable on query computation
enable query rewrite
as
select y , count(*) c1
from t2
group by y;
SCOTT@test01p> select y as y_new_parse1, count(*) from t2 group by y;
Y_NEW_PARSE1 COUNT(*)
------------ ----------
1 10000
6 10000
2 10000
4 10000
5 10000
8 10000
3 10000
7 10000
9 10000
0 10000
10 rows selected.
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID bns44pywqdv33, child number 0
-------------------------------------
select y as y_new_parse1, count(*) from t2 group by y
Plan hash value: 496717744
----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | MAT_VIEW REWRITE ACCESS FULL| MV_NEW | 10 | 60 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$E1B4E35D / MV_NEW@SEL$DB93197A
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
24 rows selected.
--//查詢直接檢視物化檢視.
SCOTT@test01p> insert into t2 select 1e5+rownum, mod(rownum, 3) from dual connect by level <= 999;
999 rows created.
SCOTT@test01p> commit ;
Commit complete.
3.繼續測試:
SCOTT@test01p> select y as y_new_parse1, count(*) from t2 group by y;
Y_NEW_PARSE1 COUNT(*)
------------ ----------
6 10000
4 10000
5 10000
8 10000
3 10000
7 10000
9 10000
1 10333
2 10333
0 10333
10 rows selected.
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID bns44pywqdv33, child number 1
-------------------------------------
select y as y_new_parse1, count(*) from t2 group by y
Plan hash value: 1607751112
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 22 (100)| | | | |
| 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 | 2078K| 2078K| 899K (0)|
| 5 | VIEW | | 1 | 7 | 6 (17)| 00:00:01 | | | |
| 6 | HASH GROUP BY | | 1 | 22 | 6 (17)| 00:00:01 | 1214K| 1214K| 707K (0)|
|* 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 | 1214K| 1214K| 754K (0)|
|* 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)| | | | |
|* 19 | HASH JOIN | | 1 | 35 | 7 (15)| 00:00:01 | 1743K| 1743K| 659K (0)|
| 20 | VIEW | | 1 | 29 | 6 (17)| 00:00:01 | | | |
| 21 | HASH GROUP BY | | 1 | 22 | 6 (17)| 00:00:01 | 1214K| 1214K| 756K (0)|
|* 22 | TABLE ACCESS FULL | MLOG$_T2 | 999 | 21978 | 5 (0)| 00:00:01 | | | |
| 23 | MAT_VIEW ACCESS FULL | MV_NEW | 1 | 6 | 1 (0)| 00:00:01 | | | |
-------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$1 / from$_subquery$_003@SEL$1
2 - SET$1
3 - SEL$EC770CBB / from$_subquery$_009@SEL$7
4 - SEL$EC770CBB
5 - SEL$EC77BF7C / AV$0@SEL$2
6 - SEL$EC77BF7C
7 - SEL$EC77BF7C / MAS$@SEL$5
8 - SEL$F065AEA4 / SNA$0@SEL$2
9 - SEL$F065AEA4 / MV_NEW@SEL$3
10 - SET$2 / from$_subquery$_011@SEL$8
11 - SET$2
12 - SEL$0A26C4AD
14 - SEL$196A2F92 / AV$0@SEL$2
15 - SEL$196A2F92
17 - SEL$196A2F92 / MAS$@SEL$5
18 - SEL$0A26C4AD / MV_NEW@SEL$3
19 - SEL$0B44CC95
20 - SEL$7286615E / AV$0@SEL$2
21 - SEL$7286615E
22 - SEL$7286615E / MAS$@SEL$5
23 - SEL$0B44CC95 / MV_NEW@SEL$3
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(' 2023-02-24 21:49:03', '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(' 2023-02-24 21:49:03', 'syyyy-mm-dd hh24:mi:ss'))
18 - access("MV_NEW"."SYS_NC00003$"=SYS_OP_MAP_NONNULL("AV$0"."GB0"))
19 - access(SYS_OP_MAP_NONNULL("Y")=SYS_OP_MAP_NONNULL("AV$0"."GB0"))
filter("MV_NEW"."C1"+"AV$0"."D0">0)
22 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2023-02-24 21:49:03', 'syyyy-mm-dd hh24:mi:ss'))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- this is an adaptive plan
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
82 rows selected.
--//Real time mv利用原來的已經stale的物化檢視，結合mv log，透過計算後，幫你獲取實時的資料。即能獲得實時資料，又避免頻繁的刷
--//新mv。
SCOTT@test01p> select mview_name,staleness,REFRESH_METHOD from user_mviews where MVIEW_NAME='MV_NEW';
MVIEW_NAME STALENESS REFRESH_
-------------------- ------------------- --------
MV_NEW STALE FAST
--//我定義的物化檢視不會自動重新整理.
SCOTT@test01p> exec dbms_mview.refresh(list=>'MV_NEW',method=>'F');
PL/SQL procedure successfully completed.
SCOTT@test01p> select mview_name,staleness,REFRESH_METHOD from user_mviews where MVIEW_NAME='MV_NEW';
MVIEW_NAME STALENESS REFRESH_
-------------------- ------------------- --------
MV_NEW FRESH FAST
SCOTT@test01p> select y as y_new_parse1, count(*) from t2 group by y;
Y_NEW_PARSE1 COUNT(*)
------------ ----------
1 10333
6 10000
2 10333
4 10000
5 10000
8 10000
3 10000
7 10000
9 10000
0 10333
10 rows selected.
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID bns44pywqdv33, child number 0
-------------------------------------
select y as y_new_parse1, count(*) from t2 group by y
Plan hash value: 496717744
----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | MAT_VIEW REWRITE ACCESS FULL| MV_NEW | 10 | 60 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$E1B4E35D / MV_NEW@SEL$DB93197A
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
24 rows selected.
--//重新整理後不再訪問mv log.
--//修改為1分鐘重新整理1次.
SCOTT@test01p> alter materialized view mv_new refresh start with sysdate next sysdate + interval '1' minute;
Materialized view altered.
SCOTT@test01p> delete from t2 where y=0 and rownum<=333;
333 rows deleted.
SCOTT@test01p> commit ;
Commit complete.
--//第一次看結合mv log,執行計劃不再貼出.
--//等上一段時間.執行計劃就僅僅訪問物化檢視了.
SCOTT@test01p> select * from user_mviews where MVIEW_NAME='MV_NEW'
2 @ prxx_win
==============================
OWNER : SCOTT
MVIEW_NAME : MV_NEW
CONTAINER_NAME : MV_NEW
QUERY : select y , count(*) c1
from t2
group by y
QUERY_LEN : 41
UPDATABLE : N
UPDATE_LOG :
MASTER_ROLLBACK_SEG :
MASTER_LINK :
REWRITE_ENABLED : Y
REWRITE_CAPABILITY : GENERAL
REFRESH_MODE : DEMAND
REFRESH_METHOD : FAST
BUILD_MODE : IMMEDIATE
FAST_REFRESHABLE : DIRLOAD_DML
LAST_REFRESH_TYPE : FAST
LAST_REFRESH_DATE : 2023-02-24 22:15:31
LAST_REFRESH_END_TIME : 2023-02-24 22:15:31
STALENESS : FRESH
AFTER_FAST_REFRESH : FRESH
UNKNOWN_PREBUILT : N
UNKNOWN_PLSQL_FUNC : N
UNKNOWN_EXTERNAL_TABLE : N
UNKNOWN_CONSIDER_FRESH : N
UNKNOWN_IMPORT : N
UNKNOWN_TRUSTED_FD : N
COMPILE_STATE : VALID
USE_NO_INDEX : N
STALE_SINCE :
NUM_PCT_TABLES : 0
NUM_FRESH_PCT_REGIONS :
NUM_STALE_PCT_REGIONS :
SEGMENT_CREATED : YES
EVALUATION_EDITION :
UNUSABLE_BEFORE :
UNUSABLE_BEGINNING :
DEFAULT_COLLATION : USING_NLS_COMP
ON_QUERY_COMPUTATION : Y
PL/SQL procedure successfully completed.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2937255/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Materialized ViewZedView
- 物化檢視
- 物化檢視分割槽實驗
- 物化檢視(zt)
- 應用zabbix的實時匯出（real-time export）功能Export
- Oracle普通檢視和物化檢視的區別Oracle
- 資料庫的物化檢視資料庫
- [20181103]12c檢視V$EVENT_NAME.txt
- [20201207]12c v$open_cursor檢視.txt
- Oracle vs PostgreSQL Develop（20） - Materialized ViewOracleSQLdevZedView
- calcite物化檢視詳解
- [20181004]12c dba_source檢視定義.txt
- hg_job配置定時重新整理物化檢視
- 汽車之家基於 Apache Flink 的跨資料庫實時物化檢視探索Apache資料庫
- Oracle物化檢視的建立及使用（二）Oracle
- Oracle物化檢視的建立及使用（一）Oracle
- 用exp、imp遷移包含物化檢視日誌的資料
- MySQL View 檢視MySqlView
- 物化檢視幾個知識點
- ClickHouse 物化檢視學習總結
- 基於ROWID更新的物化檢視測試
- 用物化檢視單行同步資料庫時，源表結構變化時的處理步驟資料庫
- ClickHouse 效能優化？試試物化檢視優化
- ClickHouse效能優化？試試物化檢視優化
- hive中的 lateral view(側檢視)HiveView
- StarRocks 物化檢視重新整理流程及原理
- StarRocks 物化檢視重新整理流程和原理
- 物化檢視如何快速完成資料聚合操作？
- 使用SpringBoot+PostgreSQL物化檢視實現微服務設計模式 - vinsguruSpring BootSQL微服務設計模式
- 物化檢視日誌無法正常清除的解決方法
- Postgres使用trigger自動重新整理物化檢視
- 物化檢視快速重新整理與ORA-00001
- 使用Materialise物化檢視解耦微服務架構解耦微服務架構
- [20240911]檢視超長檢視的定義2.txt
- 基於圖神經網路的動態物化檢視管理神經網路
- .net core EF Core 檢視的應用
- 火山引擎ByteHouse：如何最佳化ClickHouse物化檢視能力？
- curl命令檢視響應時間