Home » RDBMS Server » Performance Tuning » Is possible to turn the following into a view (Oracle 10g)
Is possible to turn the following into a view [message #335263] Mon, 21 July 2008 11:53 Go to next message
bztom33
Messages: 95
Registered: June 2005
Member
Hi,

I want to improve the speed of the return query. Is possible to turn this into a view table?

my_foreast_date, crsSensor,prsSensor,arsSensor and userID are input parameters.

select a.date_time, a.value value1, b.value value2, b.flag, c.value value3 from product a, product b, product c
where a.run_date =:my_forecast_date
and a.run_date = b.run_date
and a.run_date = c.run_date
and a.date_time = b.date_time
and a.date_time = c.date_time
and a.fcst_sensor_id =:crsSensor
and b.fcst_sensor_id =:prsSensor
and c.fcst_sensor_id =:arsSensor
and a.user_id = 1
and a.user_id = b.user_id
and c.user_id = :userID
order by a.date_time

Thanks,
Re: Is possible to turn the following into a view [message #335264 is a reply to message #335263] Mon, 21 July 2008 11:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: Is possible to turn the following into a view [message #335299 is a reply to message #335263] Mon, 21 July 2008 14:51 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
do you mean something like this:

CREATE OR REPLACE VIEW SomeView
AS
  SELECT a.Date_Time,
         a.VALUE Value1,
         b.VALUE Value2,
         b.Flag,
         c.VALUE Value3,
         a.Run_Date My_Forecast_Date,
         a.fcst_Sensor_Id crsSensor,
         b.fcst_Sensor_Id prsSensor,
         c.fcst_Sensor_Id arsSensor,
         c.User_Id = UserId
  FROM   Product a,
         Product b,
         Product c
  WHERE  a.Run_Date = b.Run_Date
         AND a.Run_Date = c.Run_Date
         AND a.Date_Time = b.Date_Time
         AND a.Date_Time = c.Date_Time
         AND a.User_Id = 1
         AND a.User_Id = b.User_Id
/

SELECT *
FROM   SomeView
WHERE  My_Forecast_Date = :my_forecast_date
       AND crsSensor = :crsSensor
       AND prsSensor = :prsSensor
       AND arsSensor = :arsSensor
       AND UserId = :userID
/

Notice that we simply added the columns your want to query by, to the view select list. Then you can create a view based on the query without the where components, leaving you to submit a query that then uses these new columns.

I leave it to you to test the syntax and validity of this code.

Good luck, Kevin
Re: Is possible to turn the following into a view [message #335796 is a reply to message #335299] Wed, 23 July 2008 16:00 Go to previous messageGo to next message
bztom33
Messages: 95
Registered: June 2005
Member
Hey Kevin,

Thank you very much for helping me to simplify my query. It's working great. This view actually speed up my query result quite a bit.
Thanks Again.

Tom


CREATE OR REPLACE VIEW SomeView
AS
SELECT a.Date_Time,
a.VALUE Value1,
b.VALUE Value2,
b.Flag,
c.VALUE Value3,
a.Run_Date My_Forecast_Date,
a.fcst_Sensor_Id crsSensor,
b.fcst_Sensor_Id prsSensor,
c.fcst_Sensor_Id arsSensor,
c.User_Id UserId
FROM Product a,
Product b,
Product c
WHERE a.Run_Date = b.Run_Date
AND a.Run_Date = c.Run_Date
AND a.Date_Time = b.Date_Time
AND a.Date_Time = c.Date_Time
AND a.User_Id = 1
AND a.User_Id = b.User_Id

[Updated on: Wed, 23 July 2008 16:01]

Report message to a moderator

Re: Is possible to turn the following into a view [message #335797 is a reply to message #335263] Wed, 23 July 2008 16:03 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
I'd take another look at that performance speedup. I am not sure why converting to the view would make it faster. It is essentially the same query. This is not to say that there is no optimizer perculiarity involved. Just do some more testing.

Glad we got one right.

Kevin
Previous Topic: high cost
Next Topic: how find SGA size is sufficient or not?
Goto Forum:
  


Current Time: Tue Jul 02 11:35:30 CDT 2024