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 Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 – Production
创建实验数据表T。
SQL> create table t (chr_a char(100), vchar_a varchar2(100));
Table created
SQL> 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 inserted
SQL> commit;
Commit complete
SQL> 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(*)
----------
6
2、常量where条件测试
首先我们测试一下单独写常量条件的情况。
SQL> select count(*) from t where chr_a='SCOTT';
COUNT(*)
----------
6
SQL> select length(chr_a) from t where chr_a='SCOTT';
LENGTH(CHR_A)
-------------
100
100
100
100
100
100
6 rows selected
上面两个SQL语句表明：在使用常量作为条件的where语句时候，即使字段类型是char，系统会自动进行右侧空格的“补齐”动作，让其满足char条件，检索出正确的结果。借助length函数，我们也可以确定长度为100。
笔者一直想了解这个“偷天换日”的过程，从执行计划角度也不能看出实际的情况。
SQL> explain plan for select count(*) from t where chr_a='SCOTT';
Explained
SQL> 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$1
Outline 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(*)
----------
6
SQL> select count(*) from t where chr_a='SCOTT ';
COUNT(*)
----------
6
同样可以正确找到结果。对应执行计划为：
SQL> explain plan for select count(*) from t where chr_a='SCOTT ';
Explained
SQL> 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$1
Outline 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,' ');
Explained
SQL> 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(*)
----------
0
SQL> 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 : 6
PL/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 : 6
PL/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 : 0
PL/SQL procedure successfully completed
SQL> 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 : 0
PL/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 : 6
PL/SQL procedure successfully completed
结果和预计相同。
5、结论
综合上述的实验分析，我们可以得到如下结论：
- 对char类型，优化器方面会有一定的优化动作，主要在于末尾空格的自动补齐动作；
- 但是，自动补齐动作出现的条件是类型的匹配，就是进行比较时候变量类型一定是char类型；
- 如果是varchar2类型，自动补齐动作是不会出现的；
- 很多字符串相关函数的返回值，都是varchar2类型。