为什么 Oracle varchar2 有一个强制大小作为定义参数？

Why does Oracle varchar2 have a mandatory size as a definition parameter?

提问by user2427

I want to know why Oracle needs the size parameter in the definition of the VARCHAR2.

我想知道为什么 Oracle 在VARCHAR2.

I think that is for constraint. Would it be a better option that oracle takes this parameter as an optional like NUMBERdataType?

我认为这是为了约束。oracle 将此参数作为可选的NUMBER数据类型是否是更好的选择？

I often have problems resizing old tables to larger sizes, because sometimes a value is bigger than the size definition of the VARCHAR2column.

我经常遇到将旧表调整为更大尺寸的问题，因为有时一个值大于VARCHAR2列的大小定义。

It's the same to define a type of VARCHAR2(10) or VARCHAR2(1000).

定义VARCHAR2(10) 或的类型是一样的VARCHAR2(1000)。

I guess, it's an unnecessary constraint. If not, do you know of a real case when this constraint resulted in something useful? And why no such declaration in NUMBERtype ?

我想，这是一个不必要的约束。如果没有，您是否知道此约束产生有用的实际情况？为什么在NUMBERtype 中没有这样的声明？

回答by APC

It's the same to define a type of varchar2(10) or varchar2(1000).

定义一个类型的varchar2(10) 或varchar2(1000) 是一样的。

No, it is not the same thing at all.

不，这根本不是一回事。

1. The length of the column is useful metadata for developers building screens.
2. Similarly automatic query tools like TOAD and SQL Developer use the length of the column when they render results.
3. The database uses the length of a variable when allocating memory for PL/SQL collections. As that memory comes out of the PGA supersizing the variable declaration can lead to programs failing because the server has run out of memory.
4. There are similar issues with the declaration of single variables in PL/SQL programs, it is just that collections tend to multiply the problem.
5. Supersized columns create problems for compound indexes. The following is on a database with 8K blocks

1. 列的长度是开发人员构建屏幕的有用元数据。
2. 类似地，TOAD 和 SQL Developer 等自动查询工具在呈现结果时使用列的长度。
3. 数据库在为 PL/SQL 集合分配内存时使用变量的长度。由于内存来自 PGA 超大尺寸，变量声明可能导致程序失败，因为服务器内存不足。
4. PL/SQL 程序中的单个变量的声明也存在类似的问题，只是集合往往会使问题成倍增加。
5. 超大列会给复合索引带来问题。以下是一个有 8K 块的数据库

....

....

SQL> create table t23 (col1 varchar2(4000), col2 varchar2(4000))
  2  /

Table created.

SQL> create index t23_i on t23(col1,col2)
  2  /
create index t23_i on t23(col1,col2)
                      *
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded


SQL>

But above all else, columns sizes are a form of error checking. If the column is supposed to be ten characters long and some autonomic process is trying to load a thousand characters then something is wrong. The process ought to fail, so we can investigate why we are loading duff data. The alternative is a database full of garbage, and if that is what was wanted we should just have given everybody Excel and have done with it.

但最重要的是，列大小是一种错误检查形式。如果该列的长度应该是 10 个字符，而某个自主进程试图加载 1000 个字符，那么就会出现问题。这个过程应该会失败，所以我们可以调查为什么我们要加载 duff 数据。另一种选择是一个充满垃圾的数据库，如果这是我们想要的，我们应该给每个人 Excel 并完成它。

It is true that changing the column size when it turns out we have underestimated can be tiresome. But it doesn't happen very often, and we can mitigate a lot of the pain by using %TYPE and SUBTYPE declarations in our PL/SQL instead of hard-coding variable lengths.

确实，当事实证明我们低估时更改列大小可能会令人厌烦。但这并不经常发生，我们可以通过在 PL/SQL 中使用 %TYPE 和 SUBTYPE 声明而不是硬编码变量长度来减轻很多痛苦。

---

---

"why no such declaration in NUMBER type"

“为什么在 NUMBER 类型中没有这样的声明”

Numbers are different. For a start, the maximum size of a number is much smaller than the text equivalent (38 digits of guaranteed precision).

数字不一样。首先，数字的最大大小远小于等效的文本（38 位精度保证）。

But the key difference is that Oracle stores numeric values in scientific notationso there is not a straightforward relationship between the arithmetical size of the number and the storage space it consumes.

但关键区别在于 Oracle以科学记数法存储数值，因此数字的算术大小与其消耗的存储空间之间没有直接关系。

SQL> select vsize(123456789012345678901) n1
  2         , vsize(999999999999999999999999999999) n2
  3         , vsize(0.000000000000000000001) n3
  4         , vsize(1000000000000000000000000) n4
  5  from dual
  6  /

        N1         N2         N3         N4
---------- ---------- ---------- ----------
        12         16          2          2

SQL> 

Nevertheless, it remains good practice to specify scale and precision wherever possible, especially when we are dealing with integers, say, or money.

尽管如此，在可能的情况下指定比例和精度仍然是一种很好的做法，尤其是当我们处理整数时，例如，或金钱。

回答by Bob Jarvis - Reinstate Monica

I think it's important to remember the historical context in which relational databases were developed. At the time they were being developed (late 70's - early 80's) commonly available computers were much smaller (in terms of memory and disk space) and less powerful (in terms of CPU) than we have now, and managing these resources was necessarily a compelling concern. COBOL was the common language of business computing (and is still widely used), and object-oriented languages such as Smalltalk and C++ were unknown, for all practical purposes. At that time it was expected that programs would declare precisely how much storage they would need for each data element, e.g. 10 bytes for a string, 2 bytes for a short integer, 4 bytes for a float, etc, and so this style of declaration was used by the then-newly-developed relational databases. More to the point, the assumptionwas made that each data element would declare (implicitly or explicitly) the amount of storage it required, and this was coded into the relational engines at a very fundamental level.

