Why does Union or Union all makes a query performance slow?
788879Jul 29 2011 — edited Jul 29 2011Hi,
I am using a union. Individually the two queries run in 0.3 secs. But with the union or union all it takes over 1 min. Its strange. What could be going wrong and how do I make it work.
The query is to be run in a tool, so it is inner queries on both the sides of the union clause :
select * from
(
select
T89154.SOURCE_SYS_TRADE_ID EXTERNAL_DEAL_NUMBER
from
TRADE_MESSAGE T88728 full outer join
TRADE_WSS_ACTIONS T88751 On T88728.EXTERNAL_DEAL_NUMBER = T88751.EXTERNAL_DEAL_NUMBER
full outer JOIN (select * FROM ORS.TRADE_LIFECYCLE_EVENTS WHERE ORS.TRADE_LIFECYCLE_EVENTS.PRODUCT_CAT='FX') T89154
ON ( T89154.SOURCE_SYS_TRADE_ID=T88751.EXTERNAL_DEAL_NUMBER OR
T89154.SOURCE_SYS_TRADE_ID=T88728.EXTERNAL_DEAL_NUMBER))
where EXTERNAL_DEAL_NUMBER='L11050400316200'
union all
select * from
(
select
T88751.EXTERNAL_DEAL_NUMBER EXTERNAL_DEAL_NUMBER
from
TRADE_MESSAGE T88728 full outer join
TRADE_WSS_ACTIONS T88751 On T88728.EXTERNAL_DEAL_NUMBER = T88751.EXTERNAL_DEAL_NUMBER
full outer JOIN (select * FROM ORS.TRADE_LIFECYCLE_EVENTS WHERE ORS.TRADE_LIFECYCLE_EVENTS.PRODUCT_CAT='FX') T89154
ON ( T89154.SOURCE_SYS_TRADE_ID=T88751.EXTERNAL_DEAL_NUMBER OR
T89154.SOURCE_SYS_TRADE_ID=T88728.EXTERNAL_DEAL_NUMBER))
where EXTERNAL_DEAL_NUMBER='L11050400316200'
The explain plan is
Plan
SELECT STATEMENT ALL_ROWSCost: 1,077,738 Bytes: 19,770,779 Cardinality: 1,363,502
45 UNION-ALL
22 VIEW SYS. Cost: 538,895 Bytes: 8,181,012 Cardinality: 681,751
21 UNION-ALL
7 NESTED LOOPS Cost: 538,843 Bytes: 30,678,750 Cardinality: 681,750
4 VIEW VIEW SYS.VW_FOJ_0 Cost: 25 Bytes: 74,250 Cardinality: 2,250
3 HASH JOIN FULL OUTER Cost: 25 Bytes: 74,250 Cardinality: 2,250
1 TABLE ACCESS FULL TABLE ODW.TRADE_WSS_ACTIONS Cost: 9 Bytes: 36,924 Cardinality: 2,172
2 TABLE ACCESS FULL TABLE ODW.TRADE_MESSAGE Cost: 15 Bytes: 36,000 Cardinality: 2,250
6 VIEW SYS. Cost: 239 Bytes: 3,636 Cardinality: 303
5 TABLE ACCESS FULL TABLE ORS.TRADE_LIFECYCLE_EVENTS Cost: 239 Bytes: 2,424 Cardinality: 303
20 HASH JOIN ANTI Cost: 53 Bytes: 25 Cardinality: 1
9 TABLE ACCESS BY INDEX ROWID TABLE ORS.TRADE_LIFECYCLE_EVENTS Cost: 3 Bytes: 8 Cardinality: 1
8 INDEX RANGE SCAN INDEX ORS.AK_TLC_TRADE_ID Cost: 1 Cardinality: 1
19 VIEW VIEW SYS.VW_SQ_1 Cost: 49 Bytes: 76,500 Cardinality: 4,500
18 UNION-ALL
13 VIEW VIEW ODW.VW_FOJ_1 Cost: 25 Bytes: 4,500 Cardinality: 2,250
12 HASH JOIN FULL OUTER Cost: 25 Bytes: 74,250 Cardinality: 2,250
10 TABLE ACCESS FULL TABLE ODW.TRADE_WSS_ACTIONS Cost: 9 Bytes: 36,924 Cardinality: 2,172
11 TABLE ACCESS FULL TABLE ODW.TRADE_MESSAGE Cost: 15 Bytes: 36,000 Cardinality: 2,250
17 VIEW VIEW ODW.VW_FOJ_1 Cost: 25 Bytes: 4,500 Cardinality: 2,250
16 HASH JOIN FULL OUTER Cost: 25 Bytes: 74,250 Cardinality: 2,250
14 TABLE ACCESS FULL TABLE ODW.TRADE_WSS_ACTIONS Cost: 9 Bytes: 36,924 Cardinality: 2,172
15 TABLE ACCESS FULL TABLE ODW.TRADE_MESSAGE Cost: 15 Bytes: 36,000 Cardinality: 2,250
44 VIEW SYS. Cost: 538,843 Bytes: 11,589,767 Cardinality: 681,751
43 UNION-ALL
29 NESTED LOOPS OUTER Cost: 538,843 Bytes: 22,497,750 Cardinality: 681,750
26 VIEW VIEW SYS.VW_FOJ_2 Cost: 25 Bytes: 74,250 Cardinality: 2,250
25 HASH JOIN FULL OUTER Cost: 25 Bytes: 74,250 Cardinality: 2,250
23 TABLE ACCESS FULL TABLE ODW.TRADE_WSS_ACTIONS Cost: 9 Bytes: 36,924 Cardinality: 2,172
24 TABLE ACCESS FULL TABLE ODW.TRADE_MESSAGE Cost: 15 Bytes: 36,000 Cardinality: 2,250
28 VIEW SYS. Cost: 239 Cardinality: 303
27 TABLE ACCESS FULL TABLE ORS.TRADE_LIFECYCLE_EVENTS Cost: 239 Bytes: 2,424 Cardinality: 303
42 FILTER
41 HASH JOIN RIGHT ANTI Cost: 289 Bytes: 1,517,475 Cardinality: 60,699
39 VIEW VIEW SYS.VW_SQ_2 Cost: 49 Bytes: 76,500 Cardinality: 4,500
38 UNION-ALL
33 VIEW VIEW ODW.VW_FOJ_3 Cost: 25 Bytes: 4,500 Cardinality: 2,250
32 HASH JOIN FULL OUTER Cost: 25 Bytes: 74,250 Cardinality: 2,250
30 TABLE ACCESS FULL TABLE ODW.TRADE_WSS_ACTIONS Cost: 9 Bytes: 36,924 Cardinality: 2,172
31 TABLE ACCESS FULL TABLE ODW.TRADE_MESSAGE Cost: 15 Bytes: 36,000 Cardinality: 2,250
37 VIEW VIEW ODW.VW_FOJ_3 Cost: 25 Bytes: 4,500 Cardinality: 2,250
36 HASH JOIN FULL OUTER Cost: 25 Bytes: 74,250 Cardinality: 2,250
34 TABLE ACCESS FULL TABLE ODW.TRADE_WSS_ACTIONS Cost: 9 Bytes: 36,924 Cardinality: 2,172
35 TABLE ACCESS FULL TABLE ODW.TRADE_MESSAGE Cost: 15 Bytes: 36,000 Cardinality: 2,250
40 TABLE ACCESS FULL TABLE ORS.TRADE_LIFECYCLE_EVENTS Cost: 239 Bytes: 485,632 Cardinality: 60,704