SQL & PL/SQL

Hi,

SELECT api.invoice_id inv_id, pod.po_line_id line_id
FROM ap_invoices_all api,
po_distributions_all pod,
(SELECT invoice_id, po_distribution_id
FROM ap_invoice_distributions_all
WHERE reversal_flag IS NULL
AND line_type_lookup_code NOT IN ('FREIGHT', 'TAX')) apid,
po_line_locations_all poll,
(select /* +index po_vendors */ vendor_id, vendor_type_lookup_code from po_vendors
where NVL (vendor_type_lookup_code, 'NONE') != 'INTERNAL') pov
WHERE NOT EXISTS (
SELECT 'X'
FROM ap_holds_all aph
WHERE aph.invoice_id = api.invoice_id
AND TO_NUMBER (aph.attribute4) = pod.po_line_id
AND aph.hold_lookup_code IN
('Approval Required', 'Approval Error')
AND NVL (aph.release_lookup_code, 'NONE') !=
'Approval Status Changed'
AND aph.org_id = 764)
AND api.invoice_type_lookup_code NOT IN
('CREDIT', 'DEBIT', 'EXPENSE REPORT')
AND api.SOURCE != 'USE'
AND NVL (api.pay_group_lookup_code, 'NONE') != 'M' /* Europe */
AND api.payment_status_flag = 'N'
AND api.org_id = 764 --v_org_id
AND NVL (poll.receipt_required_flag, 'N') = 'N'
-- AND NVL (pov.vendor_type_lookup_code, 'NONE') != 'INTERNAL'
AND pov.vendor_id = api.vendor_id
AND pod.po_distribution_id = apid.po_distribution_id
AND api.invoice_id = apid.invoice_id
AND pod.line_location_id = poll.line_location_id
GROUP BY api.invoice_id, pod.po_line_id

this query shows table access full, when checked with explain plan??


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6306 | 911K| 86423 |
| 1 | HASH GROUP BY | | 6306 | 911K| 86423 |
| 2 | NESTED LOOPS ANTI | | 6306 | 911K| 86210 |
| 3 | NESTED LOOPS | | 6306 | 621K| 71330 |
| 4 | NESTED LOOPS | | 12631 | 1147K| 45957 |
| 5 | NESTED LOOPS | | 12631 | 925K| 20573 |
| 6 | HASH JOIN | | 4975 | 276K| 6377 |
| 7 | TABLE ACCESS BY INDEX ROWID| AP_INVOICES_ALL | 4975 | 218K| 4835 |
| 8 | INDEX RANGE SCAN | AP_INVOICES_N3 | 81850 | | 275 |
| 9 | TABLE ACCESS FULL | PO_VENDORS | 106K| 1244K| 1535 |
| 10 | TABLE ACCESS BY INDEX ROWID | AP_INVOICE_DISTRIBUTIONS_ALL | 3 | 54 | 5 |
| 11 | INDEX RANGE SCAN | AP_INVOICE_DISTRIBUTIONS_U1 | 9 | | 2 |
| 12 | TABLE ACCESS BY INDEX ROWID | PO_DISTRIBUTIONS_ALL | 1 | 18 | 2 |
| 13 | INDEX UNIQUE SCAN | PO_DISTRIBUTIONS_U1 | 1 | | 1 |
| 14 | TABLE ACCESS BY INDEX ROWID | PO_LINE_LOCATIONS_ALL | 1 | 8 | 2 |
| 15 | INDEX UNIQUE SCAN | PO_LINE_LOCATIONS_U1 | 1 | | 1 |
| 16 | TABLE ACCESS BY INDEX ROWID | AP_HOLDS_ALL | 1 | 47 | 4 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 17 | INDEX RANGE SCAN | AP_HOLDS_N1 | 3 | | 2 |
--------------------------------------------------------------------------------------------------


vendor_id, has unique index in table po_vendors and i made use of the same for join condition.
why i'm getting as table access full, and how to i avoid this?

Thanks,
Santhosh