Results 1 to 2 of 2

Thread: UNION problem

  1. #1
    Join Date
    Sep 2002
    Posts
    1

    Question Unanswered: UNION problem

    I have a peculiar problem in DB2 UDB ver 7.x. I have a SQL with "UNION". The first half of the SQL runs in less than 1 sec. Second half also runs in less than 1 sec. But when I join both the halves, the query takes any where between 8-10 min. Any ideas why is this so? Are there any setting at Database level which is responsible for this ?

    Details :

    The following SQLs run in less than a sec when run independently.

    select a, b, c from <table d>

    select a, b, c from <table e>

    But When I join the SQL by UNION, (as given below) , it is taking 8-10 min.

    select a, b, c from <table d>
    union
    select a, b, c from <table e>

  2. #2
    Join Date
    Feb 2002
    Location
    Germany
    Posts
    141

    Re: UNION problem

    Did you "explain" the statement? What about your cardinality? You need more CPU for the second than for the sum of the first ones...
    select a, b, c from <table d>
    -> give me all the pages from "table d"

    select a, b, c from <table e>
    -> give me all pages from "table e"

    select a, b, c from <table d>
    union
    select a, b, c from <table e>

    -> take all pages from "table d" and all pages from "table e", eliminate the duplicates and give me a result set! The work DB2 is doing to eliminate the duplicates can not be underestimated!!!

    Of course, the difference is VERY big: 2 seconds for 10 minutes is a lot of time. Here you must check, as usual, bufferpools, tablespaces for sort, and so on...

    HTH,
    Rodney Krick

Posting Permissions

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