Oracle DB에서 CLOB 타입 컬럼의 DISTINCT SELECT 문제 해결 방법 - 초보자를 위한 꼬리의 꼬리를 무는 ORACLE

오라클 데이터베이스에서 테이블을 조회하다가 CLOB 타입을 가진 컬럼을 DISTINCT로 SELECT하는 데 문제가 발생했을 때, 이를 해결하는 방법을 소개드리겠습니다.
 

문제 원인

CLOB 타입은 큰 텍스트 데이터를 저장하는데 사용되며, DISTINCT 연산을 수행할 때 인덱스를 사용하지 못하는 특성 때문에 문제가 발생합니다.
 

문제 쿼리

SELECT DISTINCT mt.id, mt.name, mt.my_clob_column
FROM my_table mt;

 

에러 메시지

ORA-00932: 형식이나 연산이 잘못되었습니다.

--or

ORA-00932: 일관성 없는 데이터 유형: -이(가) 필요하지만 CLOB임

--or

00932. 00000 -  "inconsistent datatypes: expected - got CLOB"

 

해결 방법

CLOB는 Character Large OBject의 약어로,
CLOB 타입 컬럼의 DISTINCT SELECT를 위해 DBMS_LOB.SUBSTR 함수를 사용할 수 있습니다. 이 함수는 CLOB 데이터를 VARCHAR2 타입으로 변환해줍니다.
 

해결 예시 쿼리

아래는 CLOB 컬럼인 'my_clob_column'을 DISTINCT하게 선택하는 쿼리의 예시입니다.

SELECT DISTINCT mt.id, mt.name, DBMS_LOB.SUBSTR(mt.my_clob_column, 4000, 1) AS unique_text
FROM my_table mt;

이렇게 DBMS_LOB.SUBSTR 함수를 사용하면 CLOB 데이터를 적절하게 변환하여 DISTINCT 연산을 수행할 수 있습니다.
 

하지만 위 쿼리는 조건이 있습니다. 컬럼의 CLOB 이고 4000자 미만일 경우에만 가능합니다.

4000자 이상일 경우 해결방법

select m2.id, m2.name, mt1.my_clob_column
from 
	my_table m1 
    	inner join (
            select distinct mt.id, mt.name
            from my_table mt
    	) m2 on m1.id = m2.id;

 
위 쿼리 처럼 inner join 문 안에 CLOB 컬럼을 제외한 모든 컬럼을 distinct로 select 하고, CLOB 컬럼은 inner join 으로 데이터만 그대로 조회 하여 해결 할 수 있습니다.