转载

聊聊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将常量认可为charrpad结果认可为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类型。

 


正文到此结束
Loading...