인덱스를 이용한 소트 연산 생략

• 인덱스는 항상 키 컬럼 순으로 정렬상태 유지
• 이를활용하면 SQL에 Order By또는 Group By 절이있어도 소트연산 생략가능.
• 여기에 Top N쿼리 특성을 결합합면, OLTP시스템에서 대량 데이터 조회할때 매우 빠른 응답속도를 낼 수 있다.
• 특정조건만족하는 최소값, 최대값도 빨리 찾을수 있어 이력 데이터 조회시 유리

1. Sort Order By 생략

• 인덱스 선두 컬럼을 [종목코드 + 거래일시] 순으로 구성하지 않으면, 아래쿼리에서 소트 연산을 생략할 수 없다

  select 거래일시, 체결건수, 체결수량, 거래대금
  from 종목거래
  where 종목코드 = 'KR123456'
  order by 거래일시

• 인덱스로 소트연산을 생략할수 없을때 실행계획
  • 종목코드='KR123456'조건을 만족하는 레코드를 인덱스에서 모두 읽어야하고, 그만큼 많은 테이블 랜덤 액세스 발생
  • 모든데이터를 다 읽어 거래일시 순으로 정렬하고 출력시작하므로 OLTP환경에서 빠른응답속도내기 어려움
• SQL문에 Order By절이 있어도 인덱스로 소트연산이되면 옵티마이저가 Sort Order By오퍼레이션을 생략한다.
• 소트연산을 생략함으로써 위의 종목코드='KR123456'조건을 만족하는 전체 레코드를 읽지 않고도 결과집합 출력을 시작할 수 있게 되었다. > 범위처리가 가능한 상태가 되었다
• 이원리를 사용하면 소트해야할 대상 레코드가 무수히 많은상황에서 극적인 성능 개선효과를 얻을 수 있다.

부분범위 처리를 활용한 튜닝 기법, 아직도 유효한가?

• 토드나 오렌지등 클라이언트 프로그램이 DB서버 접속하는 2-Tier환경에서는 이특징을 활용한 튜닝기법을 많이 활용
• 클라이언트와 DB서버 사이 WAS, AP서버 등이 존재하는 3-Tier아키텍처는 서버 리소스를 수많은 클라이언트가 공유하는 구조이므로 클라이언트가 특정 DB커넥션을 독접할 수 없다
• 단위작업을 마치면 DB커넥션을 바로 커넥션 풀에 반환해야 하므로 그전에 쿼리조회결과를 클라이언트에 '모두'전송하고 커서(Cursor)를 닫아야만 한다.
• 따라서 쿼리결과 집합을 조금씩 나눠서 전송하는 방식을 사용할 수 없다.
• 하지만 부분 처리원리는 3-Tier환경에서도 여전히 유효, Top N쿼리 활용!

2. Top N 쿼리

• 전체 결과집합 중 상위 N개 레코드만 선택하는 쿼리
• SQL Server(TOP 10)나 Sybase(FETCH FIRST 10 ROWS ONLY)는
  Top N쿼리를 아래와 같이 손쉽게 작성할수 있다
• 오라클에서는 아래처럼 인라인 뷰로 한번 감싸야 하는 불편함이 있다.

  select * from(
  select 거래일시, 체결건수, 체결수량, 거래대금
  from 종목거래
  where 종목코드 = 'KR123456'
  and 거래일시 >= '20180304'
  order by 거래일시
  )
  where rownum <= 10

• [종목코드 + 거래일시] 인덱스 이용하면, 옵티마이저는 소트연산을 생략하며, 인덱스를 스캔하다 열 개 레코드를 읽는순간 바로 멈춘다.
• 실행계획에 COUNT(STOPKEY), Top N Stopkey알고리즘

  페이징 처리

• 3 Tier환경에서는 대량의 결과 집합을 조회할때 페이징 처리 기법을 활용
• 표준패턴

  select
     from (
        select rownum no, a.
        from
         (
          / SQL Body*/
         ) a
     where rownum <= (:page10)
     )
  where no >= (:page-1) * 10 + 1

• 뒤쪽 페이지로 이동할수록 데이터량이 많아지는 단점이있지만, 보통 앞쪽 일부데이터만 확인하므로 문제가 되지않는다.
• 예를들어 인터넷카페 게시글 목록 조회나, 은행사이트 입출금내역조회시 일반적으로 앞쪽페이지만 확인한다.
• 3-Tier환경에서 부분범위 처리를 활용하기 위해 우리가 할 일은 다음과 같다
  1. 부분범위 처리 가능하도록 SQL작성, 부분범위 처리가 잘작동하는지 토드, 오렌지등 쿼리툴에서 테스트
  2. 작성한 SQL문을 페이징 처리용 표준패턴 SQL Body부분에 붙여 넣는다.
