Re: optimum datatype for primary key column O9i

sminni_SPAM_DIVERTER_at_planage.com (Sanjay Minni) wrote in message news:<4fe109d.0310282228.7358f8c4_at_posting.google.com>...
> > make PK columns NUMBER whenever possible -- with our without a maximum
> > precision
> >
> > always declare the FK column(s) with the exact same datatype and precision
> > as the referenced PK column(s)
> >
> > assign the PK value with an Oracle SEQUENCE object
 

> > Mark C. Stock
> > www.enquery.com
>
> Thanks,
> Now as I need only
> - integer values and of
> - 12 digit positions all filled (i.e. maximum 1 or 2 leading zeroes)
>
> in an attempt to optimise
> I will declare as NUMBER(12,0)
>
> Q. Is the storage pattern same if the number is shorter say NUMBER(10,2)
> except off course the saving of say one byte per value
> specially in indexes (I can even go shorter if required)
>
> and ...
>
> Q is there any overheads in joins when using NUMBER datatype
> The highest use of the primary key will be in Joins
> and the implicit internal index accesses and comparisions
> in joins.
>
> There will hardly be any other use of the primary key values
>
> Regards
> Sanjay Minni

You DON'T NEED leading zeroes, and you can't store them in a number datatype (which is good, as leading zeroes is a *display* property). Number(10,2) means you have 10 positions, of which 2 are used as fraction.
There is NO overhead in using a NUMBER datatype in joins!! There is overhead in using VARCHAR2s (which you already demonstrate as you seem to feel compelled mistakenly to pad them)

Could you please brush up your manual reading skills? It's all there.

Sybrand Bakker
Senior Oracle DBA Received on Wed Oct 29 2003 - 11:23:39 CET