问题描述
还是应该?
(标题的灵感来自于为什么Oracle varchar2具有强制大小作为定义参数?)
请考虑以下变量:
declare
-- database table column interfacing variable
v_a tablex.a%type; -- tablex.a is varchar2
-- PL/SQL only variable
v_b varchar2(32767); -- is this a poor convention ?
begin
select a into v_a from tablex where id = 1;
v_b := 'Some arbitrary string: ' || v_a; -- ignore potential ORA-06502
insert into tabley(id, a) values(1, v_a); -- tablex.a and tabley.a types match
v_b := v_b || ' More arbitrary characters';
end;
/
变量v_a用于连接数据库表列，因此使用 %type属性.但是，如果我知道数据类型为varchar2，为什么不应该使用varchar2(4000)或varchar2(32767)来保证从数据库列读取的字符串始终适合PL/SQL变量呢?除了%type属性的优越性之外，还有其他反对该约定的说法吗?
变量v_b仅在PL/SQL代码中使用，通常返回到JDBC客户端(Java/Python程序，Oracle SOA/OSB等)或转储到平面文件中(使用UTL_FILE).如果varchar2出现例如csv-line为什么我要费心计算确切的最大可能长度(除非要验证该行在所有情况下都适合32767字节，所以我不需要clob)并在每次数据模型更改时重新计算?
有很多问题涵盖了SQL中varchar2长度的语义，并解释了为什么varchar2(4000)在SQL中是较差的做法.同样，SQL和PL/SQL varchar2类型之间的差异也得到了很好的覆盖:
- Oracle中varchar2 PL/SQL子程序参数的大小限制是多少?
- Oracle中的VARCHAR(MAX)与VARCHAR(n)
- 为什么Oracle varchar2具有强制大小作为定义参数?
- 为什么VARCHAR需要长度说明?
- 向Oracle存储过程输入的varchar2的默认大小是多少，可以更改吗?
- 为什么使用除VARCHAR2(4000)以外的其他任何方式将字符串存储在Oracle数据库中?
- 为什么Oracle plsql varchar2变量需要大小但参数不需要大小?
- 问汤姆我与一个建模人员小组合作，该小组希望定义最大长度的每个varchar2字段."
我看到的唯一讨论此问题的地方是APC在 answer 中的第3点和第4点:
例如Oracle PL/SQL编程，第5版，作者Steven Feuerstein没有提到声明过长的varchar2变量的任何缺点，因此这不是一个严重的错误，对吧?
更新
经过更多的搜索之后，我发现Oracle文档在发行过程中得到了发展:
PL/SQL用户指南和参考10g第2版的引用第3章PL/SQL数据类型:
PL/SQL用户指南和参考11g第1版中的引文第3章PL/SQL数据类型:
但是PL/SQL用户指南和参考11g第2版第3章PL/SQL数据类型不再提及内存分配，而且我根本找不到任何其他有关内存分配的信息. (我正在使用此发行版，所以我只检查11.2文档.)PL/SQL用户指南和参考12c第1版第3章PL/SQL数据类型.
我还找到了Jeffrey Kemp的答案，它也解决了这个问题.但是，Jeffrey的答案是参考10.2文档，而问题根本不是关于PL/SQL.
当Oracle实施了不同的优化时，这似乎是PL/SQL功能相对于发行版有所发展的领域之一.
请注意，这也意味着OP中列出的某些答案也是特定于发行版的，即使这些问题/答案中未明确提及.随着时间的流逝和使用旧版Oracle版本的结束(我在做白日梦?)，信息将变得过时(可能需要数十年的思考).
上面的结论得到以下 quote 来自 PL/SQL语言参考11g R1 的第12章优化PL/SQL应用程序的性能:
11g R2 也不 12c R1 版本文档.这与第3章PL/SQL数据类型的发展是一致的.
答案:
从11gR2开始，使用varchar2(10)或varchar2(32767)与内存使用的观点没有区别. Oracle PL/SQL编译器将以最佳方式为您处理脏细节！
对于11gR2之前的版本，有一个临界点，在此处使用了不同的内存管理策略，并且每个版本的 PL/SQL语言参考中都明确记录了这一点.
以上内容仅适用于不存在可从问题域派生的自然长度限制的纯PL/SQL变量.如果varchar2-variable表示 GTIN-14 ，则应将其声明为varchar2(14). /p>
当带有表列的PL/SQL变量接口使用%type -attribute时，这是使您PL/SQL代码与数据库结构保持同步的零努力方法.
内存测试结果:
我在Oracle Database 11g企业版11.2.0.3.0版中运行了内存分析，结果如下:
str_size iterations UGA PGA
-------- ---------- ----- ------
10 100 65488 0
10 1000 65488 65536
10 10000 65488 655360
32767 100 65488 0
32767 1000 65488 65536
32767 10000 65488 655360
因为PGA更改是相同的，并且仅取决于iterations而不是str_size，所以我得出varchar2声明的大小无关紧要.测试可能太幼稚了-欢迎发表评论！
测试脚本:
-- plsql_memory is a convenience package wrapping sys.v_$mystat s and
-- sys.v_$statname tables written by Steven Feuerstein and available in the
-- code-zip file accompanying his book.
set verify off
define str_size=&1
define iterations=&2
declare
type str_list_t is table of varchar2(&str_size);
begin
plsql_memory.start_analysis;
declare
v_strs str_list_t := str_list_t();
begin
for i in 1 .. &iterations
loop
v_strs.extend;
v_strs(i) := rpad(to_char(i), 10, to_char(i));
end loop;
plsql_memory.show_memory_usage;
end;
end;
/
exit
试运行示例:
$ sqlplus -SL <CONNECT_STR> @memory-test.sql 32767 10000
Change in UGA memory: 65488 (Current = 1927304)
Change in PGA memory: 655360 (Current = 3572704)
PL/SQL procedure successfully completed.
$
Or should I ?
(The title is inspired by Gary Myers' comment in Why does Oracle varchar2 have a mandatory size as a definition parameter?)
Consider the following variables:
declare
-- database table column interfacing variable
v_a tablex.a%type; -- tablex.a is varchar2
-- PL/SQL only variable
v_b varchar2(32767); -- is this a poor convention ?
begin
select a into v_a from tablex where id = 1;
v_b := 'Some arbitrary string: ' || v_a; -- ignore potential ORA-06502
insert into tabley(id, a) values(1, v_a); -- tablex.a and tabley.a types match
v_b := v_b || ' More arbitrary characters';
end;
/
Variable v_a is used to interface a database table column and therefore uses a %type attribute. But if I know the data type is varchar2 why shouldn't I use varchar2(4000) or varchar2(32767) that also guarantee the string read from database column will always fit to the PL/SQL variable ? Is there any other argument against this convention except the superiority of %type attribute ?
Variable v_b is only used in PL/SQL code and is usually returned to a JDBC client (Java/Python program, Oracle SOA/OSB etc.) or dumped into a flat file (with UTL_FILE). If the varchar2 presents e.g. csv-line why I should bother to calculate the exact maximum possible length (except to verify the line will fit into 32767 bytes in all cases so I don't need a clob) and re-calculate every time my data model changes ?
There is plenty of questions that covers varchar2 length semantics in SQL and explains why varchar2(4000) is a poor practice in SQL. Also the difference between SQL and PL/SQL varchar2-type is well covered:
- What is the size limit for a varchar2 PL/SQL subprogram argument in Oracle?
- VARCHAR(MAX) versus VARCHAR(n) in Oracle
- Why does Oracle varchar2 have a mandatory size as a definition parameter?
- Why does VARCHAR need length specification?
- What is the default size of a varchar2 input to Oracle stored procedure, and can it be changed?
- Why using anything else but VARCHAR2(4000) to store strings in an Oracle database?
- Why does an oracle plsql varchar2 variable need a size but a parameter does not?
- Ask Tom question: "I work with a modelers group, who would like to define every varchar2 field with the maximum length."
The only place where I have seen this issue discussed is the points #3 and #4 in an answer by APC:
E.g. Oracle PL/SQL Programming, 5th Edition By Steven Feuerstein doesn't mention any drawbacks of declaring too long varchar2 variables, so it can't be a critical mistake, right ?
Update
After some more googling I found out that Oracle documentation has evolved during releases:
A quote from PL/SQL User's Guide and Reference 10g Release 2 Chapter 3 PL/SQL Datatypes:
A quote from PL/SQL User's Guide and Reference 11g Release 1 Chapter 3 PL/SQL Datatypes:
But PL/SQL User's Guide and Reference 11g Release 2 Chapter 3 PL/SQL Datatypes doesn't mention memory allocation any more and I fail to find any other information about memory allocation at all. (I'm using this release so I check only 11.2 documentation.) The same holds also for PL/SQL User's Guide and Reference 12c Release 1 Chapter 3 PL/SQL Datatypes.
I also found an answer by Jeffrey Kemp that addresses this question too. However Jeffrey's answer refers to 10.2 documentation and the question is not about PL/SQL at all.
It looks like this is one of the areas where the PL/SQL functionality has evolved over releases when Oracle has implemented different optimizations.
Note this also means some of the answers listed in the OP are also release specific even that is not explicitly mentioned in those questions/answers. When the time pass by and use of older Oracle releases ends (me daydreaming ?) that information will became outdated (might take decades thought).
The conclusion above is backed with the following quote from chapter 12 Tuning PL/SQL Applications for Performance of PL/SQL Language Reference 11g R1:
This issue is no longer mentioned in 11g R2 nor 12c R1 version of the document. This is in line with the evolution of the chapter 3 PL/SQL Datatypes.
Answer:
Since 11gR2 it makes no difference from memory use of point of view to use varchar2(10) or varchar2(32767). Oracle PL/SQL compiler will take care of the dirty details for you in an optimal fashion !
For releases prior to 11gR2 there is a cutoff-point where different memory management strategies are used and this is clearly documented in each release's PL/SQL Language Reference.
The above only applies to PL/SQL-only variables when there is no natural length restriction that can be derived from the problem domain. If a varchar2-variable represents a GTIN-14 then one should declare that as varchar2(14).
When PL/SQL-variable interfaces with a table column use %type-attribute as that is the zero-effort way to keep you PL/SQL-code and database structure in sync.
Memory test results:
I run a memory analysis in Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 with the following results:
str_size iterations UGA PGA
-------- ---------- ----- ------
10 100 65488 0
10 1000 65488 65536
10 10000 65488 655360
32767 100 65488 0
32767 1000 65488 65536
32767 10000 65488 655360
Because the PGA changes are identical and depend only on iterations and not str_size I conclude the varchar2 declared size doesn't matter. The test might be too naïve though - comments welcome !
The test script:
-- plsql_memory is a convenience package wrapping sys.v_$mystat s and
-- sys.v_$statname tables written by Steven Feuerstein and available in the
-- code-zip file accompanying his book.
set verify off
define str_size=&1
define iterations=&2
declare
type str_list_t is table of varchar2(&str_size);
begin
plsql_memory.start_analysis;
declare
v_strs str_list_t := str_list_t();
begin
for i in 1 .. &iterations
loop
v_strs.extend;
v_strs(i) := rpad(to_char(i), 10, to_char(i));
end loop;
plsql_memory.show_memory_usage;
end;
end;
/
exit
Test run example:
$ sqlplus -SL <CONNECT_STR> @memory-test.sql 32767 10000
Change in UGA memory: 65488 (Current = 1927304)
Change in PGA memory: 655360 (Current = 3572704)
PL/SQL procedure successfully completed.
$
这篇关于为什么我不应该将所有仅用于PL/SQL的VARCHAR2设置为32767字节?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！