Query is taking exceptionally large amount of time [message #321478] |
Tue, 20 May 2008 07:08 |
sreenadhaj
Messages: 5 Registered: May 2008
|
Junior Member |
|
|
Dear All,
I have a view which follows.
create or replace view v_tip_shipment as
select distinct ar.org_id org_id,
ar.id ar_inv_no,
trunc(ar.transaction_date) ar_inv_date,
decode(po.ext_sys_ref_no,po.trans_id_1,po.id,po.ext_sys_ref_no) po_no,
po.customer_po_number,
(select trunc(presentation_date) from lc_negotiations where lc_org_id = lcnd.lcn_lc_org_id
and lc_id = lcnd.lcn_lc_id and rownum=1) presentation_date,
ari.trans_id_1 ppl_no,
trunc(pal.trans_date) ppl_rec_date,
trunc(tsch.eta_pol) vsl_eta,
trunc(erh.created_on) dtd_create_dt,
trunc(erh.ship_on_board_date) shpt_dt,
trunc(erh.reference_Date_3) dtd_sent_dt,
trunc(dpm.dispatch_date) shpg_docs_dsptch_dt,
trunc(si.created_on) si_issued_dt,
trunc(si.sched_insp_date) inspctn_appl_dt,
trunc(si.actual_insp_date) actual_inspctn_dt
from ar_invoices ar,
ari_items ari,
packing_lists pal,
palcs_items palcsi,
er_houses erh,
transport_orders si,
transport_schedules tsch,
job_orders jo,
lcn_details lcnd,
purchase_orders po,
dispatch_monitors dpm
where ar.org_id = ari.ari_org_id
and ar.id = ari.ari_id
and ar.org_id = pal.org_id
and ar.id = pal.trans_id_3
and pal.org_id = erh.er_org_id
and pal.trans_id_2 = erh.id
and pal.ext_sys_ref_org_id = si.org_id
and pal.trans_id_1 = si.trans_id_1
and si.org_id = jo.org_id
and si.trans_id_1 = jo.id
and jo.org_id = tsch.org_id
and jo.tsch_id = tsch.id
and ar.org_id = lcnd.lcn_lc_org_id(+)
and ar.id = lcnd.ari_id(+)
and pal.org_id = palcsi.palcs_pal_org_id
and pal.id = palcsi.palcs_pal_id
and palcsi.palcs_pal_org_id= po.org_id
and palcsi.trans_id_1 = po.id
and erh.er_org_id = dpm.erhserd_erhser_erh_er_org_id(+)
and erh.er_id = dpm.erhserd_erhser_erh_er_id(+)
and erh.id = dpm.erhserd_erhser_erh_id(+)
When i query this with
select count(1) from (SELECT * from v_tip_shipment WHERE ORG_ID='****')
It has to fetch 124169 records.its taking around 634 seconds.
All the tables used in this view are indexed.
Please can any one advise me on this, as what could be the problem causing this drastic peformance down.
|
|
|
|
|
|
|
|