Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5

    Unanswered: Rewrite of a nested, nested,nested subselect

    I was wondering if someone might have some ideas on how to rewrite this subselect, so it is a bit easier to understand. I have already told the developers to remove the GROUP BY as its unnecessary.
    Code:
    select a bunch of columns
       from some tables
    where B_SYS_ID IN (select B_SYS_ID 
                         from B_XREF_TB
                       WHERE B_ALT_ID IN (select B_ALT_ID
                                            from B_XREF_TB
                                          WHERE B_SYS_ID IN (select B_SYS_ID
                                                               from B_XREF_TB
                                                             WHERE B_ALT_ID = '948797930L')
                                          )
                       GROUP BY B_SYS_ID)
    Dave Nance

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I forgot to mention, that portion of the query is the only indexable input for the query.
    Dave Nance

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Are there any unique column?

    For example:
    1) b_sys_id in b_xref_tb
    2) b_alt_id in b_xref_tb
    3) b_sys_id in one of outmost tables

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    no. there is a table where b_sys_id is unique, but it is not part of the query right now. The idea is that the b_alt_id, is mostly unique, but it is not enforced by the database. The primary key of the B_XREF_TB is the combination of the two, B_SYS_ID, B_ALT_ID. I do have another index on the table that is the clustering index B_ALT_ID, B_SYS_ID as that is the way the table is most commonly queried.

    My original thought when just the subselect was sent to me was to replace it with an exists clause, until I got the rest of the query and saw that it is the only indexable input. That section of the query on its own, runs in a respectable amount of time, .23 seconds elapsed.
    Other pieces of info I should have sent earlier. The database is on Z/OS, DB2 V9. Query is dynamic from a workstation, using DB2 Connect gateway.
    Dave Nance

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think the query can be rewritten like the following.

    Wheather it may perform well or not, it is easier to understand(at least for me).
    Indexes (b_sys_id , b_alt_id) and (b_alt_id , b_sys_id) on B_XREF_TB might be usefull.

    Code:
    select a bunch of columns
     from  some tables
     where B_SYS_ID
           IN (select x3.B_SYS_ID 
                from  B_XREF_TB x1
                INNER JOIN
                      B_XREF_TB x2
                  ON  x2.B_SYS_ID = x1.B_SYS_ID
                INNER JOIN
                      B_XREF_TB x3
                  ON  x3.B_ALT_ID = x2.B_ALT_ID
                WHERE x1.B_ALT_ID = '948797930L'
              )
    ;

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Tonkuma,
    Thanks. That is a bit easier to comprehend and performance is, exactly the same.

    Dave Nance

Posting Permissions

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