Oracle SQL efficiency

Question

I have this query that gets almost a thousand records. The problem is the return of data is so slow about a minute or two.

Here is my query

SELECT POLNO, COLID, FORCEWRITE, INSURETAG, TO_CHAR(TO_DATE(TO_CHAR(STARTDT,'MM/DD/YYYY'),'MM/DD/YYYY'),'MM/DD/YYYY') AS STARTDT, TO_CHAR(TO_DATE(TO_CHAR(EXPIRYDT,'MM/DD/YYYY'),'MM/DD/YYYY'),'MM/DD/YYYY') AS EXPIRYDT, TO_CHAR(TO_DATE(TO_CHAR(DUEDT,'MM/DD/YYYY'),'MM/DD/YYYY'),'MM/DD/YYYY') AS DUEDT, CANTAG, PREMAMT, NETPREM FROM LPCINS WHERE CANTAG IS NULL AND COLID IN (SELECT COLID FROM LCCOLASS WHERE LOANNO IN (SELECT LOANNO FROM TMPPORT3)) ORDER BY EXPIRYDT DESC

Is there a way to improve this? I also have an index placed but it is still slow. I am using oracle database.

Answer 1

I would rewrite this using exists:

SELECT POLNO, COLID, FORCEWRITE, INSURETAG, 
       TO_CHAR(TO_DATE(TO_CHAR(STARTDT,'MM/DD/YYYY'),'MM/DD/YYYY'),'MM/DD/YYYY') AS 
STARTDT,
       TO_CHAR(TO_DATE(TO_CHAR(EXPIRYDT,'MM/DD/YYYY'),'MM/DD/YYYY'),'MM/DD/YYYY') AS EXPIRYDT, TO_CHAR(TO_DATE(TO_CHAR(DUEDT,'MM/DD/YYYY'),'MM/DD/YYYY'),'MM/DD/YYYY') AS DUEDT,
       CANTAG, PREMAMT, NETPREM
FROM LPCINS L
WHERE CANTAG IS NULL AND
      EXISTS (SELECT 1
              FROM LCCOLASS LC JOIN
                   TMPPORT3 T
                   ON LC.LOANNO = T.LOANNO
              WHERE L.COLID = LC.COLID
             )
ORDER BY EXPIRYDT DESC

Then try an index on LPCINS(CANAG, COLID) and LCCOLASS(COLID, LOANNO) and TMMPORT3(LOANNO).