| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: NUMERIC data type always takes up 22 bytes. Alternatives?
Hi, Tom,
The rule to interpret positive integers in a datafile dump is as follows (credit to Jonathan Lewis). The first byte is about exponent. You subtract c1 from it (why c1? I don't know). The remainder times 2 will be the exponent if this byte is less than 0a; else if greater than or equal to 0a, times 2 plus 1 (This info is not from Jonathan Lewis). The rest of the bytes are characters representing numbers in BCD (binary coded decimal) format, but they also need to be subtracted by 1. You then convert them to decimal and concatinate. Lastly, apply the exponent.
Example (from your dump file):
c3 0c 0c 0c
2 12 12 12 <-- c3 minus c1, 0c converted to decimal 12
5 11 11 11 <-- 2 doubled to 4 and plus 1 (because the second byte is 0c >=
0a),
three 12's minus 1
Suppose you had inserted 11111 instead of 111111. The dump file would show:
c3 02 0c 0c
2 2 12 12
4 1 11 11
1.1111 x 10**4 = 11111
Negative integers are stored quite differently. I can post a separate message about it if there's interest.
I haven't completely figured out stored format of real numbers.
By the way, everybody says the number is stored as exponent and mantissa. But the word "mantissa" is the decimal part of a logarithm. E.g., log(123.4) is 2.0913. The mantissa of 2.0913 is 0913. It makes no sense talking about the mantissa of the original number 123.4 (as people would say 1234 is its mantissa), unless you think of 123.4 as the result of a log function on another number.
Yong Huang
yong321_at_yahoo.com
you wrote:
From: "Tom Pall" <tom_at_cdproc.com>
Date: Sat, 21 Oct 2000 19:26:38 -0500
Subject: Re: NUMERIC data type always takes up 22 bytes. Alternatives?
The dump is hex. Numbers are stored in Oracle in a rather strange format. The 11111 got stored, with exponent and mantissa, in 4 bytes.
> Hi, Tom,
>
> Your dump file says you actually executed
>
> insert into test_numbers values (111,111111,111111111111);
>
> but you said the middle number is 11111, five digits.
>
> Back to the original question. It's not true when you create a NUMBER
datatype,
> it always takes 22 (or 38 whatever) bytes. Dumping the datafile for this
> purpose is an overkill. Take Tom's example, you can simply select vsize(a),
> vsize(b), vsize(c) from test_numbers and see they take 3, 4, 7 bytes,
> respetively.
>
> But Steve Adams once said it's a good habit to explicitly specify the
precision
> (and scale) instead of simply say NUMBER. I can't remember the exact reason.
>
> Yong Huang
> yong321_at_yahoo.com
>
> Tom Pall wrote:
>
> 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
 |
 |