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

Make sure that the data type of the column is NUMBER or some derivative. If the column is a VARCHAR2, then the server will perform implicit conversion on the COLUMN instead of the value because by default when comparing a NUMBER to a VARCHAR2, the VARCHAR2 is always converted to a number first. From your explain plans this is what I guess is happening, because even with the INDEX hint, you are doing a full index scan. Try changing your select to

   SELECT field1 FROM CUSTDATA WHERE ACCESSKEY='98'

and then see if it uses the index appropriately.

Jerry.

Maoz Mussel wrote:

> 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 - 08:39:49 CST