Oracle 11g中的char类型使用

在Oracle数据表和程序类型中，字符类型可能是使用最多的一种了。从历史上看，Oracle首先推出了固定长度char类型字段，之后才推出了变长度类型varchar2。目前，主流Oracle应用开发都已经普遍接受了varchar2作为数据表字段类型表示。如果存在超长字符，都考虑使用CLOB这类大对象进行存储。但是，我们在实际领域中，还是会在各种遗留系统中发现很多char类型字段。而且连带这些遗留系统的下游系统中，char类型也会经常出现。在这样的背景下，开发运维人员其实还是有很多的接触char的机会的。Char最大的特点就是固定长度存储，例如定义长度char（10），保存的字符串为’kkk’三位长度。在保存的时候，Oracle会自动将其尾部补齐空格。这样就确保了每个保存在char（10）的字段都是10位长度。根据笔者的猜测，char的策略是在Oracle对于存储空间管理能力较弱的背景下提出的一种折中策略。随后，varchar2的出现逐步将这种变通策略加以替代。在一些时候，笔者还是会遇到一些朋友的问题：明明检索全表时候看到字符串取值是xxx，但是用SQL加在后面where条件的时候，就没有检索结果，仿佛“见鬼一样”。究其根源，常常是char在里面“捣乱”，where条件后面加上若干个空格或者使用rpad命令进行补全。本篇主要介绍几个在11g里面常见的使用场景。注意：随着版本的升级，Oracle对于char的支持其实也在不断升级（默默地），尽可能让其靠近varchar2的使用效果。所以，本文中的实验内容，在其他版本下可能会有不同的测试结果，请注意。1、环境说明本文使用Oracle 11gR2进行测试，具体版本为11.2.0.4。SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionPL/SQL Release 11.2.0.4.0 - ProductionCORE 11.2.0.4.0 ProductionTNS for Linux: Version 11.2.0.4.0 - ProductionNLSRTL Version 11.2.0.4.0 – Production创建实验数据表T。SQL> create table t （chr_a char（100）, vchar_a varchar2（100））;Table createdSQL> desc t;Name Type Nullable Default Comments ------- ------------- -------- ------- -------- CHR_A CHAR（100） Y VCHAR_A VARCHAR2（100） Y SQL> insert into t select owner, owner from dba_objects;119498 rows insertedSQL> commit;Commit completeSQL> exec dbms_stats.gather_table_stats（user,"T",cascade => true）;PL/SQL procedure successfully completed数据取值’SCOTT’的记录数目为6。SQL> select count（*） from t where vchar_a="SCOTT"; COUNT（*）---------- 62、常量where条件测试首先我们测试一下单独写常量条件的情况。SQL> select count（*） from t where chr_a="SCOTT"; COUNT（*）---------- 6SQL> select length（chr_a） from t where chr_a="SCOTT";LENGTH（CHR_A）------------- 100 100 100 100 100 1006 rows selected上面两个SQL语句表明：在使用常量作为条件的where语句时候，即使字段类型是char，系统会自动进行右侧空格的“补齐”动作，让其满足char条件，检索出正确的结果。借助length函数，我们也可以确定长度为100。笔者一直想了解这个“偷天换日”的过程，从执行计划角度也不能看出实际的情况。SQL> explain plan for select count（*） from t where chr_a="SCOTT";ExplainedSQL> select * from table（dbms_xplan.display（format => "advanced"））;PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 2966233522---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 101 | 510 （1）| 00:00:07 || 1 | SORT AGGREGATE | | 1 | 101 | | ||* 2 | TABLE ACCESS FULL| T | 11 | 1111 | 510 （1）| 00:00:07 |---------------------------------------------------------------------------Query Block Name / Object Alias （identified by operation id）:------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T@SEL$1Outline Data------------- /*+PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------- BEGIN_OUTLINE_DATA FULL（@"SEL$1" "T"@"SEL$1"） OUTLINE_LEAF（@"SEL$1"） ALL_ROWS DB_VERSION（"11.2.0.4"） OPTIMIZER_FEATURES_ENABLE（"11.2.0.4"） IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */Predicate Information （identified by operation id）:--------------------------------------------------- 2 - filter（"CHR_A"="SCOTT"）Column Projection Information （identified by operation id）:----------------------------------------------------------- 1 - （#keys=0） COUNT（*）[22]39 rows selected从执行计划上，我们看不出什么过多的端倪。filter条件中也没有对chr_a取值进行格外的处理。这个过程其实是针对char类型进行的特殊处理，如果我们在where条件后面人为加上空格，会如何呢？SQL> select count（*） from t where chr_a="SCOTT "; COUNT（*）---------- 6SQL> select count（*） from t where chr_a="SCOTT "; COUNT（*）---------- 6同样可以正确找到结果。对应执行计划为：SQL> explain plan for select count（*） from t where chr_a="SCOTT ";ExplainedSQL> select * from table（dbms_xplan.display（format => "advanced"））;PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 2966233522---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 101 | 510 （1）| 00:00:07 || 1 | SORT AGGREGATE | | 1 | 101 | | ||* 2 | TABLE ACCESS FULL| T | 11 | 1111 | 510 （1）| 00:00:07 |---------------------------------------------------------------------------Query Block Name / Object Alias （identified by operation id）:------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T@SEL$1Outline Data------------- /*+PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------- BEGIN_OUTLINE_DATA FULL（@"SEL$1" "T"@"SEL$1"） OUTLINE_LEAF（@"SEL$1"） ALL_ROWS DB_VERSION（"11.2.0.4"） OPTIMIZER_FEATURES_ENABLE（"11.2.0.4"） IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */Predicate Information （identified by operation id）:--------------------------------------------------- 2 - filter（"CHR_A"="SCOTT "）Column Projection Information （identified by operation id）:----------------------------------------------------------- 1 - （#keys=0） COUNT（*）[22]39 rows selected执行计划上同样看不到什么变化。上面的实验，告诉我们char常量使用规则：如果在where条件后面使用常量取值，Oracle会根据字段类型和常量情况进行自动补齐空格动作。常量后面人为添加的空格也会作为自动空格加以处理。下面讨论如果是rpad函数在常量上，效果如何呢？3、函数操作效果在一些比较老的版本Oracle版本里面，一些开发人员为了避免char空格影响，广泛使用了rpad函数，自动的添加后面的空格。SQL> select count（*） from t where chr_a=rpad（"SCOTT",100," "）; COUNT（*）---------- 6正常是可以找到结果的。执行计划上，filter部分显然是先计算出函数取值，之后再进行处理。SQL> explain plan for select count（*） from t where chr_a=rpad（"SCOTT",100," "）;ExplainedSQL> select * from table（dbms_xplan.display（format => "advanced"））;PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 2966233522---------------------------------------------------------------------------（篇幅原因，有省略……）Predicate Information （identified by operation id）:--------------------------------------------------- 2 - filter（"CHR_A"="SCOTT "）Column Projection Information （identified by operation id）:----------------------------------------------------------- 1 - （#keys=0） COUNT（*）[22]40 rows selected但是，如果设置函数补齐的长度不是“一步到位”，而是部分长度的。Oracle是不能够找到对应结果的。SQL> select count（*） from t where chr_a=rpad（"SCOTT",10," "）; COUNT（*）---------- 0SQL> select count（*） from t where chr_a=rpad（"SCOTT",50," "）; COUNT（*）---------- 0使用常量时候的自动补齐动作不再生效了！这就表明：优化器在处理常量时候，有一些特殊的规则逻辑，这部分在应用函数之后就不再使用了。另外一种可能性就是针对等号两端取值进行类型匹配的时候，常量和函数量的取值方式是有差别的。rpad返回的虽然是一个字符类型，但是变长度还是固定长度是不容易确定的。如果Oracle将常量认可为char，rpad结果认可为varchar2，那么结果不一样是可以理解的。4、绑定变量处理SQL优化器是目前Oracle中非常复杂的组件之一。其中很多步骤和逻辑都需要我们不断地进行测试和合理猜测得到的。绑定变量是我们在实际开发过程中常常使用的技术之一。下面我们测试一下在PL/SQL代码片段中绑定变量的使用。SQL> declare 2 a char（100）; 3 coun number; 4 begin 5 a := "SCOTT"; 6 execute immediate "select count（*） from t where chr_a=:1" 7 into coun 8 using a; 9 dbms_output.put_line（"Result Is : "||to_char（coun））; 10 end; 11 /Result Is : 6PL/SQL procedure successfully completed绑定变量使用上，关键的一个问题就是绑定变量定义的类型。在上面的代码中，如果使用了char类型的变量，虽然定义取值的时候后面没有空格，Oracle还是事先了常量变量中的“自动补齐动作”。如果和常量时候一样，赋值是有空格结尾的，那么会如何呢？SQL> set serveroutput on size 10000;SQL> declare 2 a char（100）; 3 coun number; 4 begin 5 a := "SCOTT "; 6 execute immediate "select count（*） from t where chr_a=:1" 7 into coun 8 using a; 9 dbms_output.put_line（"Result Is : "||to_char（coun））; 10 end; 11 /Result Is : 6PL/SQL procedure successfully completed效果相同。那么，可以得到结论：如果使用char类型的绑定变量，效果和常量是一样的。那么，如果使用varchar2类型绑定变量，效果如何呢？SQL> declare 2 a varchar（100）; 3 coun number; 4 begin 5 a := "SCOTT"; 6 execute immediate "select count（*） from t where chr_a=:1" 7 into coun 8 using a; 9 dbms_output.put_line（"Result Is : "||to_char（coun））; 10 end; 11 /Result Is : 0PL/SQL procedure successfully completedSQL> set serveroutput on size 10000;SQL> declare 2 a varchar（100）; 3 coun number; 4 begin 5 a := "SCOTT "; 6 execute immediate "select count（*） from t where chr_a=:1" 7 into coun 8 using a; 9 dbms_output.put_line（"Result Is : "||to_char（coun））; 10 end; 11 /Result Is : 0PL/SQL procedure successfully completed如果绑定变量明确是varchar2，自动补齐效果是不发生的！和之前函数的问题是没有关系的。如果我们在char类型中使用rpad函数进行赋值，看结果应该是可以进行自动补齐的。SQL> set serveroutput on size 10000;SQL> declare 2 a char（100）; 3 coun number; 4 begin 5 a := rpad（"SCOTT",10," "）; 6 execute immediate "select count（*） from t where chr_a=:1" 7 into coun 8 using a; 9 dbms_output.put_line（"Result Is : "||to_char（coun））; 10 end; 11 /Result Is : 6PL/SQL procedure successfully completed结果和预计相同。5、结论综合上述的实验分析，我们可以得到如下结论：

对char类型，优化器方面会有一定的优化动作，主要在于末尾空格的自动补齐动作；
但是，自动补齐动作出现的条件是类型的匹配，就是进行比较时候变量类型一定是char类型；
如果是varchar2类型，自动补齐动作是不会出现的；
很多字符串相关函数的返回值，都是varchar2类型。

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址