개발자를 위한 DBMS 3종 기능비교

Q1. 집합비교 연산자는 각 RDBMS마다 차이가 있는가?

다음의 <표 1>은 현재 어떤 집합연산자들이 어떻게 지원되는지를 보여준다.

<표 1> 집합연산자 지원 현황

참고로 DB2에서는 EXCEPT ALL , INTERSECT ALL이라는 추가적인 연산자를 지원한다. EXCEPT ALL, INTERSECT ALL 둘 다 공통적으로 중복행을 제거하지 않는다. 예를 들어서 1, 1, 2, 3, 4, 5 EXCEPT 1, 2, 3, 4는 4, 5가 표시되지만 1, 1, 2, 3, 4, 5 EXCEPT ALL 1, 2, 3, 4의 경우 1, 4, 5와 같이 같은 값을 중복해서 빼지 않는다.

---

Q2. 각각의 RDBMS에서 DUMMY 테이블로 현재 시각을 가져오는 방법은 어떻게 표현하는가?

다음의 <리스트 1>과 같이 표현된다. MS SQL의 경우 FROM 절에 아무것도 기술하지 않는다.

<리스트 1> 각 RDBMS에서의 현재 시각 가져오기

<FONT size=2>[Oracle] SQL> SELECT SYSDATE FROM DUAL; (SYSDATE OR SYSTIMESTAMP 둘다 가능) [DB2] db2=> SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1; [MS SQL] SELECT GETDATE()</FONT>

 

---

Q3. MS SQL Server 2005부터는 열을 행으로 처리하는 경우 pivot 기능으로 표현할 수 있다. 그렇다면 타 RDBMS에서도 이와 같은 표현이 가능한가?

PIVOT 기능이란 행이나 열로 되어 있는 데이터를 열과 행으로 바꿀 수 있는 기능을 말한다. 간단히 EXCEL에서 구현이 가능하지만 데이터베이스 상에서는 행과 열의 개수의 최대값이 고정되어 있는 경우에만 CASE 문과 그룹함수를 통해 구현할 수 있었다. 그러나 SQL Server 2005부터는 <표 2>의 데이터를 PIVOT 구문으로 간단히 구현 가능하다.

<표 2> PIVOT 기능

<리스트 2> SQL Server의 PIVOT 구문 활용

<FONT size=2>SELECT * FROM SALES PIVOT (SUM(QYANTITY) FOR CUST IN ([SMITH],[MIKE],[ALLEN],[SCOTT])) PVT</FONT>

 

Oracle의 경우 아쉽게도 현재 가장 많이 사용하는 10g에서는 지원하지 않지만 최신 릴리즈인 11g 버전에서는 이 PIVOT 기능을 드디어 지원하기 시작했다. 사용법은 Pivot 절 안에 해당되는 컬럼으로 바꿀 리스트를 IN 절에 기술하면 된다.

<리스트 3> Oracle 11g의 pivot 활용

<FONT size=2>select * from ( select QUANTITY,PRODUCT, CUST from SALES S ) pivot ( SUM(QUANTITY) for CUST in ('SMITH','MIKE','ALLEN','SCOTT'))</FONT>

 

DB2는 명시적으로 PIVOT 절을 지원하지는 않는다. 종래의 case, sum을 이용하는 방법으로 처리해야 한다. 그러나 DB2의 강력한 기능 중 하나인 VALUES 절을 사용하여 unpivot 처리를 할 수 있는 방법도 존재한다.

<리스트 4> DB2의 VALUES 활용 예

<FONT size=2>CREATE TABLE SalesAgg ( year INTEGER, q1 INTEGER, q2 INTEGER, q3 INTEGER, q4 INTEGER ); 데이터의 형태는 다음과 같이 입력된다. YEAR Q1 Q2 Q3 Q4 -------- -------- -------- -------- -------- 2004 20 30 15 10 2005 18 40 12 27 SELECT S.Year, Q.Quarter, Q.Results FROM SalesAgg AS S, TABLE (VALUES(1, S.q1), (2, S.q2), (3, S.q3), (4, S.q4)) AS Q(Quarter, Results); YEAR QUARTER RESULTS ----------- ----------- ----------- 2004 1 20 2004 2 30 2004 3 15 2004 4 10 2005 1 18 …….</FONT>

 

명시적으로 unpivot 기능 또한 SQL Server와 Oracle에서 지원된다.

---

