Home » RDBMS Server » Performance Tuning » WHERE CLAUSE TUNING
WHERE CLAUSE TUNING [message #220085] Sun, 18 February 2007 15:45 Go to next message
bm_souj
Messages: 7
Registered: February 2007
Location: TX,USA
Junior Member

I have this WHERE clause in a query which is taking long time and foing thru FTS bcos of using >=, < & nvl or coalesce functions though I have all my indexes in place....can somebody help me to rewrite this WHERE clause plsssssssss


-------------------------------------------------------------
FROM cv_strm_day_stream, STRM_VERSION oa, STREAM o
WHERE oa.object_id = cv_strm_day_stream.object_id
AND cv_strm_day_stream.object_id = o.object_id
AND cv_strm_day_stream.DAYTIME >= oa.daytime
AND cv_strm_day_stream.DAYTIME < coalesce(oa.end_date,to_date('01.01.2200','dd.mm.yyyy'))
Re: WHERE CLAUSE TUNING [message #220086 is a reply to message #220085] Sun, 18 February 2007 16:14 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>AND cv_strm_day_stream.DAYTIME < coalesce(oa.end_date,to_date('01.01.2200','dd.mm.yyyy'))
Hmmm.... I have never seen this before.
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/functions023.htm#sthref1139
I am not convinced the above is valid usage.
Exactly what do/did you want it to do?
Re: WHERE CLAUSE TUNING [message #220095 is a reply to message #220086] Sun, 18 February 2007 19:54 Go to previous messageGo to next message
bm_souj
Messages: 7
Registered: February 2007
Location: TX,USA
Junior Member
Thanks for the reply....

CV_STRM_DAY_STREAM is a view.

coalesce(oa.end_date,to_date('01.01.2200','dd.mm.yyyy'))
--here if the end_date is null, then we want it to be replaced it with date returned by to_date().

---I have also created a function index on
coalesce(oa.end_date,to_date('01.01.2200','dd.mm.yyyy'))

As it is using "<" operator, my function index is not being used by optimizer.


ANy ideas to workaround in faster way....?
Re: WHERE CLAUSE TUNING [message #220097 is a reply to message #220085] Sun, 18 February 2007 20:01 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You could try the NVL function
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/functions105.htm#sthref1721
Re: WHERE CLAUSE TUNING [message #220102 is a reply to message #220097] Sun, 18 February 2007 20:15 Go to previous messageGo to next message
bm_souj
Messages: 7
Registered: February 2007
Location: TX,USA
Junior Member
Yes...i did.........first i tried with NVL then coalesce...no change in performance
Re: WHERE CLAUSE TUNING [message #220105 is a reply to message #220102] Sun, 18 February 2007 20:26 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Take a look here.

Ross Leishman
Re: WHERE CLAUSE TUNING [message #220176 is a reply to message #220085] Mon, 19 February 2007 06:03 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Post EXPLAIN and some data about your tables ( number of rows, existing indexes, etc.)
Re: WHERE CLAUSE TUNING [message #220179 is a reply to message #220085] Mon, 19 February 2007 06:07 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Continued:

You have only join conditions in your query, so optimizer does not have any other choice but using full table scans for at least one table. It may join other tables via indexes (NL join) or using another FTS (hash or sort-merge join). The method depends on number of rows in your tables.

Previous Topic: Tunig of query
Next Topic: NOt EXISTS & NOT IN ???
Goto Forum:
  


Current Time: Fri Dec 09 06:02:58 CST 2016

Total time taken to generate the page: 0.20413 seconds