Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Index not getting used

Re: Index not getting used

From: Jonathan Lewis <>
Date: Mon, 28 Nov 2005 10:58:22 +0000 (UTC)
Message-ID: <dmense$3br$>

"Parvez" <> wrote in message
> My oracle version is so I cannot use outline feature. I am
> executing the query with concat hint but it has still not finished the
> execution, mostly I think it is ignored, can confirm only after the
> execution is completed. Also regarding concat it will use "union all"
> which will return duplicate records (same record can be created &
> updated on same day), I want a "union" operator instead.
> I estimated the statistics with the default option (1064 sample), I
> will try 10% estimate, it will take some time though.
> The created_date and updated_date have two different index. I dont have
> histograms on both the columns as the column value is uniformly
> distributed except updated_date (since it has many null value, but for
> not null the values are uniformly distributed), I dont think histogram
> is of any use, is it?

A couple of points -
It is possible that Oracle has found a way of abusing, or invalidating your use_concat hint - remember the point I made in my post:

    >                                                    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.

Looking at the original query and the rule-based path, you could need (at least) the following in the main query:


        index(a deal_idx7 deal_idx8)
        index(b medium_idx1)

with this hint in the subquery

    /*+ no_unnest */

Your comment about /*+ use_concat */ not being appropriate because it generates a "union all" and returning duplicates is incorrect. A hint cannot change the result set, it can only change the execution path. (Although there have been bugs where certain execution paths have actually resulted in the wrong result set - but these are BUGS, not design features). Note, also, that your RBO plan is using concatenation and (presumably) is returning the correct result.

Following up on the RBO plan - I've just realised that it doesn't report the aggregate subquery taking place against the send branch of the concatenation, and I can't think of a good reason why it's missing.

Finally - is there any problem with rewriting the query - you could use in-line views, even in 8.0.6, to ensure that you can isolate the DEALS section with a no_merge and get the correct deals before joining to the medium; or you could simply rewrite the query as a UNION.

Given that your query doesn't involve any bind variables, you could use "explain plan" to get the execution path without running the query - or even

    set autotrace traceonly explain
    select ...


Jonathan Lewis
The Co-operative Oracle Users' FAQ
Cost Based Oracle: Fundamentals
Public Appearances - schedule updated 4th Nov 2005
Received on Mon Nov 28 2005 - 04:58:22 CST

Original text of this message