定义长度更大的VARCHAR2列的影响 [英] Impact of defining VARCHAR2 column with greater length

问题描述

在值不超过10字节的情况下，在Oracle中用VARCHAR2(1000)而不是VARCHAR2(10)定义列的作用是什么?

该列是否只占用了存储值真正必要的空间，还是会对表空间/索引的大小/性能产生负面影响?

解决方案

答案取决于您是在谈论数据库表中的列，还是PL/SQL程序中的变量.

数据库列

使用的存储量与存储的数据大小成比例.

PL/SQL变量

如果声明的变量大小为1到4000(11g +)/1999(10g或更早)，则将为最大长度分配内存(即VARCHAR2(100)至少需要100个字节的内存). /p>

如果声明的变量大小为4001(11g +)/2000(10g或更早)，则将根据存储数据的大小分配内存. (一个有趣的附带问题是，如果更改了变量的值，如何调整内存大小-是否会以新的大小重新分配另一个缓冲区?)

10g的参考: PL/SQL数据类型

对小型VARCHAR2变量进行了性能优化，对较大的VARCHAR2变量进行了优化以提高内存使用效率.截止点是2000字节.对于长度为2000字节或更长的VARCHAR2，PL/SQL仅动态分配足够的内存来保存实际值.对于小于2000个字节的VARCHAR2变量，PL/SQL会预先分配该变量的完整声明长度.例如，如果将相同的500字节值分配给VARCHAR2(2000 BYTE)变量和VARCHAR2(1999 BYTE)变量，则前者占用500字节，而后者占用1999字节.

11g的参考:避免PL中的内存开销/SQL代码

为VARCHAR2变量指定大于4000个字符的大于大小； PL/SQL等待直到您分配了变量，然后才根据需要分配尽可能多的存储空间

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?

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?

解决方案

The answer depends on whether you're talking about a column in a database table, or a variable in a PL/SQL program.

Database column

The amount of storage used is proportionate to the size of the data stored.

PL/SQL variable

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).

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?)

Reference for 10g: PL/SQL Datatypes

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.

Reference for 11g: Avoiding Memory Overhead in PL/SQL Code

Specify a size of more than 4000 characters for the VARCHAR2 variable; PL/SQL waits until you assign the variable, then only allocates as much storage as needed

这篇关于定义长度更大的VARCHAR2列的影响的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！