Q4. Oracle에서의 다음과 같은 RECURSIVE 쿼리를 connect by, start with 절로써 구현할 수 있다. 그렇다면 타 DB에서도 동일하게 계층형 쿼리를 구현할 수 있는가?

먼저 Oracle에서는 <표 2>와 같은 계층형 구조의 데이터를 <리스트 5>의 문법으로 간단히 구현할 수 있다.

<표 3> 계층형 구조의 데이터 예

<리스트 5> Oracle의 구문 예

<FONT size=2>SELECT level, rpad('-',level-1,'-')||ename AS ename, prior ename AS manager FROM emp START WITH mgr IS NULL CONNECT BY PRIOR empno = mgr</FONT>

 

START WITH 절에서 해당되는 계층의 START POINT를 명시적으로 표시해준다. 그리고 CONNECT BY 절에서 상위부서 코드와의 매치 작업을 수행한다. 일반적인 실행계획은 <화면 1>과 같이 표시된다. 실제로는 하나의 테이블만 스캔하는 구조가 아니라 동일한 테이블에 대해 CONNECT BY 절의 연결자를 통한 중복 스캔 작업이 발생하는 것을 확인할 수 있다.

<화면 1> 일반적인 실행 계획

MS SQL에서는 Oracle과 다른 형태를 취한다. CTE라는 TEMP 테이블을 선언해서 두 번에 걸쳐 동일하게 반복 엑세스하는 형태를 취한다. 앞서 Oracle의 사용법과 많이 다르지만 안의 쿼리를 살펴보면 최상위 데이터 엑세스 + 상위와 하위 연결 쿼리로 동일하게 구성되어 있음을 발견할 수 있다.

<리스트 6> MS SQL의 구문 예

<리스트 7>에서 보는 것처럼 DB2의 경우에는 MS SQL과 거의 동일한 방법을 취한다.

<리스트 7> DB2의 구문 예

<FONT size=2>WITH parent (ename, mgr, empno) AS (SELECT ename, mgr, empno FROM emp WHERE mgr is null UNION ALL SELECT c.ename,c.mgr, c.empno FROM emp c, parent p WHERE p.empno = c.mgr ) SELECT ename, mgr, empno FROM parent ;</FONT>

 

---

Q5. 원하는 수만큼의 로우가 들어 있는 테스트 테이블을 사용하는 방법에는 어떤 것이 있나?

Oracle의 경우 DUAL 테이블과 순환 쿼리를 이용해 n개만큼 복제한 후 row 수를 무한정 늘려 <리스트 8>과 같이 사용할 수 있다.

<리스트 8> DUAL 테이블과 순환 쿼리 이용 (Oracle)

<FONT size=2>SELECT level no, level + 1 no1 FROM DUAL CONNECT BY LEVEL <= n</FONT>

 

DB2는 <리스트 9>와 같이 TEMP 테이블을 선언해서 사용할 수 있다.

<리스트 9> TEMP 테이블 선언 (DB2)

<FONT size=2>WITH TMP(C1, C2) AS ( VALUES (1, 2) UNION ALL SELECT C1+1, C2+1 FROM TMP WHERE C1<=300) SELECT * FROM TMP;</FONT>

 

MS SQL은 특별히 지정된 방법은 존재하지 않지만 SP를 이용하는 방법을 고려할 수 있다.

지금까지 3개의 RDBMS에 대해 많은 질문을 받았던 부분을 간단히 설명했다. 이 외에도 많은 부분들이 있지만 그 중에서 필자가 생각하는 중요한 포인트만을 설명한 것으로, 더 자세한 세부사항이나 정보는 구글 서치 등을 통해 습득할 수 있다.

참고자료 1. Oracle - http://www.oracle.com/technology/global/kr/documentation/ index.html 2. MSSQL - http://msdn.microsoft.com/en-us/sqlserver/default.aspx 3. DB2 - http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp

 

필자소개

김도근 kilgw@naver.com｜OracleAce, Oracle Certified Master, MCDBA. 프로젝트에서 모델링, 성능개선, 이기종 마이그레이션 작업, DBA 역할 등을 수행했다. 모델링과 성능과의 관계, 신기능을 이용한 성능 개선 방법들을 연구하고 이를 토대로 데이터베이스 구조 및 성능 개선으로 연결할 수 있는 방법을 연구 중이다.

출처 : 한국 마이크로 소프트웨어 [2009년 3월호]

제공 : DB포탈사이트 DBguide.net