Re: NUMERIC data type always takes up 22 bytes. Alternatives?

From: Tom Pall <tom_at_cdproc.com>
Date: Fri, 20 Oct 2000 16:22:32 -0500
Message-Id: <10655.119890@fatcity.com>

I beg to differ.

I create a table: create table test_numbers (a number, b number, c number);

I insert into the table

insert into test_numbers values (111,11111,111111111111);

1 row inserted

I commit

Then I dump the first data block after the header, see that I am storing

data_block_dump

---

tsiz: 0x1fb8
hsiz: 0x14
pbl: 0x0107b164
bdba: 0x02401765

flag=---------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1fa4
avsp=0x1f90
tosp=0x1f90
0xe:pti[0]      nrow=1  offs=0
0x12:pri[0]     offs=0x1fa4

block_row_dump:
tab 0, row 0, @0x1fa4
tl: 20 fb: --H-FL-- lb: 0x1 cc: 3

col  0: [ 3]  c2 02 0c
col  1: [ 4]  c3 0c 0c 0c
col  2: [ 7]  c6 0c 0c 0c 0c 0c 0c

end_of_block_dump
End dump data blocks tsn: 6 file#: 9 minblk 5989 maxblk 5989 ----- Original Message -----
From: <Val_Gamerman/Victoria_Financial.VICTORIA_FINANCIAL_at_lnn.com> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Friday, October 20, 2000 3:40 PM
Subject: NUMERIC data type always takes up 22 bytes. Alternatives?

>
>
>
> Gurus,
>
> In all the years that I have dealt with Oracle (both as a developer and a
> DBA -- altogether 3 years) I never stopped to pay much attention to the
> fact that when you define a field in a table as NUMERIC, no matter what
> precision and scale you define for it, you are always going to get 22 bytes
> allocated for this field in the table. While I see the benefit of being
> able to store really large numbers in the database, most of the time it's
> a waste.
>
> Let's say I wanted to store a record for each person living on the planet
> -- ~ 7bln records.
>
> The record would only have one field -- a unique ID of this person,
> starting with 1,2,3, ...
>
> Thus the max number I would want to store would be 7,000,000,000, plus
> planning for potential population growth - ~35 bln in 10 years.
>
> If I allocate 22 bytes for each of these numbers I would end up needing
> approx. 140Gb right away.
>
> If there was a way for me to only allocate 6 bytes (plenty of space for
> billions), I would only need 40Gb.
>
> My question is: is there a way to do it in Oracle?
>
> I just went through documentation for 7.3.4 and this is the only data type
> available (there others but they are just synonyms of NUMERIC).
>
> If somebody can shed the light on this I would be very appreciative.
>
> Thanks,
> Val Gamerman.
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: Val_Gamerman/Victoria_Financial.VICTORIA_FINANCIAL_at_lnn.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Fri Oct 20 2000 - 16:22:32 CDT