SQL튜닝이론 - TOP-N 쿼리를 활용하는 방법 (컬럼의 최대/최솟값 구하기)

SQL튜닝이론 - TOP-N 쿼리를 활용하는 방법 (컬럼의 최대/최솟값 구하기)

 

일반적으로 DBMS가 제공하는 내장함수를 이용해서 컬럼의 최대/최솟값을 구하는 실행계획을 보면 SORT AGGREGATE 오퍼레이션이 등장합니다. 전체 데이터를 실제로 정렬하지는 않지만 전체 데이터를 읽으면서 값을 비교하기 때문에 SORT AGGREGATE 오퍼레이션이 나타나게됩니다.

 

내장함수로 컬럼의 최대/최솟값 구하기

 

SELECT MAX(SAL)
FROM EMP;

-- EXECUTION PLAN
--        SELECT STATEMETN
--            SORT AGGREGATE
--                TABLE ACCESS FULL OF EMP

 

동일한 SQL에 인덱스를 이용하면 전체 테이터를 읽지 않고도 최소 또는 최댓값을 쉽게 찾을 수 있습니다. 인덱스의 첫 번째 값이 최솟값이고, 마지막 값이 최댓값이기 때문입니다. 인덱스를 이용해 최댓값을 찾을 때의 실행계획은 아래와 같습니다.

 

CREATE INDEX EMP_X1 ON EMP(SAL);

SELECT MAX(SAL) FROM EMP;

--EXECUTION PLAN
-- SELECT STATEMENT
--   SORT (AGGREGATE)
--     FIRST ROW
--         INDEX FULL SCAN MIN/MAX OF EMP_X1

 

전체 데이터를 읽지 않고 인덱스를 이용해 최소 또는 최댓값을 구하려면, 조건절 컬럼과 함수 인자 칼럼이 모두 인덱스에 포함되야 합니다. 테이블 데이터에 대한 엑세스가 발생하지 않아야 가능합니다.

 

CREATE INDEX EMP_X1 ON EMP (DEPTNO, MGR, SAL);

SELECT MAX(SAL)
FROM EMP
WHERE DEPTNO = 30 AND MGR = 7698;

-- EXECUTION PLAN
-- SELECT STATEMENT
--   SORT (AGGREGATE)
--     FIRST ROW
--       INDEX (RANGE SCAN (MIN/MAX) OF EMP_X1)

 

조건절 컬럼과 함수 인자 컬럼이 모두 인덱스에 포함돼있고, 인덱스 선두 컬럼인 DEPTNO, MGR 모두 = 조건이므로 두 조건을 만족하는 레코드 중 가장 오른쪽에 있는 인덱스 레코드를 읽습니다. 실행계획의 FIRST ROW는 조건을 만족하는 레코드를 인덱스에서 하나 찾았을 때 바로 멈춘다는 것을 의미합니다. 이를 FIRST ROW STOPKEY 알고리즘이라 합니다.

 

CREATE INDEX EMP_X1 ON EMP(DEPTNO, SAL, MGR);

SELECT MAX(SAL)
FROM EMP
WHERE DEPTNO = 30 AND MGR = 7698;

-- EXECUTION PLAN
-- SELECT STATEMENT
--   SORT (AGGREGATE)
--     FIRST ROW
--       INDEX (RANGE SCAN (MIN/MAX) OF EMP_X1)

 

위의 SQL의 실행과정은 DEPTNO 조건을 만족하는 범위의 가장 오른쪽으로 가면 가 큰 SAL 값을 읽게됩니다. 거기서부터 스캔을 시작해 MGR 조건을 만족하는 레코드를 하나 찾았을때 스캔을 멈춥니다. DRPTNO 조건은 엑세스 조건, SAL 조건은 필터조건 입니다. 여기서도 조건절 컬럼과 MAX 컬럼이 모두 인덱스에 포함돼 있으므로 FIRST ROW STOPKEY 알고리즘이 적용됩니다.

 

CREATE INDEX EMP_X1 ON EMP(DEPTNO, SAL);

SELECT MAX(SAL)
FROM EMP
WHERE DEPTNO = 30 AND MGR = 7698;

-- EXECUTION PLAN
-- SELECT STATEMENT
--   SORT (AGGREGATE)
--     TABLE ACCESS BY INDEX ROWID OF 'EMP'
--       INDEX (RANGE SCAN OF EMP_X1)

 

위 쿼리는 MGR 조건이 인덱스에 포함돼있지 않습니다. MGR 조건은 테이블에서 필터링해야합니다. 테이블 엑세스를 해야하는 경우 FIRST ROW STOPKEY 알고리즘이 작동하지 않습니다.

 

TOP-N 쿼리를 이용해 칼럼의 최소/최댓값 구하기

 

ROWNUM 을 이용해 컬럼의 최소 / 최댓값을 구할 수 있습니다.

 

CREATE INDEX EMP_X1 ON EMP (DEPTNO, SAL);

SELECT *
FROM (
  SELECT SAL
  FROM EMP
  WHERE DEPTNO = 30
  AND MGR = 7698
  ORDER BY SAL DESC
)
WHERE ROWNUM <= 1;

 

TOP-N 쿼리에 작동하는 TOP-N STOPKEY 알고리즘은 조건절의 모든 컬럼이 인덱스에 포함되지 않아도 동작합니다. 위 SQL에서 조건절에 사용된 MGR 컬럼이 인덱스에 없지만, 가장 큰 SAL 값을 찾기위해 DEPT 조건을 만족하는 레코드 전체를 읽지 않습니다. DEPTNO 조건을 만족하는 가장 오른쪽 레코드 부터 테이블 엑세스하다가 MGR 조건을 만족하는 레코드를 찾으면 스캔을 멈춥니다. 인라인뷰를 사용해 쿼리의 가독성이 떨어지지만 성능면에서 MIN/MAX 쿼리보다 우수합니다.