Shulou(Shulou.com)06/01 Report--

The purpose of this blog was to only write about the performance comparison between pls_integer and number, but after I picked up the pen, I wanted to write more about my thought process, so I just wrote an article about oracle numerical types. It's boring to read too much practical information. I try to write this article in a way of chat, hoping that my colleagues can discuss many numerical types of oracle with me in a relaxed atmosphere. Well, I'll throw a brick to attract jade first.

1 、 number

The first numerical type I came into contact with is Big Brother number, which is defined as number (pforce s), p is precision, up to 38 bits, s is the scale range, can be in the range of-84-127. Take chestnut (for example): number means that the integer plus decimal part of the decimal point has a total of 4 places, excluding decimal places, that is, the integer part is at most two digits. If the decimal part is more than 2 digits, it will be rounded (for sqlserver and mysql, it will also be rounded). If the decimal part is less than 2 digits, it will add 0 (for sqlserver, but mysql will not). If you do not write PJS, it will add 0. Then the default is 38. The number type is the most commonly used numeric type in oracle. For a long time, I thought that the only numeric type was number, because the number type was enough to handle almost all numeric types. But then, as I continued walking along the oracle road, I learned about other derived numerical types.

2 、 integer

In fact, when I first saw this numeric type, like many people, I thought it was a subtype of the number type, with a maximum of 38 digits, automatically rounding decimals, but later I found that this was not exactly the case. As a subtype of number, it is true that integer exists as a stored integer value. When we need to store integer values, we will think of this numerical type, but its maximum bit is not 38 bits. In the inserted test values, it has far exceeded 38 bits, as shown in the figure:

As you can see, the statement that integer can only store 38 bits is directly overturned. Next, let's take a look at the popular saying, "integer can only store integers and round decimals." let's take a look at an example:

Create or replace procedure integer_test (an integer)

As

B integer: = a

Begin

Dbms_output.put_line (b)

End

The output is shown in the figure:

By the time I see this result, I believe many students are already cold, which simply subverts the previous understanding. Indeed, we can see from this example that integer is a subtype of number, but it does not force rounding, and its maximum number of digits is greater than 38 digits.

3 、 int,numeric,decimal

These three numeric types exist in oracle to be compatible with other databases and are also subtypes of number. When making a type declaration, if it is directly declared as int,numeric,decimal, it will be directly saved as integer, in exactly the same way as integer; if numeric and decimal have precision and scale range, then they will be saved as number (pdepartment s), with the same processing method as number (pforce s), int type can only be declared as int, without precision and scale range, so it can only be treated as integer.

4 、 float

Float is also a subtype of number, float (n), n refers to precision, is binary precision, here is not decimal precision oh, the calculation formula is: binary precision=ceil (baked 0.30103), float has no scale range. the range of n is 1x126. So how to calculate the value stored in the database, for example: float (2), then its precision is ceil (2 = 0.30103) = 1. If it is stored in 13.5, it should be 1.35 * 10 ^ 1, and the precision is 1 * 10 ^ 1, rounded to 1, so 1 * 10 ^ 1 = 10 If it is 19.5, then it should be 1.95 * 10 ^ 1, and the precision is 1 * 10 ^ 1. 1.95 is rounded to 2, which is 2 * 10 ^ 1. The result is stored in 20. If there is no precision, it is mapped to the number type and stored directly in this value. Oracle is not directly a numeric type of type double.

5. Binary_integer and pls_integer

This number type can only be used for PL/SQL, they have more advantages than number, first of all, they occupy less storage space, and secondly, they are calculated directly through hardware, that is, they are calculated directly through CPU without conversion. When calculating, number types have to be converted to binary first, so binary_integer and pls_integer perform much better than number in terms of computing speed. It is recommended to use these two data types in PL/SQL instead of number, and you will be pleasantly surprised.

5. Binary_float and binary_double

These two numerical types do not appear until 10g, binary_float can store a single-precision 32-bit floating-point number, binary_double can store a double-precision 64-bit floating-point number, binary_float and binary_double occupy less storage space, similarly, binary_float and binary_double are also calculated directly through hardware, so it is more efficient, and they can store larger or smaller values than number However, the accuracy of these two numerical types will also be inaccurate.

