NVARCHAR 타입의 부작용

캐릭터 셋(character set)은 정보를 표현하기 위한 글자 집합이다. 오라클 데이터베이스는 데이터베이스를 생성할 때 데이터베이스 캐릭터 셋과 내셔널 캐릭터 셋을 지정한다. 내셔널 캐릭터 셋은 데이터베이스 캐릭터 셋이 KO16KSC5601인 경우는 확장 한글, KO16MSWIN949인 경우에는 다국어를 지원하기 위해 사용한다.

테스트 시스템은 AL32UTF8 데이터베이스 캐릭터 셋, AL16UTF16 내셔널 캐릭터 셋을 사용하고 있다. AL32UTF8 데이터베이스 캐릭터 셋을 사용하기 때문에 AL16UTF16 내셔널 캐릭터 셋을 사용할 필요가 없다.^[각주:1]

-- 1
SELECT *
  FROM nls_database_parameters
 WHERE parameter IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

PARAMETER              VALUE
---------------------- ---------
NLS_CHARACTERSET       AL32UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16

2 개의 행이 선택되었습니다.

오라클 데이터베이스는 내셔널 캐릭터 셋을 사용할 수 있는 NCHAR, NVARCHAR2, NCLOB 등의 데이터 타입을 제공한다. 테스트를 위해 아래와 같이 테이블을 생성하자. t1 테이블의 c1 칼럼은 VARCHAR2, c2 칼럼은 NVARCHAR2 타입으로 생성했다.

-- 2
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1 VARCHAR2(10), c2 NVARCHAR2(10));
CREATE INDEX t1_x1 ON t1 (c1);
CREATE INDEX t1_x2 ON t1 (c2);

INSERT INTO t1 VALUES ('A' , 'A' );
INSERT INTO t1 VALUES ('가', '가');
COMMIT;

AL16UTF16 내셔널 캐릭터 셋은 영숫자 저장에 2바이트를 사용하므로 저장 공간 상의 이점이 없다.

-- 3
SELECT a.*, DUMP (a.c1, 1016) AS c1d, DUMP (a.c2, 1016) AS c2d
  FROM t1 a;

C1 C2 C1D                                         C2D
-- -- ------------------------------------------- ----------------------------------------
A  A  Typ=1 Len=1 CharacterSet=AL32UTF8: 41       Typ=1 Len=2 CharacterSet=AL16UTF16: 0,41
가 가 Typ=1 Len=3 CharacterSet=AL32UTF8: ea,b0,80 Typ=1 Len=2 CharacterSet=AL16UTF16: ac,0

2 개의 행이 선택되었습니다.

VARCAHR2 타입과 NVARCHAR2 타입을 비교하면 VARCAHR2 타입이 NVARCHAR2 타입으로 변환된다. 이로 인해 쿼리의 성능이 저하될 수 있다. 아래 쿼리는 리터럴을 사용했다. 리터럴을 사용한 경우에는 성능 저하가 발생하지 않는다. 쿼리 4-2에서 리터럴이 NVARCHAR2 타입으로 변환된 것을 확인할 수 있다.^[각주:2]

-- 4-1
SELECT * FROM t1 WHERE c1 = 'A';

---------------------------------------------
| Id  | Operation                   | Name  |
---------------------------------------------
|   0 | SELECT STATEMENT            |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |
|*  2 |   INDEX RANGE SCAN          | T1_X1 |
---------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"='A')

-- 4-2
SELECT * FROM t1 WHERE c2 = 'A';

---------------------------------------------
| Id  | Operation                   | Name  |
---------------------------------------------
|   0 | SELECT STATEMENT            |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |
|*  2 |   INDEX RANGE SCAN          | T1_X2 |
---------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C2"=U'A')

VARCAHR2, NVARCHAR2 타입 칼럼을 조인하는 경우에는 조인 순서에 따라 성능 저하가 발생할 수 있다. 쿼리 5-1은 c2 칼럼이 NVARCHAR2이므로 c1 칼럼이 암시적으로 변환되어 성능 저하가 발생하지 않는 반면, 쿼리 5-2는 c1 칼럼이 VARCHAR2이므로 c1 칼럼이 암시적으로 변환되어 성능 저하가 발생했다.^[각주:3]

-- 5-1
SELECT /*+ LEADING(A) USE_NL(B) */
       *
  FROM t1 a, t1 b
 WHERE b.c2 = a.c1;

----------------------------------------------
| Id  | Operation                    | Name  |
----------------------------------------------
|   0 | SELECT STATEMENT             |       |
|   1 |  NESTED LOOPS                |       |
|   2 |   NESTED LOOPS               |       |
|   3 |    TABLE ACCESS FULL         | T1    |
|*  4 |    INDEX RANGE SCAN          | T1_X2 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T1    |
----------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("B"."C2"=SYS_OP_C2C("A"."C1"))

