Re: Is this a good situation for an Index ?

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Thu, 25 Sep 2003 21:35:42 +1000
Message-ID: <3f72d3c1$0$13416$afc38c87@news.optusnet.com.au>

Niall Litchfield wrote:

> "Mark" <marki.NOSPAM_at_libero.it> wrote in message
> news:_4zcb.345710$Ny5.11008401_at_twister2.libero.it...
>> Hi all DBAs,
>> I have a table (of about 30.000 records) with a varchar2 field 'CODE' in  it.
>> All 'CODE' have different values (even if there isn't a unique constraint  in
>> it).
>> Since CODE is in the WHERE clause, is it a good situation for using an  index

>> on it ? does it have some overhead to index a non numeric column ?

>
> Yes to the first question and No to the second, there is an overhead but
> no different in real life to indexing a number column. If CODE is designed
> to be unique then constrain it to be unique using an Unique index.
>

 I can't agree with that last one. If it's to be unique, then constrain it to be unique with a unique *constraint*, and never mind whether the index is unique or non-unique. In fact, non-unique has advantages. And also in fact, Oracle has been recommending people *not* to create unique indexes since at least the 8.1.6 doco.

Regards
HJR

>>
>> A last question: if I want to retrieve rows with CODE that begins with
>> 'A'

 ?

>> will this prevent the index to be used ?

>
> Not necessarily. WHERE CODE LIKE 'A%' might use the index depending on a
> number of factors, but WHERE CODE LIKE '%A1%' can't.
>
>
Received on Thu Sep 25 2003 - 06:35:42 CDT