无论是教科书还是平常的实践都告诉我们 - “尽量避免用UNION，尽可能用UNION ALL替代”。 原因很简单，UNION会对结果集进行排序去重操作，这是一个很消耗资源的操作。
但是，今天碰到了一个SQL (on 11.2.0.3)，如果用UNION的话，查询很快就返回结果，逻辑读不是很大。但是如果用UNION ALL的话，查询要很久才有返回，逻辑读非常大。
SQL如下（注意高亮显示的UNION ALL)
WITH v_tmp_entity_code AS ( SELECT CHILD_CODE FROM ( SELECT child_code, mother_code FROM lo_entity_links_temp WHERE process_id = '11838873' AND temp_status <> 'DELETED' AND purpose = 'Credit Hierarchy' AND TYPE = 'Customer Grouping' UNION ALL SELECT child_code, mother_code FROM lo_entity_links l WHERE purpose = 'Credit Hierarchy' AND TYPE = 'Customer Grouping' AND NOT EXISTS ( SELECT NULL FROM lo_entity_links_temp t WHERE l.id = t.id AND t.process_id = '11838873' ) ) START WITH MOTHER_CODE = 568 CONNECT BY PRIOR child_code = MOTHER_CODE ) SELECT UP_SUB.ENTITY_CODE AS "ENTITY_CODE", UP_SUB.ENTITY_NAME AS "ENTITY_NAME" FROM ( SELECT EN.ENTITY_CODE, DECODE(TA.ATTRIBUTE_6, NULL, EN.ATTRIBUTE_6, TA.ATTRIBUTE_6) AS "ATTRIBUTE_6", DECODE(TA.incorporation_country_code, NULL, EN.incorporation_country_code, TA.incorporation_country_code) AS "INCORP_COUNTRY", DECODE(TA.LONG_NAME, NULL, EN.LONG_NAME, TA.LONG_NAME) AS "ENTITY_NAME", DECODE( TA.IS_BLACKBOOK, 'T', 1, 0) IS_BLACKBOOK_CP FROM LO_ENTITY EN LEFT OUTER JOIN CD_USERS U ON EN.ATTRIBUTE_7 = U.USER_NAME LEFT OUTER JOIN LO_ENTITY_TEMP TA ON EN.ENTITY_CODE = TA.ENTITY_CODE AND TA.process_id = '11838873' AND TA.TEMP_STATUS <> 'ORIGIN' AND TA.TEMP_STATUS <> 'DELETED' AND TA.process_type = 'WORKFLOW' WHERE EN.id IN ( SELECT child_code FROM v_tmp_entity_code ) AND EN.entity_code NOT IN ( SELECT SUB_ECO.ENTITY_CODE FROM V_TEMP_ANZ_CMF SUB_ECO WHERE SUB_ECO.is_temp = 'Y' AND SUB_ECO.process_id = '11838873' AND SUB_ECO.ENTITY_CODE IN ( SELECT TO_CHAR(child_code) FROM v_tmp_entity_code ) ) ORDER BY EN.LONG_NAME ) UP_SUB UNION ALL SELECT SUPER.ENTITY_CODE AS "ENTITY_CODE", SUPER.ENTITY_NAME AS "ENTITY_NAME" FROM ( SELECT UP_SUB.ENTITY_CODE, UP_SUB.COUNTERPARTY_NAME AS "ENTITY_NAME" FROM ( SELECT SUB.* FROM ( SELECT DISTINCT DECODE(TENT.LONG_NAME, NULL, ENT.LONG_NAME, TENT.LONG_NAME) AS "COUNTERPARTY_NAME" , ENT.ENTITY_CODE, CMF.CMF_ROLE AS "ROLE", ENT.ATTRIBUTE_7 FROM V_TEMP_ANZ_CMF CMF LEFT OUTER JOIN LO_ENTITY_TEMP TENT ON CMF.ENTITY_CODE = TENT.ENTITY_CODE AND TENT.process_id = '11838873' AND TENT.TEMP_STATUS <> 'ORIGIN' AND TENT.TEMP_STATUS <> 'DELETED' LEFT OUTER JOIN LO_ENTITY ENT ON CMF.ENTITY_CODE = ENT.ENTITY_CODE LEFT OUTER JOIN LO_ENTITY_TEMP JTEMP ON JTEMP.ENTITY_CODE = CMF.GRP AND JTEMP.process_id = '11838873' AND JTEMP.TEMP_STATUS <> 'ORIGIN' AND JTEMP.TEMP_STATUS <> 'DELETED' LEFT OUTER JOIN LO_ENTITY JENT ON JENT.ENTITY_CODE = CMF.GRP WHERE CMF.is_temp = 'Y' AND CMF.process_id = '11838873' AND CMF.entity_code IN ( SELECT TO_CHAR(child_code) FROM v_tmp_entity_code ) ) SUB LEFT OUTER JOIN CD_USERS U ON SUB.ATTRIBUTE_7 = U.USER_NAME ORDER BY DECODE(SUB.ROLE, 'GRP', 1, 'GRP/CP', 2, 'CP', 3, 'AP', 4), SUB.ENTITY_CODE )UP_SUB ) SUPER ;
执行计划和统计信息如下，
Elapsed: 00:00:40.50 Execution Plan ---------------------------------------------------------- Plan hash value: 495744135 -------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 387 | 1791 (67)| 00:00:22 | | 1 | TEMP TABLE TRANSFORMATION | | | | | | | 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6FDF_24D7DA10 | | | | | |* 3 | CONNECT BY WITH FILTERING | | | | | | | 4 | VIEW | | 2 | 52 | 8 (0)| 00:00:01 | | 5 | UNION-ALL | | | | | | |* 6 | TABLE ACCESS BY INDEX ROWID | LO_ENTITY_LINKS_TEMP | 1 | 52 | 3 (0)| 00:00:01 | |* 7 | INDEX RANGE SCAN | LINKS_TEMP | 1 | | 1 (0)| 00:00:01 | | 8 | NESTED LOOPS ANTI | | 1 | 57 | 5 (0)| 00:00:01 | |* 9 | TABLE ACCESS BY INDEX ROWID | LO_ENTITY_LINKS | 1 | 45 | 2 (0)| 00:00:01 | |* 10 | INDEX RANGE SCAN | I1_LO_ENTITY_LINKS | 1 | | 1 (0)| 00:00:01 | |* 11 | TABLE ACCESS BY INDEX ROWID | LO_ENTITY_LINKS_TEMP | 1 | 12 | 3 (0)| 00:00:01 | |* 12 | INDEX RANGE SCAN | LINKS_TEMP | 1 | | 1 (0)| 00:00:01 | | 13 | VIEW | | 3 | 78 | 27 (4)| 00:00:01 | | 14 | UNION-ALL | | | | | | |* 15 | HASH JOIN | | 1 | 65 | 12 (9)| 00:00:01 | |* 16 | TABLE ACCESS BY INDEX ROWID | LO_ENTITY_LINKS_TEMP | 1 | 52 | 3 (0)| 00:00:01 | |* 17 | INDEX RANGE SCAN | LINKS_TEMP | 1 | | 1 (0)| 00:00:01 | | 18 | CONNECT BY PUMP | | | | | | |* 19 | HASH JOIN ANTI | | 2 | 140 | 16 (7)| 00:00:01 | | 20 | NESTED LOOPS | | | | | | | 21 | NESTED LOOPS | | 2 | 116 | 12 (0)| 00:00:01 | | 22 | CONNECT BY PUMP | | | | | | |* 23 | INDEX RANGE SCAN | I1_LO_ENTITY_LINKS | 1 | | 1 (0)| 00:00:01 | |* 24 | TABLE ACCESS BY INDEX ROWID | LO_ENTITY_LINKS | 1 | 45 | 2 (0)| 00:00:01 | | 25 | TABLE ACCESS BY INDEX ROWID | LO_ENTITY_LINKS_TEMP | 1 | 12 | 3 (0)| 00:00:01 | |* 26 | INDEX RANGE SCAN | LINKS_TEMP | 1 | | 1 (0)| 00:00:01 | | 27 | UNION-ALL | | | | | | | 28 | VIEW | | 1 | 129 | 602 (1)| 00:00:08 | | 29 | SORT ORDER BY | | 1 | 105 | 602 (1)| 00:00:08 | |* 30 | HASH JOIN ANTI | | 1 | 105 | 601 (1)| 00:00:08 | |* 31 | HASH JOIN OUTER | | 1 | 97 | 587 (1)| 00:00:08 | | 32 | NESTED LOOPS | | | | | | | 33 | NESTED LOOPS | | 1 | 55 | 5 (20)| 00:00:01 | | 34 | VIEW | VW_NSO_1 | 5 | 30 | 2 (0)| 00:00:01 | | 35 | HASH UNIQUE | | 1 | 30 | | | | 36 | VIEW | | 5 | 30 | 2 (0)| 00:00:01 | | 37 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6FDF_24D7DA10 | 5 | 30 | 2 (0)| 00:00:01 | |* 38 | INDEX UNIQUE SCAN | PK_LO_ENTITY | 1 | | 1 (0)| 00:00:01 | | 39 | TABLE ACCESS BY INDEX ROWID | LO_ENTITY | 1 | 49 | 2 (0)| 00:00:01 | |* 40 | TABLE ACCESS FULL | LO_ENTITY_TEMP | 2 | 84 | 581 (1)| 00:00:07 | | 41 | VIEW | VW_NSO_2 | 15 | 120 | 14 (15)| 00:00:01 | |* 42 | HASH JOIN | | 15 | 660 | 14 (15)| 00:00:01 | | 43 | VIEW | V_TEMP_ANZ_CMF | 3 | 114 | 11 (10)| 00:00:01 | | 44 | UNION-ALL | | | | | | |* 45 | TABLE ACCESS FULL | ANZ_CMF_TEMP | 1 | 25 | 3 (0)| 00:00:01 | |* 46 | FILTER | | | | | | | 47 | MERGE JOIN CARTESIAN | | 1 | 17 | 5 (20)| 00:00:01 | | 48 | VIEW | | 1 | 9 | 2 (50)| 00:00:01 | | 49 | HASH UNIQUE | | 1 | 9 | 2 (50)| 00:00:01 | |* 50 | INDEX RANGE SCAN | I1_ANZ_CMF_TEMP | 1 | 9 | 1 (0)| 00:00:01 | | 51 | BUFFER SORT | | 1 | 8 | 5 (20)| 00:00:01 | | 52 | TABLE ACCESS FULL | ANZ_CMF | 1 | 8 | 3 (0)| 00:00:01 | |* 53 | TABLE ACCESS FULL | ANZ_CMF_TEMP | 1 | 17 | 3 (0)| 00:00:01 | |* 54 | FILTER | | | | | | | 55 | TABLE ACCESS FULL | ANZ_CMF | 1 | 8 | 3 (0)| 00:00:01 | | 56 | VIEW | | 5 | 30 | 2 (0)| 00:00:01 | | 57 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6FDF_24D7DA10 | 5 | 30 | 2 (0)| 00:00:01 | | 58 | VIEW | | 2 | 258 | 1189 (1)| 00:00:15 | | 59 | SORT ORDER BY | | 2 | 376 | 1189 (1)| 00:00:15 | | 60 | VIEW | | 2 | 376 | 1188 (1)| 00:00:15 | | 61 | HASH UNIQUE | | 2 | 288 | 1188 (1)| 00:00:15 | |* 62 | HASH JOIN | | 15 | 2160 | 1187 (1)| 00:00:15 | | 63 | NESTED LOOPS OUTER | | 3 | 414 | 1184 (1)| 00:00:15 | | 64 | NESTED LOOPS OUTER | | 3 | 390 | 1181 (1)| 00:00:15 | |* 65 | HASH JOIN OUTER | | 3 | 291 | 1175 (1)| 00:00:15 | |* 66 | HASH JOIN OUTER | | 3 | 231 | 593 (1)| 00:00:08 | | 67 | VIEW | V_TEMP_ANZ_CMF | 3 | 150 | 11 (10)| 00:00:01 | | 68 | UNION-ALL | | | | | | |* 69 | TABLE ACCESS FULL | ANZ_CMF_TEMP | 1 | 37 | 3 (0)| 00:00:01 | |* 70 | FILTER | | | | | | | 71 | MERGE JOIN CARTESIAN | | 1 | 29 | 5 (20)| 00:00:01 | | 72 | VIEW | | 1 | 9 | 2 (50)| 00:00:01 | | 73 | HASH UNIQUE | | 1 | 9 | 2 (50)| 00:00:01 | |* 74 | INDEX RANGE SCAN | I1_ANZ_CMF_TEMP | 1 | 9 | 1 (0)| 00:00:01 | | 75 | BUFFER SORT | | 1 | 20 | 5 (20)| 00:00:01 | | 76 | TABLE ACCESS FULL | ANZ_CMF | 1 | 20 | 3 (0)| 00:00:01 | |* 77 | TABLE ACCESS FULL | ANZ_CMF_TEMP | 1 | 17 | 3 (0)| 00:00:01 | |* 78 | FILTER | | | | | | | 79 | TABLE ACCESS FULL | ANZ_CMF | 1 | 20 | 3 (0)| 00:00:01 | |* 80 | TABLE ACCESS FULL | LO_ENTITY_TEMP | 2 | 54 | 581 (1)| 00:00:07 | |* 81 | TABLE ACCESS FULL | LO_ENTITY_TEMP | 2 | 40 | 581 (1)| 00:00:07 | | 82 | TABLE ACCESS BY INDEX ROWID| LO_ENTITY | 1 | 33 | 2 (0)| 00:00:01 | |* 83 | INDEX UNIQUE SCAN | I6_LO_ENTITY | 1 | | 1 (0)| 00:00:01 | |* 84 | INDEX UNIQUE SCAN | I6_LO_ENTITY | 1 | 8 | 1 (0)| 00:00:01 | | 85 | VIEW | | 5 | 30 | 2 (0)| 00:00:01 | | 86 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6FDF_24D7DA10 | 5 | 30 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("MOTHER_CODE"=PRIOR "CHILD_CODE") 6 - filter("MOTHER_CODE"=568 AND "TYPE"='Customer Grouping' AND "PURPOSE"='Credit Hierarchy' AND "TEMP_STATUS"<>'DELETED') 7 - access("PROCESS_ID"='11838873') 9 - filter("TYPE"='Customer Grouping' AND "PURPOSE"='Credit Hierarchy') 10 - access("MOTHER_CODE"=568) 11 - filter("L"."ID"="T"."ID") 12 - access("T"."PROCESS_ID"='11838873') 15 - access("connect$_by$_pump$_005"."PRIOR child_code "="MOTHER_CODE") 16 - filter("TYPE"='Customer Grouping' AND "PURPOSE"='Credit Hierarchy' AND "TEMP_STATUS"<>'DELETED') 17 - access("PROCESS_ID"='11838873') 19 - access("L"."ID"="T"."ID") 23 - access("connect$_by$_pump$_005"."PRIOR child_code "="MOTHER_CODE") 24 - filter("TYPE"='Customer Grouping' AND "PURPOSE"='Credit Hierarchy') 26 - access("T"."PROCESS_ID"='11838873') 30 - access("EN"."ENTITY_CODE"="ENTITY_CODE") 31 - access("EN"."ENTITY_CODE"="TA"."ENTITY_CODE"(+)) 38 - access("EN"."ID"="CHILD_CODE") 40 - filter("TA"."PROCESS_ID"(+)='11838873' AND "TA"."TEMP_STATUS"(+)<>'ORIGIN' AND "TA"."TEMP_STATUS"(+)<>'DELETED' AND "TA"."PROCESS_TYPE"(+)='WORKFLOW') 42 - access("SUB_ECO"."ENTITY_CODE"=TO_CHAR("CHILD_CODE")) 45 - filter("PROCESS_ID"='11838873' AND "TEMP_STATUS"<>'DELETED') 46 - filter( NOT EXISTS (SELECT 0 FROM RO_ANZ_CI."ANZ_CMF_TEMP" "ANZ_CMF_TEMP" WHERE "PROCESS_ID"=:B1 AND "ENTITY_CODE"=:B2)) 50 - access("PROCESS_ID"='11838873') 53 - filter("PROCESS_ID"=:B1 AND "ENTITY_CODE"=:B2) 54 - filter(NULL IS NOT NULL AND NULL IS NOT NULL) 62 - access("CMF"."ENTITY_CODE"=TO_CHAR("CHILD_CODE")) 65 - access("JTEMP"."ENTITY_CODE"(+)="CMF"."GRP") 66 - access("CMF"."ENTITY_CODE"="TENT"."ENTITY_CODE"(+)) 69 - filter("PROCESS_ID"='11838873' AND "TEMP_STATUS"<>'DELETED') 70 - filter( NOT EXISTS (SELECT 0 FROM RO_ANZ_CI."ANZ_CMF_TEMP" "ANZ_CMF_TEMP" WHERE "PROCESS_ID"=:B1 AND "ENTITY_CODE"=:B2)) 74 - access("PROCESS_ID"='11838873') 77 - filter("PROCESS_ID"=:B1 AND "ENTITY_CODE"=:B2) 78 - filter(NULL IS NOT NULL AND NULL IS NOT NULL) 80 - filter("TENT"."PROCESS_ID"(+)='11838873' AND "TENT"."TEMP_STATUS"(+)<>'ORIGIN' AND "TENT"."TEMP_STATUS"(+)<>'DELETED') 81 - filter("JTEMP"."PROCESS_ID"(+)='11838873' AND "JTEMP"."TEMP_STATUS"(+)<>'ORIGIN' AND "JTEMP"."TEMP_STATUS"(+)<>'DELETED') 83 - access("CMF"."ENTITY_CODE"="ENT"."ENTITY_CODE"(+)) 84 - access("JENT"."ENTITY_CODE"(+)="CMF"."GRP") Statistics ---------------------------------------------------------- 59 recursive calls 22053 db block gets 5642276 consistent gets 1 physical reads 676 redo size 667 bytes sent via SQL*Net to client 499 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 8 sorts (memory) 0 sorts (disk) 2 rows processed SQL>
先不看至今计划，单看执行时间 - 大约40秒，逻辑读 5,642,276
如果把UNION ALL 改成 UNION 的话，执行时间下降为2秒，逻辑读下降为106，830 ！
Elapsed: 00:00:02.04 Execution Plan ---------------------------------------------------------- Plan hash value: 1306395122 ---------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 20 | 3350 | 1863 (66)| 00:00:23 | | 1 | TEMP TABLE TRANSFORMATION | | | | | | | 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6FE3_24D7DA10 | | | | | |* 3 | CONNECT BY WITH FILTERING | | | | | | | 4 | VIEW | | 2 | 52 | 8 (0)| 00:00:01 | | 5 | UNION-ALL | | | | | | |* 6 | TABLE ACCESS BY INDEX ROWID | LO_ENTITY_LINKS_TEMP | 1 | 52 | 3 (0)| 00:00:01 | |* 7 | INDEX RANGE SCAN | LINKS_TEMP | 1 | | 1 (0)| 00:00:01 | | 8 | NESTED LOOPS ANTI | | 1 | 57 | 5 (0)| 00:00:01 | |* 9 | TABLE ACCESS BY INDEX ROWID | LO_ENTITY_LINKS | 1 | 45 | 2 (0)| 00:00:01 | |* 10 | INDEX RANGE SCAN | I1_LO_ENTITY_LINKS | 1 | | 1 (0)| 00:00:01 | |* 11 | TABLE ACCESS BY INDEX ROWID | LO_ENTITY_LINKS_TEMP | 1 | 12 | 3 (0)| 00:00:01 | |* 12 | INDEX RANGE SCAN | LINKS_TEMP | 1 | | 1 (0)| 00:00:01 | | 13 | VIEW | | 3 | 78 | 27 (4)| 00:00:01 | | 14 | UNION-ALL | | | | | | |* 15 | HASH JOIN | | 1 | 65 | 12 (9)| 00:00:01 | |* 16 | TABLE ACCESS BY INDEX ROWID | LO_ENTITY_LINKS_TEMP | 1 | 52 | 3 (0)| 00:00:01 | |* 17 | INDEX RANGE SCAN | LINKS_TEMP | 1 | | 1 (0)| 00:00:01 | | 18 | CONNECT BY PUMP | | | | | | |* 19 | HASH JOIN ANTI | | 2 | 140 | 16 (7)| 00:00:01 | | 20 | NESTED LOOPS | | | | | | | 21 | NESTED LOOPS | | 2 | 116 | 12 (0)| 00:00:01 | | 22 | CONNECT BY PUMP | | | | | | |* 23 | INDEX RANGE SCAN | I1_LO_ENTITY_LINKS | 1 | | 1 (0)| 00:00:01 | |* 24 | TABLE ACCESS BY INDEX ROWID | LO_ENTITY_LINKS | 1 | 45 | 2 (0)| 00:00:01 | | 25 | TABLE ACCESS BY INDEX ROWID | LO_ENTITY_LINKS_TEMP | 1 | 12 | 3 (0)| 00:00:01 | |* 26 | INDEX RANGE SCAN | LINKS_TEMP | 1 | | 1 (0)| 00:00:01 | | 27 | SORT UNIQUE | | 20 | 3350 | 1863 (66)| 00:00:23 | | 28 | UNION-ALL | | | | | | |* 29 | HASH JOIN ANTI | | 5 | 575 | 641 (1)| 00:00:08 | |* 30 | HASH JOIN OUTER | | 5 | 440 | 594 (1)| 00:00:08 | | 31 | NESTED LOOPS | | | | | | | 32 | NESTED LOOPS | | 5 | 260 | 12 (0)| 00:00:01 | | 33 | VIEW | | 5 | 65 | 2 (0)| 00:00:01 | | 34 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6FE3_24D7DA10 | 5 | 30 | 2 (0)| 00:00:01 | |* 35 | INDEX UNIQUE SCAN | PK_LO_ENTITY | 1 | | 1 (0)| 00:00:01 | | 36 | TABLE ACCESS BY INDEX ROWID | LO_ENTITY | 1 | 39 | 2 (0)| 00:00:01 | |* 37 | TABLE ACCESS FULL | LO_ENTITY_TEMP | 2 | 72 | 581 (1)| 00:00:07 | | 38 | VIEW | VW_NSO_1 | 15 | 405 | 47 (5)| 00:00:01 | |* 39 | HASH JOIN | | 15 | 1050 | 47 (5)| 00:00:01 | | 40 | VIEW | V_TEMP_ANZ_CMF | 3 | 171 | 44 (3)| 00:00:01 | | 41 | UNION-ALL | | | | | | |* 42 | TABLE ACCESS FULL | ANZ_CMF_TEMP | 1 | 25 | 3 (0)| 00:00:01 | |* 43 | FILTER | | | | | | | 44 | MERGE JOIN CARTESIAN | | 1 | 35 | 5 (20)| 00:00:01 | | 45 | VIEW | | 1 | 27 | 2 (50)| 00:00:01 | | 46 | HASH UNIQUE | | 1 | 9 | 2 (50)| 00:00:01 | |* 47 | INDEX RANGE SCAN | I1_ANZ_CMF_TEMP | 1 | 9 | 1 (0)| 00:00:01 | | 48 | BUFFER SORT | | 1 | 8 | 5 (20)| 00:00:01 | | 49 | TABLE ACCESS FULL | ANZ_CMF | 1 | 8 | 3 (0)| 00:00:01 | |* 50 | TABLE ACCESS BY INDEX ROWID | ANZ_CMF_TEMP | 1 | 17 | 36 (0)| 00:00:01 | |* 51 | INDEX RANGE SCAN | I1_ANZ_CMF_TEMP | 41 | | 1 (0)| 00:00:01 | |* 52 | FILTER | | | | | | | 53 | TABLE ACCESS FULL | ANZ_CMF | 1 | 8 | 3 (0)| 00:00:01 | | 54 | VIEW | | 5 | 65 | 2 (0)| 00:00:01 | | 55 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6FE3_24D7DA10 | 5 | 30 | 2 (0)| 00:00:01 | |* 56 | HASH JOIN | | 15 | 2775 | 1220 (1)| 00:00:15 | | 57 | NESTED LOOPS OUTER | | 3 | 516 | 1217 (1)| 00:00:15 | | 58 | NESTED LOOPS OUTER | | 3 | 492 | 1214 (1)| 00:00:15 | |* 59 | HASH JOIN OUTER | | 3 | 393 | 1208 (1)| 00:00:15 | |* 60 | HASH JOIN OUTER | | 3 | 333 | 626 (1)| 00:00:08 | | 61 | VIEW | V_TEMP_ANZ_CMF | 3 | 252 | 44 (3)| 00:00:01 | | 62 | UNION-ALL | | | | | | |* 63 | TABLE ACCESS FULL | ANZ_CMF_TEMP | 1 | 37 | 3 (0)| 00:00:01 | |* 64 | FILTER | | | | | | | 65 | MERGE JOIN CARTESIAN | | 1 | 47 | 5 (20)| 00:00:01 | | 66 | VIEW | | 1 | 27 | 2 (50)| 00:00:01 | | 67 | HASH UNIQUE | | 1 | 9 | 2 (50)| 00:00:01 | |* 68 | INDEX RANGE SCAN | I1_ANZ_CMF_TEMP | 1 | 9 | 1 (0)| 00:00:01 | | 69 | BUFFER SORT | | 1 | 20 | 5 (20)| 00:00:01 | | 70 | TABLE ACCESS FULL | ANZ_CMF | 1 | 20 | 3 (0)| 00:00:01 | |* 71 | TABLE ACCESS BY INDEX ROWID| ANZ_CMF_TEMP | 1 | 17 | 36 (0)| 00:00:01 | |* 72 | INDEX RANGE SCAN | I1_ANZ_CMF_TEMP | 41 | | 1 (0)| 00:00:01 | |* 73 | FILTER | | | | | | | 74 | TABLE ACCESS FULL | ANZ_CMF | 1 | 20 | 3 (0)| 00:00:01 | |* 75 | TABLE ACCESS FULL | LO_ENTITY_TEMP | 2 | 54 | 581 (1)| 00:00:07 | |* 76 | TABLE ACCESS FULL | LO_ENTITY_TEMP | 2 | 40 | 581 (1)| 00:00:07 | | 77 | TABLE ACCESS BY INDEX ROWID | LO_ENTITY | 1 | 33 | 2 (0)| 00:00:01 | |* 78 | INDEX UNIQUE SCAN | I6_LO_ENTITY | 1 | | 1 (0)| 00:00:01 | |* 79 | INDEX UNIQUE SCAN | I6_LO_ENTITY | 1 | 8 | 1 (0)| 00:00:01 | | 80 | VIEW | | 5 | 65 | 2 (0)| 00:00:01 | | 81 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6FE3_24D7DA10 | 5 | 30 | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("MOTHER_CODE"=PRIOR "CHILD_CODE") 6 - filter("MOTHER_CODE"=568 AND "TYPE"='Customer Grouping' AND "PURPOSE"='Credit Hierarchy' AND "TEMP_STATUS"<>'DELETED') 7 - access("PROCESS_ID"='11838873') 9 - filter("TYPE"='Customer Grouping' AND "PURPOSE"='Credit Hierarchy') 10 - access("MOTHER_CODE"=568) 11 - filter("L"."ID"="T"."ID") 12 - access("T"."PROCESS_ID"='11838873') 15 - access("connect$_by$_pump$_005"."PRIOR child_code "="MOTHER_CODE") 16 - filter("TYPE"='Customer Grouping' AND "PURPOSE"='Credit Hierarchy' AND "TEMP_STATUS"<>'DELETED') 17 - access("PROCESS_ID"='11838873') 19 - access("L"."ID"="T"."ID") 23 - access("connect$_by$_pump$_005"."PRIOR child_code "="MOTHER_CODE") 24 - filter("TYPE"='Customer Grouping' AND "PURPOSE"='Credit Hierarchy') 26 - access("T"."PROCESS_ID"='11838873') 29 - access("EN"."ENTITY_CODE"="ENTITY_CODE") 30 - access("EN"."ENTITY_CODE"="TA"."ENTITY_CODE"(+)) 35 - access("EN"."ID"="CHILD_CODE") 37 - filter("TA"."PROCESS_ID"(+)='11838873' AND "TA"."TEMP_STATUS"(+)<>'ORIGIN' AND "TA"."TEMP_STATUS"(+)<>'DELETED' AND "TA"."PROCESS_TYPE"(+)='WORKFLOW') 39 - access("SUB_ECO"."ENTITY_CODE"=TO_CHAR("CHILD_CODE")) 42 - filter("PROCESS_ID"='11838873' AND "TEMP_STATUS"<>'DELETED') 43 - filter( NOT EXISTS (SELECT 0 FROM RO_ANZ_CI."ANZ_CMF_TEMP" "ANZ_CMF_TEMP" WHERE "PROCESS_ID"=:B1 AND "ENTITY_CODE"=:B2)) 47 - access("PROCESS_ID"='11838873') 50 - filter("ENTITY_CODE"=:B1) 51 - access("PROCESS_ID"=:B1) 52 - filter(NULL IS NOT NULL AND NULL IS NOT NULL) 56 - access("CMF"."ENTITY_CODE"=TO_CHAR("CHILD_CODE")) 59 - access("JTEMP"."ENTITY_CODE"(+)="CMF"."GRP") 60 - access("CMF"."ENTITY_CODE"="TENT"."ENTITY_CODE"(+)) 63 - filter("PROCESS_ID"='11838873' AND "TEMP_STATUS"<>'DELETED') 64 - filter( NOT EXISTS (SELECT 0 FROM RO_ANZ_CI."ANZ_CMF_TEMP" "ANZ_CMF_TEMP" WHERE "PROCESS_ID"=:B1 AND "ENTITY_CODE"=:B2)) 68 - access("PROCESS_ID"='11838873') 71 - filter("ENTITY_CODE"=:B1) 72 - access("PROCESS_ID"=:B1) 73 - filter(NULL IS NOT NULL AND NULL IS NOT NULL) 75 - filter("TENT"."PROCESS_ID"(+)='11838873' AND "TENT"."TEMP_STATUS"(+)<>'ORIGIN' AND "TENT"."TEMP_STATUS"(+)<>'DELETED') 76 - filter("JTEMP"."PROCESS_ID"(+)='11838873' AND "JTEMP"."TEMP_STATUS"(+)<>'ORIGIN' AND "JTEMP"."TEMP_STATUS"(+)<>'DELETED') 78 - access("CMF"."ENTITY_CODE"="ENT"."ENTITY_CODE"(+)) 79 - access("JENT"."ENTITY_CODE"(+)="CMF"."GRP") Statistics ---------------------------------------------------------- 56 recursive calls 13 db block gets 106830 consistent gets 1 physical reads 1344 redo size 667 bytes sent via SQL*Net to client 499 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 7 sorts (memory) 0 sorts (disk) 2 rows processed SQL>
很显然，造成执行时间差别这么大的原因在于逻辑读的差别。逻辑读的差别肯定来自于执行计划的差别。比较下两种情况下的执行计划，主要不同在于访问表ANZ_CMF_TEMP的方式不同 - 用UNION ALL的时候没有用索引扫描！
(1) UNION ALL
| 43 | VIEW | V_TEMP_ANZ_CMF | 3 | 114 | 11 (10)| 00:00:01 | | 44 | UNION-ALL | | | | | | |* 45 | TABLE ACCESS FULL | ANZ_CMF_TEMP | 1 | 25 | 3 (0)| 00:00:01 | |* 46 | FILTER | | | | | | | 47 | MERGE JOIN CARTESIAN | | 1 | 17 | 5 (20)| 00:00:01 | | 48 | VIEW | | 1 | 9 | 2 (50)| 00:00:01 | | 49 | HASH UNIQUE | | 1 | 9 | 2 (50)| 00:00:01 | |* 50 | INDEX RANGE SCAN | I1_ANZ_CMF_TEMP | 1 | 9 | 1 (0)| 00:00:01 | | 51 | BUFFER SORT | | 1 | 8 | 5 (20)| 00:00:01 | | 52 | TABLE ACCESS FULL | ANZ_CMF | 1 | 8 | 3 (0)| 00:00:01 | |* 53 | TABLE ACCESS FULL | ANZ_CMF_TEMP | 1 | 17 | 3 (0)| 00:00:01 | |* 54 | FILTER | | | | | | | 55 | TABLE ACCESS FULL | ANZ_CMF | 1 | 8 | 3 (0)| 00:00:01 |
(2) UNION
| 40 | VIEW | V_TEMP_ANZ_CMF | 3 | 171 | 44 (3)| 00:00:01 | | 41 | UNION-ALL | | | | | | |* 42 | TABLE ACCESS FULL | ANZ_CMF_TEMP | 1 | 25 | 3 (0)| 00:00:01 | |* 43 | FILTER | | | | | | | 44 | MERGE JOIN CARTESIAN | | 1 | 35 | 5 (20)| 00:00:01 | | 45 | VIEW | | 1 | 27 | 2 (50)| 00:00:01 | | 46 | HASH UNIQUE | | 1 | 9 | 2 (50)| 00:00:01 | |* 47 | INDEX RANGE SCAN | I1_ANZ_CMF_TEMP | 1 | 9 | 1 (0)| 00:00:01 | | 48 | BUFFER SORT | | 1 | 8 | 5 (20)| 00:00:01 | | 49 | TABLE ACCESS FULL | ANZ_CMF | 1 | 8 | 3 (0)| 00:00:01 | |* 50 | TABLE ACCESS BY INDEX ROWID | ANZ_CMF_TEMP | 1 | 17 | 36 (0)| 00:00:01 | |* 51 | INDEX RANGE SCAN | I1_ANZ_CMF_TEMP | 41 | | 1 (0)| 00:00:01 | |* 52 | FILTER | | | | | | | 53 | TABLE ACCESS FULL | ANZ_CMF | 1 | 8 | 3 (0)| 00:00:01 |
这个例子告诉我们用UNION ALL去替换UNION的时候要谨防执行计划的改变！
其实这个SQL如果只是执行UNION (ALL)的上面的SQL，或是下面的SQL都会很慢，因为执行计划都是走了表扫描的方式来访问ANZ_CMP_TEMP。如果要改变执行计划（比如只执行UNION的上面部分）怎么办呢，基于这个例子启发，尝试用了如下的写法（highlight部分），结果执行计划就走了索引扫描。
WITH v_tmp_entity_code AS ( SELECT CHILD_CODE FROM ( SELECT child_code, mother_code FROM lo_entity_links_temp WHERE process_id = '11838873' AND temp_status <> 'DELETED' AND purpose = 'Credit Hierarchy' AND TYPE = 'Customer Grouping' UNION ALL SELECT child_code, mother_code FROM lo_entity_links l WHERE purpose = 'Credit Hierarchy' AND TYPE = 'Customer Grouping' AND NOT EXISTS ( SELECT NULL FROM lo_entity_links_temp t WHERE l.id = t.id AND t.process_id = '11838873' ) ) START WITH MOTHER_CODE = 568 CONNECT BY PRIOR child_code = MOTHER_CODE ) SELECT UP_SUB.ENTITY_CODE AS "ENTITY_CODE", UP_SUB.ENTITY_NAME AS "ENTITY_NAME" FROM ( SELECT EN.ENTITY_CODE, DECODE(TA.ATTRIBUTE_6, NULL, EN.ATTRIBUTE_6, TA.ATTRIBUTE_6) AS "ATTRIBUTE_6", DECODE(TA.incorporation_country_code, NULL, EN.incorporation_country_code, TA.incorporation_country_code) AS "INCORP_COUNTRY", DECODE(TA.LONG_NAME, NULL, EN.LONG_NAME, TA.LONG_NAME) AS "ENTITY_NAME", DECODE( TA.IS_BLACKBOOK, 'T', 1, 0) IS_BLACKBOOK_CP FROM LO_ENTITY EN LEFT OUTER JOIN CD_USERS U ON EN.ATTRIBUTE_7 = U.USER_NAME LEFT OUTER JOIN LO_ENTITY_TEMP TA ON EN.ENTITY_CODE = TA.ENTITY_CODE AND TA.process_id = '11838873' AND TA.TEMP_STATUS <> 'ORIGIN' AND TA.TEMP_STATUS <> 'DELETED' AND TA.process_type = 'WORKFLOW' WHERE EN.id IN ( SELECT child_code FROM v_tmp_entity_code ) AND EN.entity_code NOT IN ( SELECT SUB_ECO.ENTITY_CODE FROM V_TEMP_ANZ_CMF SUB_ECO WHERE SUB_ECO.is_temp = 'Y' AND SUB_ECO.process_id = '11838873' AND SUB_ECO.ENTITY_CODE IN ( SELECT TO_CHAR(child_code) FROM v_tmp_entity_code ) ) ORDER BY EN.LONG_NAME ) UP_SUB UNION SELECT NULL, NULL FROM DUAL WHERE 1=0;