2

I have the below query in my application which was running on DB2:

SELECT COD.POST_CD,CLS.CLASS,COD2.STATUS_CD
FROM DC01.POSTAL_CODES COD
INNER JOIN DC02.STATUS_CODES COD2
ON COD.ORDER=COD2.ORDER
INNER JOIN DC02.VALID_ORDERS ORD
ON ORD.ORDER=COD.ORDER
WHERE
( 
    ( EXISTS (SELECT 1 FROM DC00.PROCESS_ORDER PRD
          WHERE PRD.ORDER=COD.ORDER
              AND PRD.IDNUM=COD.IDNUM
        )
    ) OR
    ( EXISTS (SELECT 1 FROM DC00.PENDING_ORDER PND
          WHERE PND.ORDER=COD.ORDER
              AND PND.IDNUM=COD.IDNUM
        )
    )
)
AND EXISTS (SELECT 1 FROM DC00.CUSTOM_ORDER CRD 
           WHERE CRD.ORDER=COD.ORDER
                  )
;

When we changed to UDB (LUW v9.5) we are getting the below warning:

IWAQ0003W SQL warnings were found
SQLState=01602 Performance of this complex query might be sub-optimal.  
Reason code: "3".. SQLCODE=437, SQLSTATE=01602, DRIVER=4.13.111

I know this warning is due to the EXISTS () OR EXISTS statements. But I am not sure any other way I can write this query to replace. If it is AND, I could have made an INNER JOIN, but I am not able to change this condition as it is OR. Can any one suggest better way to replace these EXISTS Statements?

APC
  • 144,005
  • 19
  • 170
  • 281
user2986298
  • 21
  • 1
  • 2
  • Now, that's one cool warning message ;) - What happens if you combine the two sub-queries on PENDING_ORDER and PROCESS_ORDER into one using `UNION` then you'd only have a single EXISTS (albeit with a larger result) –  Nov 13 '13 at 07:14
  • Er, what table is being referenced by `CLS.CLASS`? Also, the reason you can't use a join is because you're looking for unique results, not do to and/or conditions (actually, this isn't true either, because you can use a table-reference that's a query). I guess at minimum you could combine your or condition with a `UNION ALL`, although I prefer using joins... – Clockwork-Muse Nov 13 '13 at 07:59
  • This might simply mean that you don't have current statistics on any of the queried tables and/or indexes. – mustaccio Nov 13 '13 at 13:13
  • Since you switched DB versions. There might be a bug that is effecting you. http://www-01.ibm.com/support/docview.wss?uid=swg1IZ09197 – Peter Schuetze Nov 13 '13 at 18:33

1 Answers1

0
SELECT COD.POST_CD,CLS.CLASS,COD2.STATUS_CD
FROM DC01.POSTAL_CODES COD
INNER JOIN DC02.STATUS_CODES COD2
ON COD.ORDER=COD2.ORDER
INNER JOIN DC02.VALID_ORDERS ORD
ON ORD.ORDER=COD.ORDER
WHERE
( 
     EXISTS SELECT 1 FROM 
              (SELECT ORDER,IDNUM FROM DC00.PROCESS_ORDER PRD UNION 
               SELECT ORDER,IDNUM FROM DC00.PENDING_ORDER PND) PD
          WHERE PD.ORDER=COD.ORDER
              AND PD.IDNUM=COD.IDNUM
)
AND EXISTS (SELECT 1 FROM DC00.CUSTOM_ORDER CRD 
           WHERE CRD.ORDER=COD.ORDER
              )
;
Will
  • 1