Oracle Database Discussions

Hi Gurus,

I need to tune the following query

SELECT
T.CONTROLNUMBER, T.MODTIMESTAMP, T.TXNSTATUSCODE, T.TRANSACTIONTYPE, T.TXNID,
D.LASTNAME, D.FIRSTNAME, D.MIDDLENAME, D.SUFFIX, D.DEMDATATYPE, D.DEMDATAID,
N.IDNUMBER RECORDQUEUEID,
DECODE(O.IDCATEGORY,'DLID',O.IDNUMBER,NULL) DLID, DECODE(P.IDCATEGORY,'CustomerID',P.IDNUMBER,NULL) CUSTOMERID

FROM IDS_TXNDEMDATAMAP M

inner join IDS_DEMDATA D
on M.DEMDATAID = D.DEMDATAID

inner join IDS_TXN T
on M.TXNID = T.TXNID

inner join IDS_TXNIDNUMBERS N
on M.TXNID = N.TXNID and UPPER(N.IDCATEGORY) = 'RECORDQUEUEID'

left join IDS_IDNUMBERS O
on M.DEMDATAID=O.DEMDATAID and O.IDCATEGORY='DLID'

left join IDS_IDNUMBERS P
on M.DEMDATAID=P.DEMDATAID and P.IDCATEGORY='CustomerID';

As you've noticed that it is doing INNER JOIN with 3 tables BUT LEFT OUTER JOIN with the last table. By definition LEFT OUTER JOIN picks up all rows which are same for the other table (in this case IDS_TXNDEMDATAMAP) and picks up at least one occurrence of the rest of the rows from the other table (in this case IDS_IDNUMBERS), so in other word full table scan for the LEFT OUTER JOIN table (in this case IDS_IDNUMBERS).

Please let me know if my assumption is correct and is there any way I can tune this query to avoid full table scan for IDS_IDNUMBERS.


Thanks
Amitava.

Edited by: amitavachatterjee1975 on Nov 28, 2012 7:00 AM