Re: slow query - index usage question

On Wed, 19 Mar 2003 09:40:14 +0100, Holger Baer <holger.baer_at_science-computing.de> wrote:

>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.
>>
>>
>> Yes, I should have said a FAST FULL SCAN. In any event, we don't
>> understand why the inner query was using a full table scan when the
>> criteria could have been satisfied from the high-order column of one
>> of the indexes.
>>
>>
>
>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). ;-)
>

You're right, I should have known better and supplied that info. Unfortunately, my time in this group would tend to overstate my actual experience. I'll be the first to admit that even though I've been at this about seven years now I don't have seven years' experience. (I'll refrain from writing the epistle that I'd like on that subject! ) I just hope people here don't run out of patience with me.

Stats are current. Table has 5.8 million rows, avg row len is 197 bytes, VIN is evenly distributed -- almost to the point of being unique. Ooo. I just noticed there are over 532k chained rows. Guess I need to look into that.

FWIW, the developer made a change in the query, to read:

        SELECT /* + INDEX(t_warranty_claim, T_WCLM_FK_WVIN) Use T_WCLM_FK_WVIN */

	VIN_NBR
	FROM   T_WARRANTY_CLAIM A
	WHERE NOT EXISTS (SELECT VIN_NBR FROM S_WARRANTY_VIN WHERE VIN_NBR
= a.VIN_NBR)
	GROUP BY VIN_NBR
	ORDER BY VIN_NBR;

Notice the use now of the NOT EXISTS instead of the NOT IN.

What's particularly odd is that this whole question came up because a batch job that had been taking 2 to 3 minutes suddenly went to 8 hours and wasn't finished when we finally gave up and killed it. And with this new version of the query, run time is now down to less than 30 seconds!
>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 '+'.

Ah -- key points I missed. Unfortunately (and against my recommendation) all of the columns except the PK allow nulls. There wasn't a typo in the hint (it was cut'n'pasted from the original), but not haveing much exeprience with them, I figured if there were a syntax error it would have generated such rather than simply ignoring it.

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

Another point I had either not known or forgotton from lack of use.
>
>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

Thanks for your comments. You've given me some more to study. Received on Wed Mar 19 2003 - 07:49:27 CST