Saturday, October 16, 2010

ปัญหา NLS Character Set ของหมวยอินเตอร์

ปัญหา NLS Character Set ของหมวยอินเตอร์
คุณอาดีคะ หนูกุ้มใจจิง ๆ ค่ะ ไม่รู้จะไปปรึกษาใครได้แล้ว คือหนูทำงานเป็นโปรแกรมเมอร์ของบริษัทอินเตอร์ ฯ แห่งหนึ่ง Boss ใหญ่หนูเป็นอเมริกันน่ะค่ะ วันหนึ่ง Boss ก็เรียกหนูไปพบ แล้วเขาก้อสั่งมาว่าต้องการให้ฐานข้อมูลของเราเก็บข้อมูลได้หลาย ๆ ภาษาให้ไปหาวิธีมา หนูไปถามพี่แอ๊ด(มิน)ดูแล้ว พี่แอ๊ดบอกว่าฐานข้อมูลของเรามีคาร์ ๆ อะไรเซ็ท ๆ นี่ล่ะค่ะเห็นบอกว่าเป็น US แล้วเขายังพึมพำแกมบ่นอีกด้วยค่ะว่า ไผสิท๊ามมม..ด้าย หนูจะทำยังไงดีคะคุณอาดี..อิ๊ก..อิ๊ก..


คริสตีน หว่อง (T T)

คุณอาดี(บีเซอร์ทิฟาย): โจทย์ที่หนูคริสตีนได้รับมานั้นเป็นที่พบเห็นได้ไม่น้อยในบริษัทต่างชาติที่มีสาขาอยู่ในต่างประเทศ โดยเฉพาะบริษัทที่ใช้ภาษาอังกฤษเป็นภาษาหลัก ระบบเดิมของหนูคริสตีนใช้ฐานข้อมูล Oracle โดยมี US7ASCII เป็น Character set หลัก (Database Character Set) ที่ใช้กับคอลัมน์ที่มี Data Type เป็น CHAR หรือ VARCHAR2 และมี UTF8 เป็น National Character Set ซึ่งใช้กับคอลัมน์ที่มี Data Type เป็น NCHAR หรือ NVARCHAR2 (Listing 1)
(Listing 1) **************************************************************
SQL> column parameter format a30
SQL> column value format a30
SQ>> select parameter ,value from nls_database_parameters;

PARAMETER VALUE
------------------------------ ------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET US7ASCII
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM

PARAMETER VALUE
------------------------------ ------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET UTF8
NLS_RDBMS_VERSION 10.1.0.5.0

20 rows selected.

แต่เดิมไม่ได้มีการใช้ column type ที่เป็น NCHAR (หรือ NVARCHAR2) แต่อย่างใด requirement ใหม่กำหนดให้ฐานข้อมูลสามารถรองรับข้อมูลที่เกี่ยวกับที่อยู่อาศัยที่เป็นภาษาอื่นๆ ที่นอกเหนือจากภาษาปัจจุบันที่ใช้อยู่ เช่นให้รองรับ ภาษาจีน เป็นต้น จริงๆแล้วเดิมระบบของหนูคริสตีนมีการใช้ภาษาอื่น ๆ นอกเหนือจากภาษาอังกฤษในฐานข้อมูลอยู่แล้ว เช่นภาษาในยุโรปบางภาษาเป็นต้น แต่ก็เป็นตัวอักษรแบบ single byte โดยใช้ Database Character Set รวมๆกัน คือ US7ASCII
วิธีแก้ปัญหา:
การเซ็ทอัพให้ฐานข้อมูลรองรับได้ทุกๆภาษาโดยใช้ UNICODE ได้ มีสองวิธี 
วิธีแรก
คือเซ็ทอัพให้ทั้งฐานข้อมูลเป็นแบบ UNICODE วิธีนี้เหมาะกับกรณีที่ข้อมูลเป็นแบบหลายภาษาเฉลี่ยๆ กัน ไม่สามารถบอกได้ว่าภาษาใดมากกว่า และมีภาษาทั้งภาษายุโรป และเอเชียคละกันอยู่ เนื่องจากตัวอักษรที่ encode แบบ UNICODE จะมีขนาดของตัวอักษรใหญ่กว่าแบบ single-byte จึงทำให้เปลืองเนื้อที่บนดิสก์และหน่วยความจำมากกว่า และประสิทธิภาพจะด้อยกว่าฐานข้อมูลแบบ single-byte 
วิธีที่สอง
คือเซ็ทเป็นฐานข้อมูลแบบ single-byte แล้วเซ็ทแค่บางคอลัมน์ให้รองรับได้หลายภาษา วิธีนี้จะเหมาะกับฐานข้อมูลที่มีภาษาที่ใช้ตัวอักษรแบบ single-byte เป็นหลัก (เช่นภาษาอังกฤษ) แล้วมีบางคอลัมน์เป็นภาษานานาชาติ วิธีนี้จะได้ performance ของระบบที่ดีกว่าและประหยัดทรัพยากรระบบมากกว่า
การเซ็ทอัพนี้ทำได้เมื่อตอน create database เท่านั้น หากต้องการเปลี่ยนจะต้อง recreate database ใหม่หรือใช้ CSALTER script ร่วมกับ exp/imp utilities
เนื่องจากระบบเดิมใช้ Database Character Set เป็นแบบ single-byte Character Set (US7ASCII) ซึ่งไม่สามารถรองรับภาษาทางเอเชียได้ และเนื่องจากความต้องการใช้ภาษาเพิ่มเติมเหล่านี้ในเฉพาะบางคอลัมน์เท่านั้น จึงใช้วิธีการกำหนดให้บางคอลัมน์เป็น NCHAR (,NVARCHAR2) เพื่อรองรับภาษาที่เป็น multi-byte (UNICODE) โดยคอลัมน์เหล่านี้จะใช้ National Character Set ที่เป็น UTF8 ตามที่ได้กำหนดไว้เมื่อตอน create database

