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

Yes, datatype conversion can prevent the use of an index.

also remember that it must be useful to use the index. How many distinct values for 'accesskey' are there? As your explain plan indicates, you are running cost-based. Did you analyze your schema objects?

JM

Gerald Bull wrote in message <36FA4AB5.67A35A89_at_fmr.com>...
>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 - 09:20:35 CST