oracle 定义更大长度的 VARCHAR2 列的影响
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1882073/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Impact of defining VARCHAR2 column with greater length
提问by Peter Lang
What are the effects of defining a column with VARCHAR2(1000)instead of VARCHAR2(10)in Oracle, when the values are not longer than 10 Byte?
当值不超过 10 字节时，用VARCHAR2(1000)而不是VARCHAR2(10)在 Oracle中定义列有什么影响？
Does the column only take the space really necessary to store the values, or would that have any negative impacts on the size/performance of tablespaces/indexes?
该列是否只占用存储值所真正需要的空间，还是会对表空间/索引的大小/性能产生负面影响？
采纳答案by Jeffrey Kemp
The answer depends on whether you're talking about a column in a database table, or a variable in a PL/SQL program.
答案取决于您是在谈论数据库表中的列还是 PL/SQL 程序中的变量。
Database column
数据库列
The amount of storage used is proportionate to the size of the data stored.
使用的存储量与存储的数据大小成正比。
PL/SQL variable
PL/SQL 变量
If the variable is declared with a size 1 to 4000 (11g+) / 1999 (10g or earlier), memory will be allocated for the maximum length (i.e. VARCHAR2(100) will require at least 100 bytes of memory).
如果变量声明的大小为 1 到 4000 (11g+) / 1999（10g 或更早版本），将分配最大长度的内存（即 VARCHAR2(100) 将需要至少 100 字节的内存）。
If the variable is declared with a size 4001 (11g+) / 2000 (10g or earlier) or greater, memory will be allocated according to the size of the data stored. (an interesting side question would be, if the variable's value is changed, how is the memory resized - does it reallocate another buffer with the new size?)
如果变量声明的大小为 4001 (11g+) / 2000（10g 或更早版本）或更大，则将根据存储的数据大小分配内存。（一个有趣的附带问题是，如果变量的值发生变化，内存是如何调整大小的——它是否会重新分配另一个具有新大小的缓冲区？）
Reference for 10g: PL/SQL Datatypes
10g 参考：PL/SQL 数据类型
Small VARCHAR2 variables are optimized for performance, and larger ones are optimized for efficient memory use. The cutoff point is 2000 bytes. For a VARCHAR2 that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to hold the actual value. For a VARCHAR2 variable that is shorter than 2000 bytes, PL/SQL preallocates the full declared length of the variable. For example, if you assign the same 500-byte value to a VARCHAR2(2000 BYTE) variable and to a VARCHAR2(1999 BYTE) variable, the former takes up 500 bytes and the latter takes up 1999 bytes.
小的 VARCHAR2 变量针对性能进行了优化，而较大的变量针对有效的内存使用进行了优化。截止点是 2000 字节。对于 2000 字节或更长的 VARCHAR2，PL/SQL 仅动态分配足够的内存来保存实际值。对于短于 2000 字节的 VARCHAR2 变量，PL/SQL 会预分配变量的完整声明长度。例如，如果将相同的 500 字节值分配给 VARCHAR2(2000 BYTE) 变量和 VARCHAR2(1999 BYTE) 变量，则前者占用 500 个字节，后者占用 1999 个字节。
Reference for 11g: Avoiding Memory Overhead in PL/SQL Code
11g 参考：避免 PL/SQL 代码中的内存开销
Specify a size of morethan 4000 characters for the VARCHAR2 variable; PL/SQL waits until you assign the variable, then only allocates as much storage as needed
指定尺寸的多个超过4000个字符为VARCHAR2变量; PL/SQL 等待直到您分配变量，然后仅根据需要分配尽可能多的存储
回答by Quassnoi
In the database, there is no difference. VARCHAR2are stored with variable length, the declared length is only a limitation.
在数据库中，没有区别。VARCHAR2以可变长度存储，声明的长度只是一个限制。
However, some client applications will reserve 1000bytes per column instead of 10if they see that the column is defined as VARCHAR2(1000)
但是，某些客户端应用程序将保留1000每列的字节数，而不是10看到列被定义为VARCHAR2(1000)
回答by Gary Myers
Define the column size as the maximum length you are prepared to deal with. For a staging table, where I'm loading a file into the database, I may use VARCHAR2(4000). Then once all the data is in the database, I can do whatever validations I need to (data types, data lengths, min/max values, acceptable characters...) and have the valid values passed into a column with the appropriate definition/constraints.
将列大小定义为您准备处理的最大长度。对于我将文件加载到数据库中的临时表，我可以使用 VARCHAR2(4000)。然后，一旦所有数据都在数据库中，我就可以进行任何我需要的验证（数据类型、数据长度、最小/最大值、可接受的字符...）并将有效值传递到具有适当定义的列中/约束。
I can leverage DML error loggingso that data that doesn't fit the definition/constraints gets pumped off to a rejection table without any tricky row-by-row coding.
我可以利用DML 错误记录，以便不符合定义/约束的数据被抽取到拒绝表中，而无需任何棘手的逐行编码。
If you have a VARCHAR2(1000) then, at some point you WILL get data put in there that is longer than you expect (eg you may get a string of 10 characters but which has 14 bytes because some characters are multi-byte character set values.)
如果你有一个 VARCHAR2(1000) 那么，在某些时候你会得到比你预期更长的数据（例如，你可能会得到一个 10 个字符的字符串，但它有 14 个字节，因为有些字符是多字节字符集值。）
