소개: CUBRID2008 에서 사용되는 쿼리 예제를 소개한다
적용 대상: CUBRID2008 R1.1 이상
JOIN 구문 사용
CUBRID2008은 ANSI92 표준으로 쿼리를 작성할 것을 권장하고 있다. 따라서 where절에 JOIN 조건을 명시하는 oracle 스타일의 JOIN보다는 ON 절에 JOIN 조건을 명시하는 ANSI92 표준을 따를 것을 권장한다. 특히, oracle style의 OUTER JOIN을 경우에는 실행 결과나 실행 계획이 원하지 않는 방향으로 유도될 수 있으므로 OUTER JOIN인 경우에는 반드시 ANSI 표준으로 작성해야 한다
INNER JOIN
SELECT select_list
FROM TABLE1 T1
INNER JOIN TABLE2 T2 ON T1.COL1 = T2.COL2
WHERE T1.A = 'test' AND T2.B = 1
OUTER JOIN
CUBRID2008은 FULL OUTER JOIN을 지원하지 않는다. CUBRID2008에서 OUTER JOIN이 등장할 경우 이전에 JOIN 대상이었던 모든 테이블의 실행 순서가 FROM절에 명시된 순서대로 고정되므로 OUTER JOIN사용 시 테이블의 순서에 주의하여 쿼리를 작성한다.
SELECT select_list
FROM TABLE1 T1
LEFT OUTER JOIN TABLE2 T2 ON T1.COL1 = T2.COL2 AND T2.B=1
WHERE T1.A = 'test'
Hint 사용하기
INDEX HINT 사용
CUBRID2008은 where절 다음에 나오는 using 구문을 통하여 INDEX HINT를 제공한다. 한가지 주의할 점은, FROM절에 2개 이상의 테이블이 명시된다면, FROM절에 명시된 모든 테이블에 대한 인덱스를 HINT로 제공해야 한다. 그렇지 않으면 인덱스 힌트가 제공되지 않은 테이블은 FULL TABLE SCAN이 발생하므로 주의해야 한다. 또한, CUBRID2008은 오라클에서 제공하는 INDEX_DESC 같은 힌트를 제공하지 않으므로, 타 DBMS에서 사용하는 쿼리의 인덱스 힌트를 변환할 경우에는 반드시 매뉴얼의 "홈 > CUBRID SQL 설명서 > 데이터 조회 > SQL 힌트 사용" 내용을 참조하기 바란다.
SELECT select_list
FROM TABLE1 T1
WHERE T1.A = 'test'
using index TABLE1.INDEX1
또는
SELECT select_list
FROM TABLE1 T1
WHERE T1.A = 'test'
using index A.INDEX1
또는
SELECT select_list
FROM TABLE1 T1
WHERE T1.A = 'test'
using index INDEX1 -->> 이 경우는 해당 인덱스명이 모든 JOIN절에 등장하는 테이블에 존재하는 인덱스명 중에 UNIQUE해야 한다
JOIN HINT 사용
CUBRID2008이 제공하는 JOIN HINT는 ORDERED, MERGE, NESTED LOOP 세 가지를 제공한다.
SELECT /*+ JOIN_HINT */ select_list
FROM TABLE1 T1
INNER JOIN TABLE2 T2 ON T1.COL1 = T2.COL2
WHERE T1.A = 'test' AND T2.B = 1
JOIN절의 잠금 힌트
READ COMMITTED 이상의 CLIENT 환경에서 특정 클래스에 대한 구문에 대해서만 READ UNCOMMITTED로 데이터를 읽고 싶을 경우에는 FROM 절에 잠금 힌트를 명시할 수 있다.
SELECT select_list
FROM TABLE1 T1
INNER JOIN TABLE2 WITH (READ UNCOMMITTED) T2 ON T1.COL1 = T2.COL2
WHERE T1.A = 'test' AND T2.B = 1
페이지 처리
LIMIT RESULT SET 이해하기
큐브리드에서는 ROWNUM, INST_NUM(), ORDERBY_NUM(), GROUPBY_NUM() 등을 이용하여 resultset 을 제한하여 가져오는 기능을 제공한다. 처음 ROWNUM을 사용할 때 사람들이 혼동하는 것 중의 하나가 ROWNUM 사용에 대한 것이다. 예를 들어 무엇인가를 정렬해서 100개를 뽑아오고자 할 때 자연스럽게 다음과 같은 쿼리를 작성하게 된다
SELECT select_list
FROM TABLE1 T1
WHERE T1.A = 'test' AND ROWNUM <= 100
ORDER BY ORDER_COLUMN;
하지만 ROWNUM은 해당 데이터가 저장된 첫 페이지부터 100 건이 나올 때까지 WHERE절의 조건을 만족하는 100건을 가져오는 질의이므로 실행계획에 따라 의도한대로 결과가 나오지 않을 수 있다. 따라서 특정 칼럼으로 정렬한 100 건을 가져오고 싶다면 다음과 같은 구문으로 작성해야 한다. ROWNUM을 사용하면서도 ORDERBY_NUM을 사용하는 것과 동일한 결과를 가져오도록 쿼리를 작성하기 위해서는 INDEX HINT를 사용하면 가능하다
INST_NUM은 ROWNUM과 동일한 기능을 제공한다.
SELECT select_list
FROM TABLE1 T1
WHERE T1.A = 'test'
ORDER BY ORDER_COLUMN
FOR ORDERBY_NUM() <= 100;
GOUP BY 한 결과에서 데이터를 가져올 때도 마찬가지이다. 만약 아래와 같이 쿼리를 작성한다면, 조건에 맞는 데이터를 저장된 순서대로 100건을 가지고 온 후에 GROUP BY를 수행하기 때문에 의도한 값과 일치하지 않을 수 있다.
SELECT select_list
FROM TABLE1 T1
WHERE T1.A = 'test' AND ROWNUM <= 100
GROUP BY GROUP_COLUMN;
따라서 GROUPING된 데이터 중 100개를 출력하는 쿼리는 다음과 같이 작성해야 한다.
SELECT select_list
FROM TABLE1 T1
WHERE T1.A = 'test'
GROUP BY GROUP_COLUMN
HAVING GROUPBY_NUM() <= 100;
CUBRID2008을 이용하여 페이지 처리하기
페이지처리는 기본적으로 ORDER BY절을 동반한다(ORDER BY절, 혹은 INDEX HINT). 따라서 CUBRID2008을 이용한 페이지 처리는 ORDERBY_NUM을 사용하거나, 위에서 언급한 대로 INDEX HINT를 사용하여 구현할 수 있다. (CUBRID2008는 기본적으로 쿼리가 사용한 인덱스 순서에 따라 결과 SET을 정렬하여 만든다.)
페이지 처리를 위해 사용될 입력 변수에 대한 이름은 다음으로 정한다
- ROW_COUNT: 한 페이지에 보여줄 행의 개수
- PAGE_NUM: 페이지 번호
- TOTAL_COUNT: 전체 데이터 개수
- TOTAL_PAGE_COUNT: 전체 페이지 개수
- LAST_PAGE_COUNT: 마지막 페이지에 보여주어야 할 데이터 개수
이에 따라 APPLICATION에서 계산해야 하는 FROM 및 TO 값은 다음과 같다.
- FROM : ROW_COUNT * (PAGE_NUM - 1) + 1
- TO : ROW_COUNT * PAGE_NUM
- TOTAL_PAGE_COUNT :
IF TOTAL_COUNT MODULO ROW_COUNT
THEN TOTAL_PAGE = TOTAL_COUNT / ROW_COUNT
ELSE
TOTAL_PAGE = TOTAL_COUNT / ROW_COUNT * 1
대부분의 LIST 화면은 첫 페이지가 호출되면서 전체 데이터의 COUNT를 호출해가므로, 첫 페이지 호출 시 전체 SELECT한 전체 COUNT 개수를 APPLICATION에서 CACHING 하므로써 이후 페이지로 넘어갈 때 COUNT(*) 하는 부담을 줄일 수 있다
SELECT select_list
FROM TABLE1 T1
WHERE T1.A = 'test'
ORDER BY ORDER_COLUMN
FOR ORDERBY_NUM() BETWEEN #FROM AND #TO;
기타 쿼리 구현하기
DB_ROOT 테이블
MS-SQL 및 Mysql은 select .. from .. 의 형식을 맞추기 위한 dummy 테이블이 필요하지 않지만, CUBRID2008에서는 ORACLE의 DUAL과 같은 DUMMY 테이블을 반드시 사용해야 한다. 이 ORACLE의 DUAL 테이블과 같은 기능을 하는 테이블이 DB_ROOT 이다. 따라서 시스템 함수 및 특정 테이블이 없이 SELECT절에서의 연산 만을 실행할 경우에는 반드시 FROM절에 DB_ROOT 를 명시해 주어야한다.
대표적인 경우 SERIAL을 가져오는 경우가 있다
SELECT SERIAL_NAME.NEXT_VALUE -->> 오류 발생!!
SELECT SERIAL_NAME.NEXT_VALUE
FROM DB_ROOT;
PIVOT 기능 구현하기
CUBRID2008은 Mysql의 GROUP_CONCAT이나 오라클의 SYS_CONNECT_BY 등의 함수가 없지만, list나 set 함수를 이용하여 유사한 기능을 구현할 수 있다. 다음은 네이버 뮤직에서 실제로 사용되었던 구문이며 PIVOT 기능을 사용하도록 변경한 예이다. 한 가지 주의할 점은, LIST 로 return값을 받았을 경우 JAVA단에서 result set을 핸들링할 때 getString 이 아니라 getObject 메소드를 사용해야 한다. 또한, list함수는 특별한 구분자를 지정해줄 수는 없고 기본적으로 ,를 구분자로 하여 출력된다. | 을 구분자로 출력하고 싶다면 list(select trackid + '|' 의 형식으로 쿼리를 작성해야 한다.
SELECT tubeid, MAX (SYS_CONNECT_BY_PATH (trackid, '||')) trackid, MAX (SYS_CONNECT_BY_PATH (tracktitle, '||')) tracktitle
FROM
(
SELECT a.tubeid, a.trackid, a.tracktitle, a.discno, a.trackno
,ROW_NUMBER () OVER (PARTITION BY a.tubeid ORDER BY a.trackid) rnum
FROM tbm1_track a,
(
SELECT /*+ INDEX_DESC (tbm1_albumdic tbm1_albumdic_x1) */ tubeid
FROM tbm1_albumdic
WHERE artistid = '282'
AND rownum = 1
AND wdt BETWEEN '2008-06-01' AND '2008-06-03'
) b
WHERE a.tubeid=b.tubeid
ORDER BY a.discno, a.trackno
)
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum - 1 AND PRIOR tubeid = tubeid
group by tubeid
==>>
1. discno, trackno 로 정렬한 순서대로 trackid, tracktitle 를 출력해야하는 경우
select tubeid
, list(select trackid from (select tracktitle,tubeid from tbm1_track ti where a.tubeid = ti.tubeid order by discno, trackno) aa on aa.tubeid = a.tubeid)
, list(select tracktitle from (select tracktitle,tubeid from tbm1_track ti where a.tubeid = ti.tubeid order by discno, trackno) aa on aa.tubeid = a.tubeid)
from tbm1_albumdic a
where artistid = '282' and wdt BETWEEN '2008-06-01' AND '2008-06-03'
2. 정렬이 필요 없이 trackid, tracktitle만 출력해도 되는 경우
select tubeid
, list(select trackid from tbm1_track ti where a.tubeid = ti.tubeid )
, list(select tracktitle from tbm1_track ti where a.tubeid = ti.tubeid )
from tbm1_albumdic a
where artistid = '282' and wdt BETWEEN '2008-06-01' AND '2008-06-03'
