注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

TOTO

Think and Be Different

 
 
 

日志

 
 

Specify scale for NUMBERs【zz】  

2006-12-02 10:56:30|  分类: 默认分类 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

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).

附unscaled numbers.sql

-------------------------------------------------------------------------------  --  -- Script: unscaled_numbers.sql  -- Purpose: to find NUMBER columns with no scale and lots of digits  -- For:  8.1  --  -- Copyright: (c) Ixora Pty Ltd  -- Author: Steve Adams  --  -------------------------------------------------------------------------------  @save_sqlplus_settings    select    owner,    table_name,    column_name,    avg_col_len  from    dba_tab_columns  where    data_type = 'NUMBER' and    data_scale is null and    avg_col_len > 9  /    @restore_sqlplus_settings  
  评论这张
 
阅读(223)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017