Re: Character vs. Numeric Searches

In article <50579_at_seismo.CSS.GOV> jean_at_beno.CSS.GOV (Jean Anderson) writes:
>Lately quite a few users have asked me if a search on a uniquely indexed
>column is faster if it is numeric than character (these are all equality
>matches).
>
>I have told them 'no', that their fastest search will be on that uniquely
>indexed column, regardless of data type (path #2 in the DBA Guide, 19-17).

You are correct. The reason is because ORACLE stores numeric values in an index using the CHAR datatype. I.e., there is no difference between indexing a CHAR column that stores numbers and indexing a NUMBER column (assuming they store the same number of digits) since in both cases you are doing string comparisons.

>I have told them width of column matters more than data type. You'll have
>more physical io on a wide index because Oracle can't cache as much of the
>index. The smaller the index, the more that can be cached.

If you use a COMPRESSED index, ORACLE will only store enough characters to make that index entry unique. Therefore it is not always true that a wide indexed column will search slower than a narrow indexed column.

-- 
Michael Wang
mmwang_at_adobe.com