[4] 인덱스를 이용한 소트 연산 대체

(1) Sort Order By 대체

select custid, name, resno, status, tel1
from   customer
where  region = 'A'
order by custid

• [region + custid] 인덱스를 사용하면 sort order by 연산을 대체할 수 있음.
• 소트 오퍼레이션이 나타나지 않음
• 소트 대상 레코드가 무수히 많고 그 중 일부만 읽고 멈출 수 있을 때만 유용함.

(2) Sort Group By 대체

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

• region이 선두 컬럼인 결합 인덱스나 단일 컬럼 인덱스를 사용하면 Sort Group By 연산을 대체 할 수 있음.
• 'SORT GROUP BY NOSORT'라고 표시됨

(3) 인덱스를 활용한 Min, Max 구하기

• 인덱스가 항상 정렬 상태를 유지한다는 특징을 이용하여 Min, Max 값 추출
• [주문일자 + 주문번호]

주의할 점

max 함수 내에서 인덱스 컬럼을 가공하면 인덱스를 사용하지 못하게 될 수 있다.

[5] 소트 영역을 적게 사용하도록 SQL 작성

소트 연산이 불가피하다면 메모리 내에서 처리되게 하려고 노력해야 한다.

(1) 소트 완료 후 데이터 가공

-- 1번 쿼리
select lpad(empno, 10) || lpad(ename, 30) || lpad(job, 30)
from   emp
where  sal <= 2000
order by job;

-- 2번 쿼리
select lpad(empno, 10) || lpad(ename, 30) || lpad(job, 30)
from  (select empno  , ename , job
       from   emp
       where  sal <= 2000
       order by job);

• 1번 sql은 가공된 결과치를 소트영역에 담는다.
• 2번 sql은 가공되지 않은 상태로 정렬을 완료하고 나서 최종 출력할 때 가공 하므로
  1번 sql에 비해 소트영역을 적게 사용한다.
  • lpad 함수 사용으로 인해 가공전 컬럼보다 데이터가 늘어나는 경우에 사용

(2) Top-N 쿼리

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

• 소트 연산(=값 비교) 횟수와 소트 영역 사용량을 최소화할 수 있다.
• [종목코드 + 거래일시] 인덱스가 존재하면 order by 연산을 대체할 수 있다.
• rownum 조건으로 N건에서 멈춘다.
  • 실행계획의 'COUNT (STOPKEY)'가 그것을 의미함.

1. Top-N 쿼리의 소트 부하 경감 원리

예시 1

• [종목코드 + 거래일시] 인덱스가 없으면 종목코드를 선두로 갖는 다른 인덱스를 사용하거나,
  Full Table Scan 방식으로 처리된다.
• 이때 정렬 작업이 불가피하지만 Top-N 쿼리로 소트 영역을 최소한으로 사용하게 할 수 있다.

해결

• Top 10 (rownum <= 10)이면, 10개의 레코드를 담을 배열만 할당(소트 영역 사용량 줄임)해서
  처음 10개 레코드를 정렬된 상태로 담는다.
• 이후 읽는 레코드에 대해서 맨 우측의 가장 큰값과 비교해서,그보다 작은 값이 나타날 때만 배열 내에서 다시 정렬을 시도한다. => 소트 연산 횟수 줄임

2. Top-N 쿼리 알고리즘이 작동하지 못하는 경우

조건절에 ROWNUM 이 있어야 작동하며 없으면 작동하지 않는다.

SELECT *
FROM  (SELECT ROWNUM NO , 거래일시 , 체결건수 , 체결수량 , 거래대금
       FROM  (SELECT 거래일시 , 체결건수 , 체결수량 , 거래대금
              FROM   시간별종목거래
              WHERE  종목코드 = :isu_cd AND    거래일시 >= :trd_time
              ORDER BY 거래일시 )
       WHERE  ROWNUM <= 100  -- 빼면 Top-N 쿼리 미작동 )
WHERE NO BETWEEN 91 AND 100

[6] 소트 영역 크기 조정

  alter session set sort_area_size = 1048576;

  alter session set workarea_size_policy = manual;
  alter session set sort_area_size = '10485760;

1. Oracle 8i : sort_area_size 파라미터로 조정함

2. Oracle 9i ~ : '자동 PGA 메모리 관리(Automatic PGA Memory Management)'

• pga_aggregate_target 파라미터에 인스터스 전체 이용 가능한 PGA 메모리 총량을 지정하여
  Oracle이 자동으로 각 세션에 메모리를 할당해 준다.
• workarea_size_policy를 auto로 설정하면 됨(기본설정)
  • sort_area_size 파라미터는 무시된다.
  • 수동으로 전환하여 배치 등 작업에 필요한 만큼의 소트 영역 크기를 늘림으로써
    성능을 향상시킬 수 있다.