今天，某数据仓库系统的开发发来一条SQL，说跑了3个小时，严重拖慢了整个的跑批流程。
INSERT INTO ETL.RRR_SSSS_GGGG_INTERNAL_PTB (RPT_ORG_ID, ITEM_CD, ASSET_SPOT, LIABILITY_SPOT, FORWARD_LONG, FORWARD_SHORT, ADJ_OPTION_POSITION, EXP_POSITION, STRUCTURAL_ASSET, OVERS_BRCH, ATTACH_COMPANT, TOTAL_EXP, EXPO_TOTAL_LIAB, INT_EXP_LIMIT, DATA_DATE) SELECT '00001', (CASE WHEN T1.CURR_CD = 'USD' THEN 1 WHEN T1.CURR_CD = 'EUR' THEN 2 WHEN T1.CURR_CD = 'JPY' THEN 3 WHEN T1.CURR_CD = 'GBP' THEN 4 WHEN T1.CURR_CD = 'HKD' THEN 5 WHEN T1.CURR_CD = 'CHF' THEN 6 WHEN T1.CURR_CD = 'AUD' THEN 7 WHEN T1.CURR_CD = 'CAD' THEN 8 WHEN T1.CURR_CD NOT IN ('USD', 'EUR', 'JPY', 'GBP', 'HKD', 'CHF', 'AUD', 'CAD', 'GOLD') AND (T1.SPOT_ASSET + T1.SPOT_LIABILITY + T1.FORWARD_BUY + T1.FORWARD_SELL) >= 0 THEN 10 WHEN T1.CURR_CD NOT IN ('USD', 'EUR', 'JPY', 'GBP', 'HKD', 'CHF', 'AUD', 'CAD', 'GOLD') AND (T1.SPOT_ASSET + T1.SPOT_LIABILITY + T1.FORWARD_BUY + T1.FORWARD_SELL) < 0 THEN 11 END) AS ITEM_CD, ABS(SUM(T1.SPOT_ASSET)), ABS(SUM(T1.SPOT_LIABILITY)), ABS(SUM(T1.FORWARD_BUY)), ABS(SUM(T1.FORWARD_SELL)), NULL, SUM(T1.SPOT_ASSET + T1.SPOT_LIABILITY + T1.FORWARD_BUY + T1.FORWARD_SELL), SUM(T1.ASSET_STRUCTURAL), 0, 0, SUM(T1.SPOT_ASSET + T1.SPOT_LIABILITY + T1.FORWARD_BUY + T1.FORWARD_SELL), SUM(T1.ASSET_STRUCTURAL), 0, :B1 FROM (SELECT NVL(NVL(T1.CURR_CD, D1.CURR_CD), D2.CURR_CD) AS CURR_CD, NVL(T1.SPOT_ASSET, 0) AS SPOT_ASSET, NVL(T1.SPOT_LIABILITY, 0) AS SPOT_LIABILITY, NVL(T1.FORWARD_BUY, 0) + NVL(D1.DBUY_VAL, 0) AS FORWARD_BUY, -1 * (NVL(-1 * T1.FORWARD_SELL, 0) + NVL(D2.DSELL_VAL, 0)) AS FORWARD_SELL, NVL(T1.ASSET_STRUCTURAL, 0) AS ASSET_STRUCTURAL FROM (SELECT (CASE WHEN T2.OBJ_ATTR_ID IS NOT NULL AND T3.CD_ID <> 'GOLD' THEN T2.OBJ_ATTR_ID ELSE T1.CURR_CD END) AS CURR_CD, SUM((CASE WHEN T3.ATTR_ID = 'USD' AND ((T1.PROD_TYPE = 'A_L' AND T1.SEQ_NUM = '1') OR T1.PROD_TYPE = 'STRUCTURE' OR T1.PROD_TYPE = 'DEPECIATION' OR T1.PROD_TYPE = 'COMMONACCOUNT' OR (T1.PROD_TYPE = 'GOLD_POSITION' AND T1.SEQ_NUM = '1')) THEN NVL(T1.MARKET_VAL, 0) WHEN T3.ATTR_ID <> 'USD' AND T3.CD_ID <> 'GOLD' AND ((T1.PROD_TYPE = 'A_L' AND T1.SEQ_NUM = '1') OR T1.PROD_TYPE = 'STRUCTURE' OR T1.PROD_TYPE = 'DEPECIATION' OR T1.PROD_TYPE = 'COMMONACCOUNT') THEN NVL(T1.MARKET_VAL, 0) ELSE 0 END)) AS SPOT_ASSET, SUM((CASE WHEN T3.ATTR_ID = 'USD' AND ((T1.PROD_TYPE = 'A_L' AND T1.SEQ_NUM = '2') OR (T1.PROD_TYPE = 'GOLD_POSITION' AND T1.SEQ_NUM = '2')) THEN NVL(T1.MARKET_VAL, 0) WHEN T3.ATTR_ID <> 'USD' AND T3.CD_ID <> 'GOLD' AND T1.PROD_TYPE = 'A_L' AND T1.SEQ_NUM = '2' THEN NVL(T1.MARKET_VAL, 0) ELSE 0 END)) AS SPOT_LIABILITY, SUM((CASE WHEN T1.PROD_TYPE = 'FX_Unsettled' AND T1.SEQ_NUM IN ('1', '3') AND T1.EXT_PRODUCT_TIER2 = 'PS_000000035' AND T1.BUSINESS_PK NOT LIKE 'CAP_40604%' THEN NVL(T1.MARKET_VAL, 0) ELSE 0 END)) AS FORWARD_BUY, SUM((CASE WHEN T1.PROD_TYPE = 'FX_Unsettled' AND T1.SEQ_NUM IN ('2', '4') AND T1.EXT_PRODUCT_TIER2 = 'PS_000000035' AND T1.BUSINESS_PK NOT LIKE 'CAP_40604%' THEN NVL(T1.MARKET_VAL, 0) ELSE 0 END)) AS FORWARD_SELL, SUM((CASE WHEN T1.PROD_TYPE = 'STRUCTURE' THEN NVL(T1.MARKET_VAL, 0) * (-1) ELSE 0 END)) AS ASSET_STRUCTURAL FROM ETL.MIM_RRRR_EEEE_PPP T1 LEFT JOIN ETL.MID_REF_COD_MAP_TBL T2 ON T2.SRC_CODE_ID = 'SRC_COMM_CD' AND T1.CURR_CD = T2.SRC_ATTR_ID LEFT JOIN ETL.MIM_RRR_CCC_AAA_TTT T3 ON T3.CODE_TYPE = 'CURR_TYP' AND T3.CD_ID <> 'COMM' AND (CASE WHEN T2.OBJ_ATTR_ID IS NOT NULL THEN T2.OBJ_ATTR_ID ELSE T1.CURR_CD END) = T3.ATTR_ID WHERE T1.DATA_DATE = :B1 AND T1.EFFECTIVE_OR_NOT = 'Y' AND T1.PROD_TYPE <> 'GOLD_SPOT' AND T3.CD_ID <> 'GOLD' GROUP BY (CASE WHEN T2.OBJ_ATTR_ID IS NOT NULL AND T3.CD_ID <> 'GOLD' THEN T2.OBJ_ATTR_ID ELSE T1.CURR_CD END)) T1 LEFT JOIN (SELECT T1.CCY_CD AS CURR_CD, SUM(NVL(T1.DAY_CR_BAL, 0) * NVL(T2.EXCH_RATE, 1)) AS DBUY_VAL FROM MIM_TTT_DDDDD_BOOK_PTB T1 LEFT JOIN ETL.MIM_RRR_CCCC_EE_PPP T2 ON T1.CCY_CD = T2.NUMERATOR_CURR_CD AND T2.DATA_DATE = :B1 INNER JOIN ETL.MIM_RRR_CCC_AAA_TTT T3 ON T1.CCY_CD = T3.ATTR_ID AND T3.CODE_TYPE = 'CURR_TYP' WHERE T1.DATA_DATE = :B1 AND T1.CCY_CD NOT LIKE '' AND T1.CCY_CD <> 'CNY' AND T1.INTER_ORG_ID = 'GLS9_T' AND T1.ACCTING_COA_ID LIKE '9521%' AND T1.ACCTING_INTERIM = SUBSTR(T1.ACCTING_DT, 1, 4) || '-' || SUBSTR(T1.ACCTING_DT, 5, 2) GROUP BY T1.CCY_CD, NVL(T2.EXCH_RATE, 1)) D1 ON T1.CURR_CD = D1.CURR_CD LEFT JOIN (SELECT T1.CCY_CD AS CURR_CD, SUM(CASE WHEN T1.ACCTING_COA_ID LIKE '9522%' THEN NVL(T1.DAY_CR_BAL, 0) * NVL(T2.EXCH_RATE, 1) ELSE -1 * NVL(T1.DAY_CR_BAL, 0) * NVL(T2.EXCH_RATE, 1) END) AS DSELL_VAL FROM MIM_TTT_DDDDD_BOOK_PTB T1 LEFT JOIN ETL.MIM_RRR_CCCC_EE_PPP T2 ON T1.CCY_CD = T2.NUMERATOR_CURR_CD AND T2.DATA_DATE = :B1 INNER JOIN ETL.MIM_RRR_CCC_AAA_TTT T3 ON T1.CCY_CD = T3.ATTR_ID AND T3.CODE_TYPE = 'CURR_TYP' WHERE T1.DATA_DATE = :B1 AND T1.CCY_CD NOT LIKE '' AND T1.CCY_CD <> 'CNY' AND T1.INTER_ORG_ID = 'GLS9_T' AND (T1.ACCTING_COA_ID LIKE '9522%' OR T1.ACCTING_COA_ID LIKE '2009%') AND T1.ACCTING_INTERIM = SUBSTR(T1.ACCTING_DT, 1, 4) || '-' || SUBSTR(T1.ACCTING_DT, 5, 2) GROUP BY T1.CCY_CD) D2 ON T1.CURR_CD = D2.CURR_CD) T1 GROUP BY (CASE WHEN T1.CURR_CD = 'USD' THEN 1 WHEN T1.CURR_CD = 'EUR' THEN 2 WHEN T1.CURR_CD = 'JPY' THEN 3 WHEN T1.CURR_CD = 'GBP' THEN 4 WHEN T1.CURR_CD = 'HKD' THEN 5 WHEN T1.CURR_CD = 'CHF' THEN 6 WHEN T1.CURR_CD = 'AUD' THEN 7 WHEN T1.CURR_CD = 'CAD' THEN 8 WHEN T1.CURR_CD NOT IN ('USD', 'EUR', 'JPY', 'GBP', 'HKD', 'CHF', 'AUD', 'CAD', 'GOLD') AND (T1.SPOT_ASSET + T1.SPOT_LIABILITY + T1.FORWARD_BUY + T1.FORWARD_SELL) >= 0 THEN 10 WHEN T1.CURR_CD NOT IN ('USD', 'EUR', 'JPY', 'GBP', 'HKD', 'CHF', 'AUD', 'CAD', 'GOLD') AND (T1.SPOT_ASSET + T1.SPOT_LIABILITY + T1.FORWARD_BUY + T1.FORWARD_SELL) < 0 THEN 11 END) Plan hash value: 2210955645 ------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | | | 17493 (100)| | | | | 1 | LOAD TABLE CONVENTIONAL | | | | | | | | | 2 | HASH GROUP BY | | 1 | 117 | 17493 (1)| 00:03:30 | | | | 3 | HASH JOIN RIGHT OUTER | | 111 | 12987 | 17492 (1)| 00:03:30 | | | | 4 | VIEW | | 1 | 17 | 881 (1)| 00:00:11 | | | | 5 | SORT GROUP BY | | 1 | 97 | 881 (1)| 00:00:11 | | | | 6 | HASH JOIN | | 1 | 97 | 880 (0)| 00:00:11 | | | | 7 | HASH JOIN OUTER | | 1 | 78 | 877 (0)| 00:00:11 | | | | 8 | TABLE ACCESS BY GLOBAL INDEX ROWID | MIM_TTT_DDDDD_BOOK_PTB | 1 | 54 | 874 (0)| 00:00:11 | ROWID | ROWID | | 9 | INDEX SKIP SCAN | MIM_TTT_DDDDD_BOOK_PK | 1 | | 873 (0)| 00:00:11 | | | | 10 | PARTITION LIST SINGLE | | 35 | 840 | 3 (0)| 00:00:01 | KEY | KEY | | 11 | TABLE ACCESS FULL | MIM_RRR_CCCC_EE_PPP | 35 | 840 | 3 (0)| 00:00:01 | KEY | KEY | | 12 | TABLE ACCESS FULL | MIM_RRR_CCC_AAA_TTT | 218 | 4142 | 3 (0)| 00:00:01 | | | | 13 | HASH JOIN RIGHT OUTER | | 111 | 11100 | 16611 (1)| 00:03:20 | | | | 14 | VIEW | | 1 | 17 | 442 (1)| 00:00:06 | | | | 15 | SORT GROUP BY | | 1 | 97 | 442 (1)| 00:00:06 | | | | 16 | HASH JOIN | | 1 | 97 | 441 (0)| 00:00:06 | | | | 17 | HASH JOIN OUTER | | 1 | 78 | 438 (0)| 00:00:06 | | | | 18 | TABLE ACCESS BY GLOBAL INDEX ROWID| MIM_TTT_DDDDD_BOOK_PTB | 1 | 54 | 435 (0)| 00:00:06 | ROWID | ROWID | | 19 | INDEX SKIP SCAN | MIM_TTT_DDDDD_BOOK_PK | 1 | | 434 (0)| 00:00:06 | | | | 20 | PARTITION LIST SINGLE | | 35 | 840 | 3 (0)| 00:00:01 | KEY | KEY | | 21 | TABLE ACCESS FULL | MIM_RRR_CCCC_EE_PPP | 35 | 840 | 3 (0)| 00:00:01 | KEY | KEY | | 22 | TABLE ACCESS FULL | MIM_RRR_CCC_AAA_TTT | 218 | 4142 | 3 (0)| 00:00:01 | | | | 23 | VIEW | | 111 | 9213 | 16169 (1)| 00:03:15 | | | | 24 | SORT GROUP BY | | 111 | 14208 | 16169 (1)| 00:03:15 | | | | 25 | HASH JOIN | | 4700 | 587K| 16168 (1)| 00:03:15 | | | | 26 | TABLE ACCESS FULL | MIM_RRR_CCC_AAA_TTT | 203 | 5075 | 3 (0)| 00:00:01 | | | | 27 | HASH JOIN RIGHT OUTER | | 10385 | 1044K| 16165 (1)| 00:03:14 | | | | 28 | TABLE ACCESS FULL | MID_REF_COD_MAP_TBL | 14 | 336 | 3 (0)| 00:00:01 | | | | 29 | PARTITION LIST SINGLE | | 10385 | 801K| 16162 (1)| 00:03:14 | KEY | KEY | | 30 | TABLE ACCESS FULL | MIM_RRRR_EEEE_PPP | 10385 | 801K| 16162 (1)| 00:03:14 | KEY | KEY | -------------------------------------------------------------------------------------------------------------------------------------
这个执行计划是通过我们写的一个工具抓出来的（还不如F5）没有谓词信息和统计信息。所以要作优化一定要用explain plan for来取执行计划，当然plsql developer11之后的版本也可以显示谓词信息和统计信息了。
先获取一下 这个SQL里面的表信息和索引信息
MIM_TTT_DDDDD_BOOK_PTB 311946106 44469.19MB MIM_RRR_CCCC_EE_PPP 15084 75.63MB MIM_RRR_CCC_AAA_TTT 450 0.06MB
alter table ETL.MIM_TTT_DDDDD_BOOK_PTB add constraint MIM_TTT_DDDDD_BOOK_PTB_PK primary key (ACCTING_DT, ACCTING_INTERIM, CCY_CD, ACCTING_COA_ID, GL_SRC_CD, INTER_ORG_ID, DATA_DATE) using index tablespace ETL_INDEX_TS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited );
针对长SQL，我还是习惯性找大表【小表怎么扫描都不会引起性能问题】 MIM_TTT_DDDDD_BOOK_PTB是这个SQL里面唯一个大表， 出现问题的部分基本上就是[大表所在部分] 这段SQL单独拿出来分析
explain plan for SELECT T1.CCY_CD AS CURR_CD, SUM(CASE WHEN T1.ACCTING_COA_ID LIKE '9522%' THEN NVL(T1.DAY_CR_BAL, 0) * NVL(T2.EXCH_RATE, 1) ELSE -1 * NVL(T1.DAY_CR_BAL, 0) * NVL(T2.EXCH_RATE, 1) END) AS DSELL_VAL FROM etl.MIM_TTT_DDDDD_BOOK_PTB T1 LEFT JOIN ETL.MIM_RRR_CCCC_EE_PPP T2 ON T1.CCY_CD = T2.NUMERATOR_CURR_CD AND T2.DATA_DATE = date '2017-01-10' INNER JOIN ETL.MIM_RRR_CCC_AAA_TTT T3 ON T1.CCY_CD = T3.ATTR_ID AND T3.CODE_TYPE = 'CURR_TYP' WHERE T1.DATA_DATE = date '2017-01-10' AND T1.CCY_CD NOT LIKE '' AND T1.CCY_CD <> 'CNY' AND T1.INTER_ORG_ID = 'GLS9_T' AND (T1.ACCTING_COA_ID LIKE '9522%' OR T1.ACCTING_COA_ID LIKE '2009%') AND T1.ACCTING_INTERIM = SUBSTR(T1.ACCTING_DT, 1, 4) || '-' || SUBSTR(T1.ACCTING_DT, 5, 2) GROUP BY T1.CCY_CD; select * from table(dbms_xplan.display); Plan hash value: 3749582889 -------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 97 | 881 (1)| 00:00:11 | | | | 1 | HASH GROUP BY | | 1 | 97 | 881 (1)| 00:00:11 | | | |* 2 | HASH JOIN | | 1 | 97 | 880 (0)| 00:00:11 | | | |* 3 | HASH JOIN OUTER | | 1 | 78 | 877 (0)| 00:00:11 | | | | 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| MIM_TTT_DDDDD_BOOK_PTB | 1 | 54 | 874 (0)| 00:00:11 | 448 | 448 | |* 5 | INDEX SKIP SCAN | MIM_TTT_DDDDD_BOOK_PK | 1 | | 873 (0)| 00:00:11 | | | | 6 | PARTITION LIST SINGLE | | 34 | 816 | 3 (0)| 00:00:01 | KEY | KEY | |* 7 | TABLE ACCESS FULL | MIM_RRR_CCCC_EE_PPP | 34 | 816 | 3 (0)| 00:00:01 | 448 | 448 | |* 8 | TABLE ACCESS FULL | MIM_RRR_CCC_AAA_TTT | 218 | 4142 | 3 (0)| 00:00:01 | | | -------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T1"."CCY_CD"="T3"."ATTR_ID") 3 - access("T1"."CCY_CD"="T2"."NUMERATOR_CURR_CD"(+)) 5 - access("T1"."INTER_ORG_ID"='GLS9_T' AND "T1"."DATA_DATE"=TO_DATE(' 2017-01-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) filter("T1"."INTER_ORG_ID"='GLS9_T' AND "T1"."DATA_DATE"=TO_DATE(' 2017-01-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T1"."CCY_CD"<>'CNY' AND ("T1"."ACCTING_COA_ID" LIKE '9522%' OR "T1"."ACCTING_COA_ID" LIKE '2009%') AND "T1"."ACCTING_INTERIM"=SUBSTR("T1"."ACCTING_DT",1,4)||'-'||SUBSTR("T1"."ACCTING_DT",5,2) AND "T1"."CCY_CD" NOT LIKE '') 7 - filter("T2"."NUMERATOR_CURR_CD"(+)<>'CNY') 8 - filter("T3"."CODE_TYPE"='CURR_TYP' AND "T3"."ATTR_ID"<>'CNY')
从执行计划可以看出这个SQL引起性能瓶颈的部分就是第5步 INDEX SKIP SCAN | MID_TXN_DAILY_BOOK_PK 这个主键索引有7个列。INDEX SKIP SCAN 的引导列INTER_ORG_ID在索引的第6个列，跳扫需要跳跃引导列（ACCTING_DT）基数（DISTINCT_KEYS）次， 而ACCTING_DT列的基数非常高，所以索引需要跳跃的次数很多。 而且索引跳跃扫描所使用的引导列INTER_ORG_ID列的基数非常低，类似机构号这种参数列，一个值对应了表中很多行，会对回表产生很大的压力 在执行计划第5步的谓词信息filter里面我们看到了："T1"."ACCTING_COA_ID" LIKE '9522%' OR "T1"."ACCTING_COA_ID" LIKE '2009%'这个条件 "T1"."ACCTING_COA_ID"这个列在索引的第4列，同样会产生跳跃扫描，但是ACCTING_COA_ID的基数高，回表压力少，所以性能肯定比上一个好。 然而我们看执行计划的第5步的谓词信息，这个条件没有出现在access里面，而是出现在filter里面，所以这个列压根没起到引导作用，只是一个简单的过滤作用 所以引起性能问题的根本原因就是AND ("T1"."ACCTING_COA_ID" LIKE '9522%' OR "T1"."ACCTING_COA_ID" LIKE '2009%')这种写法导致了条件无法展开，只能走过滤
我们尝试使用hint /*+ use_concat */展开OR的条件，得到下面的执行计划（相关hint /*+ use_concat */ or条件展开 /*+ no_expand */ 条件合并）
Plan hash value: 3949042395 --------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | --------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 97 | 883 (1)| 00:00:11 | | | | 1 | HASH GROUP BY | | 1 | 97 | | | | | | 2 | CONCATENATION | | | | | | | | |* 3 | HASH JOIN | | 1 | 97 | 441 (0)| 00:00:06 | | | |* 4 | HASH JOIN OUTER | | 1 | 78 | 438 (0)| 00:00:06 | | | | 5 | TABLE ACCESS BY GLOBAL INDEX ROWID| MIM_TTT_DDDDD_BOOK_PTB | 1 | 54 | 435 (0)| 00:00:06 | 448 | 448 | |* 6 | INDEX SKIP SCAN | MIM_TTT_DDDDD_BOOK_PK | 1 | | 434 (0)| 00:00:06 | | | | 7 | PARTITION LIST SINGLE | | 34 | 816 | 3 (0)| 00:00:01 | KEY | KEY | |* 8 | TABLE ACCESS FULL | MIM_RRR_CCCC_EE_PPP | 34 | 816 | 3 (0)| 00:00:01 | 448 | 448 | |* 9 | TABLE ACCESS FULL | MIM_RRR_CCC_AAA_TTT | 218 | 4142 | 3 (0)| 00:00:01 | | | |* 10 | HASH JOIN | | 1 | 97 | 441 (0)| 00:00:06 | | | |* 11 | HASH JOIN OUTER | | 1 | 78 | 438 (0)| 00:00:06 | | | | 12 | TABLE ACCESS BY GLOBAL INDEX ROWID| MIM_TTT_DDDDD_BOOK_PTB | 1 | 54 | 435 (0)| 00:00:06 | 448 | 448 | |* 13 | INDEX SKIP SCAN | MIM_TTT_DDDDD_BOOK_PK | 1 | | 434 (0)| 00:00:06 | | | | 14 | PARTITION LIST SINGLE | | 34 | 816 | 3 (0)| 00:00:01 | KEY | KEY | |* 15 | TABLE ACCESS FULL | MIM_RRR_CCCC_EE_PPP | 34 | 816 | 3 (0)| 00:00:01 | 448 | 448 | |* 16 | TABLE ACCESS FULL | MIM_RRR_CCC_AAA_TTT | 218 | 4142 | 3 (0)| 00:00:01 | | | --------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T1"."CCY_CD"="T3"."ATTR_ID") 4 - access("T1"."CCY_CD"="T2"."NUMERATOR_CURR_CD"(+)) 6 - access("T1"."ACCTING_COA_ID" LIKE '2009%' AND "T1"."INTER_ORG_ID"='GLS9_T' AND "T1"."DATA_DATE"=TO_DATE(' 2017-01-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) filter("T1"."INTER_ORG_ID"='GLS9_T' AND "T1"."DATA_DATE"=TO_DATE(' 2017-01-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T1"."ACCTING_COA_ID" LIKE '2009%' AND "T1"."CCY_CD"<>'CNY' AND "T1"."ACCTING_INTERIM"=SUBSTR("T1"."ACCTING_DT",1,4)||'-'||SUBSTR("T1"."ACCTING_DT",5,2) AND "T1"."CCY_CD" NOT LIKE '') 8 - filter("T2"."DATA_DATE"(+)=TO_DATE(' 2017-01-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T2"."NUMERATOR_CURR_CD"(+)<>'CNY') 9 - filter("T3"."CODE_TYPE"='CURR_TYP' AND "T3"."ATTR_ID"<>'CNY') 10 - access("T1"."CCY_CD"="T3"."ATTR_ID") 11 - access("T1"."CCY_CD"="T2"."NUMERATOR_CURR_CD"(+)) 13 - access("T1"."ACCTING_COA_ID" LIKE '9522%' AND "T1"."INTER_ORG_ID"='GLS9_T' AND "T1"."DATA_DATE"=TO_DATE(' 2017-01-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) filter("T1"."INTER_ORG_ID"='GLS9_T' AND "T1"."DATA_DATE"=TO_DATE(' 2017-01-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T1"."ACCTING_COA_ID" LIKE '9522%' AND "T1"."CCY_CD"<>'CNY' AND "T1"."ACCTING_INTERIM"=SUBSTR("T1"."ACCTING_DT",1,4)||'-'||SUBSTR("T1"."ACCTING_DT",5,2) AND "T1"."CCY_CD" NOT LIKE '' AND LNNVL("T1"."ACCTING_COA_ID" LIKE '2009%')) 15 - filter("T2"."DATA_DATE"(+)=TO_DATE(' 2017-01-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T2"."NUMERATOR_CURR_CD"(+)<>'CNY') 16 - filter("T3"."CODE_TYPE"='CURR_TYP' AND "T3"."ATTR_ID"<>'CNY')从谓词第6步和第13步可以看出执行计划是按照我们的想法将OR条件展开index skip scan走正确的引导列，性能提升！！！！
其实这个SQL还可以进一步优化。重新创建以ACCTING_COA_ID为引导列的组合索引，当然生产上不让建索引，我就不往下进行了。
优化到这里我们总结一下union和or互换的情况
1）OR条件会导致无法使用条件列的索引。
2）使用HINT /*+ use_concat */ 展开OR的条件，会有一定概率无法使用到条件列索引。
3）当SQL语句中，or 条件上面有一个子查询（OR EXISTE ......）的时候，执行计划中会产生FILTER 。这个FILTER会产生很大的性能问题。这种情况只能通过使用UNION代替OR的改写来优化FILTER。
4）尽量使用UNION代替OR，可以避免很多坑。
5）UNION和OR是等价的.但是UNION效率太低。可以使用UNION ALL配合LNNVL来等价改写OR。
"T1"."ACCTING_COA_ID" LIKE '2009%' UNION ALL "T1"."ACCTING_COA_ID" LIKE '9522%' AND LNNVL("T1"."ACCTING_COA_ID" LIKE '2009%')
