| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> 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)
/*+ 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 ...
-- 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 2005Received on Mon Nov 28 2005 - 04:58:22 CST
 |
 |