对于 Oracle 中的 NUMBER 列，指定长度是否有助于提高性能？

For NUMBER columns in Oracle, does specifying a length help performance?

提问by Jake Petroules

Is there any true difference in performance between NUMBER, NUMBER(3) and NUMBER(10)? Doesn't the RDBMS use 32 bits to store the value regardless, and simply limit the length of the data when looked at as a string?

NUMBER、NUMBER(3) 和 NUMBER(10) 之间的性能是否有真正的区别？无论如何，RDBMS 不是使用 32 位来存储值，而是在将数据视为字符串时简单地限制数据的长度吗？

A colleague debated that it was, for example, more efficient to use NUMBER(10) vs NUMBER/NUMBER(11), thinking that nwas the number of bytes rather than the length of the data in characters. Normally I'd agree to limit the column size if the number of rows was guaranteed not to exceed 10^n or so, but for this particular database the limit for number of rows was literally "as many as possible", e.g. 2^32 or ~4 billion, even though we'd never reach that amount. Coming up with a "lowest maximum" for this situation seems pointless and a waste of time and I thought using NUMBER without specifying a length would be simpler and incur no penalties. Am I correct?

一位同事争论说，例如，使用 NUMBER(10) 比使用 NUMBER/NUMBER(11) 更有效，认为n是字节数而不是数据的长度（以字符为单位）。通常，如果保证行数不超过 10^n 左右，我会同意限制列大小，但是对于这个特定的数据库，行数的限制实际上是“尽可能多”，例如 2^32或约 40 亿，即使我们永远不会达到那个数量。在这种情况下提出“最低最大值”似乎毫无意义并且浪费时间，我认为使用 NUMBER 而不指定长度会更简单并且不会受到惩罚。我对么？

回答by Gary Myers

Technically, you don't define a length, but a precision and scale.

从技术上讲，您不定义长度，而是定义精度和比例。

The same numeric value (eg 100, 4.3) takes the same internal value irrespective of the precision and scale defining the column.

相同的数值（例如 100、4.3）采用相同的内部值，而与定义列的精度和小数位数无关。

The maximum value a column can hold is determined by BOTH precision and scale. That is you can store the value 100 in a column of NUMBER(3,0) but not in a column of NUMBER(3,1).

列可以容纳的最大值由精度和小数位数决定。也就是说，您可以将值 100 存储在 NUMBER(3,0) 列中，但不能存储在 NUMBER(3,1) 列中。

Generally, if a column shouldn't store a decimal the scale should be zero. Bear in mind that if you try to store 10.12 in a NUMBER(3,0) it will store the value 10. It doesn't error (because if it did, you'd have a hard time storing the value of one third in anything). If you want it to error you want to allow for a higher scale and use a constraint to stop it ever being used.

通常，如果一列不应该存储小数，则比例应该为零。请记住，如果您尝试将 10.12 存储在 NUMBER(3,0) 中，它将存储值 10。它不会出错（因为如果确实如此，您将很难将三分之一的值存储在任何事物）。如果您希望它出错，您希望允许更高的比例并使用约束来阻止它被使用。

I also believe that you should try to use a 'sensible' precision too. If you stored a thousand values each second for a thousand years you'd still fit that within 14 digits. If I see a column of NUMBER(14,0) then I know that on my screen or printout I should allow for 14 numeric characters to be displayed. If I see just NUMBER or NUMBER(38,0), then I haven't really been given any guidance and may guess at 14 characters. And if they start putting 16 character credit card numbers in there, I'll be wrong. Which is why I prefer not to guess.

我也相信您也应该尝试使用“明智”的精确度。如果您在一千年内每秒存储一千个值，您仍然可以将其放在 14 位以内。如果我看到一列 NUMBER(14,0) 那么我知道在我的屏幕或打印输出上我应该允许显示 14 个数字字符。如果我只看到 NUMBER 或 NUMBER(38,0)，那么我并没有真正得到任何指导，可能会猜测 14 个字符。如果他们开始在那里输入 16 个字符的信用卡号，那我就错了。这就是为什么我不想猜测的原因。

Also in PL/SQL, they have a PLS_INTEGER datatype which goes up to 2147483647. If I see a column of NUMBER(9,0) I know I can fit it into a PLS_INTEGER. There's probably similar considerations for Java or .Net etc. when they want to work out what datatype, scale, precision to use when pulling/pushing data to the database.

同样在 PL/SQL 中，它们有一个 PLS_INTEGER 数据类型，最高可达 2147483647。如果我看到一列 NUMBER(9,0)，我知道我可以将它放入 PLS_INTEGER。当 Java 或 .Net 等想要确定将数据拉入/推送到数据库时要使用的数据类型、规模、精度时，可能有类似的考虑。

回答by Wolph

It makes no difference wheter you use NUMBER(1)or NUMBER(10). The storage requirements are the same and depend on the data that you store in it.

无论您使用NUMBER(1)或NUMBER(10). 存储要求相同，取决​​于您存储在其中的数据。

The only possible difference in performance is between NUMBER()and NUMBER(N)because the latter will have to check the size while storing. But that is so negligible that it is probably not even measurable.

性能上唯一可能的差异是在NUMBER()和NUMBER(N)因为后者必须在存储时检查大小。但这是微不足道的，甚至可能无法衡量。

回答by Bob Jarvis - Reinstate Monica

Regarding the 32 bits - no. In Oracle the NUMBER type is a variable length base-10 floating point value with a guaranteed precision of 38 digits. See the Oracle docsand this AskTom question.

关于 32 位 - 没有。在 Oracle 中，NUMBER 类型是一个可变长度的 base-10 浮点值，保证精度为 38 位。请参阅Oracle 文档和此 AskTom 问题。

<soapbox>

<肥皂盒>

In fact, I think NUMBER is actually one of The Best Thingsin Oracle. I've read a truism to the effect that "any product (language, database, etc) that requires developers to know and care about the number of bits in a numeric variable are unfit for business programming" and agree with it completely. There is noreason for someone writing accounting software to give a hoot about whether or not their value will fit into a 32-bit scaled decimal, or whether double-length ANSI floats might cause a problem (they will), or a myriad of other issues which revolve around the issue of numbers in computers. Numbers are too important to be too efficient about. YMMV.

事实上，我认为 NUMBER 实际上是Oracle 中最好的东西之一。我读到过这样一个道理：“任何需要开发人员了解和关心数字变量中位数的产品（语言、数据库等）都不适合业务编程”，我完全同意它。有没有理由的人写会计软件给予其价值是否会放入一个32位的缩放小数或双长度ANSI浮动是否会导致问题（他们会）叱，或其他无数围绕计算机中数字问题的问题。数字太重要了，不能太有效。天啊。

</soapbox>

< /肥皂盒>

Share and enjoy.

分享和享受。