Rewrite of a nested, nested,nested subselect

dav1mo · #1 (**permalink**) 09-27-11, 16:55

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

dav1mo · #2 (**permalink**) 09-27-11, 16:57

I forgot to mention, that portion of the query is the only indexable input for the query.
Dave Nance

tonkuma · #3 (**permalink**) 09-27-11, 19:45

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

dav1mo · #4 (**permalink**) 09-28-11, 08:23

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

tonkuma · #5 (**permalink**) 09-28-11, 08:37

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'
          )
;

dav1mo · #6 (**permalink**) 09-28-11, 09:18

Tonkuma,
Thanks. That is a bit easier to comprehend and performance is, exactly the same.

Dave Nance

Internet Services

Web Development

Digital Marketing

Consumer Tech