Oracle 11g中的char類型使用

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