我认为记住关系数据库开发的历史背景很重要。在它们被开发的时候（70 年代末 - 80 年代初），通常可用的计算机比我们现在更小（就内存和磁盘空间而言）和更不强大（就 CPU 而言），管理这些资源必然是一个令人信服的关注。COBOL 是商业计算的通用语言（并且仍然被广泛使用），而面向对象的语言（例如 Smalltalk 和 C++）对于所有实际用途都是未知的。当时预计程序会准确地声明每个数据元素需要多少存储空间，例如，字符串需要 10 个字节，短整数需要 2 个字节，浮点数需要 4 个字节，等等，所以这种声明风格被当时新开发的关系数据库使用。更重要的是，该假设每个数据元素都将（隐式或显式）声明其所需的存储量，并且在非常基础的级别将其编码到关系引擎中。

Now, over time this requirement has relaxed somewhat, at least as far as storing the data on disk goes. I believe that in Oracle the NUMBER data type will flexibly allocate space so that only the minimum amount of space needed to store its value is actually used, and that VARCHAR2 columns will only use enough disk space to store the actual data without storing trailing blanks, although you still need to declare the maximum amount of storage required for a VARCHAR2.

现在，随着时间的推移，这一要求有所放松，至少就将数据存储在磁盘上而言。我相信在 Oracle 中，NUMBER 数据类型会灵活分配空间，以便实际仅使用存储其值所需的最小空间量，并且 VARCHAR2 列将仅使用足够的磁盘空间来存储实际数据，而不会存储尾随空白，尽管您仍然需要声明 VARCHAR2 所需的最大存储量。

You might take a look at the SYS.STANDARD package to get an idea of how to declare VARCHAR2 subtypes. For example, if you wanted your own 'string' type which you could use without tacking on a length specification you might try:

您可以查看 SYS.STANDARD 包以了解如何声明 VARCHAR2 子类型。例如，如果您想要自己的“字符串”类型，您可以在不附加长度规范的情况下使用它，您可以尝试：

SUBTYPE MY_STRING IS VARCHAR2(4000);

However, be wary of this if you're going to index the column in question (as pointed out earlier by @APC).

但是，如果您要为有问题的列编制索引（正如@APC 之前指出的），请注意这一点。

I agree that I'd rather just be able to declare a STRING (which is, BTW, defined in SYS.STANDARD as a subtype of VARCHAR2) without having to declare a length, but that's just not how Oracle works, and as I'm not about to start writing my own relational database (I have my own windmills at which to tilt, thank you :-) I'll just go along with the status quo.

我同意我宁愿能够声明一个 STRING（顺便说一句，在 SYS.STANDARD 中定义为 VARCHAR2 的子类型）而不必声明长度，但这不是 Oracle 的工作方式，因为我我不打算开始编写我自己的关系数据库（我有自己的风车可以倾斜，谢谢:-) 我将顺应现状。

I hope this helps.

我希望这有帮助。

回答by Jeffrey Kemp

Why not have every column in every database table be a CLOB? That way you don't have to worry about maximum lengths...

为什么不让每个数据库表中的每一列都是 CLOB？这样你就不必担心最大长度......

But, seriously:

不过实话说：

Data type length constraints are there for the same reason as any constraints: they reduce the amount of error checking you need to sprinkle through all your application code, by ensuring that any data successfully stored in the table adheres to the constraints you've defined.

数据类型长度约束的存在与任何约束的原因相同：它们通过确保成功存储在表中的任何数据符合您定义的约束，减少了您需要在所有应用程序代码中进行的错误检查量。

回答by Gary Myers

From the point of view of extracting information, it is very useful to know how big the field is. For example, if you have to print the address on an envelope or display it on a screen, you want to know how big the field has to be.

从提取信息的角度来看，知道这个领域有多大是非常有用的。例如，如果您必须在信封上打印地址或将其显示在屏幕上，您想知道该字段必须有多大。

Or buy VERY large envelopes.

或者购买非常大的信封。

回答by Nick Craver

Even though it doesn't allocate a set number of bytes on disk like a char field would, there are still decent reasons for sizing:

尽管它不像 char 字段那样在磁盘上分配一定数量的字节，但仍然有合理的调整大小的理由：

• Memory allocation on data readers (based on max row size)
• Indexing a large column brings block sizes into play
• Etc...

• 数据读取器上的内存分配（基于最大行大小）
• 索引大列会使块大小发挥作用
• 等等...

I'm sure there are more reasons someone else can think of, but those are ones I've seen in a past project where someone chose to varchar2(4000)everything.

我相信其他人可以想到更多的原因，但这些是我在过去的一个项目中看到的，有人选择了varchar2(4000)一切。

回答by Vipin Jain

There's possible performance impact: in MySQL, temporary tablesand MEMORY tablesstore a VARCHARcolumn as a fixed-length column, padded out to its maximum length.

有可能影响性能：在MySQL，temporary tables和MEMORY tables存储VARCHAR列一个固定长度列，填补了它的最大长度。

If you design VARCHARcolumns much larger than the greatest size you need, you will consume more memory than you have to. This affects cache efficiency, sorting speed, etc.

如果您设计的VARCHAR列比您需要的最大尺寸大得多，您将消耗更多的内存。这会影响cache efficiency, sorting speed, etc.

So you give the max length which is under your string come. like if you max length of character 10 so don't give his length 100 or more.

所以你给出了你的字符串下的最大长度。就像你最大长度的字符 10 所以不要给他的长度 100 或更多。