使用物化檢視查詢重寫 優化對於 UNION ALL檢視的CONNECT BY查詢
使用物化檢視查詢重寫 優化對於 UNION ALL檢視的CONNECT BY查詢
查詢SQL如下
SQL> SELECT T.INTJOBID, T.STRJOBNAME, T.INTPRIORJOBID
2 FROM (SELECT INTJOBID, STRJOBNAME, INTPRIORJOBID, INTFLOWID
3 FROM JOB_BASEINFO
4 UNION ALL
5 SELECT INTJOBID, STRJOBNAME, INTPRIORJOBID, INTFLOWID
6 FROM JOB_BASEINFO_HIS) T
7 START WITH T.INTJOBID = &1
8 CONNECT BY T.INTJOBID = PRIOR T.INTPRIORJOBID
9 ORDER BY INTJOBID ;
Enter value for 1: 14381
old 7: START WITH T.INTJOBID = &1
new 7: START WITH T.INTJOBID = 14381
Execution Plan
----------------------------------------------------------
Plan hash value: 1526360741
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 127K| 64M| 2213 (1)| 00:00:27 |
| 1 | SORT ORDER BY | | 127K| 64M| 2213 (1)| 00:00:27 |
|* 2 | CONNECT BY WITH FILTERING | | | | | |
| 3 | VIEW | | 2 | 1160 | 4 (0)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| JOB_BASEINFO | 1 | 40 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_JOB_BASEINFO | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| JOB_BASEINFO_HIS | 1 | 40 | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | IDX_JOB_BASEINFO_HIS | 1 | | 1 (0)| 00:00:01 |
|* 9 | HASH JOIN | | | | | |
| 10 | CONNECT BY PUMP | | | | | |
| 11 | VIEW | | 127K| 64M| 2213 (1)| 00:00:27 |
| 12 | UNION-ALL | | | | | |
| 13 | TABLE ACCESS FULL | JOB_BASEINFO | 63890 | 2495K| 1106 (1)| 00:00:14 |
| 14 | TABLE ACCESS FULL | JOB_BASEINFO_HIS | 63890 | 2495K| 1106 (1)| 00:00:14 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."INTJOBID"=PRIOR "T"."INTPRIORJOBID")
6 - access("INTJOBID"=14381)
8 - access("INTJOBID"=14381)
9 - access("T"."INTJOBID"=PRIOR "T"."INTPRIORJOBID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9970 consistent gets
0 physical reads
0 redo size
730 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
2 rows processed
資料庫10204
這個只是測試資料，實際資料更多，一致性讀超過10W，物理讀也高達7W。
遇到大量會話同時執行該查詢時，導致系統異常緩慢。
可能是10g CBO的限制，全表掃描無法避免，最多也就建立多列索引，將全表掃描轉換為索引快速全掃描。
在11gR2中，受用於引數 _connect_by_use_union_all提供的新特性，其可以得到良好的執行計劃。
相當了使用物化檢視來重新查詢。測試了一把，效果明顯
**********************************************************************************************************
CREATE MATERIALIZED VIEW LOG ON JOB_BASEINFO WITH ROWID,SEQUENCE INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON JOB_BASEINFO_HIS WITH ROWID,SEQUENCE INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW mv_JOB
REFRESH FAST
ON COMMIT
-- ON DEMAND
WITH ROWID enable query rewrite AS
SELECT INTJOBID,INTFLOWID,STRJOBNAME,INTCREATORID,DTTBEGINTIME,DTTENDTIME,INTJOBTYPE,INTAPPLYTYPE,STRHOST,STRPATH,INTSTATE,STRJOBDESCRIPT,
INTDATASOURCEID,INTDATASOURCETYPE,INTBATCHID,STRURL,INTPRIORJOBID,INTBRANCHID,INTTREEID,STRSELECTSETTING,INTCAPTIONINDEX,INTRETURNVALUEINDEX,
INTACTIONTYPE,INTSAVETYPE,INTSAVETERM,INTSAVETERMTYPE,INTMINDATAROW,INTMAXDATAROW,1 MARKER,ROWID RD
FROM JOB_BASEINFO
UNION ALL
SELECT INTJOBID,INTFLOWID,STRJOBNAME,INTCREATORID,DTTBEGINTIME,DTTENDTIME,INTJOBTYPE,INTAPPLYTYPE,STRHOST,STRPATH,INTSTATE,STRJOBDESCRIPT,
INTDATASOURCEID,INTDATASOURCETYPE,INTBATCHID,STRURL,INTPRIORJOBID,INTBRANCHID,INTTREEID,STRSELECTSETTING,INTCAPTIONINDEX,INTRETURNVALUEINDEX,
INTACTIONTYPE,INTSAVETYPE,INTSAVETERM,INTSAVETERMTYPE,INTMINDATAROW,INTMAXDATAROW,2 MARKER,ROWID RD
FROM JOB_BASEINFO_HIS
CREATE INDEX idx_mv_job ON mv_job(INTJOBID);
exec dbms_stats.gather_table_stats(null,'mv_job');
**********************************************************************************************************
Execution Plan
----------------------------------------------------------
Plan hash value: 2052634769
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 80 | 3 (0)| 00:00:01 |
| 1 | SORT ORDER BY | | 2 | 80 | 3 (0)| 00:00:01 |
|* 2 | CONNECT BY WITH FILTERING | | | | | |
| 3 | MAT_VIEW REWRITE ACCESS BY INDEX ROWID | MV_JOB | 2 | 126 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_MV_JOB | 2 | | 1 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | | | | |
| 6 | CONNECT BY PUMP | | | | | |
| 7 | MAT_VIEW REWRITE ACCESS BY INDEX ROWID| MV_JOB | 2 | 80 | 3 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | IDX_MV_JOB | 2 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MV_JOB"."INTJOBID"=PRIOR "MV_JOB"."INTPRIORJOBID")
4 - access("MV_JOB"."INTJOBID"=14381)
8 - access("MV_JOB"."INTJOBID"=PRIOR "MV_JOB"."INTPRIORJOBID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
730 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
2 rows processed
查詢SQL如下
SQL> SELECT T.INTJOBID, T.STRJOBNAME, T.INTPRIORJOBID
2 FROM (SELECT INTJOBID, STRJOBNAME, INTPRIORJOBID, INTFLOWID
3 FROM JOB_BASEINFO
4 UNION ALL
5 SELECT INTJOBID, STRJOBNAME, INTPRIORJOBID, INTFLOWID
6 FROM JOB_BASEINFO_HIS) T
7 START WITH T.INTJOBID = &1
8 CONNECT BY T.INTJOBID = PRIOR T.INTPRIORJOBID
9 ORDER BY INTJOBID ;
Enter value for 1: 14381
old 7: START WITH T.INTJOBID = &1
new 7: START WITH T.INTJOBID = 14381
Execution Plan
----------------------------------------------------------
Plan hash value: 1526360741
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 127K| 64M| 2213 (1)| 00:00:27 |
| 1 | SORT ORDER BY | | 127K| 64M| 2213 (1)| 00:00:27 |
|* 2 | CONNECT BY WITH FILTERING | | | | | |
| 3 | VIEW | | 2 | 1160 | 4 (0)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| JOB_BASEINFO | 1 | 40 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_JOB_BASEINFO | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| JOB_BASEINFO_HIS | 1 | 40 | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | IDX_JOB_BASEINFO_HIS | 1 | | 1 (0)| 00:00:01 |
|* 9 | HASH JOIN | | | | | |
| 10 | CONNECT BY PUMP | | | | | |
| 11 | VIEW | | 127K| 64M| 2213 (1)| 00:00:27 |
| 12 | UNION-ALL | | | | | |
| 13 | TABLE ACCESS FULL | JOB_BASEINFO | 63890 | 2495K| 1106 (1)| 00:00:14 |
| 14 | TABLE ACCESS FULL | JOB_BASEINFO_HIS | 63890 | 2495K| 1106 (1)| 00:00:14 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."INTJOBID"=PRIOR "T"."INTPRIORJOBID")
6 - access("INTJOBID"=14381)
8 - access("INTJOBID"=14381)
9 - access("T"."INTJOBID"=PRIOR "T"."INTPRIORJOBID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9970 consistent gets
0 physical reads
0 redo size
730 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
2 rows processed
資料庫10204
這個只是測試資料，實際資料更多，一致性讀超過10W，物理讀也高達7W。
遇到大量會話同時執行該查詢時，導致系統異常緩慢。
可能是10g CBO的限制，全表掃描無法避免，最多也就建立多列索引，將全表掃描轉換為索引快速全掃描。
在11gR2中，受用於引數 _connect_by_use_union_all提供的新特性，其可以得到良好的執行計劃。
相當了使用物化檢視來重新查詢。測試了一把，效果明顯
**********************************************************************************************************
CREATE MATERIALIZED VIEW LOG ON JOB_BASEINFO WITH ROWID,SEQUENCE INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON JOB_BASEINFO_HIS WITH ROWID,SEQUENCE INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW mv_JOB
REFRESH FAST
ON COMMIT
-- ON DEMAND
WITH ROWID enable query rewrite AS
SELECT INTJOBID,INTFLOWID,STRJOBNAME,INTCREATORID,DTTBEGINTIME,DTTENDTIME,INTJOBTYPE,INTAPPLYTYPE,STRHOST,STRPATH,INTSTATE,STRJOBDESCRIPT,
INTDATASOURCEID,INTDATASOURCETYPE,INTBATCHID,STRURL,INTPRIORJOBID,INTBRANCHID,INTTREEID,STRSELECTSETTING,INTCAPTIONINDEX,INTRETURNVALUEINDEX,
INTACTIONTYPE,INTSAVETYPE,INTSAVETERM,INTSAVETERMTYPE,INTMINDATAROW,INTMAXDATAROW,1 MARKER,ROWID RD
FROM JOB_BASEINFO
UNION ALL
SELECT INTJOBID,INTFLOWID,STRJOBNAME,INTCREATORID,DTTBEGINTIME,DTTENDTIME,INTJOBTYPE,INTAPPLYTYPE,STRHOST,STRPATH,INTSTATE,STRJOBDESCRIPT,
INTDATASOURCEID,INTDATASOURCETYPE,INTBATCHID,STRURL,INTPRIORJOBID,INTBRANCHID,INTTREEID,STRSELECTSETTING,INTCAPTIONINDEX,INTRETURNVALUEINDEX,
INTACTIONTYPE,INTSAVETYPE,INTSAVETERM,INTSAVETERMTYPE,INTMINDATAROW,INTMAXDATAROW,2 MARKER,ROWID RD
FROM JOB_BASEINFO_HIS
CREATE INDEX idx_mv_job ON mv_job(INTJOBID);
exec dbms_stats.gather_table_stats(null,'mv_job');
**********************************************************************************************************
Execution Plan
----------------------------------------------------------
Plan hash value: 2052634769
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 80 | 3 (0)| 00:00:01 |
| 1 | SORT ORDER BY | | 2 | 80 | 3 (0)| 00:00:01 |
|* 2 | CONNECT BY WITH FILTERING | | | | | |
| 3 | MAT_VIEW REWRITE ACCESS BY INDEX ROWID | MV_JOB | 2 | 126 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_MV_JOB | 2 | | 1 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | | | | |
| 6 | CONNECT BY PUMP | | | | | |
| 7 | MAT_VIEW REWRITE ACCESS BY INDEX ROWID| MV_JOB | 2 | 80 | 3 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | IDX_MV_JOB | 2 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MV_JOB"."INTJOBID"=PRIOR "MV_JOB"."INTPRIORJOBID")
4 - access("MV_JOB"."INTJOBID"=14381)
8 - access("MV_JOB"."INTJOBID"=PRIOR "MV_JOB"."INTPRIORJOBID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
730 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
2 rows processed
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8242091/viewspace-754784/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【MV】物化檢視查詢重寫
- 【SQL】Oracle查詢轉換之物化檢視查詢重寫SQLOracle
- Oracle最佳化技術---物化檢視查詢重寫query rewriteOracle
- 淺析物化檢視與查詢重寫(Enable query rewrite)
- 隱式轉換影響物化檢視查詢重寫
- 12c 查詢重寫物化檢視 - DBMS_ADVISOR.TUNE_MVIEW的使用View
- DB2資料庫物化檢視：MQT物化查詢表的使用DB2資料庫MQQT
- 一次系統檢視查詢的優化優化
- 檢視查詢報錯
- 【MySQL】檢視&子查詢MySql
- oracle 常用查詢檢視Oracle
- mysql查詢優化檢查 explainMySql優化AI
- 查詢集合操作union與union all
- 包含複雜查詢的快速重新整理的物化檢視
- MySQL 查詢的成本的檢視MySql
- 【Oracle】如何查詢檢視時使用索引Oracle索引
- 檢視慢查詢進度
- 【MV】group by查詢子句是否包含count(*)對物化檢視重新整理的影響
- MYSQL學習筆記24: 多表查詢(聯合查詢,Union, Union All)MySql筆記
- 【PDB】Oracle跨PDB檢視查詢Oracle
- OushuDB 檢視查詢執行情況
- sql查詢檢視列備註SQL
- 查詢基表的相關檢視
- 資料庫的查詢與檢視資料庫
- 如何使用SQL查詢檢視，Postico使用技巧分享~SQL
- RAC：在子查詢使用gv$檢視，有時查詢不出資料
- 【ORACLE】常用物化檢視相關後設資料查詢語句Oracle
- PostgreSQL 原始碼解讀（30）- 查詢語句#15（查詢優化-扁平化處理UNION ALL）SQL原始碼優化
- 檢視 Laravel 查詢資料語句Laravel
- Linux查詢檢視幫助命令Linux
- MySQL中使用or、in與union all在查詢命令下的效率對比MySql
- 物化檢視妙用__表同步使用物化檢視方法
- 查詢表空間使用情況的簡單檢視
- 關於oracle使用者許可權查詢總結檢視Oracle
- mysql求交集：UNION ALL合併查詢，inner join內連線查詢，IN/EXISTS子查詢MySql
- mysql開啟檢視慢查詢日誌MySql
- 物化檢視和query_rewrite_enabled引數配合提高select查詢效能
- 使用dbms_advisor優化物化檢視優化