Re: slow query - index usage question

Comments embedded

Holger

Ed Stevens wrote:
[snip]
 > ) I just hope people here don't run out of patience with me.

No worries ;-) I know you try hard (and rest assured, you know things in Oracle I've never heard of)

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

... which is fine if the developer is aware of the consequences. See

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:442029737684

for differences between 'not in' and 'not exists'.

> Ah -- key points I missed. Unfortunately (and against my
> recommendation) all of the columns except the PK allow nulls. There

Column not null => index not usable. End of story :-( (see also Telemachus' comments to my previous post)

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

Actually, no. A hint is syntactically a comment. So if it's malformed, there won't be an error but it will be considered as a comment. Only if it can be resolved as a hint, it will be used.

Search for 'RBO and optimizer hints' (it's the title of a thread) at:

  http://asktom.oracle.com

for an example.

HTH
Holger Received on Wed Mar 19 2003 - 10:33:25 CST