Numerical vs. Character Indexes

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? Received on Sun Jan 23 2000 - 20:20:57 CST