Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: union all vs. left outer join

Re: union all vs. left outer join

From: Stefan Rybacki <stefan.rybacki_at_gmx.net>
Date: Mon, 07 Nov 2005 22:22:53 +0100
Message-ID: <3t9uteFotv4eU1@individual.net>


henrik.sorensen_at_balcab.ch wrote:

> ... 
> 
> Any ideas, or hints would be much appreciated.
> 
> Henrik
> 
> 
> 

Its not slow over here.

Tested on postgreSQL 8

QUERY1:
SELECT *
from A
left outer join B on A.id = B.aid
left outer join C on A.id = C.aid
inner join D on

    d.bid = B.id
or d.bid = C.bid
inner join E on E.id = D.eid
where E.m1 = 'A'

Query Time: 51ms + 140ms
Query Plan:
Nested Loop (cost=108.65..451.01 rows=14 width=161)

   Join Filter: (("inner".bid = "outer".id) OR ("inner".bid = "outer".bid))    -> Hash Left Join (cost=58.25..123.87 rows=1230 width=71)

         Hash Cond: ("outer".id = "inner".aid)
         ->  Hash Left Join  (cost=30.75..74.84 rows=1230 width=47)
               Hash Cond: ("outer".id = "inner".aid)

-> Seq Scan on a (cost=0.00..22.30 rows=1230 width=32)
-> Hash (cost=26.60..26.60 rows=1660 width=15)
-> Seq Scan on b (cost=0.00..26.60 rows=1660 width=15) -> Hash (cost=24.00..24.00 rows=1400 width=24)
-> Seq Scan on c (cost=0.00..24.00 rows=1400 width=24)
-> Materialize (cost=50.40..50.49 rows=9 width=90) -> Hash Join (cost=15.40..50.39 rows=9 width=90) Hash Cond: ("outer".eid = "inner".id)
-> Seq Scan on d (cost=0.00..26.60 rows=1660 width=15)
-> Hash (cost=15.39..15.39 rows=4 width=75)
-> Index Scan using xem1 on e (cost=0.00..15.39 rows=4 width=75) Index Cond: (m1 = 'A'::bpchar)

QUERY2:
SELECT a.*,d.*,e.*
from A
left outer join B on A.id = B.aid
inner join D on

    d.bid = B.id
inner join E on E.id = D.eid
where E.m1 = 'A'

union all

SELECT a.*,d.*,e.*
from A
left outer join C on A.id = C.aid
inner join D on

   d.bid = C.bid
inner join E on E.id = D.eid
where E.m1 = 'A'

Query Time: 31ms + 110ms
Append (cost=85.42..224.12 rows=14 width=122)

   -> Subquery Scan "*SELECT* 1" (cost=85.42..114.01 rows=7 width=122)

         ->  Hash Join  (cost=85.42..113.94 rows=7 width=122)
               Hash Cond: ("outer".id = "inner".aid)

-> Seq Scan on a (cost=0.00..22.30 rows=1230 width=32)
-> Hash (cost=85.40..85.40 rows=9 width=94)
-> Hash Join (cost=50.41..85.40 rows=9 width=94) Hash Cond: ("outer".id = "inner".bid) -> Seq Scan on b (cost=0.00..26.60 rows=1660 width=8) -> Hash (cost=50.39..50.39 rows=9 width=90) -> Hash Join (cost=15.40..50.39 rows=9 width=90) Hash Cond: ("outer".eid = "inner".id) -> Seq Scan on d (cost=0.00..26.60 rows=1660 width=15) -> Hash (cost=15.39..15.39 rows=4 width=75) -> Index Scan using xem1 on e (cost=0.00..15.39 rows=4 width=75) Index Cond: (m1 = 'A'::bpchar) -> Subquery Scan "*SELECT* 2" (cost=81.51..110.11 rows=7 width=122) -> Hash Join (cost=81.51..110.04 rows=7 width=122) Hash Cond: ("outer".id = "inner".aid)
-> Seq Scan on a (cost=0.00..22.30 rows=1230 width=32)
-> Hash (cost=81.49..81.49 rows=8 width=94)
-> Hash Join (cost=50.41..81.49 rows=8 width=94) Hash Cond: ("outer".bid = "inner".bid) -> Seq Scan on c (cost=0.00..24.00 rows=1400 width=8) -> Hash (cost=50.39..50.39 rows=9 width=90) -> Hash Join (cost=15.40..50.39 rows=9 width=90) Hash Cond: ("outer".eid = "inner".id) -> Seq Scan on d (cost=0.00..26.60 rows=1660 width=15) -> Hash (cost=15.39..15.39 rows=4 width=75) -> Index Scan using xem1 on e (cost=0.00..15.39 rows=4 width=75) Index Cond: (m1 = 'A'::bpchar)

Test on MySQL 4.1

QUERY1: Query Time: 0.03 sec
Explain:
+--------+-------------+--------+--------+---------------+--------+---------+--------+--------+--------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+--------+-------------+--------+--------+---------------+--------+---------+--------+--------+--------+

| 1      | SIMPLE      | E      | ref    | PRIMARY,XE,XEM1| XEM1   | 2       | const  | 1 
      | Using where|
| 1      | SIMPLE      | A      | ALL    | [NULL]        | [NULL] | [NULL]  | [NULL] | 16 
     |        |
| 1      | SIMPLE      | B      | ref    | XBA           | XBA    | 5       | test.A.id| 2 
      |        |
| 1      | SIMPLE      | C      | ref    | XCA           | XCA    | 5       | test.A.id| 2 
      |        |
| 1      | SIMPLE      | D      | ALL    | PRIMARY,XD,XDE| [NULL] | [NULL]  | [NULL] | 3 
     | Range checked for each record (index map: 0x7)|
+--------+-------------+--------+--------+---------------+--------+---------+--------+--------+--------+

QUERY2: Query Time: 0.02 sec
Explain:
+--------+-------------+--------+--------+---------------+--------+---------+--------+--------+--------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+--------+-------------+--------+--------+---------------+--------+---------+--------+--------+--------+

| 1      | PRIMARY     | A      | ALL    | [NULL]        | [NULL] | [NULL]  | [NULL] | 16 
     |        |
| 1      | PRIMARY     | B      | ref    | XBA           | XBA    | 5       | test.A.id| 2 
      |        |
| 1      | PRIMARY     | D      | eq_ref | PRIMARY,XD,XDE| PRIMARY| 4       | test.B.id| 1 
      |        |
| 1      | PRIMARY     | E      | eq_ref | PRIMARY,XE,XEM1| PRIMARY| 4       | test.D.eid| 
1      | Using where|
| 2      | UNION       | A      | ALL    | [NULL]        | [NULL] | [NULL]  | [NULL] | 16 
     |        |
| 2      | UNION       | C      | ref    | XCA           | XCA    | 5       | test.A.id| 2 
      |        |
| 2      | UNION       | D      | eq_ref | PRIMARY,XD,XDE| PRIMARY| 4       | test.C.bid| 
1      |        |
| 2      | UNION       | E      | eq_ref | PRIMARY,XE,XEM1| PRIMARY| 4       | test.D.eid| 
1      | Using where|
| [NULL] | UNION RESULT| <union1,2>| ALL    | [NULL]        | [NULL] | [NULL]  | [NULL] | 
[NULL] |        |

+--------+-------------+--------+--------+---------------+--------+---------+--------+--------+--------+

Regards
Stefan Received on Mon Nov 07 2005 - 15:22:53 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US