Analysis of Oracle number

Author: skate

Time: 2011-02-14

 

Analysis of Oracle number

 

Understanding number from the following aspects

1. Value Range
2. Occupied storage space
3. Number Performance

 

We usually define that numbers are used to store columns. However, Oracle is also compatible with other data types, as shown below:

 

Numeric (P, S): fully mapped to number (P, S ). If P is not specified, the default value is 38.
Decimal (P, S) or Dec (P, S): fully mapped to number (P, S ). If P is specified, the default value is 38.
Integer or INT: fully mapped to the number (38) type.
Smallint: fully mapped to the number (38) type.
Float (B): ing to number type.
Double Precision: ing to number type.
Real: ing to number type.

 

These types are only supported by Oracle syntax, and are actually number at the underlying level.

 

1. Value Range

Number: The Oracle number type can store values with great precision. Specifically, the precision can reach 38 BITs. Its underlying data format is similar
"Package decimal number" indicates. Oracle number is a variable-length format with a length of 0 ~ 22 bytes. It can store as small as 10e-130,
Any value that is greater than (but not included) 10e126. This is currently the most common numeric type. It is also Oracle9i Release 2 and
The previous version only supports the only data type that is applicable to the storage of numeric data.
The ing between them is actually number at the bottom layer.

Binary_float: This is an IEEE inherent Single-precision floating point number. It occupies 5 bytes of storage space on the disk: four fixed-character nodes are used.
It is used to store floating point numbers, and there is also a Length byte. Binary_float can store 6 values with precision and range ~ + 1038. 53
Value

Binary_double: This is an IEEE inherent double-precision floating point number. It occupies 9 bytes of storage space on the disk: eight fixed-character nodes are used.
In the storage of floating point numbers, there is also a Length byte. Binary_double can store 12 bits of precision and range in ~ Of ± 10308. 25
Value.

 

Example:

 

Create test table T2
SQL> Create Table T2
2 (num_type number,
3 float_type binary_float,
4 double_type binary_double
5 );

Table created

 

 

Insert test data 1
SQL>
SQL> insert into T2
2 (num_type, float_type, double_type)
3 values
4 (1234567890.0987654321, 1234567890.0987654321, 1234567890.0987654321 );

1 row inserted

 

 

View test data 1
SQL>
SQL> select to_char (num_type ),
2 to_char (float_type, '2017. 123 '),
3 to_char (double_type, '192. 123 ')
4 From T2
5;

