최솟값, 최댓값과 이력 조회
MIN/MAX의 FIRST ROW 최적화 조건, 최종 이력 조회 SQL별 버퍼 비교와 KEEP DENSE_RANK
#핵심 요약
인덱스 선두에 조건 컬럼이 있고 그다음이 MIN/MAX 대상 컬럼이면, 인덱스 한쪽 끝에서 한 건만 읽고 멈춤(FIRST ROW, INDEX RANGE SCAN (MIN/MAX))
조건 컬럼 가운데 하나라도 인덱스에 없어 테이블에서 걸러야 하면 FIRST ROW 최적화가 작동하지 않음
인덱스 컬럼을 연결, 가공해 한 번에 MAX를 구하면 FIRST ROW가 사라짐
컬럼별로 MAX를 나눠 구하거나, index_desc 힌트와 ROWNUM <= 1로 인덱스를 뒤에서 한 건만 읽게 함
대상 장비가 소수이면 장비별 최종 이력을 인덱스로 한 건씩 찾는 방식이 유리하고, 전체 장비를 대상으로 하면 ROW_NUMBER로 한 번에 처리하는 방식을 고려함(예제 74의 대비)
#개념과 동작 원리
FIRST ROW가 깨지는 가공 예는 인덱스 사용을 방해하는 조건에도 있음
실습의 이력 테이블 분리 근거: 운영 테이블에 변경 내역까지 누적하면 현재 상태를 조회하는 모든 쿼리가 느려지고, 현재 데이터와 이력을 나누면 각자에 맞는 인덱스 전략을 쓸 수 있음
실습의 KEEP 문법 설명: MAX(컬럼) KEEP (DENSE_RANK LAST ORDER BY 기준)은 그룹 안에서 기준 컬럼으로 순위를 매겨 마지막 순위 행의 컬럼 값을 꺼냄
앞의 MAX/MIN은 값 비교용이 아니라 고른 행에서 값을 꺼내려면 집계 함수로 감싸야 하는 문법 요구 때문에 붙임FIRST는 첫 순위임
#필수 명령어 또는 SQL
#FIRST ROW가 작동하는 경우와 깨지는 경우
CREATE INDEX emp_sal ON emp (sal);SELECT MAX(sal) FROM emp; -- 결과: INDEX FULL SCAN (MIN/MAX), Buffers 1CREATE INDEX emp_x1 ON emp (deptno, sal);SELECT MAX(sal) FROM emp WHERE deptno = 30 AND mgr = 7698;
-- 결과: INDEX RANGE SCAN(A-Rows 6) 뒤 테이블에서 mgr 필터, FIRST ROW 없음결과 해석:
실습 확인 문제(emp_minmax 10만 건, 인덱스 (deptno, sal), job은 인덱스에 없음)는 MAX(sal) ... WHERE deptno = 2 AND job = 'ANALYST'가 Full Scan(Buffers 406)이었고, 답으로 인덱스를 sal 역순으로 읽으며 job 조건을 만족하는 첫 행에서 멈추는 형태를 제시했음
SELECT *
FROM (SELECT /*+ index_desc(emp_minmax idx_mm_dept_sal) */ sal
FROM emp_minmax
WHERE deptno = 2 AND job = 'ANALYST'
ORDER BY sal DESC)
WHERE ROWNUM <= 1;결과 해석:
퀴즈 해설: job 조건을 만족하는 행이 sal 내림차순 앞쪽에 없으면 여전히 많은 행을 읽을 수 있음
튜닝 후 실행계획은 기재 없음
#사원별 최종 변경 이력
실습 환경: emp_m 2000명, emp_sal_hist 60만 건(사원당 300건), PK (empno, chg_date, chg_seq)
-- 한 컬럼 MAX: FIRST ROW 작동
SELECT p.empno, p.ename, p.sal,
(SELECT /*+ no_unnest */ MAX(h.chg_date)
FROM emp_sal_hist h WHERE h.empno = p.empno) AS latest_chg_date
FROM emp_m p
WHERE p.deptno = 40;결과 해석:
| 시도 | 실습 실행계획(서브쿼리 부분) | 서브쿼리 Buffers |
|---|---|---|
MAX(chg_date) |
FIRST ROW, INDEX RANGE SCAN (MIN/MAX) |
102 |
MAX(TO_CHAR(chg_date, 'RRRRMMDD') || LPAD(chg_seq, 2, '0')) |
INDEX RANGE SCAN(A-Rows 15000) |
169 |
MAX(chg_date)와 그 날짜의 MAX(chg_seq)를 따로 |
FIRST ROW 세 번 |
102 + 204 |
연습 문제2의 답(교재 371쪽 방식):
SELECT empno, ename,
SUBSTR(latest_hist, 1, 8), SUBSTR(latest_hist, 9, 2), SUBSTR(latest_hist, 11, 6)
FROM (SELECT p.empno, p.ename,
(SELECT /*+ index_desc(x pk_emp_sal_hist) */
TO_CHAR(x.chg_date, 'YYYYMMDD') || LPAD(x.chg_seq, 2, '0') || LPAD(x.sal, 6, '0')
FROM emp_sal_hist x
WHERE x.empno = p.empno
AND ROWNUM <= 1) AS latest_hist
FROM emp_m p
WHERE p.deptno = 20);결과 해석:
실습 결과: INDEX RANGE SCAN DESCENDING 위 COUNT STOPKEY, 서브쿼리 Buffers 214
한 번에 날짜, 순번, 급여를 함께 가져옴
실습에서는 이 답만 deptno = 20으로 실행해 앞의 deptno = 40 결과와 직접 비교되지 않음
#전체 장비의 최종 이력
실습 환경: 장비 1000건, 상태변경이력 1825만 건(장비당 하루 1건), 인덱스 상태변경이력_idx01(장비번호, 변경일자, 변경순번)
-- MAX 서브쿼리 두 개
SELECT p.장비번호, p.장비명, h.변경일자, h.변경순번, h.상태코드
FROM 장비 p, 상태변경이력 h
WHERE h.장비번호 = p.장비번호
AND h.변경일자 = (SELECT MAX(변경일자) FROM 상태변경이력 h2 WHERE h2.장비번호 = p.장비번호)
AND h.변경순번 = (SELECT MAX(변경순번) FROM 상태변경이력 h3
WHERE h3.장비번호 = p.장비번호 AND h3.변경일자 = h.변경일자);
-- ROW_NUMBER
SELECT p.장비번호, p.장비명, h.변경일자, h.변경순번, h.상태코드
FROM 장비 p,
(SELECT 장비번호, 변경일자, 변경순번, 상태코드,
ROW_NUMBER() OVER (PARTITION BY 장비번호
ORDER BY 변경일자 DESC, 변경순번 DESC) rnum
FROM 상태변경이력) h
WHERE h.장비번호 = p.장비번호 AND h.rnum = 1;결과 해석:
| 시도 | 실습 실행계획 | A-Rows(결과) | Buffers |
|---|---|---|---|
| MAX 서브쿼리 | NESTED LOOPS, FIRST ROW 두 번 |
50 | 358 |
| ROW_NUMBER | MERGE JOIN, WINDOW SORT PUSHED RANK(Used-Mem 97M), 이력 Full Scan |
50 | 60372 |
실습 두 실행계획은 모두 결과 50건에서 끝났음(화면에서 첫 Fetch만 받은 상태로 보임)
전체 1000건을 받았을 때의 비교는 기재 없음
연습 문제(교재 377쪽처럼 ROW_NUMBER 방식을 해시 조인으로)의 답은 /*+ leading(p) use_hash(h) */이며 결과는 기재 없음
실습에서는 이 실습으로 대량 데이터를 넣은 뒤 아카이브 영역이 가득 차 복구 영역 크기를 늘리고 RMAN으로 정리했음
#KEEP DENSE_RANK
SELECT deptno, MAX(sal) AS 최고월급,
MAX(ename) KEEP (DENSE_RANK LAST ORDER BY sal) AS 최고월급자
FROM emp
GROUP BY deptno;
SELECT deptno, MIN(hiredate),
MIN(job) KEEP (DENSE_RANK FIRST ORDER BY hiredate) AS 최초입사자_직업
FROM emp
GROUP BY deptno;결과 해석:
KEEP 문법의 공식 정의와 같은 순위 행이 여럿일 때의 동작은 기재 없음이며 공식 문서로 확인하지 못했음