Re: Index not getting used

On Mon, 28 Nov 2005 00:34:22 -0800, Parvez wrote:

> I am having situation where my below query is not using index on
> updated date column, I have index on two date columns, created date and
> updated date, the query under focus has a OR condition with both the
> columns, the query execution is very high (full table scan) as below.
> If I just use created date it picks corresponding index and query
> execution time is very less. The tables are analyzed.

Do you have two indexes, one on "created_date", one on "updated_date" or do you have a composite index on (created_date,updated_date)? If latter is the case, then you shouldn't expect it to be used for the range scan on updated_date. You might try with the index_ss hint if you want it to use the composite index for the range scan of the composite index. Next, your range delimiters are constants. If there is a histogram and you don't have any values in the given range, an index on updated_date is useless and wouldn't be used.

-- 
http://www.mgogala.com