[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),最好全部都是这个类型,或者相关这些字段都是这个类型。如果没有这种需求不要乱用这样的数据
--类型，实际上我已经遇到过这种类型,给出的解答让我苦笑不得，我们要考虑更长远的需求，实际上他们的程序就是一堆垃圾，你要使
--用这种类型，必须了解这种数据类型的利弊。