6. Sort Area를 적게 사용하도록 SQL 작성

• 소트 연산이 불가피 할 시, 메모리 내 처리 완료하도록 함
• Sort area 크기를 늘리기보다 적게 사용하는 방법을 먼저 찾을 것

(1) 소트를 완료하고 나서 데이터 가공하기

• Sort Area를 적게 사용하는 쿼리는?

              " 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번 쿼리 : 가공된 결과치를 Sort Area를 담음 - 레코드당 105 (=30+30+10+20+15) 바이트
• 2번 쿼리 : 가공되지 않은 상태로 정렬 완료하고 최종 출력시 가공하므로 Sort Area 적게 사용

(2) TOP-N 쿼리

• 소트 연산(=값 비교) 횟수를 최소화하고, Sort Area 사용량 감소 가능

• Top-N 쿼리 작성법

• SQL Server or Sybase

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

FROM   시간대별종목거래

WHERE  종목코드 = 'KR123456'

AND    거래일시 >= '20080304';

• IBM DB2

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

FROM   시간대별종목거래

WHERE  종목코드 = 'KR123456'

AND    거래일시 >= '20080304'

ORDER BY 거래일시

FETCH FIRST 10 ROWS ONLY;

• ORACLE : Inline View로 한번 감싸야 함

SELECT * FROM   (

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

   WHERE  종목코드 = 'KR123456'

   AND    거래일시 >= '20080304'

   ORDER BY 거래일시

) 

WHERE  ROWNUM <= 10;

Execution Plan

------------------------------------------------------------------------------

0    SELECT STATEMENT Optimizer=ALL_ROWS

1  0   COUNT (STOPKEY)

2  1     VIEW

3  2       TABLE ACCESS (BY INDEX ROWID) OF '시간별종목거래' (TABLE)

4  3         INDEX (RANGE SCAN) OF '시간별종목거래_PK' (INDEX (UNIQUE))

  ☞ "종목코드 + 거래일시" 순으로 구성된 인덱스가 존재 시, 옵티마이저는 인덱스 활용으로 order by 연산 대체 가능

※ ROWNUM 조건을 사용해 N건에서 멈추도록 했으므로 조건절에 부합하는 레코드가 아무리 많아도 매우 빠른 수행 속도 낼 수 있음 

☞ 실행계획 상 "count stopkey"

TOP-N쿼리의 소트 부하 경감 원리

• "종목코드 + 거래일시" 순으로 구성된 인덱스가 없을 경우 

☞ Top-N 쿼리 알고리즘이 효과를 발휘하여 Sort order by 부하 경감

• Top-N 쿼리 알고리즘 ( rownum <= 10 경우 )

1. 우선 10개 레코드를 담을 배열 할당

2. 처음 읽은 10개 레코드를 정렬된 상태로 담기

3. 읽는 레코드과 맨 우측에 있는 값(=가장 큰 값)과 비교 (반복)

    → 작은 값이 나타날 때 배열 내에서 재정렬 시도하고, 맨 우측 값은 버림

    ☞ 전체 레코드를 정렬하지 않고 오름차순(ASC)으로 최소값을 갖는 10개 레코드를 정확히 찾아냄

        

효과 측정 : TOP-N 쿼리가 작동할 때

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

SQL> create table t as select * from all_objects;

SQL> alter session set workarea_size_policy = manual;

SQL> alter session set sort_area_size = 524288; /* Top-N 쿼리 작동 차이를 알기위해 크기를 작게 설정 */

SQL> set autotrace traceonly statistics

SQL> select count(*) from t;

Statistics

----------------------------------------------------------

         28  recursive calls

          0  db block gets

       1090  consistent gets

       1018  physical reads

          0  redo size

        :

SQL> select *

  2  from (

  3    select * from t

  4    order by object_name

  5  )

  6  where rownum <= 10 ;

Statistics

----------------------------------------------------------

        310  recursive calls

          0  db block gets

       1183  consistent gets

          0  physical reads

          0  redo size

:

          1  sorts (memory)

          0  sorts (disk)

         10  rows processed

효과 측정 : TOP-N 쿼리가 작동하지 않을 때

SQL> select *

  2  from (

  3    select a.*, rownum no

  4    from (

  5      select * from t order by object_name

  6    ) a

  7  )

  8  where no <= 10 ;

Statistics

----------------------------------------------------------

         16  recursive calls

         22  db block gets

       1153  consistent gets

       1238  physical reads

          0  redo size

:

          0  sorts (memory)

          1  sorts (disk)

         10  rows processed

(3) 분석함수에서 TOP-N 쿼리

• window sort 시에도 rank(), row_number()를 사용하면 TOP-N쿼리 알고리즘 작동 

• max() 함수 사용

SQL> select id, seq, owner, object_name, object_type, created, status

  2  from  (select id, seq

  3              , max(seq) over (partition by id) last_seq

  4              , owner, object_name, object_type, created, status

  5         from t)

  6  where  seq = last_seq;

10 개의 행이 선택되었습니다.

Execution Plan

------------------------------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |      |   752K|    70M|       | 21796   (1)| 00:04:22 |

|*  1 |  VIEW               |      |   752K|    70M|       | 21796   (1)| 00:04:22 |

|   2 |   WINDOW SORT       |      |   752K|    61M|    75M| 21796   (1)| 00:04:22 |

|   3 |    TABLE ACCESS FULL| T    |   752K|    61M|       |  1777   (1)| 00:00:22 |

------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("SEQ"="LAST_SEQ")

Statistics

----------------------------------------------------------

         51  recursive calls

          2  db block gets

       6518  consistent gets

      13603  physical reads

          0  redo size

:

          0  sorts (memory)

          1  sorts (disk)

         10  rows processed

• rank() 함수 사용

SQL> select id, seq, owner, object_name, object_type, created, status

  2  from  (select id, seq

  3              , rank() over (partition by id order by seq desc) rnum

  4              , owner, object_name, object_type, created, status

  5         from   t)

  6  where rnum = 1;

10 개의 행이 선택되었습니다.

Execution Plan

-----------------------------------------------------------------------------------------

| Id  | Operation                | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT         |      |   752K|    70M|       | 21796   (1)| 00:04:22 |

|*  1 |  VIEW                    |      |   752K|    70M|       | 21796   (1)| 00:04:22 |

|*  2 |   WINDOW SORT PUSHED RANK|      |   752K|    61M|    75M| 21796   (1)| 00:04:22 |

|   3 |    TABLE ACCESS FULL     | T    |   752K|    61M|       |  1777   (1)| 00:00:22 |

-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("RNUM"=1)

   2 - filter(RANK() OVER ( PARTITION BY "ID" ORDER BY INTERNAL_FUNCTION("SEQ") DESC )<=1)

Statistics

----------------------------------------------------------

          1  recursive calls

         66  db block gets

       6518  consistent gets

         63  physical reads

          0  redo size

:

          0  sorts (memory)

          1  sorts (disk)

         10  rows processed

• 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
• 작성자: 위충환 (실천하자)
  
• 최초작성일: 2011년 06월 13 일
• 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
• 문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^