Why Oracle does not use unique index ???

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 - 07:58:27 CST