union and union all and exists performance improvement

Purpose :
This document shows that one need to avoid union and union all as far as possible to avoid the performance impact.

Time Needed : 10min

Preparation

set autotrace on

1. Test Case 1 (Normally developer writes like this)

select * from dual
union
select * from dual;

Observe the sorting required.

2. Test Case 2 (This is what it should be)

select * from dual
union ALL
select * from dual;
Observe compare to first case it does not need sorting.But then you get extra row.

See the Large Scale Impact and real life example
select *
from dual
where exists
(select null from all_objects
union
select null from dba_objects
union
select null from all_users);

Statistics
——————————————————-
0 recursive calls
0 db block gets
534050 consistent gets
0 physical reads
0 redo size
212 bytes sent via SQL*Net to client
276 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

now replace union with union all since union all start giving the row faster exists cuts of faster. in Union it does not return the first row till the end of the sorting of complete query.

select *
from dual
where exists
(select null from all_objects
union all
select null from dba_objects
union all
select null from all_users);

Statistics
———————————————————-
0 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
212 bytes sent via SQL*Net to client
276 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

look at the reduction in consistent gets and sorting. So as far as possible use the union all and avoid sorting and especially in subquery with exists this could be really powerful.

Backout Procedure
set autotrace off

Advertisements

One Response to “union and union all and exists performance improvement”

  1. Terri Says:

    Writing blog is tedious.I know how you can get unlimited articles for your website,
    search in google:
    Anightund’s rewriter

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: