Home » RDBMS Server » Performance Tuning » Help needed in querying View
Help needed in querying View [message #278508] Sun, 04 November 2007 16:07 Go to next message
seventhstar
Messages: 1
Registered: November 2007
Junior Member
Hi All,

I have a view which I am not allowed to change. If I provide 3 paramteres to query the view on production database where data is around 3 million. It take fair amount of time. I dont have issue with it but now situation is one paramter is type of varchar2 which I want to set with LIKE but whenever I add LIKE / IN / EXISTS operators it makes me crazy cause it take long time. I need help to understand any technique which will provide fix set of values to this view so that view works fast.

example:

select * from v_test where
col1=v1
col2=v2
col3=v3; -- Works well

when

select * from v_test where
col1=v1
col2=v2
col3 like v3% OR IN (select col3 from table1) or EXISTS; -- Defeats the purpose and takes long time



Regards,

Re: Help needed in querying View [message #278509 is a reply to message #278508] Sun, 04 November 2007 16:26 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:


select * from v_test where
col1=v1
col2=v2
col3 like v3% OR IN (select col3 from table1) or EXISTS; -- Defeats the purpose and takes long time

I bet that if you run THIS 'SQL' against your db it will not take a long time at all. I bet that it will, very quickly, return an error.
Apart from that, you are asking for tuning advice for sql against a view that you have provided no details of as well as supplying code that you aren't actually running against that view. What exactly do you expect?
Re: Help needed in querying View [message #278510 is a reply to message #278508] Sun, 04 November 2007 16:33 Go to previous message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
>col3 like v3%
likely precludes the use on the index on col3 & forces a Full Table Scan (FTS).
Previous Topic: Different Query Plan and execution time of same Query
Next Topic: Help Please
Goto Forum:
  


Current Time: Wed Dec 07 14:22:56 CST 2016

Total time taken to generate the page: 0.11319 seconds