| 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?
Is this my punishment for having dumped the data block?
As I said. Oracle stores it in a strange format. BTW, the c1 has to do with preventing there being FF in first?/last? byte. FF was interpreted, in days of serial communication gone bye, as end of transmission or some such.
> 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
> 1.11111 x 10**5 = 111111
>
> 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.
>
> ----- Original Message -----
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Sent: Saturday, October 21, 2000 1:20 PM
>
> > 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
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Messenger - Talk while you surf! It's FREE.
> http://im.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: yong huang
> INET: yong321_at_yahoo.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 Sun Oct 22 2000 - 18:40:14 CDT
 |
 |