ITPub博客

首页 > 数据库 > Oracle > [20151119]nvarchar2类型.txt

[20151119]nvarchar2类型.txt

原创 Oracle 作者:lfree 时间:2015-11-19 15:25:45 0 删除 编辑

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1841140/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2898
  • 访问量
    6673283