AP_INVOICES_INTERFACE performance issue - FULL SCAN happens when queried by INVOICE NUM
I coded the below login in my program to identify duplicate invoices in ap interface.
But when querying the interface table ap_invoices_interface by invoice num and vendor id, explain shows it as FULL TABLE scan. Results into performance issue.
Please help if any smart way to reduce the performance issue.
---------------------------------------------------------------------
declare
l_dup_inv_cnt number;
cur1 is select vendor_id, invoice_id, invoice_num
from ap_invoices_interface
where source = 'XML GATEWAY';
begin
for i_rec in cur1
loop
select count(1) into l_dup_inv_cnt
FROM ap.ap_invoices_interface
WHERE vendor_id = i_rec.vendor_id