들어가며
회사에서 기존프로젝트의 mysql쿼리를 -> oracle로 모두 변환하는 작업을 하는 중인데 오라클로 변환하고 쿼리가 잘 작동하는 지 테스트하는 도중 ORA-00932: inconsistent datatypes: expected - got CLOB 이와 같은 에러가 계속 발생했다..! 어찌저찌 gpt의 힘을 빌려 해결은 하지만, 오라클 초짜인 나는 도대체 clob이 뭔데??? 왜 안되는데 ?? 싶었고, 이에 나중에 또 오류를 마주쳣을때 그때는 gpt 도움이 아닌 스스로 해결하고 싶어서 공부하고 정리해본다 !
문제상황
MBRWITHIN((SELECT CUR_ADDR FROM CUR_TABLE WHERE CUR_ID = (SELECT CUR_ID FROM USERS WHERE USER_ID=#{user_id})), CUR.CUR_ADDR)위 MYSQL 쿼리를 원래 프로젝트에서 사용중이 었는데, ORACLE로 변환하면서 CUR_ADDR 데이터가 TEXT에서 CLOB형식으로 변환되었다.
또한 oracle에서는 MBRWITHIN 대신 SDO_UTIL 함수를 사용하기에,
SDO_RELATE(
SDO_UTIL.FROM_WKTGEOMETRY(
(SELECT CUR_ADDR
FROM CUR_TABLE
WHERE CUR_ID = (SELECT CUR_ID FROM USERS WHERE USER_ID = #{user_id}))
),
SDO_UTIL.FROM_WKTGEOMETRY(CUR.CUR_ADDR),이렇게 oracle로 변경된 함수를 적용하여
쿼리를 실행시켰을때
ORA-00932: inconsistent datatypes: expected - got CLOB 과 같은 에러가 발생했다.
바뀐건
1. text 형식 -> clob 형식
2. mbrwithin -> sdo_relate와 sdo_util.from_wktgeometry
두개였는데
어쨌든 오류 메시지에 clob에 대한 내용이 있었기에, clob형식이 지금 사용한 함수에서 문제가 있는거고 문제를 해결하기위해 clob이 뭔지부터 알아야겠단 생각을 했다.
개념정리
CLOB은 무엇인가?
CLOB은 오라클 데이터베이스에서 "대용량 텍스트 데이터"를 저장하기 위해 사용되는 데이터 타입이다.
CLOB은 일반텍스트데이터를 저장하는 데 적합하며 "최대 4GB" 데이터를 저장할 수 있다.
VARCHAR2와는 무엇이 다른가?
VARCHAR2도 오라클에서 텍스트 데이터를 저장하기 위한 타입이지만, CLOB과 달리 "최대4000BYTE"까지 저장이 가능하다.
따라서, 대용량 텍스트 데이터의 경우 크기가 작은 VARCHAR2에 저장하기 어렵다.
CLOB의 제한 사항
LOB 데이터의 "대용량"이라는 특성상 I/O 작업이 추가적으로 발생하기에
오라클에서LOB 데이터 타입을 사용할 때는 일부 함수나 연산에서 제한이 있을 수 있다.
대표적으로
1. 직접 비교 연산자 (=,<,>,LIKE)를 사용할 수 없다.
2. GROUP BY 및 DISTINCT를 사용할 수 없다.
3. 공간데이터 변환 함수를 지원하지 않는다.
위와 같은 경우에 사용할 수 없는데,
이때는 DBMS_LOB 패키지를 사용해 CLOB의 일부 텍스트만 가져오거나, CLOB데이터를 비교하는 등의 작업을 해야한다.
해결방법
내가 문제된 상황은 위 CLOB 제한사항의 3번때문이었는데,
CLOB 형식은 SDO_UTIL.FROM_WKTGEOMETRY라는 공간데이터 변환 함수가 지원되지 않는다.
SDO_UTIL.FROM_WKTGEOMERY 함수는 WKT(Well-Known-Text) 형식의 문자열만 입력 받기 때문이다. 즉, varchar2만 지원한다는 뜻 !!
따라서, clob 데이터를 쿼리에서 오류없이 작동하게 하기위해서는 clob데이터를 varchar2 형식으로 바꾸는 작업이 필요했다.
SDO_RELATE(
SDO_UTIL.FROM_WKTGEOMETRY(
DBMS_LOB.SUBSTR(
NVL(SELECT CUR_ADDR
FROM CUR_TABLE
WHERE CUR_ID = (SELECT CUR_ID FROM USERS WHERE USER_ID = #{user_id})), 'EMPTY'), 4000, 1)),
SDO_UTIL.FROM_WKTGEOMETRY(DBMS_LOB.SUBSTR(NVL(CUR.CUR_ADDR, 'EMPTY'), 4000, 1)))즉, 원래 쿼리보다 위와 같이 DBMS_LOB.SUBSTR 함수를 추가하는 작업까지 더해져,, 더 복잡해진다는 뜻이었다.
(DBMS_LOB.SUBSTR는 VARCHAR2의 최대용량인 4000바이트까지 CLOB 데이터를 자르고 변환한다)
위처럼 쿼리를 바꾸면 정상적으로 작동하긴 한다.
다만, 문제는
매번 위 쿼리가 실행될때마다, CLOB데이터를 VARCHAR2로 바꾸는 작업까지 추가로 실행되기에, 성능 저하가 발생한다는 것.
특히 대량 데이터 처리를 해야하는 쿼리인 경우에는 성능 저하가 더 심할 것이다.
따라서 내가 선택한 방법은,
기존에 사용되고 있던 그 CLOB 필드의 최대 데이터 용량은 얼마인가를 계산해봤다.
SELECT MAX(CHAR_LENGTH(CUR_ADDR)) AS MAX_CHAR_LENGTH
FROM CUR_TABLE;
SELECT MAX(LENGTH(CUR_ADDR)) AS MAX_BYTE_LENGTH
FROM CUR_TABLE;위 쿼리를 기존 테이블에 실행시켜서 확인해본 결과, 해당 CLOB 필드의 데이터중 가장 긴 데이터는 45바이트도 되지 않았다.
VARCHAR2 타입도 4000바이트까지 지원되는데,,,
그렇다면 굳이 CLOB 타입일 필요가 없지 않나 ??? 라는 생각을 했다.
(이때, 진짜로 VARCHAR2로도 충분한가는 잘 검토해봐야함...)
결론적으로 나는 성능상으로도, 간단한 쿼리(?)를 위해서도 CLOB을 VARCHAR2로 바꾸는게 나을 거 같다는 생각을 하여
상사분께 CLOB -> VARCHAR2로 바꿔도 좋을 거 같다는 확인을 받고 VARCHAR2로 변경하면서 해결했다
SDO_RELATE(
SDO_UTIL.FROM_WKTGEOMETRY(
(SELECT CUR_ADDR
FROM CUR_TABLE
WHERE CUR_ID = (SELECT CUR_ID FROM USERS WHERE USER_ID = #{user_id}))
),
SDO_UTIL.FROM_WKTGEOMETRY(CUR.CUR_ADDR),DBMS_LOB.SUBSTR이 빠진 최종쿼리!로 오류 없이 잘 작동중이다.
마무리
오라클로 변환해보며 CLOB과 같은 처음보는 타입도 알게되었고, SDO_UTIL.FROM_WKTGEOMERY, SDO_RELATE와 같은 공간연산자에 대해서도 알게되었다.
앞으로 오라클로 프로젝트를 진행하면서,, 처음보는 함수나 형식들이 많을 거 같은데, 그때도 단순히 어 해결됐어! 하고 넘어가는 게 아니라 하나씩 짚고 공부해나가면서 해결해야겠다