To_char (num_type) to_char (float_type, '2017 to_char (double_type, '2017
--------------------------- Begin --------------------------------------------------------------------------------------------------------------
1234567890.0987654321 1234567940.000000000 1234567890.0987654000

 

 

 

Insert Test Data 2
SQL>
SQL> insert into T2
2 (num_type, float_type, double_type)
3 values
4 (12345678900987654321,123 45678900987654321, 12345678900987654321 );

1 row inserted

 

 

View Test Data 2
SQL>
SQL> select to_char (num_type ),
2 to_char (float_type, '123 '),
3 to_char (double_type, '123 ')
4 From T2
5;

To_char (num_type) to_char (float_type, '2017 to_char (double_type, '2017
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1234567890.0987654321 1234567940 1234567890
12345678900987654321 12345679400000000000 12345678900987654000

SQL>

 

 

From the test results, we can see that the number can correctly display the data with a high precision. binary_float only correctly displays the first seven digits. The data range and precision displayed by binary_double are much higher than those displayed by binary_float.

 

 

2. Occupied storage space

 

The number type occupies 0-22 bytes. It is actually a variable-length data type on the disk. Oracle uses as little storage space as possible to represent a number based on certain algorithms.

 

SQL> Create Table T (X number, y number );

Table created

 

SQL>
SQL> insert into T (X)
2 select to_number (rpad ('9', rownum * 2, '9'), '123 ')
3 from all_objects
4 where rownum <= 25;

25 rows inserted

 

SQL> Update t set Y = x + 1;

25 rows updated

 

SQL> column number 1 format 9999999999999999999999999999999999999999999999999999999999999999999999999

SQL> column number 2 format 9999999999999999999999999999999999999999999999999999999999999999999999999

SQL> select to_char (x) Number 1, to_char (y) Number 2, vsize (x) Number 1 occupies the number of nodes, vsize (y) Number 2 occupies the number of nodes from t order by X;

Digit 1 digit 2 digit 1 digit number Number 2 digit number
--------------------------------------------------------------------------------------------------------------------------------------------------
99, 100, 2, 2
9999 10000 3 2
999999 1000000 4 2
99999999, 100000000, 5, 2
9999999999 10000000000 6 2
999999999999 1000000000000 2
99999999999999 100000000000000 8 2
9999999999999999 10000000000000000 9 2
999999999999999999 1000000000000000000 10 2
99999999999999999999 100000000000000000000 11 2
9999999999999999999999 10000000000000000000000 12 2
999999999999999999999999 1000000000000000000000000 13 2
99999999999999999999999999 100000000000000000000000000 14 2
9999999999999999999999999999 10000000000000000000000000000 15 2
999999999999999999999999999999 1000000000000000000000000000000 16 2
99999999999999999999999999999999 100000000000000000000000000000000 17 2
9999999999999999999999999999999999 10000000000000000000000000000000000 18 2
999999999999999999999999999999999999 1000000000000000000000000000000000000 19 2
99999999999999999999999999999999999999 100000000000000000000000000000000000000 20 2
9999999999999999999999999999999999999999 1.20.000000000000000000000000000000e + 40 21 2

Digit 1 digit 2 digit 1 digit number Number 2 digit number
Certificate -----------------------------------------------------------------------------------------------------------------------------
1.20.000000000000000000000000000000e + 42 1.20.0000000000000000000000000000000000e + 42 2
1.20.000000000000000000000000000000e + 44 1.20.0000000000000000000000000000000000e + 44 2
1.20.000000000000000000000000000000e + 46 1.20.0000000000000000000000000000000000e + 46 2 2
1.20.000000000000000000000000000000e + 48 1.20.0000000000000000000000000000000000e + 48 2
1.20.000000000000000000000000000000e + 50 1.20.0000000000000000000000000000000000e + 50 2

25 rows selected

SQL>

 

 

From the example, we can see that when Oracle stores valid data (non-zero data), every two digits are added, the storage space of the data is increased by one byte until the data overflows.
When Oracle stores a number, it stores as little content as possible to represent this number. Therefore, a valid number and an index used to specify the decimal point are stored, and
Information about numeric symbols (positive or negative ). Therefore, the more valid numbers the number contains, the larger the storage space occupied.

 

Binary_float and binary_double

Floating point numbers are used to approximate numeric values. They are not as accurate as the number type built in Oracle. Floating Point Numbers are often used in scientific computing, because they are allowed in hardware (CPU, Chip)
It is useful in a variety of different types of applications, rather than in Oracle subroutines. Therefore, if you execute
Real number processing, the speed of arithmetic operations will be much faster.

 

Binary_float occupies 5 bytes of storage space on the disk: four fixed bytes are used to store floating point numbers, and one Length byte is also used.
Binary_double occupies 9 bytes of storage space on the disk: eight fixed bytes are used to store floating point numbers, and one Length byte is also used.

 

 

 

3. Number Performance
The Oracle number type is the best choice for most applications, especially in the financial industry. However, it has advantages and disadvantages, which may affect the performance.
Because the Oracle number type is a software data type, it is implemented in the Oracle software itself. We cannot use the inherent hardware operation to split two numbers
Type addition, which needs to be simulated in the software, so the performance has a great impact. Therefore, Oracle provides two floating point types: binary_float and binary_double.

 

 

The following is an example of performance comparison.

 

Create test table
SQL> Create Table T2
2 (num_type number,
3 float_type binary_float,
4 double_type binary_double
5 );

Table created

 

SQL>
SQL> insert/* + append */into T2
2 select rownum, rownum, rownum
3 from all_objects
4;

57302 rows inserted

 

SQL> alter session set events '10046 trace name context forever, level 1 ';

Session altered

 

SQL> select sum (Ln (num_type) from T2;

Sum (Ln (num_type ))
-----------------
570510.312356972

 

SQL> select sum (Ln (float_type) from T2;

Sum (Ln (float_type ))
-------------------
570510.31235697

 

SQL> select sum (Ln (double_type) from T2;

Sum (Ln (double_type ))
--------------------
570510.31235697

 

SQL> select sum (Ln (cast (num_type as binary_double) from T2;

Sum (Ln (cast (num_typeasbinary_d
------------------------------
570510.31235697

 

SQL> alter session set events '10046 trace name context off ';

Session altered

SQL>

 

View the following trace file:

 

........

**************************************** ****************************************

Select sum (Ln (num_type ))
From
T2

Call count CPU elapsed disk query current rows
-----------------------------------------------------------------------
Parse 1 0.00 0.00 0 3 1 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 2.31 2.25 38 193 0 1
-----------------------------------------------------------------------
Total 3 2.31 2.25 38 196 1 1

**************************************** ****************************************

Select sum (Ln (float_type ))
From
T2

Call count CPU elapsed disk query current rows
-----------------------------------------------------------------------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.04 0.04 0 193 0 1
-----------------------------------------------------------------------
Total 3 0.04 0.04 0 194 0 1

**************************************** ****************************************

Select sum (Ln (double_type ))
From
T2

Call count CPU elapsed disk query current rows
-----------------------------------------------------------------------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.03 0.04 0 193 0 1
-----------------------------------------------------------------------
Total 3 0.04 0.04 0 194 0 1

**************************************** ****************************************

Select sum (Ln (cast (num_type as binary_double )))
From
T2

Call count CPU elapsed disk query current rows
-----------------------------------------------------------------------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.10 0.09 0 193 0 1
-----------------------------------------------------------------------
Total 3 0.10 0.10 0 194 0 1

 

From the test results, the number performance is indeed very slow, more than 57 times slower than the floating point type binary_float and binary_double, but the cast function can be used for conversion,
Before performing complex mathematical operations on a number, convert it to a floating-point number type, which will increase the computing speed, but it is much slower than using the floating-point type directly,
But it is also a compromise.

 

 

 

 

 

 

 

-------- End -------