资讯详情

深入了解 · 专业解读

oracle sql优化随笔3

发布时间：2026/9/30 10:13:04
oracle sql优化随笔3 初始sqlSELECT * FROM XIR_MD.VECD_MAIN T WHERE T.M_TYPE IN ( XSHG,XSHE );视图展开SELECT A.I_CODE, A.A_TYPE, A.M_TYPE, B.S_NAME AS I_NAME, A.COUNTRY, A.CURRENCY, A.Q_TYPE, A.P_CLASS, A.S_DATE AS L_DATE, 1 AS PAR_VALUE FROM XIR_MD.TSTK A, (SELECT I.*, ROW_NUMBER() OVER(PARTITION BY I.I_CODE, I.A_TYPE, I.M_TYPE ORDER BY I.END_DATE) NUM FROM XIR_MD.TSTK_NAME I WHERE I.END_DATE TO_CHAR(SYSDATE, YYYY-MM-DD)) B WHERE A.I_CODE B.I_CODE AND A.A_TYPE B.A_TYPE AND A.M_TYPE B.M_TYPE AND A.CURRENCY IN (CNY,USD,HKD) AND B.NUM 1 UNION -- 债券 SELECT I_CODE ,A_TYPE ,M_TYPE ,B_NAME ,COUNTRY ,CURRENCY ,Q_TYPE ,P_CLASS ,B_LIST_DATE ,B_PAR_VALUE FROM XIR_MD.TBND UNION -- 基金 SELECT I_CODE ,A_TYPE ,M_TYPE ,F_NAME ,COUNTRY ,CURRENCY ,Q_TYPE ,P_CLASS ,F_DATE ,F_PAR_VALUE FROM XIR_MD.TFND UNION SELECT I_CODE ,A_TYPE ,M_TYPE ,W_NAME ,COUNTRY ,CURRENCY ,Q_TYPE ,P_CLASS ,ISSUE_DATE ,1 FROM XIR_MD.TWARRANT UNION SELECT I_CODE ,A_TYPE ,M_TYPE ,I_NAME ,NULL ,CURRENCY ,Q_TYPE ,P_CLASS ,I_DATE ,1 FROM XIR_MD.TIDX UNION --期货 SELECT I_CODE ,A_TYPE ,M_TYPE ,SI_NAME ,COUNTRY ,CURRENCY ,Q_TYPE ,P_CLASS ,ISSUE_DATE ,LOTSIZE FROM XIR_MD.TSTK_IDX_FUTURE UNION SELECT I_CODE ,A_TYPE ,M_TYPE ,I_NAME ,NULL ,NULL ,NULL ,P_CLASS ,L_DATE ,1 FROM XIR_MD.TCOMPOSITE_PORT UNION --商品 SELECT I_CODE ,A_TYPE ,M_TYPE ,I_NAME ,COUNTRY ,CURRENCY ,Q_TYPE ,P_CLASS ,L_DATE ,EXCHANGE_UNIT FROM XIR_MD.TCMDT UNION --回购 SELECT I_CODE ,A_TYPE ,M_TYPE ,I_NAME ,COUNTRY ,CURRENCY ,Q_TYPE ,P_CLASS ,NULL ,100 FROM XIR_MD.TIBOR union SELECT NULL AS I_CODE, NULL AS A_TYPE, NULL AS M_TYPE, NULL AS I_NAME, NULL AS COUNTRY, NULL AS CURRENCY, NULL AS Q_TYPE, NULL AS P_CLASS, NULL AS L_DATE, 1 AS PAR_VALUE FROM XIR_MD.TIBOR WHERE 12; 再整体where M_TYPE IN ( XSHG,XSHE )查看执行计划--------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 567 (100)| | 9865 |00:00:00.05 | 2538 | | 1 | VIEW | VECD_MAIN | 1 | 4424 | 1788K| 567 (3)| 00:00:07 | 9865 |00:00:00.05 | 2538 | | 2 | SORT UNIQUE | | 1 | 4424 | 305K| 567 (91)| 00:00:07 | 9865 |00:00:00.04 | 2538 | | 3 | UNION-ALL | | 1 | | | | | 9865 |00:00:00.03 | 2538 | | 4 | NESTED LOOPS | | 1 | | | | | 5525 |00:00:00.02 | 775 | | 5 | NESTED LOOPS | | 1 | 4 | 604 | 54 (4)| 00:00:01 | 5525 |00:00:00.02 | 335 | |* 6 | VIEW | | 1 | 4 | 424 | 50 (4)| 00:00:01 | 5525 |00:00:00.01 | 165 | |* 7 | WINDOW SORT PUSHED RANK | | 1 | 4 | 160 | 50 (4)| 00:00:01 | 5527 |00:00:00.01 | 165 | |* 8 | TABLE ACCESS FULL | TSTK_NAME | 1 | 4 | 160 | 49 (3)| 00:00:01 | 5527 |00:00:00.01 | 165 | |* 9 | INDEX UNIQUE SCAN | PK_TSTK | 5525 | 1 | | 0 (0)| | 5525 |00:00:00.01 | 170 | |* 10 | TABLE ACCESS BY INDEX ROWID| TSTK | 5525 | 1 | 45 | 1 (0)| 00:00:01 | 5525 |00:00:00.01 | 440 | |* 11 | TABLE ACCESS FULL | TBND | 1 | 4170 | 289K| 480 (1)| 00:00:06 | 4170 |00:00:00.01 | 1727 | |* 12 | TABLE ACCESS FULL | TFND | 1 | 81 | 5670 | 4 (0)| 00:00:01 | 81 |00:00:00.01 | 9 | |* 13 | TABLE ACCESS FULL | TWARRANT | 1 | 1 | 150 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 3 | |* 14 | TABLE ACCESS FULL | TIDX | 1 | 5 | 325 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 3 | |* 15 | TABLE ACCESS FULL | TSTK_IDX_FUTURE | 1 | 63 | 4095 | 5 (0)| 00:00:01 | 0 |00:00:00.01 | 11 | |* 16 | TABLE ACCESS FULL | TCOMPOSITE_PORT | 1 | 8 | 400 | 3 (0)| 00:00:01 | 8 |00:00:00.01 | 3 | |* 17 | TABLE ACCESS FULL | TCMDT | 1 | 5 | 290 | 3 (0)| 00:00:01 | 2 |00:00:00.01 | 3 | |* 18 | TABLE ACCESS FULL | TIBOR | 1 | 86 | 5332 | 3 (0)| 00:00:01 | 74 |00:00:00.01 | 4 | |* 19 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | | 20 | INDEX FULL SCAN | PK_TIBOR | 0 | 129 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | ---------------------------------------------------------------------------------------------------------------------------------------------主要问题在于union 了TBND的全表扫描SELECT I_CODE ,A_TYPE ,M_TYPE ,B_NAME ,COUNTRY ,CURRENCY ,Q_TYPE ,P_CLASS ,B_LIST_DATE ,B_PAR_VALUE FROM XIR_MD.TBND T where M_TYPE IN (XSHG,XSHE);M_TYPE字段的选择性太差导致单建M_TYPE索引根本不走hint后消耗反而变高无索引状态 -------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 480 (100)| | 4170 |00:00:00.01 | 1977 | |* 1 | TABLE ACCESS FULL| TBND | 1 | 4170 | 289K| 480 (1)| 00:00:06 | 4170 |00:00:00.01 | 1977 | -------------------------------------------------------------------------------------------------------------------- create index idx_haha_23 on XIR_MD.TBND(M_TYPE); -------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 846 (100)| | 4170 |00:00:00.01 | 2616 | | 1 | INLIST ITERATOR | | 1 | | | | | 4170 |00:00:00.01 | 2616 | | 2 | TABLE ACCESS BY INDEX ROWID| TBND | 2 | 4170 | 289K| 846 (1)| 00:00:11 | 4170 |00:00:00.01 | 2616 | |* 3 | INDEX RANGE SCAN | IDX_HAHA_23 | 2 | 4170 | | 12 (9)| 00:00:01 | 4170 |00:00:00.01 | 291 | --------------------------------------------------------------------------------------------------------------------------------------将全表扫描改成INDEX FAST FULL SCAN该部分消耗下来了。create index idx_haha_25 on XIR_MD.TBND(M_TYPE,A_TYPE,B_NAME,COUNTRY,CURRENCY,Q_TYPE,P_CLASS,B_LIST_DATE,B_PAR_VALUE); ------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 140 (100)| | 4170 |00:00:00.02 | 420 | |* 1 | VIEW | index$_join$_001 | 1 | 4170 | 289K| 140 (1)| 00:00:02 | 4170 |00:00:00.02 | 420 | |* 2 | HASH JOIN | | 1 | | | | | 4170 |00:00:00.01 | 420 | | 3 | INLIST ITERATOR | | 1 | | | | | 4170 |00:00:00.01 | 41 | |* 4 | INDEX RANGE SCAN | IDX_HAHA_25 | 2 | 4170 | 289K| 46 (5)| 00:00:01 | 4170 |00:00:00.01 | 41 | |* 5 | INDEX FAST FULL SCAN| IDX_TBND | 1 | 4170 | 289K| 119 (0)| 00:00:02 | 4170 |00:00:00.01 | 379 | -------------------------------------------------------------------------------------------------------------------------------------整体执行计划---------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 227 (100)| | 9865 |00:00:00.04 | 952 | | 1 | VIEW | VECD_MAIN | 1 | 4424 | 1788K| 227 (6)| 00:00:03 | 9865 |00:00:00.04 | 952 | | 2 | SORT UNIQUE | | 1 | 4424 | 305K| 227 (78)| 00:00:03 | 9865 |00:00:00.04 | 952 | | 3 | UNION-ALL | | 1 | | | | | 9865 |00:00:00.03 | 952 | | 4 | NESTED LOOPS | | 1 | | | | | 5525 |00:00:00.02 | 773 | | 5 | NESTED LOOPS | | 1 | 4 | 604 | 54 (4)| 00:00:01 | 5525 |00:00:00.01 | 334 | |* 6 | VIEW | | 1 | 4 | 424 | 50 (4)| 00:00:01 | 5525 |00:00:00.01 | 164 | |* 7 | WINDOW SORT PUSHED RANK | | 1 | 4 | 160 | 50 (4)| 00:00:01 | 5527 |00:00:00.01 | 164 | |* 8 | TABLE ACCESS FULL | TSTK_NAME | 1 | 4 | 160 | 49 (3)| 00:00:01 | 5527 |00:00:00.01 | 164 | |* 9 | INDEX UNIQUE SCAN | PK_TSTK | 5525 | 1 | | 0 (0)| | 5525 |00:00:00.01 | 170 | |* 10 | TABLE ACCESS BY INDEX ROWID| TSTK | 5525 | 1 | 45 | 1 (0)| 00:00:01 | 5525 |00:00:00.01 | 439 | |* 11 | VIEW | index$_join$_005 | 1 | 4170 | 289K| 140 (1)| 00:00:02 | 4170 |00:00:00.01 | 143 | |* 12 | HASH JOIN | | 1 | | | | | 4170 |00:00:00.01 | 143 | | 13 | INLIST ITERATOR | | 1 | | | | | 4170 |00:00:00.01 | 41 | |* 14 | INDEX RANGE SCAN | IDX_HAHA_25 | 2 | 4170 | 289K| 46 (5)| 00:00:01 | 4170 |00:00:00.01 | 41 | |* 15 | INDEX FAST FULL SCAN | IDX_TBND | 1 | 4170 | 289K| 119 (0)| 00:00:02 | 4170 |00:00:00.01 | 102 | |* 16 | TABLE ACCESS FULL | TFND | 1 | 81 | 5670 | 4 (0)| 00:00:01 | 81 |00:00:00.01 | 9 | |* 17 | TABLE ACCESS FULL | TWARRANT | 1 | 1 | 150 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 3 | |* 18 | TABLE ACCESS FULL | TIDX | 1 | 5 | 325 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 3 | |* 19 | TABLE ACCESS FULL | TSTK_IDX_FUTURE | 1 | 63 | 4095 | 5 (0)| 00:00:01 | 0 |00:00:00.01 | 11 | |* 20 | TABLE ACCESS FULL | TCOMPOSITE_PORT | 1 | 8 | 400 | 3 (0)| 00:00:01 | 8 |00:00:00.01 | 3 | |* 21 | TABLE ACCESS FULL | TCMDT | 1 | 5 | 290 | 3 (0)| 00:00:01 | 2 |00:00:00.01 | 3 | |* 22 | TABLE ACCESS FULL | TIBOR | 1 | 86 | 5332 | 3 (0)| 00:00:01 | 74 |00:00:00.01 | 4 | |* 23 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | | 24 | INDEX FULL SCAN | PK_TIBOR | 0 | 129 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | ----------------------------------------------------------------------------------------------------------------------------------------------但是由于需要添加大量的select列索引较大也不便于表的其他增删改动作并且当前该sql优先级并非最高所以暂时保留处理方式待日后查询时间接受不了了再做优化变更。

相关新闻