Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: slow query - index usage question
Ed Stevens wrote:
> On Mon, 17 Mar 2003 16:18:00 +0000, Rob Cowell <rjc4687_at_hotmail.com>
> wrote:
>
>
>> >>Ed Stevens wrote: >> >>>Platform - Ora 8.0.6 on NT4 >>> >>>User presented me with this query: >>> >>>SELECT /* + INDEX(t_warranty_claim, T_WCLM_FK_WVIN) Use T_WCLM_FK_WVIN >>>*/ >>> DISTINCT VIN_NBR >>> FROM T_WARRANTY_CLAIM >>> WHERE VIN_NBR NOT IN (SELECT DISTINCT VIN_NBR FROM >>>S_WARRANTY_VIN) >>> AND VIN_NBR > :v1 >>> ORDER BY VIN_NBR >>> >> >>Do you mean a RANGE SCAN or FAST FULL SCAN? There are no WHERE >>predicates in the sub select, how could it RANGE SCAN ? >>The optimizer is deciding to evaluate both DISTINCTs and an ORDER BY >>with a single SORT UNIQUE, sounds quite efficient in theory.
Hello Ed,
it would be nice to have the table declaration, also how recent statistics are, data size and distribution etc. (You're really long enough on this group to know that the more detailed the information, the better the answer). ;-)
But since I did a few tests, here's what I found (and what I expected from the very beginning): If the column vin_nbr isn't declared as not null, then an index cannot be used. Besides, if it's not only a typo from posting, then the hint won't be effective because there's a space between '/*' and '+'.
Furthermore: it has been said often (by such knowledgeable people as T.Kyte) that a hint is a hint not an order. A hint adjusts factors that are taken into the calculation of the costs. So depending on the information the optimizer has, it still can (and will) decide that it's more cheaper to use a full table scan compared to an index fast full scan.
If that's the case, then I'd suggest you start looking for a document called something like 'Search for intelligence in cbo', which explains some of the parameters that influence cbo's decision when to use and index.
HTH Holger Received on Wed Mar 19 2003 - 02:40:14 CST