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 ?

Back to index of questions


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


Back to top

Back to index of questions