#1
  1. No Profile Picture
    Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    May 2003
    Location
    Brisbane, Australia
    Posts
    1,442
    Rep Power
    29

    Question 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.
  2. #2
  3. No Profile Picture
    ......@.........
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2004
    Posts
    1,345
    Rep Power
    56
    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.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    May 2003
    Location
    Brisbane, Australia
    Posts
    1,442
    Rep Power
    29
    How do I use Explain Plan? I'm pretty new to oracle. I tried it in sqlplus but had no luck
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    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/docs...1a.htm#2061798

    (You need to register if not already done)
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    May 2003
    Location
    Brisbane, Australia
    Posts
    1,442
    Rep Power
    29
    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.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    May 2003
    Location
    Brisbane, Australia
    Posts
    1,442
    Rep Power
    29
    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.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    May 2003
    Location
    Brisbane, Australia
    Posts
    1,442
    Rep Power
    29
    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.
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Posts
    14
    Rep 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

IMN logo majestic logo threadwatch logo seochat tools logo