Union vs Union All - Why does Union run faster?

Dev Shed Forums Sponsor:

August 23rd, 2004, 07:05 PM

ProggerPete

Contributing User

Join Date: May 2003

Location: Brisbane, Australia

Posts: 1,438

Time spent in forums: 1 Week 1 Day 1 h 16 m 25 sec
Reputation Power: 23

Union vs Union All - Why does Union run faster?

This is more curiosity than compelling need. I've got a query that pulls some info out of the database. I originally built the query using union all to combine the results of about 12 queries. The final query ran pretty slowly, taking about 40 seconds.

I fiddled with it a bit trying to optimise and discovered (to my suprise) that swapping union for union all improved the execution speed dramatically. The whole query now runs in 2 seconds. I had to extract data from some extra columns to ensure row uniqueness because I didn't want the distinct behavior that union provides. But even getting extra and unneccessary data it still runs 20 times faster than the same query using union all.

Can anyone give me an idea as to why this might be so? I would have thought that union would work harder than union all because it does the check for uniqueness, but this doesn't seem to be the case.

August 24th, 2004, 02:23 PM

jim mcnamara

......@.........

Join Date: Jun 2004

Posts: 1,308

Time spent in forums: 1 Week 3 Days 5 h 20 m 49 sec
Reputation Power: 48

Without knowledge of the schema -

This may not happen with your next query - it depends on what the optimizer decides to do. UNION ALL may turn off index use and do a full table scan. I don't know

Try EXPLAIN PLAN for each query to see the difference between the two.

August 24th, 2004, 05:42 PM

ProggerPete

Contributing User

Join Date: May 2003

Location: Brisbane, Australia

Posts: 1,438

Time spent in forums: 1 Week 1 Day 1 h 16 m 25 sec
Reputation Power: 23

How do I use Explain Plan? I'm pretty new to oracle. I tried it in sqlplus but had no luck

August 24th, 2004, 06:06 PM

shammat

Contributing User

Join Date: Oct 2003

Location: Munich, Bavaria

Posts: 1,007

Time spent in forums: 1 Week 3 Days 13 h 7 m 13 sec
Reputation Power: 67

Quote:

Originally Posted by ProggerPete

How do I use Explain Plan? I'm pretty new to oracle. I tried it in sqlplus but had no luck

It's explained in the SQL Reference manual (it is not as straight-forward as with other DBMS)

http://download-west.oracle.com/doc...11a.htm#2061798

(You need to register if not already done)

August 24th, 2004, 06:12 PM

ProggerPete

Contributing User

Join Date: May 2003

Location: Brisbane, Australia

Posts: 1,438

Time spent in forums: 1 Week 1 Day 1 h 16 m 25 sec
Reputation Power: 23

Here is the query for anyone interested.

Code:

