DATABASE 3종(Oracle, DB2, MSSql) 간단 비교

구체적인 사례별 기능 비교

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

지난 호에서 각종 프로젝트를 수행하며 필자가 겪었던 질문과 기초적인 기능들에 대해 3대 RDBMS를 비교하며 설명하는 기회를 가졌다. 하나의 RDBMS에도 광범위한 자료가 존재하는 마당에 한정된 지면으로 많은 내용을 살펴보기란 쉽지 않다. 그러나 필자의 취지는 RDBMS는 완전히 독자적인 것이 아니라 서로간의 장점을 서로 벤치마킹하고 서로 경쟁하기도 하면서 발전해 나간다는 것을 알리는 데 있다.

필자 또한 하나의 RDBMS만을 오래 사용하다 보니 이기종 DB의 기초적인 사항조차 몰라 헤맬 때가 많았다. 그러나 최근 대부분의 RDBMS가 ANSI 표준을 지원하고 있고 아주 특수한 기능이 아니라면 명칭만 다른 동일한 기능들을 지원하는 경우가 많아 그 사용법과 문법만 안다면 이해하기가 훨씬 쉬울 것이다. 따라서 이번 호는 전 시간에 기술하지 못했던 각종 구현 팁과 기능을 비교하는 시간을 가져본다.

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에서의 현재 시각 가져오기

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

---

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

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

<표 2> PIVOT 기능

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

SELECT * FROM SALES PIVOT (SUM(QYANTITY) FOR CUST IN ([SMITH],[MIKE],[ALLEN],[SCOTT])) PVT

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

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

select * from ( select QUANTITY,PRODUCT, CUST from SALES S ) pivot ( SUM(QUANTITY) for CUST in ('SMITH','MIKE','ALLEN','SCOTT'))

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

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

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 …….

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

---

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

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

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

<리스트 5> Oracle의 구문 예

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

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의 구문 예

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 ;

---

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

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

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

SELECT level no, level + 1 no1 FROM DUAL CONNECT BY LEVEL <= n

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

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

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

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