-- TOP-N QUERY : 상위 N 개의 행을 조회하는 쿼리이다.
-- 3가지 방법으로, ROWNUM 방식, 분석함수 방식, ROW LIMITING 절을 알아보자
--ROWNUM 방식 : ORDER BY 절로 행을 정렬하고, 정렬된 행을 ROWNUM 슈도 칼럼으로 제한한다.
SELECT EMPNO, SAL, ROWNUM AS RN FROM EMP;
-- ROWNUM 슈도 칼럼은 1부터 시작하고 행이 반환될때 마다 순번이 증가하기 때문에 ROWNUM = 2는 항상 FALSE이다.
SELECT EMPNO, SAL, ROWNUM AS RN FROM EMP WHERE ROWNUM = 2;
-- ROWNUM 슈도 칼럼은 < 조건이나 <= 을 사용해야 한다.
SELECT EMPNO, SAL, ROWNUM AS RN FROM EMP WHERE ROWNUM <=2;
-- WHERE절로 5개의 행을 무작위로 제한한 후, ORDER BY 절로 행을 정렬 했기 때문에 의도한 결과가 나오지 않는다
-- SELECT 절은 논리적으로 FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY 순서로 수행된다.
SELECT EMPNO, SAL, ROWNUM AS RN FROM EMP WHERE ROWNUM <= 5 ORDER BY SAL;
-- 의도한 결과를 갖기 위해서는 인라인 뷰를 수행해야 한다
SELECT EMPNO, SAL, ROWNUM AS RN
FROM ( SELECT EMPNO, SAL FROM EMP ORDER BY SAL, EMPNO)
WHERE ROWNUM <=5;
-- PAGING QUERY 라고 한다.
-- ROWNUM 방식의 TOP-N쿼리는 ROWNUM 슈도 칼럼을 WHERE절에 직접 기술해야 한다.
VAR V_PR NUMBER = 5;
VAR V_PN NUMBER = 2;
SELECT EMPNO, SAL, RN
FROM ( SELECT EMPNO, SAL, ROWNUM AS RN FROM ( SELECT EMPNO, SAL FROM EMP ORDER BY SAL, EMPNO) WHERE ROWNUM <= :V_PR * :V_PN )
WHERE RN >= (:V_PR * ( :V_PN - 1 )) +1;
-- BETWEEN 을 이용한 페이징 쿼리 성능이 저하됨
SELECT EMPNO, SAL, RN
FROM (SELECT EMPNO, SAL, ROWNUM AS RN FROM ( SELECT EMPNO, SAL FROM EMP ORDER BY SAL, EMPNO))
WHERE RN BETWEEN (:V_PR * (:V_PN -1)) +1 AND :V_PR * :V_PN;
-- 경품 추첨 등 무작위로 N개의 행을 조회 해야하는 경우 ORDER BY 절에 DBMS_RANDOM.VALUE 함수를 사용할 수 있다 (32장 에서 나오는 내용이다)
SELECT EMPNO, SAL
FROM (SELECT EMPNO, SAL FROM EMP ORDER BY DBMS_RANDOM.VALUE) WHERE ROWNUM <= 3;
-- 문맥 전환에 의한 성능 저하가 발생할 수 있다. DBMS_RANDOM.VALUE 함수 대신 ORA_HASH함수를 사용하면 성능을 개선할 수 있다.
-- ORA_HASH(EXPR, MAX_BUCKET, SEED_VALUE) - EXPR 해시 값을 계산할 컬럼을 정의, MAX_BUCKET 해시 함수가 반환할 최대 버킷 값을 정의, SEED_VALUE : OPTIONAL 파라미터로 EXPR과 SEED_VALUE의 조합에 해시 함수를 적용
SELECT EMPNO, SAL
FROM (SELECT EMPNO, SAL FROM EMP ORDER BY ORA_HASH(EMPNO, TO_CHAR(SYSTIMESTAMP, 'FF9'), TO_CHAR(SYSTIMESTAMP, 'FF9')))
WHERE ROWNUM <= 3;
-- 존재 여부를 확인하는 쿼리에서도 ROWNUM 슈도 칼럼을 사용할 수 있다.SAL이 3000 이 넘는 행이 3행이지만 1행만 읽고 결과를 얻을수 있다.
SELECT NVL(MAX('Y'), 'N') AS C1 FROM EMP WHERE SAL>= 3000 AND ROWNUM <= 1;
-- EXISTS 의 중첩 서브쿼리에 ROWNUM 슈도 칼럼을 사용했다. 중첩 서브 쿼리는 세미 조인으로 수행될 수 있기 때문에 ROWNUM 슈도 칼럼을 사용할 필요가 없다.
-- 스칼라 서브쿼리를 통해서 최대값을 얻기위해 WHERE ROWNUM <= 1 ORDER BY ~~ DESC 를 하기 보다는 MAX() 함수를 통해서 최대 값을 얻는다.
SELECT
A.DEPTNO
, (SELECT X.EMPNO FROM EMP X WHERE X.DEPTNO = A.DEPTNO AND ROWNUM <= 1 ORDER BY X.EMPNO DESC) AS EMPNO
FROM DEPT A;-- (X)
-- 직원 번호의 가장 큰값이 리턴되었다.
-- 하지만 임금이 가장 높은 사람의 직원 번호를 찾는 것이므로 KEEP절을 통해서 EMPNO를 뽑아야 한다.
SELECT
A.DEPTNO
, (SELECT MAX(X.EMPNO) FROM EMP X WHERE X.DEPTNO = A.DEPTNO ) AS EMPNO
FROM DEPT A;
-- 스칼라 서브 쿼리의 정렬 조건이 반환값과 다른 경우 KEEP절을 사용해야 한다.
-- 하지만 KEEP 절을 사용하게 되면 INDEX SCAN을 하지 않으므로 쿼리의 성능이 저하될 수 있다.
SELECT
A.DEPTNO
, (SELECT MAX(X.EMPNO) KEEP (DENSE_RANK FIRST ORDER BY SAL DESC) FROM EMP X WHERE X.DEPTNO = A.DEPTNO ) AS EMPNO
FROM DEPT A;
-- 스칼라 서브쿼리를 중첩하여 ROWNUM 슈도 칼럼을 사용할 수 있다.
SELECT
A.DEPTNO
, (SELECT X.EMPNO FROM (SELECT EMPNO, DEPTNO FROM EMP ORDER BY SAL DESC, EMPNO DESC) X WHERE X.DEPTNO = A.DEPTNO AND ROWNUM <= 1) AS EMPNO
FROM DEPT A;
SELECT
A.DEPTNO
, (SELECT EMPNO FROM (SELECT X.EMPNO, X.DEPTNO FROM EMP X WHERE X.DEPTNO = A.DEPTNO ORDER BY X.SAL DESC, X.EMPNO DESC) X WHERE ROWNUM <= 1) AS EMPNO
FROM DEPT A;
-- 분석 함수를 사용한 TOP-N쿼리
SELECT
*
FROM
(SELECT EMPNO, SAL, ROW_NUMBER() OVER (ORDER BY SAL, EMPNO) AS RN FROM EMP)
WHERE RN <= 5
ORDER BY SAL, EMPNO;
--ROW_NUMBER를 사용한 페이징 쿼리
SELECT
*
FROM
(SELECT EMPNO, SAL, ROW_NUMBER() OVER (ORDER BY SAL, EMPNO) AS RN FROM EMP)
WHERE RN BETWEEN (:V_PR * (:V_PN -1)) +1 AND :V_PR * :V_PN
ORDER BY SAL, EMPNO;
-- COUNT(*) 함수를 통해 전체 건수를 같이 결과값으로 넘겨 받는데, FULL SCAN을 하게 되므로 성능이 저하될수 있으므로
-- 첫 페이지 로딩 시에만 전체 건수를 조회하는 쿼리를 별도로 수행하기도 한다.
SELECT
EMPNO
, SAL
, RN
, CN
FROM (SELECT EMPNO, SAL, COUNT(*) OVER () AS CN, ROW_NUMBER() OVER (ORDER BY SAL, EMPNO ) AS RN FROM EMP)
WHERE CEIL(RN / :V_PR) = :V_PN
ORDER BY SAL, EMPNO;
-- PERCENT_RANK 함수를 사용하면 백분률에 의한 TOP-N쿼리를 작성할 수 있다. 아래 쿼리는 사ㅣㅇ위 25%의 행을 반환한다.
SELECT
EMPNO, SAL, PR
FROM
(SELECT EMPNO, SAL, PERCENT_RANK() OVER (ORDER BY SAL, EMPNO ) AS PR FROM EMP)
WHERE
PR <= 0.25
ORDER BY SAL, EMPNO;
-- RANK, DENSE_RANK 를 사용한 순위처리
SELECT EMPNO, SAL, RK FROM (SELECT EMPNO, SAL, RANK() OVER (ORDER BY SAL) AS RK FROM EMP)
WHERE RK <= 6
ORDER BY SAL;
SELECT EMPNO, SAL, DR FROM (SELECT EMPNO, SAL, DENSE_RANK() OVER (ORDER BY SAL) AS DR FROM EMP)
WHERE DR<=6
ORDER BY SAL;
-- ROW LIMITING절 : ROW LIMITING 절은 ANSI표준 SQL 문법이다.
-- ROW LIMITING 절은 ORDER BY 절 다음에 기술하며, ORDWER BY 절과 함께 수행된다. ROW와 ROWS는 구분하지 않아도된다
-- [OFFSET offset { ROW | ROWS }] [ FETCH {FIRST | NEXT} [ {rowcount | percent PERCENT} ] {ROW | ROWS} {ONLY | WITH TIES}]
-- OFFSET offset : 건너뛸 행의 갯수를 지정
-- FETCH : 반환할 행의 개수나 백분율을 지정
-- ONLY : 지정된 행의 개수나 백분율 만큼 행을 반환
-- WITH TIES : 마지막 행에 대한 동순위를 포함해서 반환
SELECT EMPNO, SAL FROM EMP ORDER BY SAL, EMPNO FETCH FIRST 5 ROWS ONLY
-- ROW LIMITING을 이용한 페이징
SELECT EMPNO, SAL FROM EMP ORDER BY SAL, EMPNO OFFSET :V_PR * (:V_PN -1) ROWS FETCH NEXT :V_PR ROWS ONLY;
-- OFFSET만 기술하면 건너뛴 행 이후의 전체 행이 반환된다.
SELECT EMPNO, SAL, ROWNUM AS RN FROM EMP ORDER BY SAL, EMPNO OFFSET 5 ROWS;
-- PERCENT 키워드를 사용하면 반환할 행의 백분율을 지정할 수 있다
SELECT EMPNO, SAL FROM EMP ORDER BY SAL, EMPNO FETCH FIRST 25 PERCENT ROWS ONLY;
-- WITH TIES 키워드를 사용하면 동순위를 처리할 수 있다. // 아래 쿼리는 RANK 함수를 사용한 TOP-N 쿼리와 동일하게 작동한다
SELECT EMPNO, SAL FROM EMP ORDER BY SAL FETCH FIRST 6 ROWS WITH TIES;
SELECT EMPNO, SAL FROM EMP ORDER BY SAL FETCH FIRST 6 ROWS ONLY;
-- 고급주제 - TOP-N 쿼리와 조인
-- EMP 테이블이 14건 이므로 조인을 14번 수행하고, 2건의 결과를 반환한다. EMP 테이블과 DEPT 테이블이 M:1 관계이고, 아우터 조인을 했기 때문에 EMP 테이블을 TOP-N 처리후에 DEPT 테이블을 조인해도 동일한 결과를 얻을수 있다.
-- EMP 테이블이 DEPT 테이블에 대해 필수 관계를 가지므로 이너 조인으로 조인해도 결과가 동일하다.
select EMPNO, SAL, DEPTNO, DNAME
FROM (SELECT A.EMPNO, A.SAL, A.DEPTNO, B.DNAME FROM EMP A, DEPT B WHERE B.DEPTNO(+) = A.DEPTNO ORDER BY A.SAL, A.EMPNO)
WHERE ROWNUM <= 2;
SELECT A.EMPNO, A.SAL, A.DEPTNO, B.DNAME
FROM (SELECT * FROM (SELECT EMPNO, SAL, DEPTNO FROM EMP ORDER BY SAL, EMPNO) WHERE ROWNUM <= 2 ) A, DEPT B
WHERE B.DEPTNO(+) = A.DEPTNO
ORDER BY A.SAL, A.EMPNO;
-- 1개의 열만 조회할 경우 TOP-N 처리후 스칼라 서브쿼리를 수행할 수 있다 // FROM -> WHERE 절 ~~-> SELECT 절에서 스칼라 서브쿼리 수행
SELECT A.EMPNO, A.SAL, A.DEPTNO
, (SELECT X.DNAME FROM DEPT X WHERE X.DEPTNO = A.DEPTNO ) AS DNAME
FROM (SELECT EMPNO, SAL, DEPTNO FROM EMP ORDER BY SAL, EMPNO ) A
WHERE ROWNUM <= 2;
-- 아래 쿼리는 TOP-N 처리 후 조인하는 방식으로 변경 할 수 없다
-- B.LOC = 'DALLAS' 조건에 의해 인라인 뷰의 결과 집합이 달라질수 있기 때문이다.
SELECT EMPNO, SAL, DEPTNO, DNAME, LOC
FROM (SELECT A.EMPNO, A.SAL, A.DEPTNO, B.DNAME, B.LOC FROM EMP A, DEPT B WHERE B.DEPTNO = A.DEPTNO AND B.LOC = 'DALLAS' ORDER BY A.SAL, A.EMPNO)
WHERE ROWNUM <= 2;
SELECT EMPNO, SAL, DEPTNO, DNAME, LOC
FROM (SELECT A.EMPNO, A.SAL, A.DEPTNO, B.DNAME, B.LOC FROM EMP A, DEPT B WHERE B.DEPTNO(+) = A.DEPTNO AND B.LOC(+) = 'DALLAS' ORDER BY A.SAL, A.EMPNO)
WHERE ROWNUM <= 2;
--TOP-N 쿼리와 UNION ALL 연산자
-- UNION ALL 연산자는 순차적으로 수행된다. TP에 의해 기술 순서와 정렬 순서가 동일하므로 데이터 ㅈ비합별로 TOP-N 처리를 수행하면 소트 부하를 경감시킬수 있다.
SELECT
*
FROM
(
SELECT 1 AS TP, DEPTNO AS NO, DNAME AS NAME FROM DEPT
UNION ALL
SELECT 2 AS TP, EMPNO AS NO, ENAME AS NAME FROM EMP
)
WHERE ROWNUM <= 3;
-- 아래 쿼리는 EMP 테이블을 읽지 않고 결과를 반환한다.
SELECT *
FROM (
SELECT * FROM (SELECT 1 AS TP, DEPTNO AS NO, DNAME AS NAME FROM DEPT ORDER BY NO) WHERE ROWNUM <= 3
UNION ALL
SELECT * FROM (SELECT 2 AS TP, EMPNO AS NO, ENAME AS NAME FROM EMP ORDER BY NO) WHERE ROWNUM <= 3
)
WHERE ROWNUM <=3;