Re: Is this a good situation for an Index ?

"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.

>
> 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.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK