Re: optimum datatype for primary key column O9i

sminni_SPAM_DIVERTER_at_planage.com (Sanjay Minni) wrote in message news:<4fe109d.0310271823.58b3b2c8_at_posting.google.com>...
> What I meant was that specifically to Oracle 8i/9i,
> internal data storage methods and data & index structures
> what will be the optimum datatype to be used
> [for 12 digit key lengths] to make best use
> of space [data & index] and speed of access [joins etc].
>
> The effect will cascade as most of the primary key columns
> will be duplicated as foriegn keys in other tables
> and invariably large joins will be used
>
> Sanjay Minni
> www.planage.com

If your key is numeric, one of the most stupid moves you can make is storing them in varchar2s. Numeric columns are packed, varchar2 columns aren't. Also you are likely to end up with implicit conversion issues if someone mistakenly writes queries like this one key_column = 1234.
This will be automatically converted by Oracle to to_number(key_column) = 1234
and consequently the primary key index will not be used.

Regards
Sybrand Bakker
Senior Oracle DBA Received on Tue Oct 28 2003 - 10:18:19 CET