Home » RDBMS Server » Performance Tuning » query (oracle 10g, Linux)
query [message #403201] Thu, 14 May 2009 03:05 Go to next message
manijash
Messages: 2
Registered: May 2009
Location:
Junior Member


Pl. suggest how to rewrite the below query in better way.

SELECT sds.obj_id, sds.seq_no, ods.parent_obj_id, ods.viewable,
boj.obj_created, boj.obj_modified, boj.obj_name, boj.obj_owner,
boj.obj_owner_name, boj.obj_type, boj.obj_descr,
sds.QUERY AS QUERY, sds.content_set_name, sds.collections,
sds.search_type, sds.srch_folder_id, dds.sort_fields,
dds.retrieve_fields, dds.sort_orders, dds.content_category,
dds.name_value, dds.offset, dds.size_count, oda.alert_id,
sds.FIELDS, sds.field_values AS field_values
FROM display_details dds,
search_details sds,
base_object boj,
object_def_alert oda,
object_def_search ods
WHERE sds.obj_id = boj.obj_id
AND oda.alert_ref_id(+) = sds.obj_id
AND dds.obj_id(+) = sds.obj_id
AND ods.obj_id = sds.obj_id
AND sds.obj_id IN (323)
CONNECT BY PRIOR ods.obj_id = ods.parent_obj_id
ORDER BY sds.obj_id DESC, dds.content_category
Re: query [message #403202 is a reply to message #403201] Thu, 14 May 2009 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- Performances Tuning sticky
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

Then provide the required and usual information.

You can also have a look at Database Performance Tuning Guide.

Regards
Michel
Re: query [message #403248 is a reply to message #403202] Thu, 14 May 2009 07:48 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Your CONNECT BY doesn't have a START WITH clause. That means you will generate a sub-tree starting with every single row in object_def_search.

You sure that's what you want?

Also, you create a tree by traversing from PARENT_OBJ_ID to OBJ_ID and then filter the query by just one of those OBJ_ID values, thereby undoing all of the work of traversing the tree. Doesn't really make much sense.

Ross Leishman
Re: query [message #403253 is a reply to message #403201] Thu, 14 May 2009 08:24 Go to previous message
manijash
Messages: 2
Registered: May 2009
Location:
Junior Member
Could you please modify the query as you suggested
Previous Topic: Query Optimization (merged)
Next Topic: Inner workings: how often is data written to disk
Goto Forum:
  


Current Time: Sat Dec 03 22:00:50 CST 2016

Total time taken to generate the page: 0.04651 seconds