Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    15

    Question Unanswered: union all vs in list

    Which of these two statements are more efficient

    select col1 from table where col2 in (1, 2)

    or

    select col1 from table where col2 = 1
    union all
    select col1 from table where col2 = 2;

    And lets say for the sake of argument that there are appropriate indexes on this table.

    Which is better and why?

    Thanks

  2. #2
    Join Date
    Nov 2001
    Location
    Finland, Kirkkonummi
    Posts
    17
    I'd say the first select with IN is more efficient becouse DB2 does not have to parse second query (it would not take long but still) like with UNION clause.

    Basically: with first database runs only one query but with second database runs two queries. Running a query costs time.

    Optimizer might notice this and change second query into more efficient version though.

    Vesku

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: union all vs in list

    As ever, results are dependent on the particular data.
    However, I just tried this on a reasonably large analyzed table (around 80,000 rows in 2000 blocks), and the IN version was significantly better (noticeably faster). The autotrace outputs were:

    1) For IN:

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=50 Card=2 Bytes=14)
    1 0 INDEX (FAST FULL SCAN) OF 'BTR_PK' (UNIQUE) (Cost=50 Card=
    2 Bytes=14)

    Statistics
    ----------------------------------------------------------
    0 recursive calls
    4 db block gets
    325 consistent gets
    0 physical reads
    0 redo size
    339 bytes sent via SQL*Net to client
    434 bytes received via SQL*Net from client
    3 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    0 rows processed

    2) For UNION ALL:

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=100 Card=2 Bytes=14)
    1 0 UNION-ALL
    2 1 INDEX (FAST FULL SCAN) OF 'BTR_PK' (UNIQUE) (Cost=50 Car
    d=1 Bytes=7)

    3 1 INDEX (FAST FULL SCAN) OF 'BTR_PK' (UNIQUE) (Cost=50 Car
    d=1 Bytes=7)

    Statistics
    ----------------------------------------------------------
    0 recursive calls
    8 db block gets
    650 consistent gets
    0 physical reads
    0 redo size
    339 bytes sent via SQL*Net to client
    500 bytes received via SQL*Net from client
    3 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    0 rows processed

    The UNION ALL does twice the work of the IN. The optimizer doesn't manage to combine the separate UNION ALL queries into a single INDEX (FAST FULL SCAN), it does it twice.

    On a small table, there might be no difference.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •