This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
Hi I would like to know how we can imrove oracle queries performance.
For example i have following sample queries which i need to optimize.
1) select count(0) totalCount from analyst_call_log rl,analyst_team at, (select associate_id from
associate)creted_by_detail where rl.added_by_assoc_id = creted_by_detail.associate_id and
at.associate_id = rl.added_by_assoc_id and at.team_id = rl.team_id and rl.confidential != 'Y' and
rl.analyst_call_log_id in(select ac.analyst_call_log_id from analyst_call_log_contact ac where
ac.client_id in (select client_id from client_hier_coverage where assoc_id= 16369/*?*/) ) and
rl.call_log_dt_utc >= add_months(TO_DATE('03-19-2010'/*?*/,'MM/DD/YY'), -6)
2) select CASE WHEN added_by_assoc_id = 16369/*?*/
get_analyst_call_log_attendees(analyst_call_log_id) client_attendees, get_analyst_call_log_clients(analyst_call_log_id) client_submasters,
analyst_call_log_id callLogId ,'Analyst' callLogType,created_dt_utc,
to_char(call_log_dt_utc, 'YYYY-Mon-DD') call_log_dt_utc,
(select row_number() over (order by call_log_dt_utc ASC,rl.created_dt_utc desc) rn,rl.analyst_call_log_id,rl.created_dt_utc,rl.added_by_assoc_id,rl.subject,creted_by_detail.created_by_name,obo_user_detail.country,obo_user_detail.obo_user,rl.type
, rl.call_log_dt_utc,at.team_name, rl.tickers_discussed,
call_duration from analyst_call_log rl,analyst_team at,
(select NBK_ID,associate_short_name created_by_name from associate)creted_by_detail,
(select associate_id, associate_short_name obo_user,country from associate) obo_user_detail
where upper(rl.created_by) = upper(creted_by_detail.NBK_ID)
and rl.added_by_assoc_id = obo_user_detail.associate_id
and at.associate_id = rl.added_by_assoc_id
Anybody can help me how i can optimize database sql queries if there are any tutorials let me know for improve performance.
I would go back to the drawing table.
Bring back each of these queries (one at a time) to their most simple working form: select only values from the most important table.
Then - and only when you have the first step right - link in the additional tables, one at the time. Be sure to understand what you are doing. Only proceed to the next table if you have a working, performant and understood query.
When I look at your queries, I think it would help if you ask an experienced sql person of your team to assist you for a few hours.
You need to get the basis right. These queries are fairly complex if you are new to sql.