5.3 인덱스를 이용한 Sort 연산 생략

인덱스는 항상 키 컬럼 순으로 정렬된 상태를 유지한다.

따라서 SQL에 Order by 또는 Group by 절이 있어도 소트 연산 생략이 가능하다.

* 여기에 Top N 쿼리 특성을 결합하여 온라인 트랜잭션 처리 시스템에서 대량 데이터를 조회할 때 빠른 응답속도를 낼 수 있다.

* 또는 특정 조건을 만족하는 최소값 또는 최대값을 찾을 수 있어 이력 데이터 조회할 때 매우 유용하다

select 거래일시, 체결건수, 체결수량, 거래대금

from 종목거래

where 종목코드 = ' KR123456 '

order by 거래일시

 

해당 쿼리는 [종목코드 + 거래일시] 순으로 구성하지 않으면 소트연산 생략 불가능

 

 

여기서 SORT ORDER BY는 인덱스를 위와 같이 구성하지 않을 시 나타나고

구성하게 된다면 SORT ORDER BY Operation이 생략되게 된다.

TOP N 쿼리 (TOP N StopKey Algorithm)

전체 결과집합 중 상위 N개 레코드만 선택하는 쿼리이다.

 

SQL Server나 Sybase는 Top N쿼리를 아래와 같이 작성할 수 있다.

 

 

 

SELECT TOP 10 거래일시, 체결 건수, 체결 수량, 거래 대금

FROM 종목 거래

WHERE 종목 코드 = 'KR123456'

AND 거래일시 >= '20180304'

ORDER BY 거래일시

 

그러나 오라클에서는 아래처럼 인라인 뷰로 한 번 감싸야 한다.

 

SELECT * FROM (

SELECT 거래일시, 체결 건수, 체결 수량, 거래 대금

FROM 종목 거래

WHERE 종목 코드 = 'KR123456'

AND 거래일시 >= '20180304'

ORDER BY 거래일시

)

WHERE  ROWNUM <=10;

 

오라클 쿼리에서 [종목코드 + 거래일시] 순으로 구성된 인덱스를 이용하면

옵티마이저는 소트 연산을 생략하며 아래 그림 처럼 인덱스를 스캔하다가 열 개 레코드를 읽는 순간 바로 멈춘다.

 

 

 

위 실행계획을 보면 Sort Order by Operation이 보이지 않고 (인덱스를 구성했기에)
COUNT STOPKEY 가 눈에 띈다.

이는 조건절에 부합하는 레코드가 아무리 많아도 그 중 ROWNUM 으로 지정한 건 수 만큼 결과 레코드를 얻으면 거기서 바로 멈춘다는 뜻

 

✅ 1. 테스트 테이블 생성 CREATE TABLE paging_test (     id NUMBER PRIMARY KEY,     name VARCHAR2(100),     reg_date DATE );

✅ 2. 대용량 데이터 삽입 (1,000,000건)   BEGIN   FOR i IN 1 .. 1000 LOOP     INSERT /*+ APPEND */ INTO paging_test     SELECT       (i - 1) * 1000 + LEVEL AS id,       'USER_' || ((i - 1) * 1000 + LEVEL) AS name,       SYSDATE - DBMS_RANDOM.VALUE(0, 365)     FROM dual     CONNECT BY LEVEL <= 1000;         COMMIT;  -- 주기적인 커밋   END LOOP; END;   BEGIN   DBMS_STATS.GATHER_TABLE_STATS(USER, 'PAGING_TEST'); END;

3. OFFSET/FETCH 방식 SELECT * FROM paging_test ORDER BY id OFFSET 5000 ROWS FETCH NEXT 100 ROWS ONLY;

 

 

최소값/최대값 구하기

최소값(MIN) 또는 최대값(MAX) 을 구하는 실행계획은 아래와 같이 Sort Aggregation이 나타난다.

 

전체 데이터를 정렬하진 않지만

전체 데이터를 읽으면서 값을 비교한다고 앞에서 설명하였다.

 

 

여기서 인덱스를 CREATE INDEX EMP_X01 ON EMP(SAL); 과 같이 구성하면

실행계획은 다음과 같이 바뀐다.

 

 

전체 데이터를 읽지 않고 인덱스를 이용해서 최소,최대 값을 구하려면

조건절 컬럼과 MIN/MAX 함수 인자 컬럼이 모두 인덱스에 포함되어 있어야 한다.

 

 

 

이렇게 구성하면 두 조건을 만족하는 범위 가장 오른쪽에 있는 값 하나를 읽는다.

 

실행계획 'FIRST ROW' 는 조건을 만족하는 레코드 하나를 찾았을 때 바로 멈춘다는 것을 의미한다.

 

FIRST ROW StopKey 알고리즘이라고 한다.

 

 

인덱스를 [DEPTNO + SAL + MGR] 로 구성한 경우

 

SELECT MAX(SAL) FROM EMP

WHERE DEPTNO = 30

AND MGR  = 7698;

 

 

여기서도 조건절 컬럼과 MAX 컬럼이 모두 인덱스에 포함되어 있으므로

'First Row StopKey' 알고리즘이 작동한다.

 

MGR은 필터조건으로 동작한다.

 

인덱스를 [ SAL + DEPTNO + MGR] 로 구성한 경우
 

SELECT MAX(SAL) FROM EMP

WHERE DEPTNO = 30

AND MGR  = 7698;

 

DEPTNO 와 MGR이 모두 선두컬럼이 아니므로

Index Range Scan은 불가하고

Index Full Scan 방식으로 풀리게 된다.

 

 

 

인덱스를 [  DEPTNO + SAL ] 로 구성한 경우
 

SELECT MAX(SAL) FROM EMP

WHERE DEPTNO = 30

AND MGR  = 7698;

 

DEPTNO=30 을 만족하는 조건은 바로 찾을 수 있으므로 MAX(SAL)은 쉽게 찾을 수 있으나

MGR=7698 조건은 테이블에서 필터링 해야만 한다.

 

이럴 때는 전체 레코드를 읽어야 한다.