File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Oracle Queries performace Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Oracle Queries performace" Watch "Oracle Queries performace" New topic

Oracle Queries performace

Naresh Nimmala

Joined: Jan 26, 2008
Posts: 3
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/*?*/
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.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

Joined: Jun 21, 2008
Posts: 423
See these informative links:
Jan Cumps

Joined: Dec 20, 2006
Posts: 2565

Welcome to JavaRanch, Naresh.

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

Regards, Jan

OCUP UML fundamental and ITIL foundation
Naresh Nimmala

Joined: Jan 26, 2008
Posts: 3
yes they are working but taking time to execute, i am new to sql queries.
Jan Cumps

Joined: Dec 20, 2006
Posts: 2565

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
I agree. Here's the link:
subject: Oracle Queries performace
jQuery in Action, 3rd edition