Oracle number 类型,指定或不指定其长度和精度的区别和对性能的影响
(2010-10-22 10:18:02)
标签:
杂谈
From book "Oracle Performance Survival Guide: A Systematic Approach
to Database Optimization"
Numeric and date data types generally pose few complications when
converting
from the logical to the physical model. As we discussed previously,
Oracle usually
stores numbers in a flexible large magnitude, flexible precision
data type. So
although you might choose to supply a NUMBER with a precision, this
serves
more as a constraint or as documentation than as a performance
optimization.
However, one circumstance in which setting a precision might help
performance
is when there is a possibility of high precision values being
accidentally
assigned to numbers that do not require such precision.
For example, currency values will often be rounded up to 2 decimal
places
(dollars and cents) as a matter of course. However, a numeric
operation on such a
number might result in a high precision number, such as an
“irrational” fraction,
in which the decimal values repeat endlessly. For example, say we
decide to calculate
a credit equal to 1/6th of a sales amount for each sale. Many
values will
not divide evenly. So for instance while $99.99/6 returns $16.665,
$100/6 returns
$16.66666666666666666666666666666666666667. If the column that
receives this
unnecessary precision has no precision defined (that is, is simply
defined as
NUMBER), Oracle must allocate storage for all the significant
digits after the initial
.66. The additional storage results in larger row lengths and some
degradation
in scan performance. However, if the column has a precision,
defined as NUMBER(*,2)
for example, the unnecessary precision will be truncated and
row
length will be reduced accordingly.
Oracle does support BINARY_FLOAT and BINARY_DOUBLE data types
that map to 32-bit and 64-bit floating point native data types.
Using these
data types might lead to some computational or storage efficiencies
not provided
by the NUMBER data type, but for most applications these would not
be
measurable.
分享:
喜欢
0
赠金笔
加载中,请稍候......
评论加载中,请稍候...
发评论
登录名: 密码: 找回密码 注册记住登录状态
昵 称:
评论并转载此博文
发评论
以上网友发言只代表其个人观点,不代表新浪网的观点或立场。