Re: optimum datatype for primary key column O9i
Date: Tue, 28 Oct 2003 13:24:20 -0500
Message-ID: <6bSdndc-0p_rKQOiRVn-tA_at_comcast.com>
to illustrate, VARCHAR2 is variable length, so no padding is used in storage (although a length byte or 2 is required)
however, most number in a VARCHAR2 requires more storage than the same value in a NUMBER column, since the NUMBER column stores the significant digits and precision, vs each of the literal digits:
SQL> create table tbl1 (
2 as_varchar2 varchar2(12)
3 , as_number number(12)
4 );
Table created.
SQL> insert into tbl1 values( '20000000', 20000000 ); SQL> insert into tbl1 values ('12345678', 12345678 );
SQL> select as_number, vsize(as_varchar2), vsize(as_number) from tbl1; ...
AS_NUMBER VSIZE(AS_VARCHAR2) VSIZE(AS_NUMBER)
---------- ------------------ ---------------- 20000000 8 2 12345678 8 5
so, real simple:
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 (888) 512-2048 "Sanjay Minni" <sminni_SPAM_DIVERTER_at_planage.com> wrote in message news:4fe109d.0310280846.5833135d_at_posting.google.com...Received on Tue Oct 28 2003 - 19:24:20 CET
> 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