full table scan with insert into select... statement

[scottrid]

I'm having a problem with an insert statement, doing a full table scan. The statement is below:

INSERT INTO AUTO_CHEQUE_REQUEST
(AUTOCHEQUE_NUM, TRANS_NUM, NOM_ACC_NUMBER, PAYEE,
VALUE, CANCELLED_FLAG, PRINTED_FLAG, PRINT_CONFIRMED_FLAG,
REMITTANCE_ADVICE_FLAG, COMPOSITE_FLAG, DELETED_FLAG,
LDATE_CREATED,USERNAME, PRINTER_NAME)
SELECT NP.AUTOCHEQUE_NUM,
NAE.TRANS_NUM,
NAE.NOM_ACC_NUMBER,
NAE.PAYEE_PAYER,
NAE.VALUE_CR,
'N','N','N','N','N','N',
TRUNC(SYSDATE),
NP.USERNAME,
NP.PRINTER_NAME
FROM NOMINAL_ACCOUNT_ENTRY NAE,
NOMINAL_PAYMENT NP
WHERE NAE.TRANS_REF = NP.NOM_PAY_NUM
AND NAE.TRANS_KEY = NP.TRANS_KEY
AND NAE.VALUE_DR IS NULL
AND NAE.TRANS_NUM =
(SELECT MIN(TRANS_NUM)
FROM NOMINAL_ACCOUNT_ENTRY NAE1
WHERE NAE1.TRANS_REF = NP.NOM_PAY_NUM
AND NAE1.TRANS_KEY = NP.TRANS_KEY
AND NAE1.VALUE_DR IS NULL
AND NAE1.TRANS_TYPE IN ('NPAY','CNPAY'))
AND NP.AUTO_CHEQUE_FLAG = 'Y'
AND NAE.TRANS_TYPE IN ('NPAY','CNPAY')
AND :B.BATCH_NUM = NP.BATCH_NUM

I have run this through explain plan, and it seems the full table scan comes from the subquery.

If I remove the insert into part of the statement, and run just the select through explain plan, then the query (and the sub query) use the required indexes and no full table scan.

I've tried putting some index hints in the subquery, and it doesn't make any difference.

Can anyone help????

Thanks

Scott

 

[SantaMufasa]

Scott,

Can you please post here the inventory of indexes, along with their respective index columns, for each of the tables that appear in your query? If proper indexes exist on the columns that exist in your WHERE clauses and your TRANS_NUM column, then you shouldn't have a problem with full-table scans.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]