问题描述:
Oracle 库里有一视图 A,select * from A ;1秒出结果。 返回数据370条。我新建了一张表B, 执行 insert into B select * from A 却执行了6分30秒。下面是视图的执行计划和insert into 的执行计划,求高手指点。视图的:
insert into 的
Oracle 库里有一视图 A,select * from A ;1秒出结果。 返回数据370条。我新建了一张表B, 执行 insert into B select * from A 却执行了6分30秒。下面是视图的执行计划和insert into 的执行计划,求高手指点。视图的:
insert into 的
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")
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)