Oracle Data Types and storage methods (2)

Part 2 value type
§ 2.1 number
The Number type is oralce numeric, and the precision of the stored numeric value can reach 38 BITs. Number is a variable-length type with a length of 0 to 22 bytes. Value Range: 10e-130-10e 126 (not included)
Number (p, s)
P and s are optional.
P indicates the precision, that is, the total number of digits. By default, the precision is 38. The value range of precision is 1 ~ 38.
S refers to the number of digits to the right of the decimal point. The valid decimal point value is-48 ~ 127. The default value of decimal places is determined by the precision. If no precision is specified, the default decimal place is the maximum value range. If precision is specified, no decimal place is specified. The default decimal place is 0 (that is, there is no decimal place ).
Precision and decimal places do not affect how data is stored, but only the allowed values and how to round.
1. Create a new table
SQL> create table test_number (col_number number (6, 2 ));
Table created
2. Insert some different data
SQL> insert into test_number values (-1 );
1 row inserted
SQL> insert into test_number values (0 );
1 row inserted
SQL> insert into test_number values (1 );
1 row inserted
SQL> insert into test_number values (2 );
1 row inserted
SQL> insert into test_number valuees (11.00 );
1 row inserted
SQL> insert into test_number valuees (11.11 );
1 row inserted
SQL> insert into test_number valuees (1234.12 );
1 row inserted
SQL> insert into test_number values (-0.1 );
1 row inserted
SQL> insert into test_number values (-11.11 );
1 row inserted
SQL> insert into test_number values (-1234.12 );
1 row inserted
SQL> commit;
Commit complete
3. view results
SQL> select * from test_number;
COL_NUMBER
----------
-1.00
0.00
1.00
2.00
11.00
11.11
1234.12
-0.10
-11.11
-1234.12
10 rows selected
5. view the Storage Structure
SQL> select col_number, dump (col_number) from test_number;
COL_NUMBER DUMP (COL_NUMBER)
------------------------------------------------------------------------------------------
-1.00 Typ = 2 Len = 3: 62,100,102
0.00 Typ = 2 Len = 1: 128
1.00 Typ = 2 Len = 2: 193,2
2.00 Typ = 2 Len = 2: 193,3
11.00 Typ = 2 Len = 2: 193,12
11.11 Typ = 2 Len = 3: 193,12, 12
1234.12 Typ = 2 Len = 4: 194,13, 35,13
-0.10 Typ = 2 Len = 3: 102
-11.11 Typ = 2 Len = 4: 90,102
-1234.12 Typ = 2 Len = 5: 102
10 rows selected
It can be seen that:
Number Type Internal encoding: 2
According to the len value of each row, number is a variable-length type. Different values occupy different spaces.
If the precision is specified, the displayed result is related to the precision.
Just as if the insert statement is
Insert into test_number values (0 );
But the result is 0.00.
If the value is a negative number, fill in a complement code 102 on the last digit, that is, the value is a negative number.
0 is a special value, which is stored as 128 in oracle.
The first digit is the flag. Compare with 128. If the value is greater than 128, it is greater than 0. If less than 128 is less than 0.
-1 Internal Storage:
-1.00 Typ = 2 Len = 3: 62,100,102
The last digit is 102, which is a negative number.
The first digit is less than 128, so it is less than 10.
Except for the first flag, all other values are numerical values.
If the value is a positive number. The storage value of each bit is reduced by 1 to the actual value of each bit.
1.0 of the storage structure is:
1.00 typ = 2 Len = 2: 193,2
The storage result of 1.00 in real value is the same as that of 1.
The first 193 is the flag, greater than 128, greater than 0.
The second digit is the value. Because it is a positive number, the actual value is the storage value minus 1. 2-1 = 1.
If the value is a negative number, the actual value of each bit is 101 minus the stored value.
-The storage structure of 1.00 is:
-1.00 Typ = 2 Len = 3: 62,100,102
The last 102 is the complement.
The first 62 is the flag, less than 128. The actual value is less than 0.
The second digit is the value because it is a negative number. Actual value: 101-100 = 1.
§ 2. Where are the decimal places?
As shown in the above storage results, when storing decimal places, it does not have a decimal flag. However, it is actually determined by the first flag bit and the value bit (the second digit.
When the stored number is a positive number, the first few digits of the value are: the first digit * power (100, (flag-193 ));
When the stored number is a negative number, the first digit of the value is: the first digit * power (100, (62-flag ));
11.11 of the storage results are:
11.11 Typ = 2 Len = 3: 193,12, 12
The first value is 12. The actual value is 11.
Flag: 193
12 * power (100, (193-193 );
The fraction of 100 is 1.
12 by 1 equals 12.
So the first digit of this number is 12. This is followed by decimal places.
1234.12 of the storage structure is:
1234.12 Typ = 2 Len = 4: 194,13, 35,13
The first value is 13, and the actual value is 12.
Flag: 193
13 * power (100, (194-193) = 1300
Therefore, the first four digits are integers, followed by decimal places.
-The storage structure of 0.10 is:
-0.10 Typ = 2 Len = 3: 102
Flag: 63
The first value is 91, and the actual value is 10.
91 * (100, (62-63) =-9100.
Therefore, the decimal point is before 91.
-The storage structure of 1234.12 is:
-1234.12 Typ = 2 Len = 5: 102
Flag: 61
The first value is 89.
89 * (100, (62-61) = 8900
Therefore, the decimal point is after 67.
§ 2. 3 number precision and decimal places
The Number type has a maximum precision of 38 BITs. Decimal place: 84--127.
SQL> create table test_number1 (col_number number (39 ));
Create table test_number1 (col_number number (39 ))
ORA-01727: numeric precision specifier is out of range (1 to 38)
When a decimal point is specified, the precision can only be 1-38. Cannot be 0
SQL> create table test_number1 (col_number number (0,127 ));
Create table test_number1 (col_number number (0,127 ))
ORA-01727: numeric precision specifier is out of range (1 to 38)
SQL> create table test_number1 (col_number number (1,128 ));
Create table test_number1 (col_number number (1,128 ))
ORA-01728: numeric scale specifier is out of range (-84 to 127)
The relationship between precision and decimal places. Precision is not the sum of decimal places and integer digits.
Let's first look at the case where the decimal point is 0.
SQL> create table test_number1 (col_char varchar2 (200), col_num number (10 ));
Table created
Number (10). Only the precision is defined, and the decimal point is 0.
Check the data that can be stored.
SQL> insert into test_number1 values ('20140901', 9999999999 );
1 row inserted
Insert 10 to 9. No problem. insert one more bit to check.
SQL> insert into test_number1 values ('20140901', 99999999991 );
Insert into test_number1 values ('20140901', 99999999991)
ORA-01438: value larger than specified precision allowed for this column
An error is reported. The precision is insufficient.
Let's see if we can insert decimal places?
SQL> insert into test_number1 values ('0. 9', 0.9 );
1 row inserted
SQL> select * from test_number1;
Col_char COL_NUM
----------------------------------
9999999999 9999999999
0.9 1
Note that after inserting a value of 0.9, the storage is 1. This is the role of decimal places. Where to perform rounding.
With decimal places and precision.
SQL> create table test_number2 (col_char varchar (20), col_num number (1, 3 ));
Table created
The precision is 1, and the decimal point is 3.
It can be seen that the precision is not rounded up by decimal places. But what is the relationship between precision and decimal places?
SQL> insert into test_number2 values ('0. 100', 111 );
Insert into test_number2 values ('0. 100', 111)
ORA-01438: value larger than specified precision allowed for this column
Insert 3 decimal places. If the number is 0.111, an error is returned, indicating that the precision is not enough.
SQL> insert into test_number2 values ('0. 001', 0.001 );
1 row inserted
Insert 0.001 successfully.
SQL> insert into test_number2 values ('0. 001', 0.0015 );
1 row inserted
Insert 0.0015 is successful.
Look at the inserted value.
SQL> select * from test_number2;
COL_CHAR COL_NUM
---------------------------
0.001 0.001
0.0015 0.002
Note that 0.0015 is rounded to 0.002.
Precision greater than decimal places
SQL> create table test_number3 (col_char varchar (20), col_number number (5, 3 ));
Table created
SQL> insert into test_number3 values ('99. 100', 899 );
1 row inserted
SQL> insert into test_number3 values ('99. 100', 999 );
1 row inserted
SQL> insert into test_number3 values ('99. 100', 9999 );
Insert into test_number3 values ('99. 9999 ', 99.9999)
ORA-01438: value larger than specified precision allowed for this column
Note: When 99.9999 is inserted, the system reports an error. Because the decimal point is three digits. The fourth decimal place is 9, so it goes forward. The final result is 100. 000, which exceeds the precision.
The maximum Number (5, 3) that can be stored is 99.999.
Now, we can understand the relationship between decimal places and precision.
Number (38,127)
The maximum number of decimal places that can be stored is: 127 decimal places, and the last 38 is 9.
That is, 0.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000099999999999999999999999999999999999999
Decimal places are negative.
We know from the beginning that the decimal point value is-48 ~ 127
Why is the decimal point negative? This is a bit weird. Like the number (0.001) above, round the value to the nearest
Number (5,-3) is to round the value to the nearest 1000
SQL> create table test_number5 (col_char varchar (20), col_num number (5,-3 ));
Table created
Insert 10999
SQL> insert into test_number5 values ('20140901', 10999 );
1 row inserted
View the result
SQL> select * from test_number5;
COL_CHAR COL_NUM
---------------------------
10999 11000
Storage result: 11000
When the decimal part is negative, it is to round the decimal part.
So what is the role of precision in this case? What is the relationship with decimal places?
SQL> insert into test_number5 values ('20140901', 111111111 );
Insert into test_number5 values ('20140901', 111111111)
ORA-01438: value larger than specified precision allowed for this column
When you insert 9 to 1, the error is not accurate enough.
SQL> insert into test_number5 values ('20140901', 11111111 );
1 row inserted
When eight values are inserted, they are inserted correctly.
Let's take a look at its results and see how it is rounded up.
SQL> select * from test_number5;
COL_CHAR COL_NUM
---------------------------
11111111 11111000
The result is 1111100 instead of 1111100.
Infinitely close to 1000, that is, the number of digits starting from is rounded to 0.
So we can see that the maximum value of number (5,-3) is 99999000.
SQL> insert into test_number5 values ('192. 100', 99999499 );
1 row inserted
SQL> select * from test_number5;
COL_CHAR COL_NUM
---------------------------
99999999 99999000
99999499.999999 99999000
Now we should understand the relationship between precision and decimal places.
Decimal places tell the system how many decimal places are retained and where to start rounding.
After precision rounding, the number of BITs allowed in the value starts from the rounding position.
§ 2. 4 binary_float and binary_double
These two types are new numeric types introduced by oracle 10 Gb. These two types are not available before oracle 10 Gb.
The Number type is supported by oracle software. Floating point numbers are used to approximate values. However, its floating point number allows running on the hard disk (CPU, Chip. Instead of operating in the volume El process. If you want to execute real number processing in a scientific computing, hardware-dependent arithmetic operations are much faster. However, its accuracy is very small. If you want to store financial values, you must use number.
BINARY_FLOAT is an IEEE inherent Single-precision floating point number. It can store 6-bit precision and the value range is ~ The value of ± 1038. 25.
BINARY_DOUBLE is an IEEE inherent double-precision floating point number. It can store 12-bit precision. The value range is ~ Value of ± 10308. 25
SQL> create table test_floatdouble (col_number number, col_float binary_float, col_double binary_double );
Table created
SQL> insert into test_floatdouble values (9876543210.0123456789, 9876543210.0123456789, 9876543210.0123456789 );
1 row inserted
2 SQL> select to_char (col_number), to_char (col_float), to_char (col_double) from test_floatdouble;
3
4 TO_CHAR (COL_NUMBER) TO_CHAR (COL_FLOAT) TO_CHAR (COL_DOUBLE)
5 ------------------------------------------------------------------------------------------------------------------------
6 9876543210.0123456789 9.87654349E + 009 9.8765432100123163e + 009
It can be seen that binary_float cannot represent this number. Binary_float and binary_double cannot be used for data with high precision requirements.
SQL> select dump (col_float) from test_floatdouble;
DUMP (COL_FLOAT)
--------------------------------------------------------------------------------
Typ = 100 Len = 4: 208,19, 44,6
BINARY_FLOAT type encoding is 100
Len = 4 occupies 4 bytes. It uses fixed bytes for storage.
SQL> select dump (col_double) from test_floatdouble;
DUMP (COL_DOUBLE)
--------------------------------------------------------------------------------
Typ = 101 Len = 8: 101,128,183, 73
BINARY_DOUBLE type encoding: 101
Leng = 8 occupies 8 bytes. It also uses fixed bytes for storage.
Note: The CPU time used by the number type is 50 times that of the floating point type. A floating point number is an approximate value of a value with an accuracy between 6 and 12 digits. The result obtained from the Number type is more accurate than the result obtained from the floating point Number. However, when we perform data mining and complex numerical analysis on scientific data, the loss of precision is acceptable and will also bring significant performance improvements.
In this case, you need to use the built-in CAST function to perform a real-time conversion of the NUMBER type. Before performing complex mathematical operations, convert it to a floating point type. The CPU usage time is very close to the CPU usage time of the inherent floating point type.
Select ln (cast (number_col as binary_double) from test_number.
§ 2. 5 numeric data types supported by Oracle syntax
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): Same as NUMERIC (p, s ).
INTEGER or int: fully mapped to NUMBER (38)
SMALLINT: fully mapped to NUMBER (38)
FLOAT (B): ing to NUMBER
Double precision: ing to NUMBER
REAL: map to NUMBER.