Re: Physical Storage Of NUMBER Data Type

Alan N Turner <anturner_at_sc1500.dseg.ti.com> wrote:
>How does Oracle physically store columns of data type NUMBER?
>I know that the maximum physical size of this data type is 22 bytes.
>I know that the maximum number of significant digits for this
>data type is 38.
>
>I assume that a valid calculation to get from 38 to 22 is:
>
> (# significant digits/2) + 3 overhead bytes
>
>However, the USER_IND_COLUMNS and USER_TAB_COLUMNS show the column
>length for a NUMBER column always as 22.
>
>Is the previous calculation correct for determining the physical
>storage requirements for a NUMBER data type? If not, what is?
>If so, what is the reason for the two user views displaying the
>length as always being 22?

I noticed this problem when porting to V7 (although it existed in V6 as well). The answer we got from Oracle when we asked about it was the following (after waiting several weeks):

        Problem 302774

	Number datatype is stored as a variable length with
	a maximum length of 21 bytes.  Unfortunately, our
	data dictionary view always reports the length as 22.

	Refer to Oracle 7 Server Administrator's Guide,
	Chapter 8 for more details.

I'm fairly sure this issue is not mentioned at all in the V6 documentation.

I hope this is of some help.

What I would like to know is how a problem like this could be allowed to persist in V7 when it existed in V6 if not earlier.

David Feldcamp

Parallel Computation Laboratory
Dept. of Computer Science
University of British Columbia Received on Sat Mar 19 1994 - 02:19:22 CET