内联视图无法发生视图合并的几种情况
在优化SQL的时候，我们可能遇到各种奇奇怪怪的情况，当我们掌握一些原理性的内容后，我们对于SQL优化就会显得游刃有余。这就是我们需要掌握一些优化原理的意义，因为自己在优化方面也属于半路出家，写文章也是为了更好的学习，这篇文章会慢慢搜集一些相关资料，慢慢的进行扩展。
一：内联视图中使用伪列
1.正常情况下发生视图合并
select emp.ename, emp.deptno
from emp, (select deptno from dept where dname = 'SALES') v
where emp.deptno = v.deptno;
ENAME DEPTNO ---------- ---------- ALLEN 30 WARD 30 MARTIN 30 BLAKE 30 TURNER 30 JAMES 30
执行计划如下，通过以下执行计划可以判定，视图v发生了视图合并
2.当视图中含有rownum伪列时，无法发生视图合并
select emp.ename, emp.deptno
from emp, (select rownum rn,deptno from dept where dname = 'SALES') v
where emp.deptno = v.deptno;
ENAME DEPTNO ---------- ---------- ALLEN 30 WARD 30 MARTIN 30 BLAKE 30 TURNER 30 JAMES 30
##从这里的执行计划可以看到视图v并未发生视图合并
二：内联视图中使用union all
1.优化前SQL
这里提供的案例是内联视图使用union all无法进行视图合并，从id=8这个位置，可以看到视图QRY_WORKITEM_ALL并未发生合并，且谓词无法推入。
通过分析问题SQL，发现QRY_WORKITEM_ALL视图无法进行谓词推入，导致WORKITEM大表发生全表扫描执行效率低下，是该问题SQL主要负载问题点，原SQL如下，通过使用hint push_pred(QRY_WORKITEM_ALL)强制进行谓词推入，执行时间由原来的34.08s下降到0.03s，buffer get由原来的675K下降到22，read由原来的674K下降到13。SQL性能提升99%
SELECT 1
FROM BIZLOG BIZLOG
JOIN ROBXDJ ROBXDJ
ON BIZLOG.PARENTID = ROBXDJ.ROBXDJ_LCSL
LEFT OUTER JOIN QRY_WORKITEM_ALL QRY_WORKITEM_ALL
ON BIZLOG.WORKITEMID = QRY_WORKITEM_ALL.WORKITEMID
LEFT OUTER JOIN VW_RO_GSPUSER_SIGNINFO VW_RO_GSPUSER_SIGNINFO
ON QRY_WORKITEM_ALL.ACTUALPARTICIPANT = VW_RO_GSPUSER_SIGNINFO.UserID
WHERE ROBXDJ.ROBXDJ_NM = '99ae0d76-0bd4-4586-861d-33d6c21ba546';
2.优化后SQL
SELECT /*+ push_pred(QRY_WORKITEM_ALL)*/1
FROM BIZLOG BIZLOG
JOIN ROBXDJ ROBXDJ
ON BIZLOG.PARENTID = ROBXDJ.ROBXDJ_LCSL
LEFT OUTER JOIN QRY_WORKITEM_ALL QRY_WORKITEM_ALL
ON BIZLOG.WORKITEMID = QRY_WORKITEM_ALL.WORKITEMID
LEFT OUTER JOIN VW_RO_GSPUSER_SIGNINFO VW_RO_GSPUSER_SIGNINFO
ON QRY_WORKITEM_ALL.ACTUALPARTICIPANT = VW_RO_GSPUSER_SIGNINFO.UserID
WHERE ROBXDJ.ROBXDJ_NM = '99ae0d76-0bd4-4586-861d-33d6c21ba546';
附录视图相关创建代码
CREATE OR REPLACE FORCE EDITIONABLE VIEW "xxxxxxx"."QRY_WORKITEM_ALL" ("ACTIVITYDEFINITIONID", "ACTIVITYINSTANCEID", "COMPLETEDDATE", "DUEDATE", "WORKITEMNAME", "PARTICIPANT", "ACTUALPARTICIPANT", "ACTUALPARTICIPANTNAME", "PERFORMER", "PRIORITY", "PROCESSDEFINITIONID", "PROCESSINSTANCEID", "STARTEDDATE", "STATE", "STATUS", "TARGETDATE", "TOOLINDEX", "EXCUTEINDEX", "WORKITEMID", "PROCESSDEFINITIONNAME", "PROCESSINSTANCENAME", "PROCINSTDESCRI
PTION", "ROOTPROCINSTID", "BIZPROCID", "WORKTYPE", "PARTICIPANTNAME", "PICKUPDATE", "CREATOR", "CREATORNAME", "REFBIZID", "LASTMODIFIEDTIME") AS
(
SELECT
WR.ACTIVITYDEFINITIONID,
WR.ACTIVITYINSTANCEID,
WR.COMPLETEDDATE,
WR.DUEDATE,
WR.NAME AS WORKITEMNAME,
WR.PARTICIPANT,
WR.ACTUALPARTICIPANT,
WR.ACTUALPARTICIPANTNAME,
WR.PERFORMER,
WR.PRIORITY,
WR.PROCESSDEFINITIONID,
WR.PROCESSINSTANCEID,
WR.STARTEDDATE,
WR.STATE,
WR.STATUS,
WR.TARGETDATE,
WR.TOOLINDEX,
WR.EXCUTEINDEX,
WR.WORKITEMID,
WR.PROCESSDEFINITIONNAME,
WR.PROCESSINSTANCENAME,
WR.PROCINSTDESCRIPTION,
WR.ROOTPROCINSTID,
WR.BIZPROCID,
WR.WORKTYPE,
WR.PARTICIPANTNAME,
WR.PICKUPDATE,
WR.CREATOR,
WR.CREATORNAME,
PR.REFBIZID,
WR.LASTMODIFIEDTIME
FROM WORKITEM WR JOIN PROCESSINSTANCE PR
ON WR.PROCESSINSTANCEID = PR.PROCESSINSTANCEID)
UNION ALL
(SELECT
WB.ACTIVITYDEFINITIONID,
WB.ACTIVITYINSTANCEID,
WB.COMPLETEDDATE,
WB.DUEDATE,
WB.NAME AS WORKITEMNAME,
WB.PARTICIPANT,
WB.ACTUALPARTICIPANT,
WB.ACTUALPARTICIPANTNAME,
WB.PERFORMER,
WB.PRIORITY,
WB.PROCESSDEFINITIONID,
WB.PROCESSINSTANCEID,
WB.STARTEDDATE,
WB.STATE,
WB.STATUS,
WB.TARGETDATE,
WB.TOOLINDEX,
WB.EXCUTEINDEX,
WB.WORKITEMID,
WB.PROCESSDEFINITIONNAME,
WB.PROCESSINSTANCENAME,
WB.PROCINSTDESCRIPTION,
WB.ROOTPROCINSTID,
WB.BIZPROCID,
WB.WORKTYPE,
WB.PARTICIPANTNAME,
WB.PICKUPDATE,
WB.CREATOR,
WB.CREATORNAME,
PB.REFBIZID,
WB.LASTMODIFIEDTIME
FROM WORKITEMBACK WB JOIN PROCESSINSTANCEBACK PB
ON WB.PROCESSINSTANCEID = PB.PROCESSINSTANCEID)
;
