Re: optimum datatype for primary key column O9i

sybrandb_at_yahoo.com wrote in message news:<a1d154f4.0310280118.1f47d7a2_at_posting.google.com>...
> sminni_SPAM_DIVERTER_at_planage.com (Sanjay Minni) wrote in message news:<4fe109d.0310271823.58b3b2c8_at_posting.google.com>...
> > [snip...]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].
>
> 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.
>

good, so once again, assuming I need around 12 digit long values in primary keys what is the best datatype to use to optimise Primary keys, indexes, foriegn keys, joins and matches i.e. "=" , ">"...

will I gain anything if I reduce length to (say minimum 10 digit positions) or instead use fixed length character datatypes on 12 positions

Actually we are generating key values and padding with a leading (number) value to make up 12 positions We can go minimum 10 positions if it really leads to significant savings in any manner

Sanjay

Sanjay Minni Received on Tue Oct 28 2003 - 17:46:28 CET