Re: Index not getting used

"Parvez" <parvez_mk_at_yahoo.com> wrote in message
news:1133172991.888854.63820_at_z14g2000cwz.googlegroups.com...
> My oracle version is 8.0.6.1.0 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:

/*+

ordered
use_concat
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