국내외적으로 Oracle 사용자가 많은 관계로 (여러 DBMS의 제조사 세미나를 가보면서) Oracle은 DBMS 계의 공공의 적이 되었다는 생각이 들곤 한다. IBM DB2의 경우 Mainframe (z-Series) 분야에서는 평정한 DBMS 이나 많이 사용되는 Unix, Linux, Windows 시장에서는 그렇지 못하기에 DB2 for LUW 제품에는 오라클 SQL을 실행할 수 있는 요건이 필수적이 되었다. 오라클을 사용하는 고객을 대상으로 DB2로 전환하기 위해서는 Oracle 의 기능을 흡수할 필요도 있고 낯선 DB2를 친숙하게 하는 효과를 만들었어야 했을 것이다.
V9.1까지는 java 기반으로 만들어진 udf를 Oracle DB 전환 시 사용하였으나, V9.5부터는 DB2 자체에 변환기를 내포하기 시작했다. V9.7에서는 훨씬 많은 기능들이 내장되어 Oracle DB를 전환하는 경우 많은 수작업들을 안해도 되어 DB2 기술자의 단순 작업 부담이 줄었다 할 수 있겠다.
그러나 이로 인한 단점도 생긴다. (필자의 경우는 오라클 호환성 기능이 생기면서 부정적인 생각을 많이 했다. DB2 자체 기능으로 개발하여 보완하는 식으로 갔으면 좋은데 변환 방식으로 갔기 때문이다..)
앞으로 말하려고 하는 오라클 호환성 rownum 은 테스트를 해본 바, Oracle rownum 보다 더 일관적인 느낌이여서 낫다는 생각을 해본다.
KDUG 등에 오라클과의 rownum 비교 자료를 보곤 했는데, 그것 말고도 고려할 점이 있었다.
테이블 DDL Script
오라클
create table t1(c1 integer,c2 integer);
insert into t1 values(1,6);
insert into t1 values(2,5);
insert into t1 values(3,4);
insert into t1 values(4,3);
insert into t1 values(5,2);
insert into t1 values(6,1);
commit;
DB2
db2 "create table t1(c1 integer,c2 integer)"
db2 "insert into t1 values (1,6),(2,5),(3,4),(4,3),(5,2),(6,1)" |
ORACLE DB에 저장된 데이터
|
ORACLE> select rownum, t1.* from t1;
ROWNUM C1 C2
---------- ---------- ----------
1 1 6
2 2 5
3 3 4
4 4 3
5 5 2
6 6 1 |
DB2 DB에 저장된 데이터
|
DB2 > db2 "select rownum, t1.* from t1"
ROWNUM C1 C2
-------------------- ----------- -----------
1 1 6
2 2 5
3 3 4
4 4 3
5 5 2
6 6 1 |
1. ORDER BY 시의 오라클과 DB2의 차이점
오라클 결과
|
ORACLE> select rownum, t1.* from t1 order by c2;
ROWNUM C1 C2
---------- ---------- ----------
6 6 1
5 5 2
4 4 3
3 3 4
2 2 5
1 1 6 |
DB2 결과
|
ROWNUM C1 C2
-------------------- ----------- -----------
1 6 1
2 5 2
3 4 3
4 3 4
5 2 5
6 1 6 |
오라클과 DB2의 rownum 결과값이 달라졌다.
이 결과를 보면, Oracle은 Insert 시에 rownum의 값이 만들어져 저장되는 것 처럼 여겨진다. DB2는 “최종 데이터 결과 집합”에 rownum 값을 부여하는 것처럼 여겨진다.
그러면서 의문을 하게 갖게 된다. 테이블 2개를 join하여 rownum 값을 출력시키면 어떻게 될 것인가? DB2는 “최종 데이터 결과 집합”에 부여를 하니 문제없을 것 같은데, Oracle은 rownum 값에 대해 “애매하다” (ambiguous)는 에러를 반환하게 되지 않을까? 하는 예측을 하게 된다.
이 내용관련 정리하는 시점에는 에러가 발생하지 않았지만, 맨 처음 테스트를 했을 때는 오라클에서는 이런 에러 메시지가 났었다.
| ORA-01747: 열명을 올바르게 지정해 주십시오 |
동일 SQL을 DB2와 oracle에 동일하게 수행했으니 신택스 오류는 없었을 것으로 생각되는데, 다시 테스트를 했을 때는 동일한 오류가 발생하지 않으니 신택스 문제처럼 보여지게 된다..
위 메시지가 났을 때 “오라클은 Insert 시에 rownum 값에 많은 영향을 주는 것 처럼” 판단을 했었다.
2. JOIN 시의 rownum 결과
오라클
|
ORACLE> select rownum, t1.c1, t2.c1 from t1, t2 where t1.c1=t2.c1;
ROWNUM C1 C1
---------- ---------- ----------
1 2 2
2 3 3 |
DB2
|
DB2 > db2 "select rownum, t1.c1, t2.c1 from t1, t2 where t1.c1=t2.c1"
ROWNUM C1 C1
-------------------- ----------- -----------
1 2 2
2 3 3 |
위 결과를 보면, 오라클도 DB2로 “최종 데이터 결과 집합”에 rownum을 부여하는 것으로 나온다.
이 결과로 오라클은 rownum 처리 방식을 약간 혼란스럽게 만드는 반면, DB2는 예측되는 결과를 가져다 준다. (DB2가 더 일관적인 처리를 해준다라는 생각을 한다.)
이런 결과들을 통해서 group by 절과 조건 절에 rownum을 추가하면 어떤 결과를 주게 될 것인가?를 예측해 본다면 “최종 결과 집합”에 대해 rownum 으로 값이 짤리겠지하는 예측을 하게 될 것이다.
3. GROUP BY 및 검색 조건의 ROWNUM
|
오라클
create table t1 (c1 integer, c2 varchar(3));
insert into t1 values (1,'a');
insert into t1 values (2,'a');
insert into t1 values (3,'b');
create table t2 (c1 integer, c2 varchar(3));
insert into t2 values (1,'a');
insert into t2 values (2,'b');
create table t3 (c1 integer, c2 integer);
insert into t3 values (1,1);
insert into t3 values (1,2);
insert into t3 values (1,3);
insert into t3 values (2,1);
insert into t3 values (2,2);
DB2
db2 "create table t1 (c1 integer, c2 varchar(3))"
db2 "create table t2 (c1 integer, c2 varchar(3))"
db2 "create table t3 (c1 integer, c2 integer)"
db2 "insert into t1 values (1,'a'),(2,'a'),(3,'b')"
db2 "insert into t2 values (1,'a'),(2,'b')"
db2 "insert into t3 values (1,1),(1,2),(1,3),(2,1),(2,2)" |
오라클 결과
|
ORACLE> select t1.c1, count(t1.c2),count(t2.c1) from t1, t2, t3 where t1.c2=t2.c2 and t1.c1=t3.c1 and rownum < 4 group by t1.c1 ;
C1 COUNT(T1.C2) COUNT(T2.C1)
---------- ------------ ------------
1 3 3
|
DB2 결과
|
DB2> db2 "select t1.c1, count(t1.c2),count(t2.c1) from t1, t2, t3 where t1.c2=t2.c2 and t1.c1=t3.c1 and rownum < 4 group by t1.c1"
C1 2 3
----------- ----------- -----------
1 3 3 |
rownum < 4 이므로 3건의 데이터가 나와야 하는데, 1건만 나온다.
예측한 결과와 다른 반응을 보여준다.
그렇다면 group by 만 제외한 결과는 어떻게 나올까?
오라클
|
ORACLE> select t1.c1, t2.c1, t3.c1 from t1, t2, t3 where t1.c2=t2.c2 and t1.c1=t3.c1 and rownum < 4 ;
C1 C1 C1
---------- ---------- ----------
1 1 1
1 1 1
1 1 1 |
DB2
|
DB2> db2 "select t1.c1, t2.c1, t3.c1 from t1, t2, t3 where t1.c2=t2.c2 and t1.c1=t3.c1 and rownum < 4"
C1 C1 C1
----------- ----------- -----------
1 1 1
1 1 1
1 1 1 |
위 결과를 통해서 “이유”를 알게 되었을 것이다.
rownum 보다 group by가 우선 실행되어지는 것을!!
이 문제는 프로젝트 지원하면서 개발자가 페이징처리를 하면서 “왜 rownum으로 짜른 만큼의 데이터 건 수가 나오지 않느냐?” 하는 질문을 하게 되어 살펴 보게 되었다.
이런 현상에 대해 이것 저것 테스트를 하면서 위와 같은 궁금증에 대해 테스트를 해보면서 (SQL을 잘 쓰지 않아서 생기는 문제일 수도 있겠지만) 사용자의 허를 찌르는 부분이 존재하는 것을 깨닫게 되었다.
맨 처음 접했을 때는 SQL의 조그마한 변경에도 일관되지 않는 결과값들이 나와서,,별별 의심을 다 해보게 되고, DB2의 rownum에 무슨 문제가 있는 것은 아닌가 하는 부정적인 시각도 갖게 되었었다.
위 경우 페이징처리는 오라클도, DB2도 subquery (결과 집합을 한번 더 감싸서 짤라내는)로 만들어 처리할 수 밖에는 없다. (페이징처리를 하는 기준 컬럼이 인덱스를 활용할 수 있다면 다른 방법으로 처리할 수도 있겠지만..)
