Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Oracle Queries performace

 
Naresh Nimmala
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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/*?*/
THEN 'Y'
ELSE 'N'
END coveredByUser,
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,
added_by_assoc_id,subject,created_by_name,type,team_name,country,obo_user,tickers_discussed,call_duration from
(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.

 
Ireneusz Kordal
Ranch Hand
Posts: 423
 
Jan Cumps
Bartender
Posts: 2584
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to JavaRanch, Naresh.

Do these queries actually work?
Besides the performance issues you have, do they give you the correct results?

Regards, Jan
 
Naresh Nimmala
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yes they are working but taking time to execute, i am new to sql queries.
 
Jan Cumps
Bartender
Posts: 2584
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.


Regards, Jan
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic