Sql Performance

[14w6119]

Hello

This query is taking 12 minutes to execute because of the correlated subquery.I need to rewrite it so that it takes very less time.Can Some one help please ?

select count(*)


from service_requests sr

, appointment_statuses appst

, appointments app

, service_request_processing srp

, paths p

where sr.service_request_id = app.service_request_id

and app.appointment_id = appst.appointment_id

and sysdate between appst.eff_start_dt and nvl(appst.eff_end_dt, sysdate)

and appst.appointment_status_type in ('Show','Pending')

and not exists ( select 'x'

from service_request_task_statuses srts

, service_request_tasks srt

, invoice_items_log iil

where srt.service_request_id = sr.service_request_id

and srt.task_id = 24

and srt.service_request_task_id = srts.service_request_task_id

and srts.task_status_name = 'Appt.Invoice'

and srts.task_object_pk_value = iil.invoice_items_log_id

and iil.appointment_id = app.appointment_id

and sysdate between srts.eff_start_dt and nvl(srts.eff_end_dt, sysdate)

and srts.task_status_value_type = 'Completed')

and srp.service_request_id = sr.service_request_id

and p.path_id = srp.path_id

and substr(p.path_name, 1, 3) = office.officename;

 

[SantaMufasa]

14W,

Among the pieces of information that would help us help you better:

1) What does Oracle say that your PLAN is for your code?
2) What are the statistics (rows) for each of the tables?
3) What indexes have you on the columns appearing in your WHERE statements?
4) If you remark out the correlated subquery, how much time does the query then consume?

Mufasa
(aka Dave of Sandy, Utah, USA @ 07:02 (03Jun04) UTC (aka "GMT" and "Zulu"), 00:02 (03Jun04) Mountain Time)

 

[aryeh1010]

You have function calls in the not exists sub-query this will usually result in full table scans.

You might consider changing the "and sysdate between srts.eff_start_dt and nvl(srts.eff_end_dt, sysdate)
" condition in the sub-query to

"and sysdate between srts.eff_start_dt and srts.eff_end_dt and arts.eff_end_dt is not null"

"and sysdate between srts.eff_start_dt and sysdate and arts.eff_end_dt is null"

and then run the query with union all.

Another item is the substr function on the paths table. If you create a function-based index, you'll be able to use an index for the correlated sub-query.

Good luck,

Aryeh Keefe

 

[sem]

Areyeh1010, can you explain how your conditions are better than the original?

Regards, Dima

http://seminihin.narod.ru