Home » RDBMS Server » Performance Tuning » Query is taking more time to execute (Oracle 10G, Actuate reports)
| Query is taking more time to execute [message #327431] |
Mon, 16 June 2008 07:02  |
sreenadhaj
Messages: 5
Registered: May 2008
|
Junior Member |
|
|
I heve a cursor with a select statement as shown below.It is taking very long execution time due to which I am not able to generate report.I use actuate reporting tool.What could be the potential solutions for this.Can any one please advise on this
Select ********,********,*********.................
From ar_invoices ari, ari_items arii, packing_lists pal,
pal_cases palcs, palcs_items palcsi, purchase_orders po,
sales_orders sao, sao_items saoi, job_orders jo,
osch_items oschi, po_items poi
Where sao.org_id = saoi.sao_org_id
And sao.id = saoi.sao_id
And saoi.sao_org_id = poi.po_org_id
And saoi.sao_id = poi.trans_id_1
And saoi.id = poi.trans_id_2
And saoi.sao_org_id = palcsi.palcs_pal_org_id(+)
And saoi.sao_id = palcsi.trans_id_3(+)
And saoi.id = palcsi.trans_id_4(+)
And palcsi.palcs_pal_org_id = palcs.pal_org_id(+)
And palcsi.palcs_pal_id = palcs.pal_id(+)
And palcsi.palcs_id = palcs.id(+)
And palcs.pal_org_id = pal.org_id(+)
And palcs.pal_id = pal.id(+)
And palcsi.palcs_pal_org_id = arii.ari_org_id(+)
And palcsi.palcs_pal_id = arii.trans_id_1(+)
And palcsi.id = arii.trans_id_2(+)
And palcsi.palcs_id = arii.trans_id_3(+)
And arii.ari_org_id = ari.org_id(+)
And arii.ari_id = ari.id(+)
And pal.org_id = jo.org_id(+)
And pal.trans_id_1 = jo.id(+)
And sao.org_id = p_org_id
And nvl(sao.orggp_id
,'x') = nvl(p_orggp_id
,nvl(sao.orggp_id
,'x'))
And nvl(po.orgp_id_4
,'x') = nvl(p_factory
,nvl(po.orgp_id_4
,'x'))
And nvl(sao.orgp_id
,'x') = nvl(p_customer
,nvl(sao.orgp_id
,'x'))
And nvl(sao.orgreg_id
,'x') = nvl(p_region
,nvl(sao.orgreg_id
,'x'))
And poi.po_org_id = po.org_id
And poi.po_id = po.id
And saoi.sao_org_id = oschi.osch_org_id(+)
And saoi.sao_id = oschi.trans_id_3(+)
And saoi.id = oschi.trans_id_4(+)
And saoi.status <> 'CANCELLED'
And poi.status <> 'CANCELLED'
And
(saoi.sao_org_id, saoi.sao_id, saoi.id) Not In
(Select saoi.sao_org_id, saoi.sao_id, saoi.id
From palcs_items palcsi, ari_items arii,
ar_invoices ari, sao_items saoi
Where saoi.sao_org_id = palcsi.palcs_pal_org_id
And saoi.sao_id = palcsi.trans_id_3
And saoi.id = palcsi.trans_id_4
And palcsi.palcs_pal_org_id = arii.ari_org_id
And palcsi.palcs_pal_id = arii.trans_id_1
And palcsi.id = arii.trans_id_2
And palcsi.palcs_id = arii.trans_id_3
And arii.ari_org_id = ari.org_id
And arii.ari_id = ari.id
And nvl(ari.bl_confirmed
,'N') = 'Y')
And (saoi.customer_request_date Is Null Or
(trunc(saoi.customer_request_date) Between
nvl(to_date(p_cus_req_date_from
,'DD-MM-YYYY')
,trunc(saoi.customer_request_date)) And
nvl(to_date(p_cus_req_date_to
,'DD-MM-YYYY')
,trunc(saoi.customer_request_date))))
And (saoi.factory_due_date Is Null Or
(trunc(saoi.factory_due_date) Between
nvl(to_date(p_due_date_from
,'DD-MM-YYYY')
,trunc(saoi.factory_due_date)) And
nvl(to_date(p_due_date_to
,'DD-MM-YYYY')
,trunc(saoi.factory_due_date))))
And (sao.received_date Is Null Or
(trunc(sao.received_date) Between
nvl(to_date(p_received_date_from
,'DD-MM-YYYY')
,trunc(sao.received_date)) And
nvl(to_date(p_received_date_to
,'DD-MM-YYYY')
,trunc(sao.received_date))));
[Updated on: Mon, 16 June 2008 07:06] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Query is taking more time to execute [message #330279 is a reply to message #327431] |
Sat, 28 June 2008 15:08  |
Kriptas
Messages: 118
Registered: June 2008
|
Senior Member |
|
|
how meny items in cursor?
if tere >100 then do not use cursor.
cursors generate nested loops all the time, and optimiser runs query by the indexes...
it is fast if you need select by one patameter from starting table.
but better do not use cursors, and optmiser will use hash joins in meny joins, so it will be faster
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Nov 26 17:40:58 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
