在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

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

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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型別。

聊聊Oracle 11g中的char型別使用

相關文章