• 부분범위 처리 가능하도록 SQL을 작성한다는것은 인덱스 사용이 가능하도록 조건절을 구사하고, 조인은NL위주로 처리하고, Order By절이 있어도 소트연산을 생략할 수 있도록 인덱스를 구성

페이징 처리 ANTI패턴

• 위 SQL문분석해보면 Order By 아래쪽 rownum조건이 불필요해보인다, 이조건을 제거하고 아래와같이 표현하는게 더 간결하다

  select
  from (
         select rownum no, a.
         from (
              select 거래일시, 체결건수, 체결수량, 거래대금
              from 종목거래
              where 종목코드='KR123456'
              and 거래일시 >= '20180304'
              order by 거래일시
              )a
          )
  where no between (:page-1)10 + 1 and (:page 10)

• 이렇게 할경우 Stopkey 실행계획이 없어진다, 소트가능하도록 인덱스 구성하면 소트생략은 가능하지만,
  Stopkey가 작동하지 않아 전체범위를 처리한다

3. 최소값/최대값 구하기

• MIN, MAX구하는 SQL의 실행계획을 보면, Sort Aggregate오퍼레이션이 나타남.
• Sort Aggregate를위해 전체 데이터 정렬은 않지만, 전체데이터를 읽으며 값을 비교.
• 인덱스는 정렬되어있어 전체데이터 읽지않아도 최소/최대값 쉽게찾음
• 인덱스로 최대값찾을때 실행계획 INDEX (FULL SCAN(MIN/MAX)) OF 'EMP_X1'

3.1 인덱스 이용해 최소/최대값 구하기 위한 조건

• 조건절 컬럼과 MIN/MAX함수인자 컬럼이 모두 인덱스에 포함돼 있어야한다.
• 실행계획 FIRST ROW는 조건을 만족하는 레코드 하나를 찾았을때 바로 멈춘다는 뜻.(First Row Stopkey)

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

• 위 SQL일때 인덱스가 [DEPTNO + SAL + MGR]순이면 DEPT는 액세스조건, MGR은 필터조건이다.(Range Scan (MIN/MAX))
  조건절컬럼과 MAX컬럼이 모두 인덱스포함되어있으므로 First Row Stopkey알고리즘이 작동
• 인덱스가 [SAL + DEPTNO + MGR]순일때 조건절 컬럼이 둘다 인덱스 선두 컬럼이 아니므로 INDEX RANGE SCAN은 불가
• INDEX FULL SCAN(MIN/MAX) 방식으로 전체레코드중 가장 오른쪽에서 스캔시작해 DEPTNO, MGR 조건을 만족하는 레코드하나를 찾았을때 멈추면된다, 둘다 필터조건, 여기서도 조건절 컬럼과 MAX컬럼이 모두 인덱스 포함돼 있으므로 First Row Stopkey 알고리즘이 작동
  
  
• 조건절 컬럼과 MAX컬럼 중 어느 하나가 인덱스에 포함돼 있지않은경우 SORT AGGREGATE동작 수행
• [DEPTNO+SAL]로 구성시 DEPTNO조건을 만족하는 '전체'레코드를 읽어 테이블에서 MGR조건을 필터링한 후 MAX(SAL)값을 구한다. First Row Stopkey알고리즘이 작동하지 않는다.

3.2 Top N 쿼리를 이용해 최소/최대값 구하기

-[DEPTNO + SAL] 인덱스일때 Top N 쿼리 수행시 COUNT(STOPKEY) 수행

• 모든 컬럼이 인덱스에 포함돼 있지않아도 잘 작동한다.
• DEPT = 30을 만족하는 '전체'레코드를 읽지않는다, 가장 오른쪽부터 역순으로 스캔하면서 테이블을 액세스하다가 MGR=7698조건을 만족하는 레코드하나를 찾으면 바로 멈춘다.

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

4. 이력 조회

• 이력조회가 필요시, 이력테이블을 따로 관리해야 한다.
• 이력데이터 조회시 First Row Stopkey나 Top N Stopkey알고리즘이 작동할 수 있게 인덱스 설계 및 SQL구현하는일은 고급 SQL튜너가 되기위해 반드시 정복해야할 산.
• 이력중 최신데이터를 조회하는 여러방법
  • Top N stopkey
  • exists
  • INDEX_DESC힌트 + ROWNUM 사용
• 인라인 뷰 안쪽으로 메인쿼리 컬럼이 파고드는것을 'Predicate Pushing'이라고 부르는 쿼리변환이 작동
  11g부터 제공하는기능.

4.1 상황에 따라 달라져야하는 이력 조회 패턴

