問題描述：
Oracle 庫里有一視圖 A，select * from A ；1秒出結果。 回傳資料370條。
我新建了一張表B， 執行 insert into B select * from A 卻執行了6分30秒。
下面是視圖的執行計劃和insert into 的執行計劃，求高手指點。
視圖的：
insert into 的
uj5u.com熱心網友回復：
這么復雜的select能叫select * from A。。。從insert執行6分鐘來看，select不可能1秒出的，之所以一秒出，是因為plsql developer自作主張只給你回傳頭幾行的關系，你的性能問題在于select，而不是insert。
執行計劃不完整，沒法分析更多，先在最后一個經歷TABLE ACCESS FULL的表的關聯欄位上加上索引試試。
uj5u.com熱心網友回復：
視圖內SQL：SELECT
TO_NUMBER(TO_CHAR(C.TRD_DT, 'YYYYMMDD')) AS JYRQ,
A.TRD_CODE AS ZSDM,
A.CHI_NAME AS ZSMC,
A.ENG_NAME AS ZSYW,
B.TRD_CODE AS ZQDM,
B.SECU_SHT AS ZQMC,
D.ENG_NAME AS ZQYW,
CASE
WHEN D.EXCH_CODE = 101 THEN
'1'
WHEN D.EXCH_CODE = 105 THEN
'2'
END AS ZQSC,
C.CLS_PRC AS JSPJ,
E.TTL_SHR AS ZGB,
NULL AS JSGB,
f.wt_val AS QZYZ,
C.TMKT_VAL AS ZSZ,
NULL AS JSSZ,
f.wt_val AS QZ,
'CNY' AS JYHB
FROM IDX_BAS_INFO A
JOIN IDX_COMP B --指數成分構成
ON A.SECU_ID = B.IDX_SECU_ID
LEFT join idx_comp_wt F
join (select IDX_SECU_ID ,max(trd_dt)as trd_dt from idx_comp_wt group by IDX_SECU_ID ) FF
on f.IDX_SECU_ID=ff.IDX_SECU_ID and f.trd_dt=ff.trd_dt
on B.IDX_SECU_ID=F.IDX_SECU_ID and b.secu_id=f.secu_id
LEFT JOIN STKCN_BAS_INFO D
--ON F.SECU_ID = D.SECU_ID
ON B.SECU_ID=D.SECU_ID
LEFT JOIN STKCN_MKT_QUOT_IDX C
JO
視圖執行計劃：
Plan Hash Value : 3421027833
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99 | 36729 | 1515 | 00:00:19 |
| * 1 | HASH JOIN OUTER | | 99 | 36729 | 1515 | 00:00:19 |
| 2 | NESTED LOOPS OUTER | | 95 | 34200 | 1357 | 00:00:17 |
| 3 | NESTED LOOPS OUTER | | 95 | 30685 | 311 | 00:00:04 |
| * 4 | HASH JOIN RIGHT OUTER | | 95 | 19570 | 216 | 00:00:03 |
| 5 | VIEW | | 2 | 78 | 55 | 00:00:01 |
| 6 | NESTED LOOPS | | 2 | 88 | 55 | 00:00:01 |
| 7 | NESTED LOOPS | | 2 | 88 | 55 | 00:00:01 |
| 8 | VIEW | | 1 | 22 | 27 | 00:00:01 |
| 9 | HASH GROUP BY | | 1 | 13 | 27 | 00:00:01 |
| * 10 | INDEX RANGE SCAN | IDX_IDX_COMP_WT | 4646 | 60398 | 27 | 00:00:01 |
| * 11 | INDEX RANGE SCAN | IDX_IDX_COMP_WT | 2 | | 26 | 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | IDX_COMP_WT | 2 | 44 | 28 | 00:00:01 |
| 13 | NESTED LOOPS | | 95 | 15865 | 161 | 00:00:02 |
| 14 | TABLE ACCESS BY INDEX ROWID | IDX_BAS_INFO | 1 | 139 | 2 | 00:00:01 |
| * 15 | INDEX UNIQUE SCAN | IX_IDX_BAS_INFO | 1 | | 1 | 00:00:01 |
| * 16 | TABLE ACCESS BY INDEX ROWID | IDX_COMP | 95 | 2660 | 159 | 00:00:02 |
| * 17 | INDEX RANGE SCAN | IX_IDX_COMP | 190 | | 2 | 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID | STKCN_BAS_INFO | 1 | 117 | 1 | 00:00:01 |
| * 19 | INDEX UNIQUE SCAN | IX_STKCN_BAS_INFO | 1 | | 0 | 00:00:01 |
| 20 | VIEW PUSHED PREDICATE | | 1 | 37 | 11 | 00:00:01 |
| 21 | NESTED LOOPS | | 1 | 46 | 11 | 00:00:01 |
| 22 | NESTED LOOPS | | 1 | 46 | 11 | 00:00:01 |
| 23 | VIEW | | 1 | 22 | 8 | 00:00:01 |
| 24 | SORT GROUP BY | | 1 | 13 | 8 | 00:00:01 |
| * 25 | INDEX RANGE SCAN | IX_STKCN_MKT_QUOT_IDX | 1611 | 20943 | 8 | 00:00:01 |
| * 26 | INDEX RANGE SCAN | IX_STKCN_MKT_QUOT_IDX | 1 | | 2 | 00:00:01 |
| 27 | TABLE ACCESS BY INDEX ROWID | STKCN_MKT_QUOT_IDX | 1 | 24 | 3 | 00:00:01 |
| 28 | TABLE ACCESS FULL | COMCN_SHR_CHG_NEW | 17996 | 197956 | 158 | 00:00:02 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("D"."COM_ID"="E"."COM_ID"(+))
* 4 - access("B"."SECU_ID"="F"."SECU_ID"(+) AND "B"."IDX_SECU_ID"="F"."IDX_SECU_ID"(+))
* 10 - access("IDX_SECU_ID"=37627)
* 11 - access("F"."IDX_SECU_ID"=37627 AND "F"."TRD_DT"="FF"."TRD_DT")
* 11 - filter("F"."TRD_DT"="FF"."TRD_DT")
* 15 - access("A"."SECU_ID"=37627)
* 16 - filter("B"."NEW_STS"='1')
* 17 - access("B"."IDX_SECU_ID"=37627)
* 19 - access("B"."SECU_ID"="D"."SECU_ID"(+))
* 25 - access("SECU_ID"="D"."SECU_ID")
* 26 - access("C"."SECU_ID"="D"."SECU_ID" AND "C"."TRD_DT"="CC"."TRD_DT")
* 26 - filter("C"."SECU_ID"="CC"."SECU_ID")
insert 執行計劃：
Plan Hash Value : 3487003956
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 99 | 34551 | 1374590 | 04:34:56 |
| 1 | LOAD TABLE CONVENTIONAL | HSZG_ZSCFQZ_JAMMY_T | | | | |
| * 2 | HASH JOIN OUTER | | 99 | 34551 | 1374590 | 04:34:56 |
| 3 | NESTED LOOPS OUTER | | 95 | 32110 | 1374433 | 04:34:54 |
| 4 | NESTED LOOPS OUTER | | 95 | 28595 | 3014 | 00:00:37 |
| 5 | NESTED LOOPS OUTER | | 95 | 26980 | 256 | 00:00:04 |
| 6 | NESTED LOOPS | | 95 | 15865 | 161 | 00:00:02 |
| 7 | TABLE ACCESS BY INDEX ROWID | IDX_BAS_INFO | 1 | 139 | 2 | 00:00:01 |
| * 8 | INDEX UNIQUE SCAN | IX_IDX_BAS_INFO | 1 | | 1 | 00:00:01 |
| * 9 | TABLE ACCESS BY INDEX ROWID | IDX_COMP | 95 | 2660 | 159 | 00:00:02 |
| * 10 | INDEX RANGE SCAN | IX_IDX_COMP | 190 | | 2 | 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | STKCN_BAS_INFO | 1 | 117 | 1 | 00:00:01 |
| * 12 | INDEX UNIQUE SCAN | IX_STKCN_BAS_INFO | 1 | | 0 | 00:00:01 |
| 13 | VIEW PUSHED PREDICATE | | 1 | 17 | 29 | 00:00:01 |
| * 14 | FILTER | | | | | |
| 15 | NESTED LOOPS | | 1 | 44 | 29 | 00:00:01 |
| 16 | NESTED LOOPS | | 1 | 44 | 29 | 00:00:01 |
| 17 | VIEW | | 1 | 22 | 27 | 00:00:01 |
| 18 | SORT GROUP BY | | 1 | 13 | 27 | 00:00:01 |
| * 19 | FILTER | | | | | |
| * 20 | INDEX RANGE SCAN | IDX_IDX_COMP_WT | 4646 | 60398 | 27 | 00:00:01 |
| * 21 | INDEX UNIQUE SCAN | IDX_IDX_COMP_WT | 1 | | 1 | 00:00:01 |
| 22 | TABLE ACCESS BY INDEX ROWID | IDX_COMP_WT | 1 | 22 | 2 | 00:00:01 |
| 23 | VIEW PUSHED PREDICATE | | 1 | 37 | 14436 | 00:02:54 |
| 24 | NESTED LOOPS | | 1 | 33 | 14436 | 00:02:54 |
| 25 | TABLE ACCESS BY INDEX ROWID | STKCN_MKT_QUOT_IDX | 1611 | 38664 | 1531 | 00:00:19 |
| * 26 | INDEX RANGE SCAN | IX_STKCN_MKT_QUOT_IDX | 1611 | | 8 | 00:00:01 |
| * 27 | VIEW PUSHED PREDICATE | | 1 | 9 | 8 | 00:00:01 |
| * 28 | FILTER | | | | | |
| 29 | SORT AGGREGATE | | 1 | 13 | | |
| * 30 | INDEX RANGE SCAN | IX_STKCN_MKT_QUOT_IDX | 1611 | 20943 | 8 | 00:00:01 |
| 31 | TABLE ACCESS FULL | COMCN_SHR_CHG_NEW | 17996 | 197956 | 158 | 00:00:02 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("D"."COM_ID"="E"."COM_ID"(+))
* 8 - access("A"."SECU_ID"=37627)
* 9 - filter("B"."NEW_STS"='1')
* 10 - access("B"."IDX_SECU_ID"=37627)
* 12 - access("B"."SECU_ID"="D"."SECU_ID"(+))
* 14 - filter("B"."IDX_SECU_ID"=37627)
* 19 - filter("B"."IDX_SECU_ID"=37627)
* 20 - access("IDX_SECU_ID"=37627)
* 21 - access("F"."IDX_SECU_ID"="B"."IDX_SECU_ID" AND "F"."SECU_ID"="B"."SECU_ID" AND "F"."TRD_DT"="FF"."TRD_DT")
* 21 - filter("F"."IDX_SECU_ID"=37627)
* 26 - access("C"."SECU_ID"="D"."SECU_ID")
* 27 - filter("C"."TRD_DT"="CC"."TRD_DT")
* 28 - filter(COUNT(*)>0)
* 30 - access("SECU_ID"="C"."SECU_ID")
uj5u.com熱心網友回復：
視圖SQL：SELECT
TO_NUMBER(TO_CHAR(C.TRD_DT, 'YYYYMMDD')) AS JYRQ,
A.TRD_CODE AS ZSDM,
A.CHI_NAME AS ZSMC,
A.ENG_NAME AS ZSYW,
B.TRD_CODE AS ZQDM,
B.SECU_SHT AS ZQMC,
D.ENG_NAME AS ZQYW,
CASE
WHEN D.EXCH_CODE = 101 THEN
'1'
WHEN D.EXCH_CODE = 105 THEN
'2'
END AS ZQSC,
C.CLS_PRC AS JSPJ,
E.TTL_SHR AS ZGB,
NULL AS JSGB,
f.wt_val AS QZYZ,
C.TMKT_VAL AS ZSZ,
NULL AS JSSZ,
f.wt_val AS QZ,
'CNY' AS JYHB
FROM IDX_BAS_INFO A
JOIN IDX_COMP B
ON A.SECU_ID = B.IDX_SECU_ID
LEFT join idx_comp_wt F
join (select IDX_SECU_ID ,max(trd_dt)as trd_dt from idx_comp_wt group by IDX_SECU_ID ) FF
on f.IDX_SECU_ID=ff.IDX_SECU_ID and f.trd_dt=ff.trd_dt
on B.IDX_SECU_ID=F.IDX_SECU_ID and b.secu_id=f.secu_id
LEFT JOIN STKCN_BAS_INFO D
--ON F.SECU_ID = D.SECU_ID
ON B.SECU_ID=D.SECU_ID
LEFT JOIN STKCN_MKT_QUOT_IDX C
JOIN (SELECT SECU_ID, MAX(TRD_DT) AS TRD_DT
FROM STKCN_MKT_QUOT_IDX
GROUP BY SECU_ID) CC
ON C.SECU_ID = CC.SECU_ID AND C.TRD_DT = CC.TRD_DT
ON D.SECU_ID = C.SECU_ID
LEFT JOIN COMCN_SHR_CHG_NEW E
ON D.COM_ID = E.COM_ID
WHERE B.NEW_STS='1' and b.IDX_SECU_ID in (37267)
uj5u.com熱心網友回復：
能遠程嗎？可以遠程看看uj5u.com熱心網友回復：
執行計劃完全不一樣，cost也差了1000倍，手動加hint試試。uj5u.com熱心網友回復：
先在COMCN_SHR_CHG_NEW表的連接欄位COM_ID上加上索引試試uj5u.com熱心網友回復：
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/24619.html
標籤：高級技術
上一篇：優化SQL查詢陳述句
下一篇：Oracle 兩張表連接問題