Friday, 13 June 2008

Precision and scale do not specify column size

Precision and scale do not determine the physical storage allocation for a number, only the maximum size of the value that can be stored in it. A NUMBER column will occupy as much or as little space as it needs to, up to a maximum of 22 bytes. The physical size will grow and shrink depending on the size of the number to be held. In this sense, it behaves in a similar way to a VARCHAR2 datatype which also dynamically allocates enough bytes to hold the value up to the maximum specified size.

To demonstrate this we will use the VSIZE function which returns the number of bytes in the internal representation of a column.

CREATE TABLE t (
num_col NUMBER(16)
);
CREATE TABLE succeeded.

INSERT INTO t (num_col) 
VALUES (1234567890);
1 rows inserted

SELECT num_col, VSIZE(num_col) 
FROM t;
NUM_COL                VSIZE(NUM_COL)         
---------------------- ----------------------
1234567890 6
1 rows selected
If we change the value we see that the internal size, the number of bytes required to hold our number, has changed.
UPDATE t 
SET num_col = 1;
1 rows updated

SELECT num_col, VSIZE(num_col) 
FROM t;
NUM_COL                VSIZE(NUM_COL)         
---------------------- ----------------------
1 2
1 rows selected
So when we specify a precision and scale for a number we are not telling Oracle to reserve a fixed number of bytes for every number in the column. We are only providing the maximum size and the number of decimal places. Oracle will store the number in the minimum number of bytes necessary to hold the value.

2 comments:

Anita Heigl said...
This comment has been removed by a blog administrator.
Joselin Smitt said...
This comment has been removed by a blog administrator.