Oracle database has A view of A, the select * from A; Article 1 seconds out as a result, the data returned 370,
I built A table B, an insert into the select * from B has performed A 6 minutes and 30 seconds,
Here is a view of the execution plan and insert into the execution plan, seek expert advice,
View:
Insert into
CodePudding user response:
Such A complex can select the select * from A,,,From the point of insert executions 6 minutes, select impossible 1 seconds out, was a second, because PLSQL developer only give you back a few lines of relationship without permission, the performance of the problem is that you select, instead of the insert,
Execution plan is not complete, more can't analysis, first on the final experience TABLE ACCESS FULL TABLE index of field associated with a try,
CodePudding user response:
View in SQL:SELECT
TO_NUMBER (TO_CHAR (C.T RD_DT, 'YYYYMMDD')) AS JYRQ,
A.T RD_CODE AS ZSDM,
A.C HI_NAME AS ZSMC,
A.E NG_NAME AS ZSYW,
B.T RD_CODE AS ZQDM,
B.S ECU_SHT AS ZQMC,
D.E NG_NAME AS ZQYW,
CASE
The WHEN D.E XCH_CODE=101 THEN
'1'
The WHEN D.E XCH_CODE=105 THEN
'2'
END AS ZQSC,
Arthur c. LS_PRC AS JSPJ,
E. TL_SHR AS ZGB,
NULL AS JSGB,
F.w t_val AS QZYZ,
C.T MKT_VAL AS ZSZ,
NULL AS JSSZ,
F.w t_val AS QZ,
'CNY AS JYHB
The FROM IDX_BAS_INFO A
JOIN IDX_COMP - index composition B
ON A.S ECU_ID=B.I DX_SECU_ID
LEFT the join idx_comp_wt F
The join (select IDX_SECU_ID, Max (trd_dt) as trd_dt from idx_comp_wt group by IDX_SECU_ID) FF
On f.I DX_SECU_ID=ff. IDX_SECU_ID and f.t rd_dt=ff. Trd_dt
On B.I DX_SECU_ID=F.I DX_SECU_ID and b.s ecu_id=f.s ecu_id
LEFT the JOIN STKCN_BAS_INFO D
- ON F.S ECU_ID=D.S ECU_ID
ON B.S ECU_ID=D.S ECU_ID
LEFT the JOIN STKCN_MKT_QUOT_IDX C
JO
View the execution plan:
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 | | | 27 22 00:00:01 |
| | 9 HASH GROUP BY | | 1 | | | 27 13 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 44 | | 2 | | 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 | | 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 |
22 23 | | VIEW | | 1 | | | 8 00:00:01 |
24 | | SORT GROUP BY | | 1 | | | 8 13 00:00:01 |
| | x 25 INDEX RANGE SCAN | IX_STKCN_MKT_QUOT_IDX | 1611 | 20943 | | 8 00:00:01 |
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull