Oracle Database Discussions

I have a query which is executing in 4 minutes 25 seconds. I have created a view on this query. when I do a select count(*) or count(1) on this view, it takes huge amount of time. IT does not return any result even after 50 minutes.

Can anybody suggest anything ? Below is the query and the explain plan :

select +all_rows driving_site(aida) parallel(aida,4) parallel(aia,4) use_nl(aia aida pda pha pll pv gcc atl) use_nl(aia pvsa) index(att ap_terms_tl_u1)
(case
when pha.attribute8 = 'NEL' or pha.attribute8 = 'FGDMFG' then
'7'
when pha.attribute8 = 'FGDMEX' then
'3'
end) sys_ent_id,
(case
when pha.attribute8 = 'NEL' or pha.attribute8 = 'FGDMFG' then
'STOUGHTON CORPORATE'
when pha.attribute8 = 'FGDMEX' then
'SAN LUIS POTOSI'
end) location_id,
'F' Transaction_flag,
(case
when substr(pha.segment1, 1, 3) = 'IND' or
upper(substr(pha.segment1, 1, 1)) between 'A' and 'Z' then
'Indirect'
when gcc.segment4 in ('0480990', '0201060', '4911000', '4911003') or
gcc.segment4 like '39%' then
'Direct'
when gcc.segment4 not in ('0480990', '0201060') and
gcc.segment4 not like '39%' then
'Indirect'
end) invoice_category,
invoice_num invoice_no,
aida.distribution_line_number invoice_line_no,
aia.invoice_date invoice_supplier_date,
aia.creation_date invoice_transaction_date,
aia.creation_date invoice_entry_date,
pv.vendor_name supplier_name,
pv.segment1 supplier_no,
(case
when pha.attribute8 = 'NEL' or pha.attribute8 = 'FGDMFG' then
pv.segment1
when pha.attribute8 = 'FGDMEX' then
pv.vendor_site_code
end) supplier_site_code,
'R' supplier_site_type,
pll.vendor_product_num part_no,
aia.invoice_type_lookup_code invoice_type,
nvl(aida.quantity_invoiced, 0) invoice_line_qty,
aida.unit_price invoice_line_unit_cost_home,
aida.amount invoice_ine_total_amount_home,
aida.line_type_lookup_code invoice_line_type,
aia.invoice_currency_code home_currency_code,
decode(aia.payment_status_flag,
'Y',
'PAID',
decode(aida.po_distribution_id, null, 'NO MATCH', 'MATCHED')) invoice_status,
att.name Invoice_Terms,
matched_uom_lookup_code invoice_uom,
null invoice_line_desc,
GCC.segment1 BC_CODE,
GCC.segment2 RC_CODE,
GCC.segment3 DEPT_CODE,
gcc.segment1 || gcc.segment2 || gcc.segment3 || gcc.segment4 ||
gcc.segment5 || gcc.segment6 || gcc.segment7 gl_acct_id,
decode(aia.payment_status_flag, 'y', 'y', 'n', 'n') voucher_paid,
pha.segment1 po_no,
pv.ATTRIBUTE1 Legacy_Supplier_no,
aia.creation_date aia_creation_date,
aia.last_update_date aia_last_update_date,
aida.creation_date aida_creation_date,
aida.last_update_date aida_last_update_date,
least(aia.creation_date, aida.creation_date) source_create_date,
least(aia.last_update_date, aida.last_update_date) source_update_date
FROM ap.ap_invoices_all aia,
ap.ap_invoice_distributions_all aida,
(select /*+ no_merge index(po po_vendors_u1) index(apce apc_entity_supp_mapping_u1) index(pvsa po_vendor_sites_u1) */
distinct po.vendor_id,
po.attribute1,
vendor_name,
vendor_site_id,
vendor_site_code,
-- commented the above 2 lines by Padmashree , since entity supplier no does not give duplicates for giea slp
-- Modified this decode so that it is entity supplier no for giea slp and segment1 for nelson
--decode(pvsa.attribute10,'MXFGD',po.segment1,entity_supplier_num) segment1
--po.segment1 segment1
--decode(pvsa.attribute10,'MXFGD',entity_supplier_num,po.segment1) segment1,
po.segment1 segment1
from po.po_vendors po,
po.po_vendor_sites_all pvsa,
apc_entity_supplier_mapping apce
where (pvsa.attribute10 in ('USNELSON', 'MXFGD') OR
supplier_mapping_code = 'NELSON')
and po.vendor_id = apce.vendor_id(+)
and po.vendor_id = pvsa.vendor_id) pv,
ap.ap_terms_tl att,
gl.gl_code_combinations gcc,
po.po_distributions_all pda,
po.po_lines_all pll,
po.po_headers_all pha
WHERE aia.invoice_id = aida.invoice_id
and aia.vendor_id = pv.vendor_id
and aia.vendor_site_id = pv.vendor_site_id
and aia.terms_id = att.term_id(+)
and aida.dist_code_combination_id = gcc.code_combination_id--(+)
and pda.po_distribution_id(+) = aida.po_distribution_id
and pda.po_header_id = pll.po_header_id(+)
and pda.po_line_id = pll.po_line_id(+)
and pha.po_header_id(+) = pll.po_header_id
and pha.attribute8 in ('FGDMEX', 'NEL', 'FGDMFG')






Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Hint=HINT: ALL_ROWS 1 K 2938085
NESTED LOOPS 1 K 614 K 2938085
FILTER
NESTED LOOPS OUTER
NESTED LOOPS OUTER 1 K 536 K 2937131
NESTED LOOPS OUTER 1 K 510 K 2936495
NESTED LOOPS 1 K 489 K 2935859
HASH JOIN OUTER 355 115 K 2935415
TABLE ACCESS BY INDEX ROWID AP_INVOICES_ALL 1 70 130
NESTED LOOPS 355 111 K 2935398
VIEW 22 K 5 M 23760
SORT UNIQUE 22 K 2 M 23760
HASH JOIN 22 K 2 M 23352
TABLE ACCESS BY INDEX ROWID PO_VENDOR_SITES_ALL 70 K 2 M 13622
INDEX FULL SCAN PO_VENDOR_SITES_U1 70 K 137
MERGE JOIN OUTER 42 K 3 M 9625
TABLE ACCESS BY INDEX ROWID PO_VENDORS 42 K 2 M 6182
INDEX FULL SCAN PO_VENDORS_U1 42 K 82
SORT JOIN 6 K 85 K 3443
TABLE ACCESS BY INDEX ROWID APC_ENTITY_SUPPLIER_MAPPING 6 K 85 K 3398
INDEX FULL SCAN APC_ENTITY_SUPP_MAPPING_U1 6 K 39
INDEX RANGE SCAN AP_INVOICES_N2 355 3
TABLE ACCESS BY INDEX ROWID AP_TERMS_TL 139 1 K 17
INDEX FULL SCAN AP_TERMS_TL_U1 1 1
TABLE ACCESS BY INDEX ROWID AP_INVOICE_DISTRIBUTIONS_ALL 4 240 5
INDEX RANGE SCAN AP_INVOICE_DISTRIBUTIONS_U1 13 2
TABLE ACCESS BY INDEX ROWID PO_DISTRIBUTIONS_ALL 1 17 2
INDEX UNIQUE SCAN PO_DISTRIBUTIONS_U1 1 1
TABLE ACCESS BY INDEX ROWID PO_LINES_ALL 1 21 2
INDEX UNIQUE SCAN PO_LINES_U1 1 1
TABLE ACCESS BY INDEX ROWID PO_HEADERS_ALL 1 20 1
INDEX UNIQUE SCAN PO_HEADERS_U1 1
TABLE ACCESS BY INDEX ROWID GL_CODE_COMBINATIONS 1 43 2
INDEX UNIQUE SCAN GL_CODE_COMBINATIONS_U1 1 1