ผลข้างเคียงที่อาจเกิดขึ้นได้:
การเปลี่ยนคอลัมน์จากเดิมเป็น CHAR (, VARCHAR2) ไปเป็น NCHAR (,NVARCHAR2) จะไม่มีผลกระทบอะไร เนื่องจากเป็นการเปลี่ยนไปเป็น Character Set ที่ใหญ่กว่า (ดู Listing 2)

(Listing 2) **************************************************************
SQL> create table test_char (cname char(10), vname varchar2(10));

Table created.

SQL> insert into test_char values ('SCOTT','SCOTT');

1 row created.

SQL> select dump(cname,1010), dump(vname,1010) from test_char2;

DUMP(CNAME,1010)
----------------------------------------------------------------
DUMP(VNAME,1010)
----------------------------------------------------------------
Typ=96 Len=10 CharacterSet=US7ASCII: 83,67,79,84,84,32,32,32,32,32
Typ=1 Len=5 CharacterSet=US7ASCII: 83,67,79,84,84

--<< สังเกต CharacterSet เป็น US7ASCII

SQL> alter table test_char modify (cname nchar(10), vname nvarchar2(10));

Table altered.

SQL> select dump(cname,1010) , dump(vname,1010) from test_char;

DUMP(CNAME,1010)
----------------------------------------------------------------
DUMP(VNAME,1010)
----------------------------------------------------------------
Typ=96 Len=10 CharacterSet=UTF8: 83,67,79,84,84,32,32,32,32,32
Typ=1 Len=5 CharacterSet=UTF8: 83,67,79,84,84

--<< สังเกต CharacterSet เป็น UTF8 เมื่อแปลงมาเป็น NCHAR (,NVARCHAR2)

*** DUMP เป็นฟังก์ชันของ Oracle ที่จะแสดงรหัสของตัวอักษรออกมาเป็นไบท์

แต่ในทางกลับกันเราไม่สามารถที่จะเปลี่ยน NCHAR (,NVARCHAR2) ไปเป็น CHAR (,VARCHAR2) โดยไม่ลบข้อมูลออกจากคอลัมน์นั้นก่อนได้ ด้วยเหตุผลที่กล่าวมาแล้วคือ NCHAR (,NVARCHAR2) มีขนาดของ Character Set ที่ใหญ่กว่า (ดู Listing 3)

(Listing 3) **************************************************************
SQL> desc test_char
Name Null? Type
----------------------- -------- ----------------------
CNAME NCHAR(10)
VNAME NVARCHAR2(10)

SQL> alter table test_char modify (cname char(10), vname varchar2(10));
alter table test_char modify (cname char(10), vname varchar2(10))
*
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype{/xtypo_code}
นอกจากนี้เราสามารถเปลี่ยนจาก NCHAR มาเป็น NVARCHAR2 หรือในทางกลับกันจาก NVARCHAR2 มาเป็น NCHAR ได้ อย่างไรก็ดีให้สังเกตดู space ที่ถูก pad เข้าไปเมื่อ data type ถูกเปลี่ยนเป็น NCHAR ด้วย (ดู Listing 4)
(Listing 4) **************************************************************
SQL> alter table test_char modify (cname nvarchar2(10), vname nchar(10));

Table altered.

SQL> select dump(cname,1010), dump(vname,1010) from test_char;

DUMP(CNAME,1010)
-----------------------------------------------------------------
DUMP(VNAME,1010)
-----------------------------------------------------------------
Typ=1 Len=10 CharacterSet=UTF8: 83,67,79,84,84,32,32,32,32,32
Typ=96 Len=10 CharacterSet=UTF8: 83,67,79,84,84,32,32,32,32,32

ผลกระทบที่อาจจะเกิดกับ application: NCHAR (, NVARCHAR2) กำหนดความกว้างของคอลัมน์เป็นจำนวนตัวอักษร ในขณะที่ CHAR (,VARCHAR2) กำหนดเป็น byte ดังนั้นหากเดิม กำหนดเป็น CHAR(10) ซึ่งกินพื้นที่ 10 bytes เมื่อแปลงเป็น NCHAR(10) อาจจะกินพื้นที่ได้ตั้งแต่ 10 - 40 ไบท์ ขึ้นอยู่กับว่าข้อมูลที่เก็บเป็นภาษาอะไรเช่นถ้าเป็นภาษาเอเชียใช้ 3 bytes ต่อตัวอักษรก็จะกินพื้นที่ถึง 30 bytes เป็นต้น