select mid, line_num, customer_fk, '2310' as costcenter, gst_exclud, gst from us, service
where service.servnum = us.servnum
and (serv_type_fk = 1 or serv_type_fk = 56)
union all
select mid, line_num, customer_fk, '2320' as costcenter, gst_exclud, gst from si, service
where service.servnum = si.servnum
and (serv_type_fk = 1 or serv_type_fk = 56)
union all
select mid, line_num, customer_fk, '2320' as costcenter, gst_exclud, gst from pl, service
where service.servnum = pl.servnum
and (serv_type_fk = 1 or serv_type_fk = 56)
union all
select mid, line_num, customer_fk, '2330' as costcenter, gst_exclud, gst from us, service
where service.servnum = us.servnum
and serv_type_fk = 10
union all
select mid, line_num, customer_fk, '2340' as costcenter, gst_exclud, gst from si, service
where service.servnum = si.servnum
and serv_type_fk = 10
union all
select mid, line_num, customer_fk, '2340' as costcenter, gst_exclud, gst from pl, service
where service.servnum = pl.servnum
and serv_type_fk = 10
union all
select mid, line_num, customer_fk, '2370' as costcenter, gst_exclud, gst from us, service
where service.servnum = us.servnum
and (serv_type_fk = 73 or serv_type_fk = 57)
union all
select mid, line_num, customer_fk, '2360' as costcenter, gst_exclud, gst from si, service
where service.servnum = si.servnum
and (serv_type_fk = 73 or serv_type_fk = 57)
union all
select mid, line_num, customer_fk, '2360' as costcenter, gst_exclud, gst from pl, service
where service.servnum = pl.servnum
and (serv_type_fk = 73 or serv_type_fk = 57)
union all
select mid, line_num, customer_fk, '2312' as costcenter, gst_exclud, gst from us, service
where service.servnum = us.servnum
and (serv_type_fk = 58 or serv_type_fk = 60 or serv_type_fk = 78 or serv_type_fk = 84 or serv_type_fk = 85 or serv_type_fk = 86)
union all
select mid, line_num, customer_fk, '2350' as costcenter, gst_exclud, gst from si, service
where service.servnum = si.servnum
and (serv_type_fk = 58 or serv_type_fk = 60 or serv_type_fk = 78 or serv_type_fk = 84 or serv_type_fk = 85 or serv_type_fk = 86)
union all
select mid, line_num, customer_fk, '2350' as costcenter, gst_exclud, gst from pl, service
where service.servnum = pl.servnum
and (serv_type_fk = 58 or serv_type_fk = 60 or serv_type_fk = 78 or serv_type_fk = 84 or serv_type_fk = 85 or serv_type_fk = 86)
union all
select mid, line_num, customer_fk, '2380' as costcenter, gst_exclud, gst from us, service
where service.servnum = us.servnum
and serv_type_fk <> 58 and serv_type_fk <> 60 and serv_type_fk <> 78 and serv_type_fk <> 84 and serv_type_fk <> 85 and serv_type_fk <> 86 and serv_type_fk <> 1 and serv_type_fk <> 56 and serv_type_fk <> 10 and serv_type_fk <> 57 and serv_type_fk <> 73
union all
select mid, line_num, customer_fk, '2380' as costcenter, gst_exclud, gst from si, service
where service.servnum = si.servnum
and serv_type_fk <> 58 and serv_type_fk <> 60 and serv_type_fk <> 78 and serv_type_fk <> 84 and serv_type_fk <> 85 and serv_type_fk <> 86 and serv_type_fk <> 1 and serv_type_fk <> 56 and serv_type_fk <> 10 and serv_type_fk <> 57 and serv_type_fk <> 73
union all
select mid, line_num, customer_fk, '2380' as costcenter, gst_exclud, gst from pl, service
where service.servnum = pl.servnum
and serv_type_fk <> 58 and serv_type_fk <> 60 and serv_type_fk <> 78 and serv_type_fk <> 84 and serv_type_fk <> 85 and serv_type_fk <> 86 and serv_type_fk <> 1 and serv_type_fk <> 56 and serv_type_fk <> 10 and serv_type_fk <> 57 and serv_type_fk <> 73
union all
select mid, line_num, customer_fk, '2380' as costcenter, gst_exclud, gst from oi, service
where service.servnum = oi.servnum

Runs in about 33 seconds. If you swap any of the union all's for a union (doesn't matter which one) and presto! It runs in 3 seconds. Swapping xtra union all's for unions makes no difference after the 1st one. Quite odd.

August 24th, 2004, 06:15 PM

ProggerPete

Contributing User

Join Date: May 2003

Location: Brisbane, Australia

Posts: 1,438

Time spent in forums: 1 Week 1 Day 1 h 16 m 25 sec
Reputation Power: 23

Got that explain plan thing to work. Using a union in there results in a number of scans changing from full to range. Obviously this is going to result in a speed increase. Can't say I understand y it's happenning though.

August 24th, 2004, 06:47 PM

ProggerPete

Contributing User

Join Date: May 2003

Location: Brisbane, Australia

Posts: 1,438

Time spent in forums: 1 Week 1 Day 1 h 16 m 25 sec
Reputation Power: 23

Curiouser and curiouser. I've discovered that the query above actually runs faster with union all as opposed to union. (By about half a second, 16ms vs 500ms)
However, i'm further qualifying the results with.

Code:

where customer_fk in (
SELECT customer_pk
FROM CUSTOMER
CONNECT BY PRIOR customer_pk=customer_fk
START WITH customer_pk = 25202
)

Once this is added to the query the union out performs the union all and I get my 3 vs 33 second comparison.

September 24th, 2004, 08:58 AM

santosh_pai

Registered User

Join Date: Sep 2004

Posts: 14

Time spent in forums: < 1 sec
Reputation Power: 0

Difference in Union and union all

Union is like the distinct operation ..
Results query 1
union
Results Query 2

the result would be distinct result set of the two, Rows present in Query2 aready selected in 1 would not be fetched again. In short they would be filtered out

A union all would give just the union operation
Results query 1
union all
Results Query 2
would give a join of all results of query 1 and query 2.
Records would be repeated. It will not return you the distinct informations
Hope this clarifies
Cheers
Santosh

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: