Home » RDBMS Server » Performance Tuning » query for optimisation (oracle 10g)
query for optimisation [message #557620] Thu, 14 June 2012 05:41 Go to next message
raja859
Messages: 2
Registered: June 2012
Junior Member
Hi below query is causing performance issue.Is there any other way of doing it so by procedure or with in sql statement itsef.
please do needful

SELECT DISTINCT rt.result_value_1 rsv
   FROM t_fms_ratetable rt,
  t_fms_rt_column_def rtd ,
  t_fms_col_value rtv
  WHERE rtd.ratetable_id LIKE ?
AND rt.column_value LIKE
  (SELECT rtv.col_value_id
     FROM t_fms_rt_col_value rtv
    WHERE rtv.rt_column_id LIKE ?
  AND rtv.col_value_desc LIKE ?
  )
  ||'-'
  ||
  (SELECT rtv.col_value_id
     FROM t_fms_rt_col_value rtv
    WHERE rtv.rt_column_id LIKE ?
  AND rtv.col_value_desc LIKE ?
  )
  ||'-'
  ||?)



Input: 
Rate TABLE ID	: GS_33_000000900001760 
Column_id	: GS_33_000000900002400 
Col_value_desc  : 1 TO 5 ANY value 
Column_id 	: GS_33_000000900002401 
Col_value_desc 	: 500,1000,1500,2000 
Daterange       : GS_33_000000900439240

Re: query for optimisation [message #557627 is a reply to message #557620] Thu, 14 June 2012 06:12 Go to previous messageGo to next message
Michel Cadot
Messages: 67952
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel
Re: query for optimisation [message #557638 is a reply to message #557627] Thu, 14 June 2012 08:19 Go to previous messageGo to next message
raja859
Messages: 2
Registered: June 2012
Junior Member
Hi Michel,

thanks for the response. please help me with help of writing a procedure else a sql statement using join as i tried this way causing a bit more time.

Regards,
Raj
Re: query for optimisation [message #557642 is a reply to message #557638] Thu, 14 June 2012 08:40 Go to previous messageGo to next message
Michel Cadot
Messages: 67952
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Help us to help you by posting what is requested.

Regards
Michel
Re: query for optimisation [message #557650 is a reply to message #557638] Thu, 14 June 2012 10:11 Go to previous message
flyboy
Messages: 1899
Registered: November 2006
Senior Member
raja859 wrote on Thu, 14 June 2012 15:19
please help me with help of writing a procedure else a sql statement using join as i tried this way causing a bit more time.

At the first look, that query contains DISTINCT keyword, which is just masking insufficient/non-existent join conditions. You should deffinitely remove it.

For next corrections, it is necessary to know current table definition with their relationship (CREATE TABLE statements, including definition of primary/foreign keys) and detailed requirements for the output (ideally in words). Some sample data illustrating it (INSERT statements with expected result set) would help in understanding it. Michel asked you twice for providing it.

Unfortunately your post contains only one unformatted query. I am afraid that nobody is willing to reverse engineer it for reconstructing (rather guessing) missing parts.

Good luck in your effort.
Previous Topic: Performace issue in transaction system
Next Topic: how to find the SQL statement which have many hard parse
Goto Forum:
  


Current Time: Mon Sep 27 23:07:17 CDT 2021