• 전체장비 대상으로 조회할때는 Stopkey기능 작동여부가 튜닝의 핵심요소가 아니다.
• 전체 장비 이력을 조회할때는 윈도우함수를 이용하는것이 효과적.
• 전체조회시 데이터량이 많으면 full scan에 해시조인을 이용하면 더빠르다

  MAX(변경순번) KEEP (DENSE_RANK LAST ORDER BY 변경일자, 변경순번) 변경순번
  , MAX(상태코드) KEEP (DENSE_RANK LAST ORDER BY 변경일자, 변경순번) 상태코드

5. Sort Group By 생략

• 그룹핑 연산에도 인덱스를 활용할 수 있다.
• 아래 SQL에 region이 선두 컬럼인 인덱스를 이용하면, Sort Group By 연산을 생략할 수 있다.
• 실행계획에는 Sort group By Nosort라고나온다

  select region, agv(age), count(*)
  from customer
  group by region
  

• 수행순서
  1. 인덱스에서 'A'구간을 스캔하면서 테이블을 액세스하다가 'B'를 만나는 순간, 그때까지 집계한 값을 운반단위에 저장한다.
  2. 계속해서 'B'구간을 스캔하다 'C'를 만나는 순간, 그때까지 집계한 값을 운반단위에 저장한다.
  3. 계속해서 'C'구간을 스캔하다가 'D'를 만나는 순간, 그때까지 집계한 값을 운반단위에 저장한다, ARRAY SIZE가 3이므로 지금까지 읽은 A,B,C에 대한 집계결과를 클라이언트에게 전송하고 다음 Fetch Call이 올때까지 기다린다.(추가 Fetch Call이 오지않을 수도 있다 그럴 경우. 작업은 여기서 끝)
  4. 클라이언트로부터 다음 Fetch Call이오면, 1~3과정반복, 물론 'D'부터 시작
• 이처럼 인덱스를 이용해 Nosort방식으로 Group By를 처리하면 부분범위 처리가 가능해진다.

6.SORT AREA를 적게 사용하도록 SQL 작성

• 소트연산이 불가피하다면 메모리내에서 처리를 완료할 수 있도록 노력해야 한다.
• Sort Area를 늘릴수도있지만 Sort Area를 적게사용할 방법부터 찾아본다

6.1 소트데이터 줄이기

• 1번

  select lpad(상품번호, 30) || lpad(상품명, 30) || lpad(고객ID, 10)
  || lpad(고객명, 20) || to_char(주문일시, 'yyyymmdd hh24:mi:ss')
  from 주문상품
  where 주문일시 between :start and :end
  order by 상품번호

• 2번

  select lpad(상품번호, 30) || lpad(상품명, 30) || lpad(고객ID, 10)
  || lpad(고객명, 20) || to_char(주문일시, 'yyyymmdd hh24:mi:ss')
  from (
  select 상품번호, 상품명, 고객ID, 고객명, 주문일시
  from 주문상품
  where 주문일시 between :start and :end
  order by 상품번호
  )

• 1번은 레코드당 107바이트로 가공한 결과집합을 Sort Area에 담는다, 2번 SQL은 가공하지 않은 상태로 정렬을 완료하고나서 최종 출력할 때 가공한다, 2번이 Sort Area를 훨씬 적게 사용한다.
• 똑같은 쿼리에서 SELECT * 조회보다 SELECT 필요한컬럼만 조회하는것이 Sort Area를 적게사용한다.

6.2 Top N 쿼리의 소트 부하 경감 원리

• 전교생 1000명중 가장 큰 학생 열명을 선발하려한다, 어떤방법이있을까
• 전교생을 키순서대로 정렬한 학생명부가있으면 가장 위쪽 10명을 선발하면된다 (Top N Stopkey 알고리즘)
• 학생명부가없을경우 (Top N 소트)
  • 전교생 운동장 집합
  • 맨앞줄 맨 왼쪽있는 학생 열명을 단상 앞으로 불러 키순서대로 세운다.
  • 나머지 990명을 한명씩 교실로 들여보내면서 현재 TOP10 위치에있는 학생과 키를 비교
    더 큰 학생이 나타나면, 현재 TOP10위치에 있는 학생을 교실로 보낸다
  • TOP10에 새로 진입한 학생 키에 맞춰 자리를 재배치
  • 전교생이 교실로 들어갈때까지 위의 3,4번과정을 반복하면 가장키큰 10명이 운동장에 남는다.
• 위설명에서 top 10명이 Sort Area공간에 있는것, Sort Area사용량 최소화, 소트연산회수 최소화.
• 이방식으로 처리하면 대상 집ㅎ바이 아무리 커도 많은 메모리 공간이 필요하지 않다.

6.3 Top N쿼리가 아닐때 발생하는 소트부하

• physical read와 physical write(temp table space)가 발생한다

6.4 분석함수에서의 Top N 소트

• 윈도우함수 중 rank나 row_number함수는 max함수보다 소트 부하가 적다. Top N 소트 알고리즘이 작동하기 때문