SQL & PL/SQL

Hi, guys:

I am looking for any suggestion to improve performance of the following query:

select inv2.invno, inv2.amt, inv2.clientkey, inv2.debtorkey, inv2.invdate, inv_temp.client_name, inv_temp.clientno, inv_temp.debtor_name,  inv_temp.debtorno,
inv_temp.earlest_inv_date, inv_temp.last_inv_date
from invoices inv2,
(select cl.clientkey, cl.name client_name, cl.clientno, dt.debtorkey, dt.name debtor_name,  dt.debtorno, max(inv.invdate) last_inv_date, min(inv.invdate) earlest_inv_date
from invoices inv, payments pa, debtors dt, clients cl
where inv.invoicekey=pa.invoicekey
and inv.clientkey=cl.clientkey
and inv.debtorkey=dt.debtorkey
and (upper(dt.name)<>upper('ADVANCES') or  upper(dt.name)<>upper('cash'))
and upper(dt.debtorno)<>upper('advance')
group by cl.clientkey, cl.name, cl.clientno, dt.debtorkey, dt.name,  dt.debtorno
having max(inv.invdate)>min(inv.invdate)
)inv_temp
where inv2.debtorkey=inv_temp.debtorkey
and inv2.clientkey=inv_temp.clientkey
and inv2.balance>0
and inv2.lastpmtdate is null
and inv2.invoicekey not in (select payments.invoicekey FROM payments)
and inv2.invdate <= inv_temp.last_inv_date
and inv2.invdate >= inv_temp.earlest_inv_date
and (trunc(sysdate) - inv2.invdate) <= :P45_DP_INVOICE_PAYMENT_DATE

The major cost looks the full table scan of payments, but I do not know how to reduce the cost of it. here is the execution plan:

 SELECT STATEMENT 
	    	    	 6344 
    	
	 SORT 
	    	 ORDER BY  	 6344 
    	    	
	 FILTER 
	    	    	   
    	    	    	
	 Filter Predicates 
    	    	    	    	
	 AND 
    	    	    	    	    	
	 MAX(INV.INVDATE)>MIN(INV.INVDATE) 
    	    	    	    	    	
	 INV2.INVDATE<=MAX(INV.INVDATE) 
    	    	    	    	    	
	 INV2.INVDATE>=MIN(INV.INVDATE) 
    	    	    	
	 HASH 
	    	 GROUP BY  	 6344 
    	    	    	    	
	 FILTER 
	    	    	   
    	    	    	    	    	
	 Filter Predicates 
    	    	    	    	    	    	
	 NOT EXISTS (SELECT 0 FROM PAYMENTS PAYMENTS WHERE PAYMENTS.INVOICEKEY IS NULL) 
    	    	    	    	    	
	 NESTED LOOPS 
	    	 ANTI SNA  	 6342 
    	    	    	    	    	    	
	 NESTED LOOPS 
	    	    	 2657 
    	    	    	    	    	    	    	
	 NESTED LOOPS 
	    	    	 2655 
    	    	    	    	    	    	    	    	
	 NESTED LOOPS 
	    	    	 2574 
    	    	    	    	    	    	    	    	    	
	 NESTED LOOPS 
	    	    	 2541 
    	    	    	    	    	    	    	    	    	    	
	 TABLE ACCESS 
	 DEBTORS  	 FULL  	 368 
    	    	    	    	    	    	    	    	    	    	    	
	 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	
	 AND 
    	    	    	    	    	    	    	    	    	    	    	    	    	
	 UPPER(DT.DEBTORNO)<>'ADVANCE' 
    	    	    	    	    	    	    	    	    	    	    	    	    	
	 OR 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
	 UPPER(DT.NAME)<>'ADVANCES' 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
	 UPPER(DT.NAME)<>'CASH' 
    	    	    	    	    	    	    	    	    	    	
	 TABLE ACCESS 
	 INVOICES  	 BY INDEX ROWID  	 34 
    	    	    	    	    	    	    	    	    	    	    	
	 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	
	 AND 
    	    	    	    	    	    	    	    	    	    	    	    	    	
	 INV2.LASTPMTDATE IS NULL 
    	    	    	    	    	    	    	    	    	    	    	    	    	
	 INV2.BALANCE>0 
    	    	    	    	    	    	    	    	    	    	    	    	    	
	 TRUNC(SYSDATE@!)-INV2.INVDATE<=TO_NUMBER(:P45_DP_INVOICE_PAYMENT_DATE) 
    	    	    	    	    	    	    	    	    	    	    	
	 INDEX 
	 INVOICE_DEBTORKEY_INDEX  	 RANGE SCAN  	 2 
    	    	    	    	    	    	    	    	    	    	    	    	
	 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	
	 INV2.DEBTORKEY=DT.DEBTORKEY 
    	    	    	    	    	    	    	    	    	
	 TABLE ACCESS 
	 CLIENTS  	 BY INDEX ROWID  	 1 
    	    	    	    	    	    	    	    	    	    	
	 INDEX 
	 AAAAACLIENTS_PK  	 UNIQUE SCAN  	 0 
    	    	    	    	    	    	    	    	    	    	    	
	 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	
	 INV2.CLIENTKEY=CL.CLIENTKEY 
    	    	    	    	    	    	    	    	
	 TABLE ACCESS 
	 INVOICES  	 BY INDEX ROWID  	 3 
    	    	    	    	    	    	    	    	    	
	 INDEX 
	 INVOICES_CLIENT_DEBTOR_INDEX  	 RANGE SCAN  	 2 
    	    	    	    	    	    	    	    	    	    	
	 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	
	 AND 
    	    	    	    	    	    	    	    	    	    	    	    	
	 INV.DEBTORKEY=DT.DEBTORKEY 
    	    	    	    	    	    	    	    	    	    	    	    	
	 INV.CLIENTKEY=CL.CLIENTKEY 
    	    	    	    	    	    	    	
	 INDEX 
	 PAYMENTS_INVOICEKEY_IDX  	 RANGE SCAN  	 2 
    	    	    	    	    	    	    	    	
	 Access Predicates 
    	    	    	    	    	    	    	    	    	
	 INV.INVOICEKEY=PA.INVOICEKEY 
    	    	    	    	    	    	
	 INDEX 
	 PAYMENTS_INVOICEKEY_IDX  	 RANGE SCAN  	 2 
    	    	    	    	    	    	    	
	 Access Predicates 
    	    	    	    	    	    	    	    	
	 INV2.INVOICEKEY=PAYMENTS.INVOICEKEY 
    	    	    	    	    	
	 TABLE ACCESS 
	 PAYMENTS  	 FULL  	 3683 
    	    	    	    	    	    	
	 Filter Predicates 
    	    	    	    	    	    	    	
	 PAYMENTS.INVOICEKEY IS NULL 

Thanks a lot in adavnce!

Edited by: lxiscas on Dec 12, 2012 2:40 PM

Edited by: lxiscas on Dec 12, 2012 2:41 PM

Edited by: lxiscas on Dec 12, 2012 3:52 PM