DEVELOPyo

[ ORACLE에서의 자료형 VARCHAR2, CLOB 사용 및 가공시 주의점 ]

보통 4000bytes 이하의 문자열에 사용되는 VARCHAR2

대용량 데이터 LOB 의 문자자료형인 CLOB 데이터

[ Charset 에 따른 한글 문자 bytes 크기 ]

ORACLE 의 권장 character set 은 UTF-8 이며, UTF-8 은 문자 하나에 최대 4bytes.

* 한글은 UTF-8 에서 문자 하나당 3bytes, EUC-KR 에서 문자 하나당 2bytes

* SQL에서 VARCHAR2 는 최대 4000bytes 길이 제한.

※ DB charset 확인하는 방법

1) 설정확인(권장)

SELECT name, value$
FROM sys.props$
WHERE name = 'NLS_CHARACTERSET';

2) 문자를 조회하여 확인

SELECT LENGTHB('일') FROM DUAL;

결과로 리턴되는 BYTES 크기 확인

* LENGTHB 는 BYTES 크기 반환

아래와 같이 TABLE 생성시 

CREATE TABLE sample.persons(
    name VARCHAR2(9) NOT NULL,
    PRIMARY KEY(name)
);

VARCHAR2(9) 의 9은 bytes 를 의미하며 UTF-8 기준으로 한글은 3글자밖에 들어갈 수 없다.

[ VARCHAR2 칼럼에 데이터 넣기 ]

1. Query 에서 잘라 넣기

1) SUBSTR 사용하여 CHAR 문자 잘라 넣기

INSERT INTO persons VALUES (SUBSTR('네자이름'), 1, 3));

* SUBSTR 은 문자 length 를 기준으로 자름

* TABLE DESC 에 명시된 자료형의 크기는 default가 bytes 기준이므로 CHAR length를 기준으로 위와 같이 문자열을 잘라넣는건 부적절.

2) SUBSTRB 사용하여 BYTES 단위로 문자 잘라 넣기

INSERT INTO persons VALUES (SUBSTRB('네자이름'), 1, 9));

* SUBSTRB 는 문자 bytes 를 기준으로 자름

* 위와같이 데이터를 넣을 경우 bytes단위로 문자열이 잘려나가기 때문에 한글이 깨질 수 있으므로 부적절.

※ 또 다른 문제.

SQL에서 VARCHAR2 는 최대 4000bytes 길이 제한이 있어

1), 2) 에서 사용된 SUBSTR, SUBSTRB 함수 모두 input 값이 4000bytes 가 넘을시 "ORA-01704: 문자열이 너무 깁니다" 에러가 발생한다.

2. 소스레벨에서 BYTES 단위로 잘라 넣기

정해진 bytes 크기의 한도내에서 한글이 깨지지 않게 문자열 자르기

SQL 에서 문자열을 가공하기보단 소스레벨에서 가공

[ CLOB 데이터 읽기 및 가공하기 ]

CLOB 데이터는 LENGTH, LENGTHB, SUBSTRB 함수 등을 지원하지 않음.

VARCHAR2 데이터에서 사용가능한 함수 사용을 위해 VARCHAR2 로 변환하고자 할 경우,

DBMS_LOB.SUBSTR(clob칼럼, 가져올길이, offset)을 사용하여 VARCHAR2 로 변환이 가능

* CLOB 데이터 가공시 SUBSTR 보다 DBMS_LOB.SUBSTR 의 퍼포먼스가 뛰어남 (참고)

* DBMS_LOB.SUBSTR() 사용시에도 4000bytes 를 넘는 데이터를 가져오지 않도록 주의 해야한다

※ 또 다른 문제.

temp segment 가 unrelease 되는 현상.

CLOB 칼럼에 함수를 직접 사용할 경우, 메모리와 같은 역할을 하는 temp segment를 사용하게 되며 release 하지 않고 쌓일 수 있다. 이는 서버 장애를 유발하여 서비스 전체에 영향을 줄 수 있다.

실제로 이와 관련하여, 운영중인 DB서버 전체가 마비되어 서비스 장애를 경험해 보았다..

=> CLOB 칼럼에 함수 사용은 지양하자. 소스레벨에 그대로 가져와서 소스레벨에서 자르는 걸 권장.

[ 관련에러 ]

ORA-01704: 문자열이 너무 깁니다

ORA-64203: 문자 집합 변환 후 대상 버퍼가 너무 작아 CLOB 데이터를 보유할 수 없습니다.

ORA-06502: 문자열 버퍼가 너무 작습니다.

참고 :

https://tomining.tistory.com/95