6. When it comes to floating-point storage, I need to talk about the reasons why floating-point storage is not accurate:

First of all, we need to know how floating point is stored in the computer, for example, to store 13.75, first convert the integer part to binary, 13 to binary is 1101, then convert the decimal part to binary, the decimal part is 0.75. multiply the decimal part by 2, take the integer part of the result as a binary digit, and then continue to multiply the decimal part of the result by 2. Until the decimal part is all 0, in this process, there will be endless loop multiplication, but because the decimal place of the floating point is determined (float is 23 places, double is 52 places), it will stop when it comes to the specified decimal place, which is the reason why the floating point number is not accurate. You can see that if it can be multiplied within the range of decimal places, then the result is stored accurately. Let's take a look at the process of converting the fractional part (0.75) to binary:

0.75 to 2, 1.5 to 1.

0.5 / 2 / 1 / 1 / 2 / 2 / 1 / 1 / 2 / 2 / 1 / 1 / 2 / 2 / 1 / 1 / 2 / 2 / 1 / 2 / 2 / 1 / 2 / 2 / 1 / 2 / 2 / 1 / 1 / 2 / 2 / 1 / 1 / 2 / 2 / 1 / 1 / 2 / 2 / 1 / 1 / 2 / 2 / 1 / 1 / 2 / 1 / 1 / 2 / 2 / 1 / 1 / 2 / 2 / 1 / 1 / 2 / 2 / 1 / 1 / 2 / 2 / 1 / 1 / 2 / 2 / 1 / 1 / 2 / 2 / 1 / 1 / 2 / 2 / 1 / 2 / 2 / 1 / 1 / 2 / 2 / 1 / 1 / 2 / 2 / 1 / 1 / 2 / 2 / 1 / 2 / 2 / 1 / 1 / 2 / 2 / 1 / 2 / 2 / 1 /

When we multiply here, there is no decimal part in 0. 520. 1, so it stops here. The final result is 1101.11, which is converted to 1101.11, that is, 1.10111 * 2 ^ 3, 1.10111 is the decimal part, 2 is the bottom, 6 is the exponential part, and this 1.10111 * 2 ^ 3 is the converted binary floating point number. Then the binary floating-point number is stored in the computer.

If you store it in your computer, you will use a floating-point representation, which is divided into three parts:

The first part: symbol bit, occupies 1 bit, is used to distinguish between positive and negative numbers, 0 is positive, 1 is negative; for 1.10111 * 2 ^ 3, it is positive, so the sign bit is 0

The second part: exponential bit, float occupies 8 bits, double occupies 11 bits, which is used to represent the index; for 1.10111 * 2 ^ 3, the exponential bit is 3

The third part: decimal places. Float occupies 23 places and double occupies 52 places, which is used to represent decimal places. For 1.10111 * 2 ^ 3, the decimal place is 10111.

So for float, symbol place + index place + decimal place = 1 "8" 23 = 32 places, for double, symbol place + index place + decimal place = 64 places, at the same time, we can see that exponential place determines the size range, and decimal places determine the precision. Because the exponential bit may be positive or negative, the negative number is more troublesome than the positive number, so in order to reduce the trouble of calculation, when storing the index, you need to store it as an unsigned integer, so an offset will be added on the exponential bit. The offset of float is 127 doubles, and the offset is 1023. If you need to convert it to decimal, then the index minus the corresponding value on it. So for 13.75 (float), originally its binary index value is 3, now it is 3 "127" 130, converted to binary is 10000010, and finally: 13.75 (float) is stored as: symbol index index decimal places (insufficient places 0) = 0 10000010 10111 00000 00000 00000 000000

7. Ten thousand tall buildings rise from the ground, and sometimes we pursue some advanced grammar, or lofty things, but we can't grasp the basic things firmly. In fact, the basic data type of the database is often not as simple as it seems. In fact, any language is the same. Only by mastering the basic knowledge and being a basic proficient person, then we will be able to achieve it on our way to the advanced level. At the end of writing, I found that as a whole, the whole article was not so easy, and I was used to writing serious things. I was not used to writing easy things all of a sudden.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.