查看性能与性能的对比
查看性能与性能的对比
提问于 2018-06-25 19:29:01
我有一个从视图和多个表中选择的查询。
当我“按原样”执行这个查询时，性能非常差。
当我使用WITH CLAUSE获取视图的查询并将其添加到外部查询时，它运行得非常快。
我的测试表明，问题不在于视图的查询本身，而在于优化器将视图数据与其他数据连接的方式。
这个问题的解释是什么？
由于30K字符的限制，select查询中有一些缺少的字段。
--View:
CREATE OR REPLACE VIEW DWH.CC_MC_VIEW AS
SELECT /*+ full(E) */
DISTINCT/* a lot of fields */
FROM dwh.t2002_merkazei_tamhir_cur a
LEFT JOIN dwh.allovdim b
ON a.oved_achrai_char = TO_CHAR(b.mis_oved)
AND a.company = b.company
LEFT JOIN dwh.t2003_kodei_merkazei_tamhir h
ON h.year = TO_CHAR(SYSDATE-28,'yyyy')
AND h.erech = a.c01_agaf
AND h.company = A.company
AND h.amuda = '01'
LEFT JOIN dwh.t2003_kodei_merkazei_tamhir g
ON g.erech = a.c03_minhal_rb
AND g.amuda = '03'
AND g.year = TO_CHAR(SYSDATE-28,'yyyy')
AND g.company = A.company
LEFT JOIN DWH_COMPANIES E
ON A.COMPANY = E.COM_COMPANY_CODE
LEFT JOIN dwh.t2003_kodei_merkazei_tamhir d
ON d.erech = a.c02_minhalhativa
AND d.amuda = '02'
AND D.year = TO_CHAR(SYSDATE-28,'yyyy')
AND A.COMPANY = D.COMPANY
LEFT JOIN dwh.allovdim c
ON d.oved_achrai = c.mis_oved
AND d.company = c.company
LEFT JOIN DWH_COMPANIES F
ON D.COMPANY = F.COM_COMPANY_CODE
WHERE KOD_SGIRA = 1;视图查询(性能不佳)
(SELECT ROUND(dwh.plm_docdelivery.RELEASEDDATE-dwh.plm_docdelivery.PROCESSSTARTDATE),
ROUND(sysdate -dwh.plm_docdelivery.PROCESSSTARTDATE),
dwh.plm_docdelivery.*,
dwh.cc_mc_view.*,
dwh.plm_programs.*,
FROM dwh.plm_docdelivery,
DWH.PLM_PROGRAMS,
dwh.cc_mc_view,
dwh.allovdim ENG_OWNER,
dwh.plm_last_rev
WHERE
(
dwh.plm_docdelivery.PROGRAMNUMBER=dwh.plm_programs.PROGRAMNUMBER
AND dwh.plm_docdelivery.COMPANY=dwh.plm_programs.COMPANY
AND dwh.plm_docdelivery.LOGISTICCOMPANY=dwh.plm_programs.LOGISTICCOMPANY
)
AND
(
dwh.plm_docdelivery.ENGOWNERID= ENG_OWNER.EMP_ID(+)
)
AND
(
dwh.cc_mc_view.MERKAZ_TAMHIR(+)=ENG_OWNER.MIS_TAMH
AND dwh.cc_mc_view.COMPANY(+) =ENG_OWNER.COMPANY
)
AND
(
dwh.plm_last_rev.COMPANY=dwh.plm_docdelivery.COMPANY
AND dwh.plm_last_rev.LOGISTICCOMPANY=dwh.plm_docdelivery.LOGISTICCOMPANY
AND dwh.plm_last_rev.PROGRAMNUMBER=dwh.plm_docdelivery.PROGRAMNUMBER
AND dwh.plm_last_rev.DOCUMENT=dwh.plm_docdelivery.DOCUMENT
)
AND
(ENG_OWNER.COMPANY<>'SOL')
AND
(dwh.plm_docdelivery.PROGRAMNUMBER IN ('X395','X419','X422','X379','X454','X372','X308', 'X394','X426','X433','X423','X391','X391A','X393','X490','X510','X460','X466A','X466B', 'X466C','X448','T622','T610','X461','X470','X520','X436','X560','D308','X922B','X922','X435', 'X494','X402','X995SP','X493','X481','X990','T799')
AND
(dwh.plm_docdelivery.CDRLTYPE <> 'SDRL'
OR dwh.plm_docdelivery.CDRLTYPE IS NULL)
AND dwh.plm_last_rev.LAST_REV = dwh.plm_docdelivery.DOCUMENTREVISION
AND((dwh.plm_docdelivery.AUTHORDATE <= '16-07-2017 00:00:00'
AND(dwh.plm_docdelivery.STATUS IN ( '(Freeze)','(Released)' )
OR dwh.plm_docdelivery.STATUS IS NULL))
OR dwh.plm_docdelivery.RELEASEDDATE IS NOT NULL)
AND dwh.plm_programs.COMPANY IN ('ESL','TCL'))
MINUS
SELECT ROUND(dwh.plm_docdelivery.RELEASEDDATE-dwh.plm_docdelivery.PROCESSSTARTDATE),
ROUND(sysdate -dwh.plm_docdelivery.PROCESSSTARTDATE),
dwh.plm_docdelivery.STATUS,
dwh.plm_docdelivery.SIGNERSLEFT,
dwh.plm_docdelivery.RELEASEDDATE,
dwh.plm_docdelivery.PROGRAMNUMBER,
dwh.plm_programs.PROGRAMNAME,
dwh.cc_mc_view.MINHALHATIVA,
TO_CHAR(dwh.plm_docdelivery.PLANNEDVALUE)
||
dwh.plm_docdelivery.TIMEUNIT
||
' '
||
CASE
WHEN dwh.plm_docdelivery.PLANNEDRELATION='After'
THEN '+'
WHEN dwh.plm_docdelivery.PLANNEDRELATION='Before'
THEN '-'
ELSE ''
END
||
dwh.plm_docdelivery.CDRLGATENAME,
ENG_OWNER.MISHPAHA,
ENG_OWNER.MISHPAHA_ENG,
ENG_OWNER.PRATI,
ENG_OWNER.PRATI_ENG,
dwh.plm_docdelivery.ENGOWNERID,
dwh.plm_docdelivery.ENGDISCIPLINE,
dwh.cc_mc_view.ACHRAI_HATIVA_NAME,
dwh.cc_mc_view.OVED_ACHRAI_HATIVA_FULL,
dwh.cc_mc_view.ACHRAI_MT_NAME,
dwh.cc_mc_view.OVED_ACHRAI_MT_FULL,
dwh.plm_docdelivery.DOCUMENTREVISION,
dwh.plm_docdelivery.DOCUMENTNAME,
dwh.plm_docdelivery.DOCUMENT,
dwh.plm_docdelivery.DELIVERY,
dwh.plm_docdelivery.CURRENTUSER ,
dwh.cc_mc_view.MERKAZ_TAMHIR,
dwh.plm_docdelivery.CDRLTYPE,
dwh.plm_docdelivery.CDRL,
dwh.plm_docdelivery.AUTHORDATE,
dwh.plm_docdelivery.DOC_REV_CREATION_DATE,
dwh.plm_docdelivery.DELIVERY_CREATION_DATE,
dwh.plm_docdelivery.GATEORIGINALDATE,
dwh.plm_programs.STATUS_DATE,
dwh.plm_docdelivery.ProcedureName,
dwh.plm_last_rev.LAST_REV,
dwh.plm_programs.COMPANY
FROM dwh.plm_docdelivery,
DWH.PLM_PROGRAMS,
dwh.cc_mc_view,
dwh.allovdim ENG_OWNER,
dwh.plm_last_rev
WHERE
(dwh.plm_docdelivery.PROGRAMNUMBER =dwh.plm_programs.PROGRAMNUMBER
AND dwh.plm_docdelivery.COMPANY =dwh.plm_programs.COMPANY
AND dwh.plm_docdelivery.LOGISTICCOMPANY=dwh.plm_programs.LOGISTICCOMPANY)
AND
(dwh.plm_docdelivery.ENGOWNERID= ENG_OWNER.EMP_ID(+))
AND
(dwh.cc_mc_view.MERKAZ_TAMHIR(+)=ENG_OWNER.MIS_TAMH
AND dwh.cc_mc_view.COMPANY(+) =ENG_OWNER.COMPANY)
AND
(dwh.plm_last_rev.COMPANY =dwh.plm_docdelivery.COMPANY
AND dwh.plm_last_rev.LOGISTICCOMPANY=dwh.plm_docdelivery.LOGISTICCOMPANY
AND dwh.plm_last_rev.PROGRAMNUMBER =dwh.plm_docdelivery.PROGRAMNUMBER
AND dwh.plm_last_rev.DOCUMENT =dwh.plm_docdelivery.DOCUMENT)
AND
(ENG_OWNER.COMPANY<>'SOL')
AND
(dwh.plm_docdelivery.PROGRAMNUMBER IN ('X395','X419','X422','X379','X454','X372','X308', 'X394','X426','X433','X423','X391','X391A','X393','X490','X510','X460','X466A','X466B', 'X466C','X448','T622','T610','X461','X470','X520','X436','X560','D308','X922B','X922','X435', 'X494','X402','X995SP','X493','X481','X990','T799')
AND dwh.plm_docdelivery.DELIVERY IS NOT NULL
AND dwh.plm_docdelivery.DOCUMENT IS NULL
AND dwh.plm_programs.COMPANY IN ('CMP1','CMP2')))解释视图的计划(性能不佳)
Plan hash value: 822592127
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation| Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 57 | 52896 | 32848 (2)| 00:01:39 |
| 1 | MINUS | | | | | |
| 2 | SORT UNIQUE | | 57 | 26448 | 16424 (2)| 00:00:50 |
| 3 | NESTED LOOPS OUTER | | 57 | 26448 | 16423 (2)| 00:00:50 |
| 4 | NESTED LOOPS | | 20 | 5540 | 14146 (3)| 00:00:43 |
|* 5 | HASH JOIN | | 20 | 4660 | 14106 (3)| 00:00:43 |
|* 6 | TABLE ACCESS FULL | PLM_LAST_REV | 50 | 1300 | 13959 (3)| 00:00:42 |
| 7 | NESTED LOOPS | | 798 | 161K| 147 (2)| 00:00:01 |
| 8 | NESTED LOOPS | | 798 | 161K| 147 (2)| 00:00:01 |
|* 9 | TABLE ACCESS FULL| PLM_PROGRAMS | 44 | 1496 | 14 (8)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | IDX_DELIVERY_1 | 1 | | 2 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID | PLM_DOCDELIVERY | 18 | 3114 | 3 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | ALLOVDIM | 1 | 44 | 2 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | ALLOVDIM_I_EMP_CMP | 1 | | 1 (0)| 00:00:01 |
| 14 | VIEW PUSHED PREDICATE| CC_MC_VIEW | 1 | 187 | 114 (2)| 00:00:01 |
| 15 | SORT UNIQUE | | 1 | 217 | 114 (2)| 00:00:01 |
|* 16 | FILTER | | | | | |
| 17 | NESTED LOOPS OUTER| | 1 | 217 | 113 (1)| 00:00:01 |
| 18 | NESTED LOOPS OUTER | | 1 | 173 | 97 (2)| 00:00:01 |
|* 19 | HASH JOIN OUTER | | 1 | 167 | 96 (2)| 00:00:01 |
| 20 | NESTED LOOPS OUTER | | 1 | 161 | 48 (0)| 00:00:01 |
| 21 | NESTED LOOPS OUTER | | 1 | 117 | 46 (0)| 00:00:01 |
| 22 | NESTED LOOPS OUTER | | 1 | 90 | 32 (0)| 00:00:01 |
| 23 | NESTED LOOPS OUTER | | 1 | 65 | 18 (0)| 00:00:01 |
|* 24 | TABLE ACCESS BY INDEX ROWID| T2002_MERKAZEI_TAMHIR_CUR | 1 | 40 | 4 (0)| 00:00:01 |
|* 25 |INDEX RANGE SCAN | T2002_MERKAZEI_TAMHIR_CUR_I1 | 3 | | 1 (0)| 00:00:01 |
| 26 | TABLE ACCESS BY INDEX ROWID| T2003_KODEI_MERKAZEI_TAMHIR | 1 | 25 | 14 (0)| 00:00:01 |
|* 27 |INDEX RANGE SCAN | T2003_K_MT97_Y_AE_I | 1 | | 13 (0)| 00:00:01 |
| 28 | TABLE ACCESS BY INDEX ROWID | T2003_KODEI_MERKAZEI_TAMHIR | 1 | 25 | 14 (0)| 00:00:01 |
|* 29 | INDEX RANGE SCAN | T2003_K_MT97_Y_AE_I | 1 | | 13 (0)| 00:00:01 |
| 30 | TABLE ACCESS BY INDEX ROWID | T2003_KODEI_MERKAZEI_TAMHIR | 1 | 27 | 14 (0)| 00:00:01 |
|* 31 | INDEX RANGE SCAN | T2003_K_MT97_Y_AE_I | 1 | | 13 (0)| 00:00:01 |
| 32 | TABLE ACCESS BY INDEX ROWID | ALLOVDIM | 1 | 44 | 2 (0)| 00:00:01 |
|* 33 | INDEX RANGE SCAN | ALLOVDIM_I_CMP_MIS | 1 | | 1 (0)| 00:00:01 |
|* 34 | TABLE ACCESS FULL | DWH_COMPANIES| 1 | 6 | 47 (0)| 00:00:01 |
| 35 | TABLE ACCESS BY INDEX ROWID | DWH_COMPANIES| 1 | 6 | 1 (0)| 00:00:01 |
|* 36 | INDEX UNIQUE SCAN | COMP_IDX1 | 1 | | 0 (0)| 00:00:01 |
| 37 | TABLE ACCESS BY INDEX ROWID | ALLOVDIM | 1 | 44 | 16 (0)| 00:00:01 |
|* 38 | INDEX RANGE SCAN| ALLOVDIM_I_CMP_MIS | 1 | | 15 (0)| 00:00:01 |
| 39 | SORT UNIQUE | | 57 | 26448 | 16424 (2)| 00:00:50 |
| 40 | NESTED LOOPS OUTER | | 57 | 26448 | 16423 (2)| 00:00:50 |
| 41 | NESTED LOOPS | | 20 | 5540 | 14145 (3)| 00:00:43 |
|* 42 | HASH JOIN | | 20 | 4660 | 14105 (3)| 00:00:43 |
|* 43 | TABLE ACCESS FULL | PLM_LAST_REV | 50 | 1300 | 13959 (3)| 00:00:42 |
| 44 | NESTED LOOPS | | 64 | 13248 | 146 (1)| 00:00:01 |
| 45 | NESTED LOOPS | | 64 | 13248 | 146 (1)| 00:00:01 |
|* 46 | TABLE ACCESS FULL| PLM_PROGRAMS | 44 | 1496 | 14 (8)| 00:00:01 |
|* 47 | INDEX RANGE SCAN | IDX_DELIVERY_1 | 1 | | 2 (0)| 00:00:01 |
|* 48 | TABLE ACCESS BY INDEX ROWID | PLM_DOCDELIVERY | 1 | 173 | 3 (0)| 00:00:01 |
| 49 | TABLE ACCESS BY INDEX ROWID | ALLOVDIM | 1 | 44 | 2 (0)| 00:00:01 |
|* 50 | INDEX RANGE SCAN | ALLOVDIM_I_EMP_CMP | 1 | | 1 (0)| 00:00:01 |
| 51 | VIEW PUSHED PREDICATE| CC_MC_VIEW | 1 | 187 | 114 (2)| 00:00:01 |
| 52 | SORT UNIQUE | | 1 | 217 | 114 (2)| 00:00:01 |
|* 53 | FILTER | | | | | |
| 54 | NESTED LOOPS OUTER| | 1 | 217 | 113 (1)| 00:00:01 |
| 55 | NESTED LOOPS OUTER | | 1 | 173 | 97 (2)| 00:00:01 |
|* 56 | HASH JOIN OUTER | | 1 | 167 | 96 (2)| 00:00:01 |
| 57 | NESTED LOOPS OUTER | | 1 | 161 | 48 (0)| 00:00:01 |
| 58 | NESTED LOOPS OUTER | | 1 | 117 | 46 (0)| 00:00:01 |
| 59 | NESTED LOOPS OUTER | | 1 | 90 | 32 (0)| 00:00:01 |
| 60 | NESTED LOOPS OUTER | | 1 | 65 | 18 (0)| 00:00:01 |
|* 61 | TABLE ACCESS BY INDEX ROWID| T2002_MERKAZEI_TAMHIR_CUR | 1 | 40 | 4 (0)| 00:00:01 |
|* 62 |INDEX RANGE SCAN | T2002_MERKAZEI_TAMHIR_CUR_I1 | 3 | | 1 (0)| 00:00:01 |
| 63 | TABLE ACCESS BY INDEX ROWID| T2003_KODEI_MERKAZEI_TAMHIR | 1 | 25 | 14 (0)| 00:00:01 |
|* 64 |INDEX RANGE SCAN | T2003_K_MT97_Y_AE_I | 1 | | 13 (0)| 00:00:01 |
| 65 | TABLE ACCESS BY INDEX ROWID | T2003_KODEI_MERKAZEI_TAMHIR | 1 | 25 | 14 (0)| 00:00:01 |
|* 66 | INDEX RANGE SCAN | T2003_K_MT97_Y_AE_I | 1 | | 13 (0)| 00:00:01 |
| 67 | TABLE ACCESS BY INDEX ROWID | T2003_KODEI_MERKAZEI_TAMHIR | 1 | 27 | 14 (0)| 00:00:01 |
|* 68 | INDEX RANGE SCAN | T2003_K_MT97_Y_AE_I | 1 | | 13 (0)| 00:00:01 |
| 69 | TABLE ACCESS BY INDEX ROWID | ALLOVDIM | 1 | 44 | 2 (0)| 00:00:01 |
|* 70 | INDEX RANGE SCAN | ALLOVDIM_I_CMP_MIS | 1 | | 1 (0)| 00:00:01 |
|* 71 | TABLE ACCESS FULL | DWH_COMPANIES| 1 | 6 | 47 (0)| 00:00:01 |
| 72 | TABLE ACCESS BY INDEX ROWID | DWH_COMPANIES| 1 | 6 | 1 (0)| 00:00:01 |
|* 73 | INDEX UNIQUE SCAN | COMP_IDX1 | 1 | | 0 (0)| 00:00:01 |
| 74 | TABLE ACCESS BY INDEX ROWID | ALLOVDIM | 1 | 44 | 16 (0)| 00:00:01 |
|* 75 | INDEX RANGE SCAN| ALLOVDIM_I_CMP_MIS | 1 | | 15 (0)| 00:00:01 |Variant with子句(良好性能)
with CC as
(SELECT /*+full(E) */
DISTINCT/* a lot of fields */
FROM dwh.t2002_merkazei_tamhir_cur a
LEFT JOIN dwh.allovdim b
ON a.oved_achrai_char = TO_CHAR(b.mis_oved)
AND a.company = b.company
LEFT JOIN dwh.t2003_kodei_merkazei_tamhir h
ON h.year = TO_CHAR(SYSDATE-28,'yyyy')
AND h.erech = a.c01_agaf
AND h.company = A.company
AND h.amuda = '01'
LEFT JOIN dwh.t2003_kodei_merkazei_tamhir g
ON g.erech = a.c03_minhal_rb
AND g.amuda = '03'
AND g.year = TO_CHAR(SYSDATE-28,'yyyy')
AND g.company = A.company
LEFT JOIN DWH_COMPANIES E
ON A.COMPANY = E.COM_COMPANY_CODE
LEFT JOIN dwh.t2003_kodei_merkazei_tamhir d
ON d.erech = a.c02_minhalhativa
AND d.amuda = '02'
AND D.year = TO_CHAR(SYSDATE-28,'yyyy')
AND A.COMPANY = D.COMPANY
LEFT JOIN dwh.allovdim c
ON d.oved_achrai = c.mis_oved
AND d.company = c.company
LEFT JOIN DWH_COMPANIES F
on D.COMPANY = F.COM_COMPANY_CODE
WHERE KOD_SGIRA = 1
)
(
SELECT ROUND(dwh.plm_docdelivery.RELEASEDDATE-dwh.plm_docdelivery.PROCESSSTARTDATE),
ROUND(sysdate -dwh.plm_docdelivery.PROCESSSTARTDATE),
dwh.plm_docdelivery.*,
dwh.cc_mc_view.*,
dwh.plm_programs.*,
FROM dwh.plm_docdelivery,
DWH.PLM_PROGRAMS,
cc,
dwh.allovdim ENG_OWNER,
dwh.plm_last_rev
WHERE
(
dwh.plm_docdelivery.PROGRAMNUMBER=dwh.plm_programs.PROGRAMNUMBER
AND dwh.plm_docdelivery.COMPANY=dwh.plm_programs.COMPANY
AND dwh.plm_docdelivery.LOGISTICCOMPANY=dwh.plm_programs.LOGISTICCOMPANY
)
AND
(dwh.plm_docdelivery.ENGOWNERID= ENG_OWNER.EMP_ID(+))
AND
(cc.MERKAZ_TAMHIR(+)=ENG_OWNER.MIS_TAMH
AND cc.COMPANY(+) =ENG_OWNER.COMPANY)
AND
(dwh.plm_last_rev.COMPANY =dwh.plm_docdelivery.COMPANY
AND dwh.plm_last_rev.LOGISTICCOMPANY=dwh.plm_docdelivery.LOGISTICCOMPANY
AND dwh.plm_last_rev.PROGRAMNUMBER =dwh.plm_docdelivery.PROGRAMNUMBER
AND dwh.plm_last_rev.DOCUMENT =dwh.plm_docdelivery.DOCUMENT)
AND
(ENG_OWNER.COMPANY<>'SOL')
AND
(dwh.plm_docdelivery.PROGRAMNUMBER IN ('X395','X419','X422','X379','X454','X372','X308', 'X394','X426','X433','X423','X391','X391A','X393','X490','X510','X460','X466A','X466B', 'X466C','X448','T622','T610','X461','X470','X520','X436','X560','D308','X922B','X922','X435', 'X494','X402','X995SP','X493','X481','X990','T799')
AND
(dwh.plm_docdelivery.CDRLTYPE <> 'SDRL'
OR dwh.plm_docdelivery.CDRLTYPE IS NULL)
AND dwh.plm_last_rev.LAST_REV = dwh.plm_docdelivery.DOCUMENTREVISION
AND((dwh.plm_docdelivery.AUTHORDATE <= '16-07-2017 00:00:00'
AND(dwh.plm_docdelivery.STATUS IN ( '(Freeze)','(Released)' )
OR dwh.plm_docdelivery.STATUS IS NULL))
OR dwh.plm_docdelivery.RELEASEDDATE IS NOT NULL)
AND dwh.plm_programs.COMPANY IN ('ESL','TCL'))
MINUS
SELECT ROUND(dwh.plm_docdelivery.RELEASEDDATE-dwh.plm_docdelivery.PROCESSSTARTDATE),
ROUND(sysdate-dwh.plm_docdelivery.PROCESSSTARTDATE),
dwh.plm_docdelivery.STATUS,
dwh.plm_docdelivery.SIGNERSLEFT,
dwh.plm_docdelivery.RELEASEDDATE,
dwh.plm_docdelivery.PROGRAMNUMBER,
dwh.plm_programs.PROGRAMNAME,
cc.MINHALHATIVA,
TO_CHAR(dwh.plm_docdelivery.PLANNEDVALUE)
||
dwh.plm_docdelivery.TIMEUNIT
||
' '
||
CASE
WHEN dwh.plm_docdelivery.PLANNEDRELATION='After'
THEN '+'
WHEN dwh.plm_docdelivery.PLANNEDRELATION='Before'
THEN '-'
ELSE ''
END
||
dwh.plm_docdelivery.CDRLGATENAME,
ENG_OWNER.MISHPAHA,
ENG_OWNER.MISHPAHA_ENG,
ENG_OWNER.PRATI,
ENG_OWNER.PRATI_ENG,
dwh.plm_docdelivery.ENGOWNERID,
dwh.plm_docdelivery.ENGDISCIPLINE,
cc.ACHRAI_HATIVA_NAME,
cc.OVED_ACHRAI_HATIVA_FULL,
cc.ACHRAI_MT_NAME,
cc.OVED_ACHRAI_MT_FULL,
dwh.plm_docdelivery.DOCUMENTREVISION,
dwh.plm_docdelivery.DOCUMENTNAME,
dwh.plm_docdelivery.DOCUMENT,
dwh.plm_docdelivery.DELIVERY,
dwh.plm_docdelivery.CURRENTUSER ,
cc.MERKAZ_TAMHIR,
dwh.plm_docdelivery.CDRLTYPE,
dwh.plm_docdelivery.CDRL,
dwh.plm_docdelivery.AUTHORDATE,
dwh.plm_docdelivery.DOC_REV_CREATION_DATE,
dwh.plm_docdelivery.DELIVERY_CREATION_DATE,
dwh.plm_docdelivery.GATEORIGINALDATE,
dwh.plm_programs.STATUS_DATE,
dwh.plm_docdelivery.ProcedureName,
dwh.plm_last_rev.LAST_REV,
dwh.plm_programs.COMPANY
FROM dwh.plm_docdelivery,DWH.PLM_PROGRAMS,cc,dwh.allovdim ENG_OWNER,dwh.plm_last_rev
WHERE(dwh.plm_docdelivery.PROGRAMNUMBER=dwh.plm_programs.PROGRAMNUMBER
AND dwh.plm_docdelivery.COMPANY=dwh.plm_programs.COMPANY
AND dwh.plm_docdelivery.LOGISTICCOMPANY=dwh.plm_programs.LOGISTICCOMPANY)
AND(dwh.plm_docdelivery.ENGOWNERID=ENG_OWNER.EMP_ID(+))
AND(cc.MERKAZ_TAMHIR(+)=ENG_OWNER.MIS_TAMH
AND cc.COMPANY(+)=ENG_OWNER.COMPANY)
AND(dwh.plm_last_rev.COMPANY=dwh.plm_docdelivery.COMPANY
AND dwh.plm_last_rev.LOGISTICCOMPANY=dwh.plm_docdelivery.LOGISTICCOMPANY
AND dwh.plm_last_rev.PROGRAMNUMBER=dwh.plm_docdelivery.PROGRAMNUMBER
AND dwh.plm_last_rev.DOCUMENT=dwh.plm_docdelivery.DOCUMENT)
AND(ENG_OWNER.COMPANY<>'SOL')
AND(dwh.plm_docdelivery.PROGRAMNUMBER IN ('X395','X419','X422','X379','X454','X372','X308', 'X394','X426','X433','X423','X391','X391A','X393','X490','X510','X460','X466A','X466B', 'X466C','X448','T622','T610','X461','X470','X520','X436','X560','D308','X922B','X922','X435', 'X494','X402','X995SP','X493','X481','X990','T799')
AND dwh.plm_docdelivery.DELIVERY IS NOT NULL
AND dwh.plm_docdelivery.DOCUMENT IS NULL
AND dwh.plm_programs.COMPANY IN ('CMP1','CMP2'))); 解释WITH子句的计划
Plan hash value: 2179438877
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 18560 | | 28536 (3)| 00:01:26 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FDA4CB1D_E5C2B79E | | | | | |
| 3 | HASH UNIQUE | | 5692 | 1206K| 1312K| 4670 (2)| 00:00:15 |
|* 4 | HASH JOIN RIGHT OUTER | | 5692 | 1206K| | 4245 (2)| 00:00:13 |
| 5 | TABLE ACCESS FULL| DWH_COMPANIES | 101 | 606 | | 47 (0)| 00:00:01 |
|* 6 | HASH JOIN OUTER | | 5691 | 1172K| | 4197 (2)| 00:00:13 |
|* 7 | HASH JOIN OUTER | | 5691 | 928K| | 2452 (2)| 00:00:08 |
|* 8 | HASH JOIN RIGHT OUTER | | 5691 | 683K| | 706 (1)| 00:00:03 |
| 9 | TABLE ACCESS FULL | DWH_COMPANIES | 101 | 606 | | 47 (0)| 00:00:01 |
|* 10 | HASH JOIN RIGHT OUTER | | 5691 | 650K| | 659 (1)| 00:00:02 |
| 11 | TABLE ACCESS BY INDEX ROWID | T2003_KODEI_MERKAZEI_TAMHIR | 25 | 675 | | 37 (0)| 00:00:01 |
|* 12 | INDEX SKIP SCAN | T2003_K_MT97_Y_AE_I | 25 | | | 34 (0)| 00:00:01 |
|* 13 | HASH JOIN RIGHT OUTER | | 5691 | 500K| | 621 (1)| 00:00:02 |
| 14 | TABLE ACCESS BY INDEX ROWID | T2003_KODEI_MERKAZEI_TAMHIR | 25 | 625 | | 37 (0)| 00:00:01 |
|* 15 | INDEX SKIP SCAN | T2003_K_MT97_Y_AE_I | 25 | | | 34 (0)| 00:00:01 |
|* 16 | HASH JOIN RIGHT OUTER | | 5691 | 361K| | 584 (1)| 00:00:02 |
| 17 | TABLE ACCESS BY INDEX ROWID| T2003_KODEI_MERKAZEI_TAMHIR | 25 | 625 | | 37 (0)| 00:00:01 |
|* 18 | INDEX SKIP SCAN | T2003_K_MT97_Y_AE_I | 25 | | | 34 (0)| 00:00:01 |
|* 19 | TABLE ACCESS FULL | T2002_MERKAZEI_TAMHIR_CUR | 5691 | 222K| | 547 (1)| 00:00:02 |
| 20 | TABLE ACCESS FULL | ALLOVDIM | 51269 | 2202K| | 1744 (2)| 00:00:06 |
| 21 | TABLE ACCESS FULL | ALLOVDIM | 51269 | 2202K| | 1744 (2)| 00:00:06 |
| 22 | MINUS | | | | | | |
| 23 | SORT UNIQUE | | 20 | 9280 | | 14268 (3)| 00:00:43 |
|* 24 | HASH JOIN OUTER | | 20 | 9280 | | 14267 (3)| 00:00:43 |
| 25 | NESTED LOOPS | | 20 | 5540 | | 14146 (3)| 00:00:43 |
| 26 | NESTED LOOPS | | 20 | 5540 | | 14146 (3)| 00:00:43 |
|* 27 | HASH JOIN | | 20 | 4660 | | 14106 (3)| 00:00:43 |
|* 28 | TABLE ACCESS FULL | PLM_LAST_REV| 50 | 1300 | | 13959 (3)| 00:00:42 |
| 29 | NESTED LOOPS | | 798 | 161K| | 147 (2)| 00:00:01 |
| 30 | NESTED LOOPS | | 798 | 161K| | 147 (2)| 00:00:01 |
|* 31 | TABLE ACCESS FULL | PLM_PROGRAMS| 44 | 1496 | | 14 (8)| 00:00:01 |
|* 32 | INDEX RANGE SCAN | IDX_DELIVERY_1 | 1 | | | 2 (0)| 00:00:01 |
|* 33 | TABLE ACCESS BY INDEX ROWID | PLM_DOCDELIVERY | 18 | 3114 | | 3 (0)| 00:00:01 |
|* 34 | INDEX RANGE SCAN | ALLOVDIM_I_EMP_CMP | 1 | | | 1 (0)| 00:00:01 |
| 35 | TABLE ACCESS BY INDEX ROWID | ALLOVDIM | 1 | 44 | | 2 (0)| 00:00:01 |
|* 36 | VIEW | | 5692 | 1039K| | 121 (1)| 00:00:01 |
| 37 | TABLE ACCESS FULL | SYS_TEMP_0FDA4CB1D_E5C2B79E | 5692 | 1206K| | 121 (1)| 00:00:01 |
| 38 | SORT UNIQUE | | 20 | 9280 | | 14268 (3)| 00:00:43 |
|* 39 | HASH JOIN OUTER | | 20 | 9280 | | 14267 (3)| 00:00:43 |
| 40 | NESTED LOOPS | | 20 | 5540 | | 14145 (3)| 00:00:43 |
| 41 | NESTED LOOPS | | 20 | 5540 | | 14145 (3)| 00:00:43 |
|* 42 | HASH JOIN | | 20 | 4660 | | 14105 (3)| 00:00:43 |
|* 43 | TABLE ACCESS FULL | PLM_LAST_REV| 50 | 1300 | | 13959 (3)| 00:00:42 |
| 44 | NESTED LOOPS | | 64 | 13248 | | 146 (1)| 00:00:01 |
| 45 | NESTED LOOPS | | 64 | 13248 | | 146 (1)| 00:00:01 |
|* 46 | TABLE ACCESS FULL | PLM_PROGRAMS| 44 | 1496 | | 14 (8)| 00:00:01 |
|* 47 | INDEX RANGE SCAN | IDX_DELIVERY_1 | 1 | | | 2 (0)| 00:00:01 |
|* 48 | TABLE ACCESS BY INDEX ROWID | PLM_DOCDELIVERY | 1 | 173 | | 3 (0)| 00:00:01 |
|* 49 | INDEX RANGE SCAN | ALLOVDIM_I_EMP_CMP | 1 | | | 1 (0)| 00:00:01 |
| 50 | TABLE ACCESS BY INDEX ROWID | ALLOVDIM | 1 | 44 | | 2 (0)| 00:00:01 |
|* 51 | VIEW | | 5692 | 1039K| | 121 (1)| 00:00:01 |
| 52 | TABLE ACCESS FULL | SYS_TEMP_0FDA4CB1D_E5C2B79E | 5692 | 1206K| | 121 (1)| 00:00:01 |回答 2
Stack Overflow用户
发布于 2018-06-25 21:12:30
“一般来说，我想知道为什么视图和with之间存在差异。”
在调优过程中，有一些非常普遍适用的规则。在很大程度上取决于具体细节。因此，在您的情况下，相关点是第二个解释计划中的这一步：
TEMP TABLE TRANSFORMATION 基本上，调优连接表和视图的查询是很困难的，因为优化器不能真正确定视图将返回多少行。这通常会导致一个糟糕的计划，就像这里一样。如果您查看第一个计划，优化器选择了VIEW PUSHED PREDICATE。这意味着它将为其他表中的每一行查询一次视图。两次，因为负运算符。因为优化器将谓词推入视图查询(ENG_OWNER.MIS_TAMH, ENG_OWNER.COMPANY)中，所以它使用一系列嵌套循环来执行视图查询。对于大型表，这可能是一个开销很大的操作。
然而，使用with子句，优化器可以看到一个查询中的所有表，并可以决定将子查询结果集具体化为临时表。临时表并不总是高性能的，因为它们涉及到对临时表空间的写入和读取，而且许多DBA不会费心去适当地配置它们的表空间。然而，物化的最大优点是视图查询只执行一次，并且(假设结果集足够小)读取一次并在内存中维护。
由于物化似乎可以工作，您可以使用提示。而不是包含来自视图的整个查询的WITH子句
with CC_MC_VIEW as (select /*+ materialize */
from dwh.CC_MC_VIEW )
select ...Stack Overflow用户
发布于 2018-06-25 19:53:59
一般来说，在Oracle上应该没有区别。With子句可以是INLINEed或MATERIALIZEd。查看如何使用这些提示的文档。视图如何“嵌入”到执行计划中的方式也可以修改。查看文档和搜索参数complex_view_merging/simple_view_merging。
你能做的最好的事情就是比较两者的执行计划。如果看不到他们，没人能帮你。理论上，Oracle优化器应该为这两种情况生成相同的exec计划，前提是它们应该返回相同的结果。
更新:在第二种情况下，视图是物化的(参见临时表转换)。Oracle将视图查询上的内容存储到临时表空间中，并将其用于进一步联接。从理论上讲，这应该比内联(第一种情况)慢。
正如@William指出的那样，INLINE/MATERIALIZE提示是官方未记录的。不幸的是，其他参数也没有文档记录。尝试使用sqlplus (而不是SqlDeveloper) 4x执行慢查询，并通过设置(所有4个组合/true/false)更改CBO参数
alter session set "_complex_view_merging"=true;
alter session set "_simple_view_merging"=true;看看会发生什么。而且可能值得尝试从视图定义中删除完整的提示。您永远不会知道您将如何使用视图，将使用哪些谓词，因此可能没有必要对表DWH_COMPANIES执行“全表扫描”。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51022427
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号