이번 시간에는 Oracle RDBMS에서 Top-N Query를 처리하는 방법에 대해 알아보겠습니다. Top-N Query에 대해 간단하게 소개하면, 결과 집합의 전체 내용 중 일부분만을 추출하는 방식입니다.
일반적으로 Top-N Query는 페이징(Pagination) 처리를 효과적으로 수행하기 위해 활용하거나, 전체 결과 집합에서 특정 부분을 추출하기 위해 활용합니다. 아마도 웹 프로그래밍을 해보셨다면, 이 기법에 대해 한 번쯤은 마주하셨을 것 같습니다. Top-N Query에 대한 자세한 내용은 다음 단락에서 자세히 알아보겠습니다.
About Top-N Query
Top-N Query를 한 문장으로 정리하면, 데이터베이스 내부에 저장된 자료에서 사용자가 요청한 사항(질의 내용)에 만족하는 자료 중 특정 영역에서 몇 개의 자료를 추출하고 싶을 때 사용하는 질의입니다. 예를 들어, 올림픽의 특정 종목에 참여한 여러 참가자 중 메달을 획득한 상위 3명에 대한 정보를 추출하기 위해 사용할 수 있습니다.
Top-N Query에 대한 필요성과 시대적 요구가 증가로 말미암아 상용 RDBMS 벤더들에게도 Top-N Query를 지원하기 위한 많은 고민이 있었습니다. 안타깝게도 표준 문법의 제정이 늦어져 각 사의 고유한 문법으로 Top-N Query를 지원하고 있습니다. 대표적으로 SQLServer의 Top 구문과 MySQL의 Limit 구문이 있습니다.
ANSI SQL에서도 Top-N Query를 지원하기 위한 문법을 발표했습니다.
첫 번째 버전은 ANSI/ISO SQL:2003 버전에서 소개된 Window Functions를 활용1한 방법이며, 두 번째 버전은 ANSI/ISO SQL:2008 버전에서 소개된 FETCH 구문2입니다. Oracle RDBMS는 앞에서 소개한 두 가지 문법을 모두 지원하며, 이외에도 Oracle RDBMS 자체적으로 Top-N Query를 위해 활용할 수 있는 방법이 있습니다. 다음 절부터 Oracle RDBMS에서 Top-N Query를 수행하는 방법에 관해 살펴보겠습니다.
Sample Data
이번 단락에서는 Oracle Top-N Query의 이해를 돕기 위해 활용하게 될 예제에 대해 설명합니다.
이 글에서 사용하는 예제는 Oracle에서 제공하는 샘플 스키마 중 일부분을 활용합니다. SCOTT 계정에서 제공하는 샘플 스키마(EMP, DEPT)를 활용하여 설명하겠습니다. 예제 데이터는 다음 링크에서 확인하실 수 있습니다.
Oracle Top-N Query, Using ROWNUM
이 방법은 Oracle RDBMS에서만 지원하는 방법입니다. 이 방법을 정확하게 이해하기 위해서는 ROWNUM에 대한 선행 학습이 필요합니다. 다음 단락에서 ROWNUM에 대해 간략하게 살펴보겠습니다.
About ROWNUM
Oracle RDBMS의 ROWNUM은 의사 컬럼(Pseudo Column)으로 결과 집합에 대해 행마다 가상으로 행 번호를 부여한 순번3입니다. ROWNUM의 값은 질의를 수행했을 때, 임시로 생성되는 값으로 별도로 행에 저장되는 값이 아닙니다.
ROWNUM을 사용하면서 주의해야 할 점은 ROWNUM은 첫 번째 값이 할당된 이후 증가합니다. 그러므로 다음과 같은 질의는 정상적으로 수행되지 않습니다.
SELECT * FROM EMP WHERE ROWNUM > 1;
이뿐만 아니라 ROWNUM을 사용할 때 주의할 점에 대해 조금 더 이야기해 보면, ROWNUM 할당은 질의의 조건절(WHERE)이 처리된 후에 할당되며, 질의에 정렬/그룹 조건이 있으면 이 조건을 수행하기 전에 할당되어 원하는 값을 얻을 수 없습니다. 이에 대한 설명은 다음 단락의 예제를 활용하여 설명하겠습니다.
Example Ⅰ
첫 번째 예제는 EMP 테이블에서 급여(SAL)가 높은 상위 5명의 목록을 추출하는 예제입니다.
ROWNUM에 대해 정확하게 이해하지 못한 상태에서 질의를 작성하면 다음과 같이 작성할 것입니다.
SELECT * FROM EMP WHERE ROWNUM <= 5 ORDER BY SAL DESC;여러분의 생각과는 다르게 위와 같이 질의를 작성하면 잘못된 결과를 얻게 됩니다. 다음 질의를 수행한 후, 결과를 비교해 보겠습니다.
SELECT *
FROM (SELECT * FROM EMP ORDER BY SAL DESC)
WHERE ROWNUM <= 5;
두 질의의 결과가 다른 이유는 앞에서 설명했듯이, WHERE 절의 구문을 먼저 처리하기 때문입니다. 그러므로 첫 번째 질의는 먼저 5개의 행을 추출한 후, 이 결과 집합에서 정렬을 수행합니다. 두 번째 질의는 전체 EMP 테이블의 행을 대상으로 정렬을 수행한 후, 정렬을 수행한 결과 집합에서 상위 5개의 행을 추출합니다.
Example Ⅱ
두 번째 예제는 EMP 테이블에서 급여(SAL)가 높은 순위로 정렬한 후, 6~10위에 위치한 직원들의 목록을 추출하는 예제입니다.
이 질의도 비교적 간단하게 작성할 수 있습니다만, 좀 더 생각해봐야 합니다. 이 문제를 해결하는 방법은 Example I의 해결 방법에서 한 걸음 더 나아가야 합니다. 조금 고민해 보신 후, 다음 결과를 확인하시는 것을 추천합니다. 문제의 해결 방법은 두 번째 서브 쿼리에서 ROWNUM 값을 구하고, 이 값을 활용하여 결과 목록을 추출하는 것입니다.
SELECT B.*
FROM (SELECT ROWNUM RN, A.*
FROM (SELECT * FROM EMP ORDER BY SAL DESC) A
) B
WHERE RN BETWEEN 6 AND 10;
Conclusion
지금까지 ROWNUM을 활용한 Top-N Query에 대해 살펴봤습니다. 일반적으로 상용 RDBMS와 비교했을 때, Top-N Query를 처리하는 구문이 다소 복잡하며, 번거로운 추가 작업을 병행해야 하는 것을 알 수 있습니다.
Oracle Top-N Query, Using Window Function
이 단락에서는 Oracle RDBMS에서 Top-N Query를 처리하는 두 번째 방법에 대해 알아보겠습니다. Oracle RDBMS에서 Top-N Query를 처리하는 두 번째 방법은 ANSI/ISO SQL:2003에서 소개한 Window Function을 활용하는 방법입니다. Window Function에 관해 여기서 설명하는 것은 이 글의 범주를 벗어나는 것 같아 간단하게 살펴보겠습니다.
About Window Function
SQL의 한계로 말미암아 RDBMS에서 행과 행의 관계를 정의하거나 연산을 효과적으로 처리하기 어려웠습니다. 그래서 이런 문제를 해결하기 위해 별도로 프로그래밍 작업을 수행하거나, 복잡한 SQL 문을 작성해야만 했습니다. 이런 문제를 해결하기 위해서 만들어진 것이 Window Function입니다.
앞에서도 언급했듯이, Window Function은 ANSI/ISO SQL:2003에서 소개되었습니다. Window Function을 사용하면, 복잡한 SQL 문장을 간단한 SQL 문장으로 변경할 수 있으며 수행 성능도 보장할 수 있습니다. Window Function에 대해 더 자세히 알고 싶으시면, 다음 링크에서 압축 파일을 다운 받아 5WD-02-Foundation-2003-09.pdf 파일의 Window Function 단락을 읽으시거나, 그루비(오라클 클럽)에서 제공하는 웹 페이지의 내용을 참조하시면 됩니다.
Example Ⅰ
ROWNUM에서 살펴본 예제와 같은 문제를 Window Function을 활용하여 풀어보겠습니다. 이 단락에서 활용하는 Window Function은 순위를 구할 수 있는 RANK() 함수를 사용하겠습니다. ROWNUM을 사용한 부분이 RANK()라는 Window Function으로 대체된 것을 제외하면 큰 차이가 없습니다. 다만 Window Function으로 작성된 질의는 Window Function을 지원하는 다른 RDBMS에서도 활용할 수 있는 장점이 있습니다4.
SELECT A.*
FROM (SELECT EMP.*
, RANK() OVER (ORDER BY SAL DESC) AS RN
FROM EMP
) A
WHERE RN <= 5;
Example Ⅱ
ROWNUM에서 단락에서 수행했던 두 번째 예제도 아주 쉽게 해결할 수 있습니다. 이번에는 행의 번호를 얻을 수 있는 ROW_NUMBER() 함수를 이용하여 문제를 해결해 보겠습니다. 이 문제를 RANK() 함수로 풀이하면 어떻게 될까요? 이 질문에 대한 대답은 다음 단락에 있습니다.
SELECT A.*
FROM (SELECT EMP.*
, ROW_NUMBER() OVER (ORDER BY SAL DESC) AS RN
FROM EMP
) A
WHERE RN BETWEEN 5 AND 10;
Conclusion
지금까지 Window Function을 활용한 Top-N Query에 대해 살펴봤습니다. ROW_NUMBER()는 행의 번호를 얻을 수 있으며, RANK()는 순위 정보를 구할 수 있습니다. 다만 RANK() 함수를 사용할 경우, 함수의 특성으로 말미암아 추출되는 마지막 값이 중복되었을 때 모두 출력됩니다. Example Ⅱ의 질의에서 RANK() 함수를 사용하면, 5개의 행이 반환되는 것을 기대했음에도 중복 값으로 말미암아 6개의 값이 추출되는 것을 확인할 수 있습니다. 그러므로 Window Function의 각 특성을 정확하게 이해하고, 요구 사항에 적합한 방법을 적용해야 할 것입니다.
Oracle Top-N Query, Using New Mechanism
마지막으로 Oracle RDBMS에서 Top-N Query를 처리하는 세 번째 방법에 대해 알아보겠습니다. Oracle RDBMS에서 Top-N Query를 처리하는 세 번째 방법은 ANSI/ISO SQL;2011에서 소개한 Fetch 구문을 활용하는 방법입니다. SQL:2011 스펙을 보는 것이 가장 좋지만, 꽤 비싼 가격으로 구매를 해야 하므로, 문서에 쉽게 접근하기 어렵습니다. 조금 부족하지만 다음 논문을 보시면 SQL:2011에 대한 주요 내용을 쉽게 이해하실 수 있습니다.
About New Mechanism - Row Limiting Clause
Oracle RDBMS에서는 이 새로운 기법을 Row Limiting Clause이라고 부릅니다. 이 기법에 대한 자세한 사항은 다음 링크에서 확인하실 수 있습니다. 이름에서 유추할 수 있듯이, Row Limiting Clause은 질의에 의해 반환되는 행의 수 또는 비율을 제한할 수 있습니다.
Row Limiting Clause의 주요 구문은 다음과 같습니다. 새로운 문법이므로 조금 자세하게 살펴보겠습니다.
[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
{ ROW | ROWS } { ONLY | WITH TIES } ]
OFFSET은 Row Limiting이 시작되기 전에, 행의 시작 지점을 지정하고 싶을 때 사용합니다. OFFSET은 반드시 숫자로 지정해야 하며, 음수를 입력하면 0으로 설정됩니다. OFFSET 구문을 생략하면 기본값으로 0으로 설정되며, 첫 번째 행부터 시작됩니다. 이 구문에서 ROW와 ROWS는 의미 명확성을 위해 제공할 뿐, 두 키워드 사이에 커다란 차이는 없습니다.
FETCH는 반환할 행 또는 행의 비율을 지정할 때 사용합니다. 이 절을 지정하지 않으면, 모든 행은 OFFSET+1행에서 시작되어 모든 행을 반환합니다. 이 구문에서 FIRST와 NEXT는 의미 명확성을 위해 제공할 뿐, 두 키워드 사이에 커다란 차이점은 없습니다. 다음으로 rowcount를 지정하면 지정한 수만큼의 행을 추출하며, percent PERCENT를 지정하면 전체 행의 수에서 지정한 비율만큼의 행을 추출합니다. 이때, rowcount와 percent는 반드시 숫자로 입력해야 합니다. 만약 이 값들을 음수로 지정하면 0으로 처리되며, NULL을 지정하면 0개의 행을 반환합니다. 다음으로 ROW와 ROWS는 앞 단락에서 살펴본 내용과 동일합니다. 마지막으로 ONLY와 WITH TIES에 관해 알아보겠습니다. 먼저 ONLY는 행의 수 또는 비율을 정확하게 지정한 값만큼 반환합니다. 반면, WITH TIES는 마지막 행과 같은 값을 가진 행도 함께 추출합니다. 단, WITH TIES를 지정할 때에는 반드시 Order By 절을 지정해야 합니다. 만약 Order By 절을 지정하지 않으면, 같은 값을 가진 행이 반환되지 않을 수도 있습니다.
Row Limiting Clause의 제약 사항은 다음과 같습니다.
첫째, For update clause과 함께 사용할 수 없습니다. 둘째, Row Limiting Clause을 질의에서 사용하면, Select List에 Sequence의 의사 컬럼인 Currval과 Nextval을 포함할 수 없습니다. 마지막으로 질의에 Row Limiting Clause을 정의하면, 실체화 뷰(Materialized View)의 Incremental refresh를 사용할 수 없습니다.
Example Ⅰ
ROWNUM에서 살펴본 예제와 같은 문제를 Row Limiting Clause 절을 활용하여 풀어보겠습니다. 앞에서 살펴본 방법과 비교해보면, 단순하면서도 직관적으로 질의를 작성할 수 있습니다.
SELECT *
FROM EMP
ORDER BY SAL DESC
FETCH FIRST 5 ROWS ONLY;
Example Ⅱ
ROWNUM에서 단락에서 수행했던 두 번째 예제도 아주 쉽게 해결할 수 있습니다. 이 문제의 키워드는 OFFSET을 활용하는 것입니다. 만약 요구 사항이 중복 값을 포함하는 것이라면, ONLY 키워드를 WITH TIES 키워드로 변경하면 됩니다.
SELECT *
FROM EMP
ORDER BY SAL DESC
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;
Conclusion
지금까지 Row Limiting Clause을 활용한 Top-N Query에 대해 살펴봤습니다. 실제로 Row Limiting Clause은 이 예제에서 살펴본 것 이외에 더 많은 기능을 제공하는 아주 유용한 구문입니다. 다양한 예제를 만들어서 정확한 사용법을 습득하면, 매우 유용하게 사용하실 수 있습니다.
Conclusion
지금까지 Top-N Query에 대한 간략한 내용과 Oracle RDBMS에서 Top-N Query를 활용하는 대표적인 방법에 대해 알아봤습니다. Oracle RDBMS5도 최신 버전인 12cR1에 이르러 다른 DBMS 벤더보다는 조금 늦었지만, ANSI/ISO SQL:2008 버전에서 소개된 Top-N Query 구문을 지원하게 되어 조금 더 편리하게 Top-N Query 구문을 사용할 수 있음을 알 수 있었습니다. 이상으로 글을 마칩니다.
References
Oracle, Oracle Database SQL Language Reference 12cR1, Oracle [관련링크]
Oracle-base, Row Limiting Clause for Top-N Queries in Oracle Database 12cR1, Oracle-base [관련링크]
변진석, 제6절 윈도우 함수, 그루비 [관련링크]
Wikipedia, SQL:2003, Wikipedia [관련링크]
Wikipedia, SQL:2008, Wikipeida [관련링크]