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

赠金笔

加载中，请稍候......

评论加载中，请稍候...

发评论

登录名： 密码： 找回密码 注册记住登录状态

昵   称：

评论并转载此博文

发评论

以上网友发言只代表其个人观点，不代表新浪网的观点或立场。