无论是教科书还是平常的实践都告诉我们 - “尽量避免用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 = ''
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 = ''
)
)
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 = ''
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 = ''
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 = ''
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 = ''
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 = ''
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"='')
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"='')
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"='')
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"='')
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"(+)='' 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"='' 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"='')
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"='' 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"='')
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"(+)='' AND "TENT"."TEMP_STATUS"(+)<>'ORIGIN' AND
"TENT"."TEMP_STATUS"(+)<>'DELETED')
81 - filter("JTEMP"."PROCESS_ID"(+)='' 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 的话，执行时间下降为秒，逻辑读下降为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"='')
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"='')
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"='')
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"='')
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"(+)='' 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"='' 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"='')
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"='' 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"='')
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"(+)='' AND "TENT"."TEMP_STATUS"(+)<>'ORIGIN' AND
"TENT"."TEMP_STATUS"(+)<>'DELETED')
76 - filter("JTEMP"."PROCESS_ID"(+)='' 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 |
|* | TABLE ACCESS FULL | ANZ_CMF_TEMP | | | ()| :: |
|* 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 |
|* | TABLE ACCESS BY INDEX ROWID | ANZ_CMF_TEMP | | | ()| :: |
|* | INDEX RANGE SCAN | I1_ANZ_CMF_TEMP | | | ()| :: |
|* 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 = ''
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 = ''
)
)
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 = ''
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 = ''
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;