Re: Numerical vs. Character Indexes

Surely some of the 'tricks' to get numerical indexes artificially in to a relational data model will run the risk of creating structures which are far less efficient to access than a simple non-numerical index would produced.

If you have the artificial example of an isolated table you may be alright but if you have tables with inter-relationships, referencial integrity checks for instance (in the database or in the software) you could be walking through more tables that you otherwise might need.

Regards

Kevin

--
Kevin A Lewis (BOCM PAULS LTD) - Animal Feed Manufacturer - Ipswich United Kingdom)

                        <Kevin_A_Lewis_at_Hotmail.com>

The views expressed herein by the author of this document are not necessarily those of BOCM PAULS Ltd. Jerry Gitomer <jgitomer_at_erols.com> wrote in message news:388BE884.DF2605F_at_erols.com...
> 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 Mon Jan 24 2000 - 03:40:54 CST