Re: Numerical vs. Character Indexes

JimR1998 wrote:
>
> I'm counting the net wisdom here in CDOS to help settle an argument.
> There is a prevailing sense around my department that indexing character
> keys are a bad thing, and that such indexes are drastically less efficient
> than numerical ones. This is resulting in people inventing all kinds of
> kludgy workarounds to circumvent character based indexes.
>
> Their argument, of course, is that the CPU can natively compare numbers in
> a single instruction, but there is no hardware mechanism for comparing
> strings. I believe this argument falls apart at the RDBMS level, as Oracle
> is doing millions of other operations for that query besides a tiny little
> compare.
>
> Here's my reasoning:
>
> - Internally, Oracle stores both characters and numbers as bytes.
> Since the Oracle NUMBER datatype isn't necessarily the CPU native integer
> format, Oracle cannot and does not combine the byte values together to
> form a larger integer. (i.e. if you have a NUMBER(4) and a CHAR(4), Oracle
> is doing 4 single byte compares either way).
>
> - Performance tests on 6 million row tables have yielded a very
> tiny performance difference, sometimes going either way.
>
> - It is the depth of the B-Tree index, the number of leaf blocks,
> and uniqueness that govern performance, not the datatype being indexed.
>
> NUMBER(10) vs. VARCHAR2(10) -- both unique indexes -- Should there be a
> difference?
>
> NUMBER(10) vs. DATE -- both non-unique indexes -- Any difference?
>
> Does anyone want to voice their opinions here? I'm in favor of numerical
> keys, but in some places it's just far more convienant to use varchar
> keys. Of course long varchar indexes will be slower, but we're talking
> 10 or 20 byte varchar's at most.
>
> Comments anyone?

I suggest that you watch their chins drop when you tell them that Oracle stores numbers in a proprietary floating point format.

When comparing Oracle will break out early if the comparison is false. When comparing Number to Number or VARCHAR2/CHAR to VARCHAR2/CHAR for equality the compare will end at the first unequal byte. When comparing NUMBER to VARCHAR2/CHAR a conversion operation is performed before the compare is performed.

Depending on database activity, size of the SGA, etc. the index may be RAM resident in which case the depth of the tree may not make very much difference in terms of performance.

My rule of thumb -- admittedly a carryover from my days as a 3GL programmer -- is to only use NUMBER when the column is used for computations. Otherwise use VARCHAR2 unless valid values are fixed length and length is used in validity checking in which case I prefer to use CHAR.

--
Jerry Gitomer
Once I learned how to spell DBA, I became one. Received on Sun Jan 23 2000 - 23:52:04 CST