Re: Index not getting used

"Parvez" <parvez_mk_at_yahoo.com> wrote in message news:1133166862.671744.66390_at_g43g2000cwa.googlegroups.com...
> Hi All,
>
> 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.
>
>
> Putting a Ordered by hint is better then no hint but still is not as
> good as Rule hint, why is it so, I want to avoid putting a Rule hint
>

Ultimately the answer to your question will be either:

    the statistics are misleading
or

    there is a transformation rule that currently blocks     what you perceive to be the best execution plan.

Regarding your index() hint - you will probably have to put in several other hints to ensure that the optimizer follows the path you expect. It is perfectly feasible for the optimizer to find an execution path that makes your hint illegal (or, more likely, out of context).

Since the rule based optimizer finds an efficient path using concatenation, you need to tell the cost based optimizer to use concatenation - at a minimum, you will need a /*+ use_concat */ hint. However, you may find that you still need to include other hints to make this produce exactly the path you want.

Suggestion.
Run the following:

    create or replace outline XXX on
    select /*+ rule */ .....

    select * from user_outline_hints
    where name = 'XXX';

Look at the hints where STAGE = 3
This is the set of hints that the optimizer thinks is necessary to replay that path through the CBO.

You may find that you also see a couple of different values for column NODE, and a few hints (typically NOREWRITE) for stages 1 and 2. Multiple nodes may make it harder to use the stored hints.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Nov 2005