-- 5-2
SELECT /*+ LEADING(A) USE_NL(B) */
       *
  FROM t1 a, t1 b
 WHERE b.c1 = a.c2;

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  NESTED LOOPS      |      |
|   2 |   TABLE ACCESS FULL| T1   |
|*  3 |   TABLE ACCESS FULL| T1   |
-----------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(SYS_OP_C2C("B"."C1")="A"."C2")

더욱 심각한 문제는 NVARCHAR2 타입이 데이터에 따라 부정확한 통계 정보가 생성될 수 있다는 점이다. 테스트를 위해 아래와 같이 테이블을 생성하자. c1 칼럼은 VARCHAR2 타입, c2 칼럼은 NVARCHAR2 타입이다. 두 컬럼 모두 선두 16자리가 X로 동일하다.  

-- 6
DROP TABLE t1 PURGE;

CREATE TABLE t1 (
    c1  VARCHAR2(100), c2  VARCHAR2(100), c3  VARCHAR2(100)
  , c4 NVARCHAR2(100), c5 NVARCHAR2(100), c6 NVARCHAR2(100));

INSERT
  INTO t1
SELECT LPAD ('X', 16, 'X') || LPAD (ROWNUM, 3, '0') AS c1
     , LPAD ('X', 16, 'X') || LPAD (ROWNUM, 3, '0') AS c2
  FROM XMLTABLE ('1 to 300');

COMMIT;

아래는 DBMS_STATS 패키지로 수집한 통계 정보 결과다. NVARCHAR2 타입의 c2 칼럼에 부정확한 통계 정보가 생성된 것을 확인할 수 있다. 해당 패키지는 히스토그램 생성시 선두 32바이트를 사용하지만, NVARCHAR2 타입의 경우 영숫자가 2바이트를 사용하므로 16자리만 사용할 수 있는 것이 원인이다.

-- 7
EXEC DBMS_STATS.GATHER_TABLE_STATS ('TUNA', 'T1', method_opt => 'FOR ALL COLUMNS');

SELECT column_name, data_type, data_length, num_distinct, density, histogram, num_buckets
     , UTL_RAW.CAST_TO_VARCHAR2 (low_value) AS low_value
  FROM user_tab_columns
 WHERE table_name = 'T1';

COLUMN_NAME DATA_TYPE DATA_LENGTH NUM_DISTINCT      DENSITY HISTOGRAM       NUM_BUCKETS LOW_VALUE
----------- --------- ----------- ------------ ------------ --------------- ----------- --------------------------------
C1          VARCHAR2          100          300 0.0033333333 HEIGHT BALANCED          75 XXXXXXXXXXXXXXXX001
C2          NVARCHAR2         200          300 0.0016666667 FREQUENCY                 1  X X X X X X X X X X X X X X X X

2 개의 행이 선택되었습니다.

ANALYZE TABLE 명령어를 사용하면 통계 정보가 더욱 부정확해진다. 아래 예제에서 c2 칼럼의 NUM_DISTINCT 값이 1인 것을 확인할 수 있다. 부정확한 통계로 인해 비효율적인 실행 계획이 생성되어 쿼리의 성능이 저하될 수 있다. 

-- 8
ANALYZE TABLE t1 COMPUTE STATISTICS;

SELECT column_name, data_type, data_length, num_distinct, density, histogram, num_buckets
     , UTL_RAW.CAST_TO_VARCHAR2 (low_value) AS low_value
  FROM user_tab_columns
 WHERE table_name = 'T1';

COLUMN_NAME DATA_TYPE DATA_LENGTH NUM_DISTINCT     DENSITY HISTOGRAM NUM_BUCKETS LOW_VALUE
----------- --------- ----------- ------------ ----------- --------- ----------- --------------------------------
C1          VARCHAR2          100          300 0.003333333 NONE                1 XXXXXXXXXXXXXXXX001
C2          NVARCHAR2         200            1           1 NONE                1  X X X X X X X X X X X X X X X X

6 개의 행이 선택되었습니다.

VARCHAR 타입과 NVARCHAR 타입을 함께 사용하는 경우 암시적 테이터 변환에 주의해야 하며, NVARCHAR 타입에 저장될 코드는 선두 중복 값이 없도록 설계할 필요가 있다.

1. AL32UTF8 캐릭터 셋이 AL16UTF16 캐릭터 셋의 super set이다. [본문으로]
2. 리터럴 앞쪽에 U가 기술되었다. [본문으로]
3. 내부적으로 SYS_OP_C2C 함수를 통해 암시적 데이터 변환을 수행한다. [본문으로]