背景是在一台11gR2的机器上，开发反映一个批处理比以前慢了3倍。经过仔细查看该SQL的执行计划，发现由于SQL中使用了or，导致CBO走出了一个非常糟糕的CONCATENATION路径。
no_expand提示的说明是 The NO_EXPAND hint prevents the cost-based optimizer from considering OR-expansion for queries having OR conditions or IN-lists in the WHERE clause. Usually, the optimizer considers using OR expansion and uses this method if it decides that the cost is lower than not using it.use_concat提示的说明是The USE_CONCAT hint forces combined OR conditions in the WHERE clause of a query to be transformed into a compound query using the UNION ALL set operator. Generally, this transformation occurs only if the cost of the query using the concatenations is cheaper than the cost without them. 为了重现这个问题，必须使用/*+ use_concat */来模拟。explain plan for SELECT /*+ use_concat */ 20130620, B.mgr_code , B.mgr_name , B.cur_name , '3.4.2' , nvl(sum(ADJUST_AMT_AF),0) as acct_bal , nvl(sum(D_CMP_BAL),0) , nvl(sum(M_CMP_BAL),0) , nvl(sum(Y_CMP_BAL),0) , nvl(sum(Y_avg_af),0) as Y_avg_bal, B.cur_code, B.unit1_code, B.unit2_code, B.unit3_code, B.dept1_code, A.idx_name FROM S_PM_IDX_CODE A,T_PM_ACCT_DTL_AF B where B.ACCT_FLAG in('OPEN','LOAN') AND A.ROWID='AABK8vAAyAAGg/2AAs' AND SUBSTR(B.FLAG,1,1) IN ('3') AND SUBSTR(B.FLAG,2,1) IN ('2') AND SUBSTR(B.FLAG,3,1) IN ('2') AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 and ((A.term_flag is null) or (A.term_flag is not null and A.begin_term = B.term and B.term_flag = A.term_flag)) AND B.data_date = 20130620 group by B.mgr_code, B.mgr_name, B.cur_code, B.cur_name, B.unit1_code,B.unit2_code, B.unit3_code, B.dept1_code, A.IDX_NAME;Plan hash value: 542663423 ------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 240 | 47236 (1)| 00:09:27 | | || 1 | HASH GROUP BY | | 1 | 240 | | | | || 2 | CONCATENATION | | | | | | | || 3 | NESTED LOOPS | | 1 | 240 | 23609 (1)| 00:04:44 | | ||* 4 | TABLE ACCESS BY USER ROWID| S_PM_IDX_CODE | 1 | 97 | 1 (0)| 00:00:01 | | || 5 | PARTITION LIST SINGLE | | 1 | 143 | 23608 (1)| 00:04:44 | KEY | KEY ||* 6 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 1 | 143 | 23608 (1)| 00:04:44 | 549 | 549 || 7 | NESTED LOOPS | | 1 | 240 | 23625 (1)| 00:04:44 | | ||* 8 | TABLE ACCESS BY USER ROWID| S_PM_IDX_CODE | 1 | 97 | 1 (0)| 00:00:01 | | || 9 | PARTITION LIST SINGLE | | 1 | 143 | 23624 (1)| 00:04:44 | KEY | KEY ||* 10 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 1 | 143 | 23624 (1)| 00:04:44 | 549 | 549 |------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id):--------------------------------------------------- 4 - filter("A"."TERM_FLAG" IS NOT NULL) 6 - filter("A"."BEGIN_TERM"<"B"."TERM" AND "A"."END_TERM">="B"."TERM" AND "B"."TERM_FLAG"="A"."TERM_FLAG" AND ("B"."ACCT_FLAG"='LOAN' OR "B"."ACCT_FLAG"='OPEN') AND SUBSTR("B"."FLAG",2,1)='2' AND SUBSTR("B"."FLAG",3,1)='2' AND SUBSTR("B"."FLAG",1,1)='3' AND "B"."DATA_DATE"=20130620) 8 - filter("A"."TERM_FLAG" IS NULL) 10 - filter(("B"."ACCT_FLAG"='LOAN' OR "B"."ACCT_FLAG"='OPEN') AND SUBSTR("B"."FLAG",2,1)='2' AND SUBSTR("B"."FLAG",3,1)='2' AND SUBSTR("B"."FLAG",1,1)='3' AND "B"."DATA_DATE"=20130620 AND (LNNVL("B"."TERM_FLAG"="A"."TERM_FLAG") OR LNNVL("A"."BEGIN_TERM"<"B"."TERM") OR LNNVL("A"."END_TERM">="B"."TERM") OR LNNVL("A"."TERM_FLAG" IS NOT NULL))) Note----- - dynamic sampling used for this statement (level=2)explain plan for SELECT 20130620, B.mgr_code , B.mgr_name , B.cur_name , '3.4.2' , nvl(sum(ADJUST_AMT_AF),0) as acct_bal , nvl(sum(D_CMP_BAL),0) , nvl(sum(M_CMP_BAL),0) , nvl(sum(Y_CMP_BAL),0) , nvl(sum(Y_avg_af),0) as Y_avg_bal, B.cur_code, B.unit1_code, B.unit2_code, B.unit3_code, B.dept1_code, A.idx_name FROM S_PM_IDX_CODE A,T_PM_ACCT_DTL_AF B where B.ACCT_FLAG in('OPEN','LOAN') AND A.ROWID='AABK8vAAyAAGg/2AAs' AND SUBSTR(B.FLAG,1,1) IN ('3') AND SUBSTR(B.FLAG,2,1) IN ('2') AND SUBSTR(B.FLAG,3,1) IN ('2') AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 and ((A.term_flag is null) or (A.term_flag is not null and A.begin_term = B.term and B.term_flag = A.term_flag)) AND B.data_date = 20130620 group by B.mgr_code, B.mgr_name, B.cur_code, B.cur_name, B.unit1_code,B.unit2_code, B.unit3_code, B.dept1_code, A.IDX_NAME;select * from table(dbms_xplan.display());Plan hash value: 2396922436 ------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 240 | 8058 (1)| 00:01:37 | | || 1 | HASH GROUP BY | | 1 | 240 | 8058 (1)| 00:01:37 | | || 2 | NESTED LOOPS | | 1 | 240 | 8057 (1)| 00:01:37 | | || 3 | TABLE ACCESS BY USER ROWID | S_PM_IDX_CODE | 1 | 97 | 1 (0)| 00:00:01 | | || 4 | PARTITION LIST SINGLE | | 1 | 143 | 8056 (1)| 00:01:37 | KEY | KEY ||* 5 | TABLE ACCESS BY LOCAL INDEX ROWID| T_PM_ACCT_DTL_AF | 1 | 143 | 8056 (1)| 00:01:37 | 549 | 549 ||* 6 | INDEX RANGE SCAN | T_PM_ACCT_DTL_AF_IDX1 | 35022 | | 136 (1)| 00:00:02 | 549 | 549 |------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id):--------------------------------------------------- 5 - filter(("B"."ACCT_FLAG"='LOAN' OR "B"."ACCT_FLAG"='OPEN') AND SUBSTR("B"."FLAG",2,1)='2' AND SUBSTR("B"."FLAG",3,1)='2' AND ("A"."TERM_FLAG" IS NULL OR "B"."TERM_FLAG"="A"."TERM_FLAG" AND "A"."BEGIN_TERM"<"B"."TERM" AND "A"."END_TERM">="B"."TERM" AND "A"."TERM_FLAG" IS NOT NULL)) 6 - access(SUBSTR("FLAG",1,1)='3') Note----- - dynamic sampling used for this statement (level=2)