ROWNUM의 활용
ROWNUM이란 어떤 테이블 내에 물리적으로 저장되어 있는 컬럼이 아니라 SYSDATE나 USER 등과 같이 모든 SQL에 그대로 삽입해서 사용할 수 있는 가상(Pseudo)의 컬럼이다. SQL이 실행되는 과정에서 발생하는 일련번호이므로 각 SQL수행시마다 같은 로우라 하더라도 서로 다른 ROWNUM을 가질 수 있다. 그러므로 이러한 내부적인 값을 조건절에 사용하여 우리가 원하는 만큼만 처리가 수행되도록 할 수 있다. 이 방법 역시 전체를 처리하지 않고 일부만 처리하도록 유도하는 방법이므로 하나의 부분범위처리라고 말할 수 있다.
SQL 실행과정의 어느 특정 부분에서 ROWNUM이 결정되어지므로 정확히 알지 못하고 사용하면 우리가 원하지 않는 결과가 추출될 수도 있다. ROWNUM이 결정되어지는 과정을 그림을 통해 살펴보자.
아래 그림을 자세히 살펴보면 인덱스를 통해 엑세스한 테이블의 로우들 중에 다른 조건을 확인하여 만족하는 로우들만 ROWNUM이 부여되어 운반단위로 보내진다는 것을 알 수 있다.
그러므로 운반단위가 채워지거나 ROWNUM이 10보다 커지는 순간 수행을 멈춘다. 만약 엑세스는 했으나 조건을 만족하는 로우가 없다면 끝까지 ROWNUM <= 10을 만족하지 못했으므로 처리는 전체범위가 끝날 때까지 계속될 것이다.
ROWNUM은 엑세스되는 로우의 번호가 아니라 조건을 만족한 결과에 대한 일련번호이므로 우리가 10건만 처리하고자 하더라도 내부적으로는 그것보다 훨씬 많은 로우가 엑세스될 수 있다.
그러므로 추출되는 로우 중에 10번째 로우를 찾기 위해 '...WHERE ROWNUM=10'을 요구했다면 이 조건을 만족하는 로우는 결코 추출될 수가 없다. 원래의 조건을 모두 만족하여 ROWNUM이 '1'이 될 수 있었다 하더라도 조건에 있는 'ROWNUM=10'에 의해서 무시되어 ROWNUM은 아직 '1'이 되지도 못했다. 그러므로 ROWNUM이 '1'이 되지 않고서는 영원히 ROWNUM은 '10'이 될 수 없으므로 이러한 조건을 만족하는 로우는 결코 찾을 수가 없다.
SQL내에 ORDER BY 나 GROUP BY가 있다면 상황은 전혀 달라진다. 우리가 어떤 조건에 맞는 데이터를 엑세스하여 정렬한 수 10건만 추출하기 위해서 다음과 같은 SQL을 수행시킨다면 우리가 원하는 결과를 얻을 수 없다.
SELECT *
FROM PRODUCT
WHERE DEPT LIKE '12%'
AND QTY > 0
AND ROWNUM <=10
ORDER BY ITEM
ROWNUM이 비록 SQL단위로 생성되는 값이지만 'ORDER BY'가 수행되기 전에 이미 WHERE 절에 있는 조건을 만족하는 로우마다 부여되어 임시공간에 저장된다. 그러므로 'ORDER BY'를 수행하기 전에 이미 'ROWNUM<=10'이 적용되어 단 10건만 정렬하여 운반단위로 보낸다. 이것은 우리가 원하는 결과가 아닐 뿐만 아니라 최종적으로 추출된 로우들의 ROWNUM은 순차적으로 나타나지도 않는다. 이와 같이 전체 범위처리를 하는 경우는 최종적으로 추출되는 로우의 순서와 ROWNUM이 일치하지 않으며 잘못된 결과를 추출한다는 사실을 명심하기 바란다.
그렇다면 우리가 원하는 결과를 추출하면서 10건만 처리하려면 어떻게 해야 할 것인가?
가능한 방법은 SQL이 부부범위처리가 되도록 바꾸어 주어야 한다는 것이다. 즉 ORDER BY를 없애고 앞서 '인덱스를 이용한 부분범위처리 유도'에서 소개한 방법으로 SQL을 변경시킨 후 'ROWNUM <=10'을 추가하면 된다.
만약 다중처리(ARRAY PROCESSING)가 되는 툴이라면 호스트 변수를 중복으로 지정하여 한번만 페치(Fetch)하거나 'SELECT INTO...'로 해결할 수 있다.
GROUP BY를 하는 경우는 좀 더 차이가 난다. 만약 우리가 앞서 소개한 ORDER BY 경우와 같이 WHERE절 내에 ROWNUM을 삽입하면 동일한 문제가 발생된다. 그렇다면 우리가 GROUP BY 한 결과를 원하는 로우만큼 추출하고자 한다면 어떻게 해야 할까?
뷰를 만들어 해결할 수 있다.
CREATE VIEW PROD_VIEW (DEPT, TOTQTY)
AS SELECT DEPT, SUM(QTY)
FROM PRODUCT
GROUP BY DEPT
조건절을 가지지 않으면서 DEPT로 GROUP BY 하는 뷰를 생성한다. 뷰란 물리적인 저장공간을 가지지 않고 단지 자료사전에 SQL문장이 저장되었다가 SQL을 수행하는 순간 뷰의 SQL과 수행시킨 SQL을 합성하여 실행계획을 수립할 뿐이다.
다음과 같이 뷰를 통해 ROWNUM을 요구해 보자.
SELECT ROWNUM, DEPT, TOTQTY
FROM PROD_VIEW
WHERE DEPT LIKE '123%'
AND ROWNUM <= 10
뷰를 생성한 SQL에는 조건절이 없어 전체 테이블이 처리대상이었지만 뷰를 이용한 SQL에 있는 조건인 DEPT LIKE '123%'가 뷰의 조건절에 파고 들어 해당 범위만 엑세스하여 GROUP BY를 수행한다.
GROUP BY를 수행하기 전에 임시공간에 저장된 ROWNUM은 지금까지 설명한 것과 같은 방법으로 생성된다. 그러나 GROUP BY를 하게 되면 임시공간에 저장된 데이터를 정렬하여 종류별로 집계한 후 다시 종류별로 한 로우씩 생성되어 다른 임시공간에 저장된다. 이때 ROWNUM은 다시 부여되며 운반단위를 통해 추출되는 ROWNUM은 순차적으로 나타난다.
이와 같이 뷰를 사용한 SQL에 있는 ROWNUM은 GROUP BY된 결과에 대한 ROWNUM을 의미하지만 다음과 같이 테이블을 사용한 경우의 ROWNUM은 그림에 있는 '1차 ROWNUM'이라는 사실을 명심하기 바란다.
SELECT DEPT, SUM(QTY)
FROM PRODUCT
WHERE DEPT LIKE '123%'
AND ROWNUM <= 10 ................(1차 ROWNUM)
GROUP BY DEPT
뷰를 사용하지 않고 해결할 수 있는 방법도 있다. 버젼 7.1이상에서는 인라인 뷰를 생성하여 앞서 뷰를 생성한 경우와 동일한 결과를 얻을 수 있다.
다음과 같은 색다른 SQL을 만들어 보자
SELECT ROWNUM, DEPT, TOTQTY
FROM (SELECT DEPT, SUM(QTY) AS TOTQTY
FROM PRODUCT
WHERE DEPT LIKE '123%'
GROUP BY DEPT)
WHERE ROWNUM <= 10
이 SQL은 뷰를 생성했을 때와 동일한 결과를 추출한다.
이런 SQL을 좀더 활용하면 다음과 같이 특정한 ROWNUM만 출력시킬 수도 있다.
SELECT DEPT, TOTQTY
FROM (SELECT ROWNUM AS RNUM, DEPT, TOTQTY
FROM (SELECT DEPT, SUM(QTY) AS TOTQTY
FROM PRODUCT
WHERE DEPT LIKE '123%'
GROUP BY DEPT))
WHERE RNUM=5
AND ROWNUM=1
위의 SQL은 GROUP BY된 로우중에서 5번째 로우만 출력된다.
여기서 조건절에 ROWNUM=1조건을 추가해야만 불필요한 처리를 없앨 수 있다. 만약 이 조건이 없다면 RNUM=5인 로우를 찾았더라도 멈추지 않고 FROM뒤에 기술한 인라인 뷰의 모든 로우들이 끝날때까지 수행하게 된다.
위의 예제와 같이 특정한 로우를 찾는 방법은 만약 많은 사용자가 어떤 범위의 데이터를 우선 순위에 따라 처리해야 하는 작업에 적용한다면 아주 양호한 수행속도를 얻을 수 있다. 이러한 방법은 많은 동시 사용자가 동일한 로우를 처리하기 위해 경합을 벌이는 것을 방지할 수 있으므로 LOCK을 피할 수 있어 온라인 수행속도를 크게 향상시킬 수 있다.
'DataBase' 카테고리의 다른 글
| 계층 쿼리 사용법을 배워보자! (0) | 2013.08.08 |
|---|---|
| CentOS에 mongoDB 설치하기 (0) | 2013.08.03 |
| 윈도우에서 mongoDB 설치하기 (0) | 2013.08.03 |
