Oracle Performance Tuning Tips

Specify scale for NUMBERs

Many developers do not specify a precision for NUMBER columns. This may be in order to minimize the work that would be needed to increase that precision should the application's requirements change, but it is often just a matter of habit. And although it is possible using the undocumented (*,scale) syntax to specify a scale without a precision, this is not normally done. If no precision and no scale are specified for an Oracle NUMBER column then it can contain arbitrary floating point numbers, and floating point numbers can be large.

In most cases this has no performance impact because the columns in fact only contain integers and the number of bytes required to store an integer is no more than one plus half the number of significant digits (more). However, large floating point values may be stored in these columns if their values are sometimes computed using floating point arithmetic. In particular, when saving the results of floating point arithmetic to a NUMBER column, if there is no scale, or if the scale is further to the right than the least significant digit of precision in the result, then rounding will not be performed (more) and thus the full storage precision will be used.

Here is an example. We will create a table with two NUMBER columns. The first will have no scale. The other will have a scale of 4 digits. Note the difference in storage space required when we then insert the same value to these two columns. Although the values are the same, the column without any scale takes 21 bytes to store the number, whereas the column with a small scale takes just 2 bytes to store the same number.

SQL> create table numbers (n1 number, n2 number(10,4));

Table created.

SQL> insert into numbers values (3*(1/3), 3*(1/3));

1 row created.

SQL> select * from numbers;

        N1         N2
---------- ----------
         1          1

1 row selected.

SQL> select vsize(n1), vsize(n2) from numbers;

 VSIZE(N1)  VSIZE(N2)
---------- ----------
        21          2

1 row selected.

The value inserted was expressed as 3*(1/3) for the illustration above to force the use of floating point arithmetic during the statement execution. The brackets were needed to prevent the optimizer from simplifying the expression before execution. The example below uses the log function to illustrate the same point and to show that the scale must be to the left of the least significant digit of precision to ensure that the results of floating point expression evaluation are rounded prior to storage. Unless this rounding occurs, the full precision of the column will be used for data storage.

SQL> create table numbers (n1 number, n2 number(*,38), n3 number(*,37));

Table created.

SQL> insert into numbers values (log(2, 4), log(2, 4), log(2, 4));

1 row created.

SQL> select * from numbers;

        N1         N2         N3
---------- ---------- ----------
         2          2          2

1 row selected.

SQL> select vsize(n1), vsize(n2), vsize(n3) from numbers;

 VSIZE(N1)  VSIZE(N2)  VSIZE(N3)
---------- ---------- ----------
        21         21          2

1 row selected.

In this case because the value 2 has its most significant digit one place to the left of the decimal point, one byte is needed for digits to the left of the decimal point leaving 19 bytes for digits to the right of the decimal point. These 19 bytes can hold at most 38 digits, of which the last might not be accurate. Thus the scale must be no greater than 37 digits right of the decimal point to ensure rounding prior to storage. For larger values a smaller scale would be needed.

Because most Oracle NUMBER columns just store integers and are never subject to floating point arithmetic, this waste of space is not very widespread despite that developers often fail to specify the scale of NUMBERs. However, where it does occur it reduces table data density which in turn increases table scan I/O, uses cache memory less effectively, and reduces index efficiency. Therefore, it is good to be in the habit of always specifying at least a scale for real NUMBERs, if not a precision as well. For integers, if a precision is specified then a scale of 0 is implied, otherwise the scale should be set to zero explicitly using the NUMBER(*,0) datatype specification or a synonymous ANSI datatype specification.

Our unscaled_numbers.sql script can be used to check an existing database for columns that are wasting space because of this issue and therefore might be impacting performance. By default it identifies NUMBER columns for which no scale has been specified and which have an average data length of more than 9 bytes (more than 15 digits).