Re: Why Oracle does not use unique index ???

In cost-based optimization Oracle does not always use the index. Sometimes it decides a table scan has a lower cost. Of course, it may be wrong so you might have to use a hint to force it to do what you want.

Van

Maoz Mussel wrote in message <7ddf6b$oca$1_at_news.netvision.net.il>...
>Hi,
>
>I have a table with few indexes. First, when I run the
>following simple select statement:
> SELECT field1 FROM CUSTDATA WHERE USERCODE = 5;
>... where USERCODE is a field having unique index, I got
>the following explain plan:
>
> 0 SELECT STATEMENT GOAL: CHOOSE
> 1 INDEX (UNIQUE SCAN) OF 'USERCODE_IND' (UNIQUE)
>
>Now, when I'm running this statement:
> SELECT field1 FROM CUSTDATA WHERE ACCESSKEY=98
>... where ACCESSKEY is also a field having unique index, I got
>this explain plan:
>
> 0 SELECT STATEMENT GOAL: CHOOSE
> 6300 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'CUSTDATA'
>
>I don't understand why it did not use the index of ACCESSKEY. I tried
>to force using this index, as follows:
> SELECT /*+INDEX (CUSTDATA ACCESSKEY_IND) */
> field1 FROM CUSTDATA WHERE ACCESSKEY=98
>
>and got this explain plan:
>
> 0 SELECT STATEMENT GOAL: CHOOSE
> 0 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'CUSTDATA'
> 0 INDEX (FULL SCAN) OF 'ACCESSKEY_IND' (UNIQUE)
>
>
>I would like to understand the reason for this behavior.
>
>thanks,
>Maoz
>
>
Received on Thu Mar 25 1999 - 17:58:07 CST