Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2009
    Posts
    42

    Unanswered: optimizer transformed non union to union

    Hello,

    Please help me understand why optimizer will change query from non-union to union. Original statement does not have union but optimized statement does.


    Thank you

  2. #2
    Join Date
    Jul 2009
    Posts
    150
    Interesting.
    Please, show the statement.

  3. #3
    Join Date
    Apr 2009
    Posts
    42
    I will attach db2exfmt output. Thank you
    Last edited by db2zip; 11-28-09 at 09:11.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    In most situations DB2 can use only one index for each table. But when you have an "OR" predicate, that means that one side of the OR will get good performance, and the other side will get poor performance. So DB2 creates separate queries and uses the UNION so that it can create an optimal access plan for each side of the UNION.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Apr 2009
    Posts
    42
    Thank you. Why it can not do index ORing when table has multiple indexes? If I drop index tam2 defined on table danny, then db2 will not use union and total cost and query execution time will decrease significantly (about 3 times better when index is dropped)?

  6. #6
    Join Date
    Jul 2009
    Posts
    150

    Cool Or <==> union

    Quote Originally Posted by db2zip View Post
    I will attach db2exfmt output. Thank you
    WHERE tree_type= 'ORG_TREE' AND child_node IN ('T252')) OR
    attribute_value IN ('T252'))) OR (attribute_id = 4 AND (
    INTEGER(attribute_value) >=700)) OR (attribute_id = 5 AND (
    INTEGER(attribute_value) <=700)) OR (attribute_id = 2....


    How you know OR and UNION working in same way....

    Kara

  7. #7
    Join Date
    Apr 2009
    Posts
    42
    Quote Originally Posted by DB2Plus View Post
    How you know OR and UNION working in same way....
    I do not know if OR and UNION working in the same way. If I drop one of indexes, db2 will know use UNION.

  8. #8
    Join Date
    Jul 2009
    Posts
    150

    Lightbulb use something symple

    Quote Originally Posted by db2zip View Post
    I do not know if OR and UNION working in the same way. If I drop one of indexes, db2 will know use UNION.
    You can use something more symple to understand how it's working....

    Kara

Posting Permissions

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