| WHERE CLAUSE TUNING [message #220085] |
Sun, 18 February 2007 15:45  |
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 #220095 is a reply to message #220086] |
Sun, 18 February 2007 19:54  |
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 #220179 is a reply to message #220085] |
Mon, 19 February 2007 06:07 |
michael_bialik
Messages: 621
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
