This is what I mean:
If a value of '5' being insert into the table, does it consume the same amount of space compared to value '9876543' ? Or does a '5' occupy less space than '9876543'?
Let's find out. First, we create a single column table with type BIGINT. Then we insert roughly 150000 rows of value '1' into it.
This is the space used:
TAB NPAGES
--------------- --------------------
BIGINT 771
Let's repeat this test using value '99999999' instead. This is the space used:
TAB NPAGES
--------------- --------------------
BIGINT 771
That suggests there is no difference.
That also means, if you know the range of the values ( number type, that is) for the column, you should pick the smallest possible datatype that would fit the range.
Below is a table showing the range for each numerical type:
| Limit | |
|---|---|
| Smallest SMALLINT value | -32 768 |
| Largest SMALLINT value | +32 767 |
| Smallest INTEGER value | -2 147 483 648 |
| Largest INTEGER value | +2 147 483 647 |
| Smallest BIGINT value | -9 223 372 036 854 775 808 |
| Largest BIGINT value | +9 223 372 036 854 775 807 |
| Largest decimal precision | 31 |
| Maximum exponent (Emax) for REAL values | 38 |
| Smallest REAL value | -3.402E+38 |
| Largest REAL value | +3.402E+38 |
| Minimum exponent (Emin) for REAL values | -37 |
| Smallest positive REAL value | +1.175E-37 |
| Largest negative REAL value | -1.175E-37 |
| Maximum exponent (Emax) for DOUBLE values | 308 |
| Smallest DOUBLE value | -1.79769E+308 |
| Largest DOUBLE value | +1.79769E+308 |
| Minimum exponent (Emin) for DOUBLE values | -307 |
| Smallest positive DOUBLE value | +2.225E-307 |
| Largest negative DOUBLE value | -2.225E-307 |
| Maximum exponent (Emax) for DECFLOAT(16) values | 384 |
| Smallest DECFLOAT(16) value1 | -9.999999999999999E+384 |
| Largest DECFLOAT(16) value | 9.999999999999999E+384 |
| Minimum exponent (Emin) for DECFLOAT(16) values | -383 |
| Smallest positive DECFLOAT(16) value | 1.000000000000000E-383 |
| Largest negative DECFLOAT(16) value | -1.000000000000000E-383 |
| Maximum exponent (Emax) for DECFLOAT(34) values | 6144 |
| Smallest DECFLOAT(34) value1 | -9.999999999999999999999999999999999E+6144 |
| Largest DECFLOAT(34) value | 9.999999999999999999999999999999999E+6144 |
| Minimum exponent (Emin) for DECFLOAT(34) values | -6143 |
| Smallest positive DECFLOAT(34) value | 1.000000000000000000000000000000000E-6143 |
| Largest negative DECFLOAT(34) value | -1.000000000000000000000000000000000E-6143 |
No comments:
Post a Comment