使⽤物化视图查询重写优化对于UNIONALL视图的CONNECTBY查询使⽤物化视图查询重写优化对于 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 |
connect和join的区别|  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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。