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).
|> 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.
|>
|> Am I right? or all wet? My answer is encountering much skepticism.
|>
|> - jean
|>
|>
|> +-----------------------------------------------------------------------+
|> | Jean Anderson, DBA email: jean_at_seismo.css.gov |
|> | SAIC Open Systems Division, MS A2-F or: jean_at_esosun.css.gov |
|> | 10210 Campus Point Drive phone: (619)458-2727 |
|> | San Diego, CA 92121 fax: (619)458-4993 |

 +-----------------------------------------------------------------------+


|> |                execute sp_disclaimer _at_opinion="mine"                  |


|> +-----------------------------------------------------------------------+

I can't see any reason for you to be wrong.

However numbers are stored in BCD (Binary coded decimal) (I hope !!) so a fairly big number will take little room. A charcter key with contains the same numbers will take more storage space, therfore will take longer to search.

I think this may be where some of the confusion lies, so the question that should have been orignal asked may be ; "If I have a numeric key will a search be faster if I store the key as a char field or a number field ?"

You can of coures search charcters in binary form (as you can chars), or even a numneric representation of the characters. When you get down to it about the only possable difference that matters in searching a load of 1's & 0's is how long the load is.

RICK DIPPER, Unix Systems Administrator                       rick_at_uk.ac.man.cs
Department of Computer Science, University of Manchester           061-275-5725
.............................Meemberrr the of Fumblee Ingers Culb..............