[20120222]Nchar以及nvarchar2型別

lfree發表於2012-02-22

我的環境:
$ echo $NLS_LANG
AMERICAN_AMERICA.ZHS16GBK

SQL> select * from v$version ;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


2.建立測試:
SQL> create table t3 (a varchar2(10),b nvarchar2(10),c char(10),d nchar(10));
Table created.


SQL> insert into t3 values('文123','文123','文123','文123');
1 row created.
SQL> commit ;
Commit complete.

SQL> column a1 format a30
SQL> column b1 format a30
SQL> column c1 format a30
SQL> column d1 format a30
SQL> select dump(a) a1,dump(b) b1,dump(c) c1,dump(d) d1 from t3;
SQL> select dump(a) a1,dump(b) b1,dump(c) c1,dump(d) d1 from t3;
A1                             B1                             C1                             D1
------------------------------ ------------------------------ ------------------------------ ------------------------------
Typ=1 Len=5: 206,196,49,50,51  Typ=1 Len=8: 101,135,0,49,0,50 Typ=96 Len=10: 206,196,49,50,5 Typ=96 Len=20: 101,135,0,49,0,
                               ,0,51                          1,32,32,32,32,32               50,0,51,0,32,0,32,0,32,0,32,0,
                                                                                             32,0,32
--使用length,lengthb看看存貯的長度。

SQL> select length(a),length(b),length(c),length(d) from t3;
 LENGTH(A)  LENGTH(B)  LENGTH(C)  LENGTH(D)
---------- ---------- ---------- ----------
         4          4          9         10

SQL> select lengthb(a),lengthb(b),lengthb(c),lengthb(d) from t3;
LENGTHB(A) LENGTHB(B) LENGTHB(C) LENGTHB(D)
---------- ---------- ---------- ----------
         5          8         10         20

--可以發現:
1.Nchar和char型別的最好不要採用。它不足的部分使用空格補充。會導致許多問題,除了儲存消耗外。
2.nvarchar2也一樣,除了佔用空間外,每個"字元"(包括中文)後面都會加0,而且在一些程式的處理上會存在問題。例子:
SQL> select substr(b,1,2),substrb(b,1,2) from t3;
SUBS SU
---- --
文1  文

3.存在隱式轉換的問題:
SQL> variable y nvarchar2(10);
SQL> exec :y := '文123';
SQL> select * from t3 where a =:y ;
A          B                    C          D
---------- -------------------- ---------- --------------------
文123      文123                文123      文123

SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  4an40344w91cg, child number 0
-------------------------------------
select * from t3 where a =:y

Plan hash value: 4161002650

--------------------------------------------------------
| Id  | Operation         | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |     3 (100)|
|*  1 |  TABLE ACCESS FULL| T3   |      1 |     3   (0)|
--------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------
   1 - (NVARCHAR2(30), CSID=2000): '文123'
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(SYS_OP_C2C("A")=:Y)
Note
-----
   - dynamic sampling used for this statement (level=2)
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
30 rows selected.

--雖然可以查詢到,但是存在隱式轉換,我定義的y變數是nvarchar2型的,而表中a欄位的資料型別是varchar2,
--存在這樣會隱式轉換,filter(SYS_OP_C2C("A")=:Y),我個人的建議最好僅僅使用varchar2型別的,其它最好
--不採用。










來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-716915/,如需轉載,請註明出處,否則將追究法律責任。

相關文章