SQL & PL/SQL

Hi All,

Can any body help me out to tune this query... its cost is 62,900.. and thete is full table scan on ap_invoices_all...

For one invoice ID its taking 20 sccs...

SELECT /*+ INDEX  ( i2 AP_INVOICES_N8 )  INDEX  ( i1 AP_INVOICES_N8 )   */ DISTINCT ou.name operating_unit,
NVL(SUBSTR(UPPER(TRANSLATE(i1.invoice_num,'a!@#\/-_$%^&*.','a')),
1,:P_MATCH_LENGTH),'NomatchKluDge1') match_string,
UPPER(v.vendor_name) upper_supplier_name,
i1.invoice_num invoice_number,
to_char(i1.invoice_date,'DD-MON-YYYY') invoice_date,
--i1.invoice_date invoice_date,
NVL(i1.invoice_amount,0) invoice_amount,
i1.invoice_currency_code currency_code,
v.segment1 supplier_number,
v.vendor_name supplier_name,
ssa.vendor_site_code supplier_code,
lc.displayed_field invoice_type,
poh.segment1 po_number,
(select min(por.release_num)
from po_releases_all por
where poh.po_header_id = por.po_header_id) release_num,
gcc.segment1 location,
i1.payment_method_code payment_method_code,
DECODE(LENGTH(TO_CHAR(aca.check_number)),9,aca.check_number,aca.doc_sequence_value) payment_doc_number
FROM ap_invoices_all i1,
ap_invoices_all i2,
ap_suppliers v ,
ap_supplier_sites_all ssa,
ap_lookup_codes lc,
/* (select distinct pha.SEGMENT1, i.PO_HEADER_ID, i.INVOICE_ID
from ap_invoice_lines_all i
,po_headers_all pha
where pha.PO_HEADER_ID = i.PO_HEADER_ID) poh, */
po_headers_all poh,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aida,
gl_code_combinations gcc,
ap_checks_all aca,
ap_invoice_payments_all ipa,
hr_all_organization_units ou
WHERE i1.invoice_id <> i2.invoice_id
AND NVL(substr(upper(translate(i1.invoice_num,'a!@#\/-_$%^&*.','a')),
1,:P_MATCH_LENGTH),'NomatchKluDge1')
= NVL(substr(upper(translate(i2.invoice_num,'a!@#\/-_$%^&*.','a')),
1,:P_MATCH_LENGTH),'abcdefghijklm')
--AND i1.creation_date between :p_creation_date_from and :p_creation_date_to
AND i1.cancelled_date IS NULL
--AND i2.creation_date between :p_creation_date_from and :p_creation_date_to
AND i2.cancelled_date IS NULL
AND i1.invoice_amount = nvl(i2.invoice_amount,-1)
--AND i1.vendor_id = i2.vendor_id
AND i1.vendor_id+0 = i2.vendor_id+0
AND nvl(i1.vendor_id,-1) = v.vendor_id
AND i1.invoice_id = aida.invoice_id
AND aida.distribution_line_number = 1
AND gcc.code_combination_id = aida.dist_code_combination_id
AND lc.lookup_code (+) = i1.invoice_type_lookup_code
AND lc.lookup_type (+) = 'INVOICE TYPE'
AND i1.vendor_site_id = ssa.vendor_site_id(+)
--AND i1.invoice_id = poh.invoice_id (+)
AND i1.invoice_id = ail.invoice_id
--AND ail.line_number = 1
AND aida.INVOICE_LINE_NUMBER = 1
--AND ail.po_header_id = poh.po_header_id (+)
AND ail.po_header_id = poh.po_header_id
AND ail.INVOICE_ID = aida.INVOICE_ID
and ail.LINE_NUMBER = aida.INVOICE_LINE_NUMBER
AND i1.invoice_id = ipa.invoice_id(+)
AND ipa.check_id = aca.check_id(+)
AND i1.org_id = ou.organization_id
and i1.invoice_id = 123456
ORDER BY upper(v.vendor_name),
NVL(substr(upper(translate(i1.invoice_num,'a!@#\/-_$%^&*.','a')),
1,:P_MATCH_LENGTH),'abcdefghijklm'),
upper(i1.invoice_num);

Regards
--Harry