The Oracle (tm) Users' Co-Operative FAQ
Which type of index can Oracle access fastest, a character key or a numeric key ?
| Author's name: David T. Bath Author's Email: David.Bath@edipost.auspost.com.au Also once known as dtb@otto.bf.rmit.edu.au or orafaq@bf.rmit.edu.au |
Date written: 7th Aug 2001 Oracle version(s): 7.x |
| Which type of index can Oracle access fastest, a character key or a numeric key ? |
It is interesting why this question is asked. There are two answers.
The technically correct one from a performance DBA. That's not me, or this answer.
Notes about a hack in foreign key design to bypass this problem you're facing and asking this question.
Background:
a) Consider the C calls for comparison.
native integers : word at a time : on the CPU strcmp(), memcmp() : byte at a time native floats : ??? : on the CPU/FPU
which implies string comparison is slower. BUT, are indices
using native types?
b) How does Oracle find a key component in an index/table?
1. Get the row 2. Get the field
Now, if the field is first in the row, it is easy. If it is a subsequent field, it depends if ANY of the previous fields were variable length or not. If ALL fields fixed, life is sweet. If not.
1. Get the first field 2. If variable length, figure out length 3. Move across that length to next field.......
Evil Hacking:
a) Create a function that converts a big integer (and Oracle lets you safely deal with 2**126 without precision loss) to a bit pattern within a fixed-width CHAR (not VARCHAR). Such beasties I call FIDs (fixed-width ids) b) In base tables, create a standard primary key on a number probably from a sequence, set during Pre-Insert. The FID must be sized appropriately. c) Pre-Insert also converts this to a FID, which is a UNIQUE but not a primary key, and can therefore be used in foreign key constraints, joins, etc. d) It is the FID that is migrated to intersect class tables.
You'd search main base tables by name, id, whatever, but the
JOINs were on FIDs, and these were blindingly fast as long as you
sized them approximately right. Max width of a FID is 255,
giving you up to 2**(255*8)-1 available ids. Mind you, if you
only expected up to 20K rows in the base table, you can get away
with a 2 byte FID with lots of room. Mind you, widening
FIDs is ugly. Doable but ugly. There is obviously a
break-point where FIDs become useful. In a small database, they
are a pain, but it was great for large databases.
One lesson is, however, put fixed-width stuff at the front of
your tables/indices.
Your mileage may vary. Get a real production DBA to answer
the "pure" version of this question. This was
true with V7 but something internal may have changed since.
Further Reading: N/A
