[20151119]nvarchar2型別.txt

[20151119]nvarchar2型別.txt

-- 前幾天幫別人看awr報表，連線如下：http://www.itpub.net/thread-1942785-1-1.html
-- 最終確定是使用navrchar2存在隱式轉換，實際上如果應用沒有國際化需求，不要使用這個型別，透過一些例子來說明問題。

1.環境：
SCOTT@book> create table t ( id number,c1 varchar2(20),c2 nvarchar2(20));
Table created.

SCOTT@book> insert into t values (1,'aaaa','bbbb');
1 row created.

SCOTT@book> insert into t values (2,'文化','文化');
1 row created.

SCOTT@book> commit ;
Commit complete.

2.先來看看內部格式:
SCOTT@book> select id,dump(c1,16) c30 ,dump(c2,16) c40 from t ;

SCOTT@book> select id,dump(c1,16) c30 ,dump(c2,16) c40 from t ;
        ID C30                            C40
---------- ------------------------------ ----------------------------------------
         1 Typ=1 Len=4: 61,61,61,61       Typ=1 Len=8: 0,62,0,62,0,62,0,62
         2 Typ=1 Len=4: ce,c4,bb,af       Typ=1 Len=4: 65,87,53,16

--可以發現儲存4個英文字元,varchar2型別c1僅僅佔用4個位元組，而nvarchar2佔用8個位元組。
--而儲存漢字"文化"，注意看nvarchar2儲存的是"65,87,53,16"(16進位制)。如果不告訴你型別很難猜到型別。
-- 0x65 對應的是 'e'.

SCOTT@book> select dump('e',16) from dual ;
DUMP('E',16)
----------------
Typ=96 Len=1: 65

SCOTT@book> select rowid,t.*  from t ;
ROWID                      ID C1                   C2
------------------ ---------- -------------------- ----
AAAVuQAAEAAAAIPAAB          1 aaaa                 bbbb
AAAVuQAAEAAAAIPAAC          2 文化                 文化

SCOTT@book> @ &r/rowid AAAVuQAAEAAAAIPAAB
    OBJECT       FILE      BLOCK        ROW DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
     88976          4        527          1 4,527                alter system dump datafile 4 block 527 ;

--補充bbed的觀察：
BBED> p *kdbr[1]
rowdata[16]
-----------
ub1 rowdata[16]                             @8157     0x2c

BBED> x /rncc
rowdata[16]                                 @8157
-----------
flag@8157: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8158: 0x00
cols@8159:    3

col    0[2] @8160: 1
col    1[4] @8163: aaaa
col    2[8] @8168: .b.b.b.b

BBED> p *kdbr[2]
rowdata[0]
----------
ub1 rowdata[0]                              @8141     0x2c

BBED> x /rncc
rowdata[0]                                  @8141
----------
flag@8141: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8142: 0x01
cols@8143:    3

col    0[2] @8144: 2
col    1[4] @8147: ....
col    2[4] @8152: e.S.

3.再來看看語句的執行計劃：

SCOTT@book> select *  from t where c1=c2;
        ID C1                   C2
---------- -------------------- -------
         2 文化                 文化

SCOTT@book> @ &r/dpcz ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  1d5vntz02hc3g, child number 0
-------------------------------------
select *  from t where c1=c2
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |      1 |    15 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C2"=SYS_OP_C2C("C1"))

--注意看過濾條件("C2"=SYS_OP_C2C("C1"))。
--如果你使用常量還沒有什麼問題，雖然都是字元型別，但是低階向高階轉換，例子如下：

SCOTT@book> select *  from t where c2='文化';
        ID C1                   C2
---------- -------------------- -----
         2 文化                 文化

SCOTT@book> @ &r/dpcz ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  8ty74dq99bn17, child number 0
-------------------------------------
select *  from t where c2='文化'
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |      1 |    15 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C2"=U'\6587\5316')

--可以發現過濾條件會轉換為("C2"=U'\6587\5316')。

4.最可怕的是混用，這樣問題更大：
--加上這個表的欄位c1在另外一些表也是nvarchar2型別：

SCOTT@book> select *  from t where c1=U'文化';
        ID C1                   C2
---------- -------------------- ------
         2 文化                 文化

SCOTT@book> @ &r/dpcz ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  gmcpuhpasg78a, child number 0
-------------------------------------
select *  from t where c1=U'文化'

Plan hash value: 1601196873

---------------------------------------------------------------------------
| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |      1 |    15 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(SYS_OP_C2C("C1")=U'\6587\5316')

--這樣要避開全部掃描，要建立SYS_OP_C2C("C1")的函式索引。

5.總結：
--如果你的應用要求使用nvarcha2(20),最好全部都是這個型別,或者相關這些欄位都是這個型別。如果沒有這種需求不要亂用這樣的資料
--型別，實際上我已經遇到過這種型別,給出的解答讓我苦笑不得，我們要考慮更長遠的需求，實際上他們的程式就是一堆垃圾，你要使
--用這種型別，必須瞭解這種資料型別的利弊。