heo-jae-won@home:~$

Archive

About

RSS

SQL Tuning summary No.3

SORT 연산

  • oracle은 가공된 데이터 집합이 필요할 때, PGA와 Temp 테이블스페이스를 활용한다.
    • sort merge join
    • hash join
    • group by
    • order by
  • sort는 PGA의 Sort Area에서 먼저 이뤄지고, 그거로도 부족하면 Temp 테이블스페이스를 활용한다.
  • Sort Area에서만 끝나면 메모리 소트(Internal sort), 디스크까지 활용하면 디스크 소트(External sort)라고 한다.
  • sort가 이뤄지는 방식은 아래 이미지와 같다.
소트할 대상을 SGA 버퍼캐시를 통해 읽는다.
sort Area에서 정렬을 해본다.
안되면 Temp 테이블스페이스에 임시 세그먼트를 만들어 저장한다.
temp에 저장해 둔 집합을 sort run이라고 부른다.
클라이언트에 전달할 때는 PGA에 있는 걸 먼저 준다.
그 다음 temp에 있는 것을 PGA에 merge해서 PGA에서 client로 전달한다.
  • 소트 연산은 메모리 집약적이며, CPU 집약적이다.
  • 거기다 PGA가 작으면 disk I/O까지 일어나니 쿼리 성능에 핵심적이다.
  • 될 수 있다면 sort를 발생시키지 않는게 좋고, 필요하면 메모리로 끝내야 한다.

SORT 연산 byte 최소화

  • sort area에서 sort를 해야 한다면, byte를 적게 잡아야 한다.
  • 그래야 메모리 안에서 sort가 이뤄진다. sort area를 벗어나면 바로 disk I/O가 돼버린다.
  • LPAD 등으로 가공하면서 공백이 늘면 그만큼 byte도 늘어난다. 
    SELECT LPAD(상품번호, 30) || LPAD(상품명, 30) || LPAD(고객ID, 10)
      ||  LPAD(고객명, 20) || TO_CHAR(주문일시, 'yyyymmdd hh24:mi:ss')
FROM 주문상품
WHERE 주문일시 BETWEEN :start and :end
ORDER BY 상품번호
  • 차라리 아래처럼 sort 자체는 기본 field로 해야한다.
  • 그래야 sort byte가 적어져 sort area에서 sort가 끝난다. 
    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 상품번호
)
  • 마찬가지 이유로 필요한 필드만 적어야 sort byte를 최소화한다. 
    SELECT 계좌번호, 총예수금
FROM 예수금원장
ORDER BY 총예수금 DESC
  • sort area를 최소화하는 기술로 top n sort도 있다.
  • top n sort는 top n stopkey보단 비효율적이지만, sort 용량을 크게 줄여주는 데서 의미가 있다.
  • top n stopkey를 유도하는 SQL패턴을 그대로 사용하면 된다.
    • top n sort는 딱 가져올 만큼의 공간만 존재하면 된다.
    • 10개라면 10개를 저장한 공간만 있으면 총데이터 집합이 엄청 커도 10개만 가져오고 멈춘다.
    • 따라서 sort area에 들어가는 건 10개의 byte뿐이다.
    • 따라서 top n sort의 경우 대부분 trace에 pw,pr이 0으로 찍힌다. disk I/O를 안타는 셈이다. 
      SELECT * 
FROM (
SELECT ROWNUM no, a.*
FROM (
SELECT 거래일시, 체결건수, 체결수량, 거래대금
FROM 종목거래
WHERE 종목코드 = 'KR123456'
AND 거래일시 >= '20180304'
ORDER BY 거래일시
) a
WHERE ROWNUM <= (:page * 10)
)
WHERE no >= (:page - 1) * 10 + 1
  • 절대 아래처럼 바꾸면 안 된다.
  • 보기에 간단하지만, 이러면 top n sort가 안 먹힌다.
  • 그럼 전체를 긁고서 sort를 하니까 공간이 부족해 disk I/O가 일어날 수 있다. 
    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)
  • 참고로 mysql은 top n sort를 지원해주지 않는다.
  • top n sort처럼 처리되지 않고 위의 절대 바꾸며 안되는 패턴처럼 처리된다. 
    SELECT 거래일시, 체결건수, 체결수량, 거래대금
FROM 종목거래
WHERE 종목코드 = 'KR123456'
AND 거래일시 >= '2018-03-04'
ORDER BY 거래일시
LIMIT :offset, 10;
  • max 대신 window function을 쓰는 것도 좋은 습관이다.
  • 그러면 top n sort 알고리즘이 발동해 sort 부하가 적다.
    • 그만큼 disk I/O가 덜 생겨서 빨라지게 된다는 의미다.
  • 실험을 위해 아래같이 sort_area 메모리공간을 줄여보자. 
    alter session set workarea_size_policy = manual;
alter session set sort_area_size = 524288;
  • 아래는 max를 썼다.
  • 그럼 disk I/O가 많다. 
    SELECT 장비번호, 변경일자, 변경순번, 상태코드, 메모
FROM (SELECT 장비번호, 변경일자, 변경순번, 상태코드, 메모
          , MAX(변경순번) OVER(PARTITION BY 장비번호) 최종변경순번
          FROM 상태변경이력
          WHERE 변경일자 = :upd_dt)
WHERE 변경순번 = 최종변경순번
  • rank 같은 window function을 쓰면 훨씬 disk I/O가 줄어든다. 
    SELECT 장비번호, 변경일자, 변경순번, 상태코드, 메모
FROM (SELECT 장비번호, 변경일자, 변경순번, 상태코드, 메모
          , RANK(변경순번) OVER(PARTITION BY 장비번호 ORDER BY 변경순번 DESC) 최종변경순번
          FROM 상태변경이력
          WHERE 변경일자 = :upd_dt)
WHERE 변경순번 = 최종변경순번

SORT execution plan

  • sort operation은 아래와 같다.
    • sort Aggregate
      • 집계함수를 사용한 것이다.
      • 실제 데이터 정렬은 없다.
      • sum, max, min, avg 등이 예시다.
    • sort order by
      • 데이터를 정렬한다.
    • sort group by
      • 데이터를 정렬한다.
      • 그룹 개수가 적으면 수억 row가 있어도 temp table space를 쓰지 않는다.
      • order by가 없으면 대부분 hash group by로 처리된다.
      • sort group by와 다른 점은 해싱알고리즘이라는 점 말고 없다.
      • hash과 sort나 group by는 결과가 정렬됨을 보장하지 않는다.
    • sort unique
      • 서브쿼리를 unnest할 때, 서브쿼리가 1대 다 중 다쪽일 때
      • 1쪽 집합인데 조인컬럼에 unique index가 없을 때 나타난다
      • 메인쿼리와 조인하기 전에 중복 레코드를 제거해야 하기 때문이다.
      • 아니면 union, minus, intersect, distinct를 써도 나타난다.
      • distinct의 경우 hash unique가 더 많이 쓰인다.
    • sort join
      • sort merge join을 수행할 때 나타난다.
    • window sort
      • 윈도우 함수를 수행할 때 나타난다.
  • 참고로 SORT GROUP BY에서도 index를 활용해 SORT를 하지 않을 수 있다.
  • 이 경우 region이 선두컬럼이어야 한다. 
    SELECT region, avg(age), count(*)
FROM customer
GROUP BY region

SORT를 피하는 SQL 1 -EXISTS/UNION ALL

  • union, minus, distinct는 최대한 자제해야 한다.
    • union보다는 union all로 쓸 수 있는지 확인해봐야 한다.
    • distinct보다는 exists를 사용할 수 있는지 확인해봐야 한다.
    • 대량데이터가 아니라면 hash join이 아니라 NL join을 써야 한다.
  • union의 예시를 들어보자.
SELECT 결제번호, 결제수단코드, 주문번호, 결제금액, 결제일자, 주문일자
FROM 결제
WHERE 결제일자 ='20180316'

UNION

SELECT 결제번호, 결제수단코드, 주문번호, 결제금액, 결제일자, 주문일자
FROM 결제
WHERE 주문일자 = '20180316'
  • 아래와 같이 union all과 <> 연산자를 이용해 sort가 일어나지 않게 바꾼다.
SELECT 결제번호, 결제수단코드, 주문번호, 결제금액, 결제일자, 주문일자
FROM 결제
WHERE 결제일자 ='20180316'

UNION ALL

SELECT 결제번호, 결제수단코드, 주문번호, 결제금액, 결제일자, 주문일자
FROM 결제
WHERE 결제일자 = '20180316'
AND 결제일자 <> '20180316'
  • 만약 결제일자가 null 허용 컬럼이면 아래와 같이 바꿔준다.
SELECT 결제번호, 결제수단코드, 주문번호, 결제금액, 결제일자, 주문일자
FROM 결제
WHERE 결제일자 ='20180316'

UNION ALL

SELECT 결제번호, 결제수단코드, 주문번호, 결제금액, 결제일자, 주문일자
FROM 결제
WHERE 결제일자 = '20180316'
AND (결제일자 <> '20180316' or 결제일자 is null) /** LNNVL(결제일자 ='20180316') */
  • distinct를 제거하는 쿼리 튜닝도 보자.
SELECT DISTINCT p.상품번호, p.상품명, p.상품가격
FROM 상품 p, 계약 c
WHERE p.상품유형코드 = :pclscd
AND c.상품번호 = p.상품번호
AND c.계약일자 BETWEEN :dt1 and :dt2
AND c.계약구분코드 = :ctpcd
  • 위의 쿼리 대신 exists를 활용해보자.
  • exists를 이용하면 데이터를 모두 읽지 않아도 된다.
  • distinct를 사용하지 않았으니 부분범위 처리도 가능하다.
SELECT p.상품번호, p.상품명, p.상품가격
FROM 상품 p
WHERE p.상품유형코드 = :pclscd
AND EXISTS (
          SELECT 'x' FROM 계약 c
          WHERE c.상품번호 = p.상품번호
          AND c.계약일자 BETWEEN :dt1 and :dt2
          AND c.계약구분코드 = :ctpcd
        )
  • minus를 튜닝해보자.
SELECT ST.상황접수번호, ST.관제일련번호, ST.상황코드
FROM 관제진행상황 ST
WHERE 상황코드 = '0001'
AND 관제일시 BETWEEN :V_TIMEEFROM || '000000' AND :V_TIMET0 || '235959'

MINUS

SELECT ST.상황접수번호, ST.관제일련번호 ,ST.상황코드, ST.관제일시
FROM 관제진행상황 ST, 구조활동 RPT
WHERE 상황코드 = '0001'
AND 관제일시 BETWEEN :V_TIMEEFROM || '000000' AND :V_TIMET0 || '235959'
AND RPT.출동센터ID = :V_CNTR_ID
AND ST.상황접수번호 = PRT.상황접수번호
ORDER BY 상황접수번호, 관제일시
  • 아래와 같이 바꿀 수 있다.
  • not exists를 이용하면 데이터를 모두 읽지 않아도 된다.
  • distinct를 사용하지 않았으니 부분범위 처리도 가능하다.
SELECT ST.상황접수번호, ST.관제일련번호 ,ST.상황코드, ST.관제일시
FROM 관제진행상황 ST, 구조활동 RPT
WHERE 상황코드 = '0001'
AND 관제일시 BETWEEN :V_TIMEEFROM || '000000' AND :V_TIMET0 || '235959'
AND NOT EXISTS (
              SELECT 'X' FROM 구조활동
              WHERE 출동센터ID = :V_CNTR_ID
              AND 상황접수번호 = ST.상황접수번호
            ) 
ORDER BY ST.상황접수번호, ST.관제일시
  • join에 관해서도 이야기해보자.
  • 계약 table의 index가 (지점ID, 계약일시)기 때문에 order by가 생략될 수 있다.
  • 그러나 hash join이 되면서 SORT ORDER BY 연산이 일어났다.
SELECT c.계약번호, c.상품코드, p.상품명, p.상품구분코드
FROM 계약 c, 상품 p
WHERE c.지점ID = :brch_id
AND p.상품코드 = c.상품코드
ORDER BY c.계얄일시 DESC
  • 이를 NL join으로 바꿔준다.
SELECT /*+ leading(c) use_nl(p) */ c.계약번호, c.상품코드, p.상품명, p.상품구분코드
FROM 계약 c, 상품 p
WHERE c.지점ID = :brch_id
AND p.상품코드 = c.상품코드
ORDER BY c.계얄일시 DESC

SORT를 피하는 SQL 2 -TOP N쿼리

  • top n 쿼리는 아래 두개로 나뉜다.
    • top n stopkey
      • top n stopkey는 정렬이 필요 없다.
      • 거기다가 부분범위 처리만 하기에 전체 scan도 하지 않는다.
      • 조건으론 index 구성 컬럼이 where 혹은 order by에 있기만 하면 된다.
      • 다만 없거나, order by에 index 비구성 컬럼이 들어가면 top n sort가 된다.
    • top n sort
      • top n sort는 정렬은 필요하다.
      • 따라서 전체 scan은 필수적이다.
      • 다만 전체 index record를 정렬하지 않고 rownum의 갯수만큼만 정렬하면 된다.
SELECT * FROM (
  SELECT 거래일시, 체결건수, 체결수량, 거래대금
  FROM 종목거래
  WHERE 종목코드 = 'KR123456'
  AND 거래일시 >= '20180304'
  ORDER BY 거래일시
)
WHERE ROWNUM <= 10
  • 실행계획은 아래와 같이 나타난다.
  • Sort Order by는 사라지고 Count Stopkey가 생겨났다.
  • COUNT STOPKEY만 있고 SORT ORDER BY STOPKEY가 없으면 top n stopkey로 가장 좋은 경우다.
  • COUNT STOPKEY가 있고 SORT ORDER BY STOPKEY가 있으면 top n sort로 그래도 괜찮은 경우다.
  • COUNT가 있고 SORT ORDER BY가 있으면 top n query 어느것도 이용하지 못한 경우다.
    • sort를 하게 되는데, 필요한만큼만 덜어서 PGA를 채우지 못하기 때문에 disk I/O가 일어날 확률이 높아진다.
  • COUNT가 있고 SORT ORDER BY가 없는 경우도 top n query 어느것도 이용하지 못한 경우다.
    • 그래도 sort는 하지 않기 때문에 disk I/O가 일어날 확률은 낮다. COUNT + SORT ORDER BY보단 성능이 낫다.
  • 실제 웹의 페이징처리를 top n query를 적용하면 보통 아래와 같다.
  • top n 쿼리므로 ROWNUM으로 지정한 건수만큼 결과 레코드를 얻으면 거기서 멈춘다.
  • 뒤쪽 페이지로 이동할수록 읽는 데이터량도 많아지지만, 보통 앞쪽만 확인하니 문제가 거의 없다. 
    SELECT *
FROM (
SELECT ROWNUM no, a.*
FROM (
  /** SQL Body */

    ) a
WHERE ROWNUM <= (:page * 10)
)
WHERE no >= (:page - 1)*10 + 1
  • 위와 같은 sql은 아래와 같은 원칙을 구현한 것이다.
    • 부분범위 처리가 가능하도록 SQL을 작성한다.
    • 작성한 SQL문을 SQL BODY쪽에 집어넣는다.
    • ORDER BY도 필수적으로 들어가야 한다.
  • 이게 뜻하는 바는 아래와 같다.
    • 인덱스 사용 가능하도록 조건절을 구사한다.
    • 조인은 NL조인 위주로 처리한다.
    • ORDER BY절이 있어도 소트 연산을 생략할 수 있게 인덱스를 구성한다.
  • 해당 원칙을 지켜 만든 SQL을 만든다면 아래와 같은 형태일 것이다. 
    SELECT *
FROM (
SELECT ROWNUM no, a.*
FROM (
  /** SQL Body */
    SELECT 거래일시, 체결건수, 체결수량, 거래대금
    FROM 종목거래
    WHERE 종목코드 = 'KR123456'
    AND 거래일시 >= '20180304'
    ORDER BY 거래일시
    ) a
WHERE ROWNUM <= (:page * 10)
)
WHERE no >= (:page - 1)*10 + 1
  • 페이징 처리의 안티 패턴은 rownum을 지워버리는 것이다.
  • rownum을 쓸데없다고 생각하고 지우는 순간, top n query 알고리즘은 멈춘다.
  • 더 문제는 top n sort 마저도 작동하지 않아 pr, pw를 해야할 수도 있다는 사실이다. 
    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)
  • rownum을 지우면 아래와 같이 실행계획이 바뀐다.

  • count 옆에 stopkey가 없다. 소트연산은 생략되어도 top n query가 작동하진 않는다.

  • stopkey를 못 하면 아래와 같이 되어버린다.

  • 전체범위를 전부 훑어야 되는 것이다.

  • 또 다른 안티 패턴은 아래와 같이 rownum의 <= 조건에서 rownum을 별칭으로 사용하는 것이다.
  • 그럼 top n query 알고리즘은 멈춘다. 따라서 전체범위를 훑게 된다. 
    SELECT *
FROM (
SELECT ROWNUM no, a.*
FROM (
  /** SQL Body */
    SELECT 거래일시, 체결건수, 체결수량, 거래대금
    FROM 종목거래
    WHERE 종목코드 = 'KR123456'
    AND 거래일시 >= '20180304'
    ORDER BY 거래일시
    ) a
WHERE no <= (:page * 10)
)
WHERE no >= (:page - 1)*10 + 1
  • 또 다른 안티 패턴은 아래와 같이 body SQL에서 형변환을 해버리는 것이다.
  • 그럼 가공된 컬럼이 되어, 정렬이 필요해지고 top n query 알고리즘은 멈춘다. 따라서 전체범위를 훑게 된다. 
    SELECT *
FROM (
SELECT ROWNUM no, a.*
FROM (
  /** SQL Body */
    SELECT 거래일시, 체결건수, 체결수량, TO_NUMBER(거래대금)
    FROM 종목거래
    WHERE 종목코드 = 'KR123456'
    AND 거래일시 >= '20180304'
    ORDER BY 거래일시
    ) a
WHERE no <= (:page * 10)
)
WHERE no >= (:page - 1)*10 + 1
  • 그럴 땐 아래와 *를 사용하지 않고 column들을 모두 전부 쓰는 수밖에 없다. 
    SELECT *
FROM (
SELECT ROWNUM no
      ,거래일시
      ,체결건수
      ,체결수량
      ,TO_NUMBER(거래대금)
FROM (
  /** SQL Body */
    SELECT 거래일시, 체결건수, 체결수량, 거래대금
    FROM 종목거래
    WHERE 종목코드 = 'KR123456'
    AND 거래일시 >= '20180304'
    ORDER BY 거래일시
    ) a
WHERE ROWNUM <= (:page * 10)
)
WHERE no >= (:page - 1)*10 + 1
  • 거래PK는 (거래일자, 계좌번호, 거래순번)로 이뤄졌다.
  • 거래_X01은 (계좌번호, 거래순번, 결제구분코드)로 이뤄졌다.
    • 그 상황에서 아래 SQL을 실행하면 TOP N sort으로 타다. 즉 sort by 연산을 생략하지 못한다.
    • 부분범위 처리는 불가능하다는 의미다. 전체를 다 읽어야 하니 오래걸린다. 
      SELECT *
FROM (
SELECT 계좌번호, 거래순번, 주문금액, 주문수량, 결제구분코드
FROM 거래
WHERE 거래일자 = :ord_dt
ORDER BY 계좌번호, 거래순번, 결제구분코드
)
WHERE ROWNUM <=50
  • 위의 SQL의 실행계획은 아래와 같다.
    • 어차피 거래_X01 index는 쓸수가 없다. where 조건문에 거래일자가 들어갔기 때문이다.
    • 그럼 거래PK를 쓰는데, 하필 결제구분코드가 더 들어갔다.
    • 그래서 sort order by 연산이 진행된 것이다.
    • 반대로 말하면 결제구분코드만 없으면 된다. 실제로 필요하지 않다면 없애자.
      • index가 (거래일자, 계좌번호, 거래순번)이고 거래일자가 = 조건이다.
      • 그럼 거래일자 데이터를 계좌번호, 거래순번 순으로 정렬하면 중복 레코드가 없다.
      • 다시 말하면 결제구분코드는 없어도 된다는 의미다. 없애면 부분범위 처리가 가능해져 매우 빨라진다.
  • top n은 max, min 연산을 대체하는 데도 사용할 수 있다.
    • 특히 index 중 하나가 빠져도 작동해서 유연하다.
    • (DEPTNO, SAL)로 구성해도 TOP N stopkey로 작동한다.
    • 아래에서 MGR이 index 구성으로 없어도 되기에 좀 더 유연하게 index를 구성할 수 있다.
    • 다시말해 선두컬럼만 where 조건문에 있고, order by에 들어가는 건 index 구성 순서를 따른 column이어야 한다. 
      SELECT *
FROM (
SELECT SAL
FROM EMP
WHERE DEPTNO = 30
AND MGR = 7698
ORDER BY SAL DESC
)
WHERE ROWNUM <= 1;

  • 실행계획은 아래와 같이 나타난다.

  • MGR 컬럼이 index에 없으니 DEPTNO column만이 index access조건이다.
  • 따라서 원래는 DEPTNO = 30을 만족하는 모든 index record를 읽어야 한다.
  • 그러나 top n stopkey의 경우, 조건을 만족하는 모든 index 레코드를 읽지 않는다.
    • DEPTNO = 30을 만족하는 가장 오른쪽부터 역순으로 스캔하며 table을 access한다.
    • 그 중 MGR = 7698 조건을 만족하는 table record를 하나 찾는 순간 바로 멈춘다.
  • min으로 하고 싶다면 DESC가 아니라 AESC로 주면 된다.
  • 다른 예시도 들어보자.
  • 아래와 같은 쿼리가 있다고 해보자.
  • index 구성이 (customer_no, login_date)기 때문에 order by에 login_date가 빠져도 정렬이 가능하다. 
    SELECT 
from login
where 
and rownum <= 1
  • 다만 위와 같은 sql은 top n stopkey 알고리즘이 작동하지 않는다.
  • 아래와 같이 만들어줘야 top n stopkey 알고리즘이 작동하게 된다.
  • 만약 저기서 order by에 index가 아닌 column을 추가하게 되면 top n sort로 변경된다. 
    SELECT * FROM (
SELECT auto_login_yn, login_date, auto_login_token, customer_grade, customer_name
FROM 종목거래
WHERE customer_no = #{customer_no}
ORDER BY login_date
)
WHERE ROWNUM <= 1
  • top n sort를 응용하면 더보기도 만들어줄 수 있다.
  • index 구성이 모자라서 top n stopkey로 작동은 못해도 top n sort로는 작동가능하다. 
    SELECT CASE WHEN COUNT(1) = 11  THEN 1 ELSE 0 END AS HAS_MORE
FROM (
SELECT board_sqno
FROM board
WHERE board_clf = #{board_clf}
AND board_display = 'Y'
<if test = "newNo != null" >
  AND board_sqno < #{newNo}
  AND board_display_fix_yn <> 'Y'
</if>
ORDER BY board_display_fix_yn DESC
        , board_display_order ASC
        , board_sqno DESC
  )
WHERE rownum <= 11
  • top n 쿼리가 좋은 점은 index 구성이 조금 불완전함에도 작동하기 때문이다.
  • 아래 board table은 (board_sqno, board_clf)밖에 없지만, 그럼에도 아래 쿼리는 top n sort로 작동한다.
  • count stopkey와 sort oredr by stopkey 지시어가 execution plan에 모두 나오는 것을 확인할 수 있다.
  • 그러나 굳이 이렇게 길게 쓸 필요가 없다. inline view를 쓸데없이 길게 쓴 것에 불과하다.
  • inline view를 가공하지 않게 하려고 inline view를 덧씌우는 inline view를 쓰는 것인데, 형변환이 없어 불필요하다. 
    SELECT b.*
      , TO_CHAR(TO_DATE(b.registered_datetime, 'YYYYMMDDHH24MISS'), 'YYYY-MM-DD') AS rgdm
      , CASE WHEN(TO_CHAR(SYSDATE,'YYYYMMDD') - TO_CHAR(TO_DATE(b.rgdm, 'YYYYMMDDHH24MISS'), 'YYYYMMDD')) <= 7 THEN 'Y' ELSE 'N' END AS boolYN
FROM (
  SELECT a.board_sqno
          , a.board_title
          , a.image_path
          , a.rgdm
          , changed_date
  FROM (
      SELECT board_sqno
              , board_title
              , board_title
              , image_path
              , registered_date
              , changed_date
      FROM board
      WHERE board_display_yn = 'Y'
      ORDER BY fix_exps_yn DESC
              , fix_exps_sq ASC
              , board_sqno DESC
      ) a
  ) b
WHERE ROWNUM <= 10;
  • 아래와 같이 간결하게 바꿔주자. 
    SELECT b.*
      , TO_CHAR(TO_DATE(b.registered_datetime, 'YYYYMMDDHH24MISS'), 'YYYY-MM-DD') AS rgdm
      , CASE WHEN(TO_CHAR(SYSDATE,'YYYYMMDD') - TO_CHAR(TO_DATE(b.rgdm, 'YYYYMMDDHH24MISS'), 'YYYYMMDD')) <= 7 THEN 'Y' ELSE 'N' END AS boolYN
FROM (
      SELECT board_sqno
              , board_title
              , board_title
              , image_path
              , registered_date
              , changed_date
      FROM board
      WHERE board_display_yn = 'Y'
      ORDER BY fix_exps_yn DESC
              , fix_exps_sq ASC
              , board_sqno DESC
      ) b
WHERE ROWNUM <= 10;
  • 위에서 말했던 것처럼 AS는 함부로 쓰면 안된다.
  • 아래와 같이 ROWNUM을 별칭으로 바꾸는 순간 STOPKEY가 전부 사라진다.
  • 일반적인 sort 연산을 수행하게 바뀌어버리는 것이다. 
    SELECT b.*
      , TO_CHAR(TO_DATE(b.registered_datetime, 'YYYYMMDDHH24MISS'), 'YYYY-MM-DD') AS rgdm
      , CASE WHEN(TO_CHAR(SYSDATE,'YYYYMMDD') - TO_CHAR(TO_DATE(b.rgdm, 'YYYYMMDDHH24MISS'), 'YYYYMMDD')) <= 7 THEN 'Y' ELSE 'N' END AS boolYN
FROM (
      SELECT ROWNUM AS RN
              , board_sqno
              , board_title
              , board_title
              , image_path
              , registered_date
              , changed_date
      FROM board
      WHERE board_display_yn = 'Y'
      ORDER BY fix_exps_yn DESC
              , fix_exps_sq ASC
              , board_sqno DESC
      ) b
WHERE b.RN <= 10;

SORT를 피하는 SQL 3 -first row

  • 위에서는 MAX, MIN을 TOP N 쿼리로 구해왔다.
  • 그런데 만약 MAX, MIN으로 쓴다면 그 땐 SORT ORDER BY 연산이 일어난다.
    • 전체 데이터를 정렬하진 않지만, 전체 데이터를 읽으면서 값을 비교한다.

  • 아래와 같이 sort aggregate라는 연산이 실행계획에 보인다.

  • index가 정렬되어 있다면, 전체 데이터를 읽지 않아도 된다.
    • index를 맨 왼쪽으로 내려가면 첫번쨰 읽는 게 최소값이다.
    • index를 맨 오른쪽으로 내려가면 첫번째 읽는 게 최대값이다.
    • index leaf block은 양방향리스트니 왼쪽이나 오른쪽이나 가는 방향을 선택할 수 있다.
  • 단 위처럼 전체 데이터를 읽지 않으려면 조건이 있다.
    • 조건절 컬럼이 index에 있어야 한다.
    • max/min 컬럼이 index에 있어야 한다.
  • 아래의 sql을 보자.
  • deptno, mgr, sal이 모두 index에 포함되어 있으면 전체 데이터를 읽지 않아도 된다.
  • 아래는 (deptno, mgr, sal)로 index를 구성한 경우다.
    • 이 경우 deptno와 mgr이 index access조건이다.
    • 안타깝게도 TOP N 처럼 MGR이 index에 없어도 되진 않다.
    • MAX 함수를 사용하면 index 효과를 톡톡히 보기 위해 MGR도 반드시 추가되어야 한다. 
      SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30 AND MGR = 7698;
  • 만약 알고리즘이 제대로 작동했다면 아래와 같이 FIRST ROW라는 실행계획이 추가된다.

  • 매우 효율적인 min,max 연산이 이뤄졌다는 의미다.

  • index를 조금 바꿔서 (deptno, sal, mgr)로 구성해보자.
    • 이 경우 deptno이 index access조건이다.
    • mgr은 index filter조건이다.
    • 그러나 First row key연산인 점은 변하지 않는다.
      • deptno = 30인 index record 범위에서 가장 오른쪽으로 내려가 가장 큰 SAL 값을 읽는다.
      • 거기서부터 스캔을 시작해 MGR = 7698을 만족하는 레코드를 찾으면 멈춘다.
  • 그림으로 이해하면 아래와 같다.

  • 조건절 선두컬럼은 index access고, 후행은 index filter인 상황이다.

  • index를 조금 바꿔서 (sal, deptno, mgr)로 구성해보자.
    • 이 경우 deptno와 mgr 모두 선두컬럼이 아니다.
    • 따라서 index range scan이 아니라 index full scan이 일어난다.
    • deptno와 mgr 모두 index filter 조건이된다.
    • 그러나 First row key연산인 점은 변하지 않는다.
      • index record 범위에서 가장 오른쪽에서 스캔을 시작한다.
      • DEPTNO = 30이면서 MGR = 7698인 조건을 찾으면 멈춘다.
  • 그림으로 이해하면 아래와 같다.

  • 조건절 모두가 index filter인 상황이다.

  • index를 조금 바꿔서 (deptno, sal)로 구성해보자.
    • 이경우 조건절에 쓰인 mgr 컬럼이 없다.
    • DEPTNO = 30이면서 MAX(SAL)인 값은 오른쪽에서부터 시작해 쉽게 찾는다.
    • 그러나 mgr 컬럼이 index에 없어 MGR = 7698 조건은 테이블에서 필터링해야한다.
    • 따라서 First row key연산이 작동하지 않는다.
  • 그림으로 이해하면 아래와 같다.
  • index column에 조건절이 없어 first row가 작동하지 않았다.

SORT 알고리즘의 꽃 -이력조회

  • 이력조회에 first row stopkey나 top n stopkey 알고리즘을 이용하는 게 필요하다.
  • 아래와 같이 장비 table이 있다고 해보자.
    • 장비번호 PK
    • 장비명
    • 장비구분코드
    • 상태코드
    • 최종변경일자
  • 상태변경 이력 table은 아래와 같다.
    • 장비번호 PK1
    • 변경일자 PK2
    • 변경순번 PK3
    • 상태코드
    • 메모
  • 가장 단순하게 이력데이터를 조회해보자.
  • 스칼라 서브쿼리에서 장비번호와 변경일자가 쓰이는데, 선두컬럼이기에 first row stopkey 알고리즘이 발동한다. 
    SELECT 장비번호, 장비명, 상태코드,
  (SELECT MAX(변경일자)
  FROM 상태변경이력
  WHERE 장비번호 = P.장비번호) 최종변경일자
FROM 장비 P
WHERE 장비구분코드 = 'A001'
  • 위의 기능에서 최종변경순번도 가져오게 추가되었다.
  • inline view에서 최종변경일자와 최종변경순번을 겹쳐 가져오려 한다.
  • 그런 시도는 좋지만, 그때문에 index 컬럼이 가공돼 index access조건으로 타지 못하게 됐다. 
    SELECT 장비번호, 장비명, 상태코드
    , SUBSTR(최종이력, 1, 8) 최종변경일자
    , TO_NUMBER(SUBSTR(최종이력, 9,4)) 최종변경순번
FROM (
SELECT 장비번호, 장비명, 상태코드
, (SELECT MAX(H.변경일자 || LPAD(H.변경순번, 4))
      FROM 상태변경이력 H
      WHERE 장비번호 = P.장비번호) 최종이력
FROM 장비 P
WHERE 장비구분코드 = 'A001'
  )
  • 따라서 아래와 같은 쿼리로 바꿔주는 게 낫다.
  • 스칼라 서브쿼리가 많아서 비효율적일 거 같지만 index access로 작동하는 게 더 빠르다. 
    SELECT 장비번호, 장비명, 상태코드
    ,(SELECT MAX(H.변경일자)
      FROM 상태변경이력 H
      WHERE 장비번호 = P.장비번호) 최종변경일자
    ,(SELECT MAX(H.변경순번)
      FROM 상태변경이력 H
      WHERE 장비번호 = P.장비번호
      AND 변경일자 = (SELECT MAX(H.변경일자)
                      FROM 상태변경이력 H
                      WHERE 장비번호 = P.장비번호)) 최종변경순번
FROM 장비 P
WHERE 장비구분코드 = 'A001'
  • 그런데 최종변경순번에다가 또 다른 field도 가져와야 한다면?
  • 최종상태코드를 가져오려면 아래와 같이 길어진다.
  • 다음은 더 길어질 것이다. 
    SELECT 장비번호, 장비명, 상태코드
    ,(SELECT MAX(H.변경일자)
      FROM 상태변경이력 H
      WHERE 장비번호 = P.장비번호) 최종변경일자
    ,(SELECT MAX(H1.변경순번)
      FROM 상태변경이력 H1
      WHERE 장비번호 = P.장비번호
      AND 변경일자 = (SELECT MAX(H2.변경일자)
                      FROM 상태변경이력 H2
                      WHERE 장비번호 = P.장비번호)) 최종변경순번
    ,(SELECT H1.상태코드
      FROM 상태변경이력 H1
      WHERE 장비번호 = P.장비번호
      AND 변경일자 = (SELECT MAX(H2.변경일자)
                      FROM 상태변경이력 H2
                      WHERE 장비번호 = P.장비번호)
      AND 변경순번 = (SELECT MAX(H3.변경순번)
                      FROM 상태변경이력 H3
                      WHERE 장비번호 = P.장비번호
                      AND 변경일자 = (SELECT MAX(H4.변경일자)
                                      FROM 상태변경이력 H4
                                      WHERE 장비번호 = P.장비번호))) 최종상태코드
FROM 장비 P
WHERE 장비구분코드 = 'A001'
  • 단순한 쿼리를 위해 전통적으로는 아래와 같이 썼다.
  • INDEX_DESC라 인덱스를 역순으로 읽는것이고, 첫 레코드에서 바로 멈춘다.
  • 다만 아래와 같은 방법은 index 구성이 완벽해야 한다. 
    SELECT 장비번호, 장비명
    , SUBSTR(최종이력, 1, 8) 최종변경일자
    , TO_NUMBER(SUBSTR(최종이력, 9, 4)) 최종변경순번
    , SUBSTR(최종이력, 13) 최종상태코드
FROM (
SELECT 장비번호, 장비명
      , (SELECT /*+ INDEX_DESC(X 상태변경이력_PK) */
                변경일자 || LPAD(변경순번, 4) || 상태코드
          FROM 상태변경이력 X
          WHERE 장비번호 = P.장비번호 
          AND ROWNUM <=1) 최종이력
FROM 장비 P
WHERE 장비구분코드 = 'A001'
)
  • 11g에서는 아래와 같이 쓰면 된다.
  • WHERE 장비번호 =P.장비번호가 서브쿼리로 들어가있다.
  • 그러나 실제로는 inline view로 들어가서 조건절이 작동한다.
  • prdicated pushing이다. 
    SELECT 장비번호, 장비명
    , SUBSTR(최종이력, 1, 8) 최종변경일자
    , TO_NUMBER(SUBSTR(최종이력, 9, 4)) 최종변경순번
    , SUBSTR(최종이력, 13) 최종상태코드
FROM (
SELECT 장비번호, 장비명
      , (SELECT 변경일자 || LPAD(변경순번, 4) || 상태코드
          FROM (SELECT 장비번호, 변경일자, 변경순번, 상태코드
                FROM 상태변경이력 
                ORDER BY 변경일자 DESC, 변경순번 DESC) /**11g에서 가능한 방식*/
          WHERE 장비번호 = P.장비번호 
          AND ROWNUM <=1 ) 최종이력
FROM 장비 P
WHERE 장비구분코드 = 'A001'
)
  • 12c에서는 아래와 같이 쓰면 된다. 
    SELECT 장비번호, 장비명
    , SUBSTR(최종이력, 1, 8) 최종변경일자
    , TO_NUMBER(SUBSTR(최종이력, 9, 4)) 최종변경순번
    , SUBSTR(최종이력, 13) 최종상태코드
FROM (
SELECT 장비번호, 장비명
      , (SELECT 변경일자 || LPAD(변경순번, 4) || 상태코드
          FROM (SELECT 장비번호, 변경일자, 변경순번, 상태코드
                FROM 상태변경이력 
                ORDER BY 변경일자 DESC, 변경순번 DESC
                WHERE 장비번호 = P.장비번호)     /**12c에서 가능한 방식*/
          AND ROWNUM <=1 ) 최종이력
FROM 장비 P
WHERE 장비구분코드 = 'A001'
)
  • 이력 조회를 하는 서브쿼리에 윈도우 함수를 사용할 수 있다.
  • 하지만 top n stopkey가 작동하지 않는다.
  • 따라서 index로 sort를 생략하는 경우는 절대 사용하면 안 된다.
  • 아래는 쿼리를 모르는 사람들이 짜는 anti 패턴이다. 
    SELECT 장비번호, 장비명
    , SUBSTR(최종이력, 1, 8) 최종변경일자
    , TO_NUBMER(SUBSTR(최종이력, 9,4)) 최종변경순번
    , SUBSTR(최종이력, 13) 최종상태코드
FROM (
SELECT 장비번호, 장비명
      , (SELECT 변경일자 || LPAD(변경순번, 4) || 상태코드
      FROM (SELECT 변경일자, 변경순번, 상태코드
                  , ROW_NUMBER() OVER (ORDER BY 변경일자 DESC, 변경순번 DESC) NO
            FROM 상태변경이력
            WHERE 장비번호 = P.장비번호)
      WHERE NO = 1) 최종이력
FROM 장비 P
WHERE 장비구분코드 = 'A001'
)
  • 12c의 row limit기능을 써도 사실 위와 같은 window 함수를 쓰는 것이다.
  • 따라서 stopkey 알고리즘이 발동하지 않는다. 
    SELECT 장비번호, 장비명
    , SUBSTR(최종이력, 1, 8) 최종변경일자
    , TO_NUBMER(SUBSTR(최종이력, 9,4)) 최종변경순번
    , SUBSTR(최종이력, 13) 최종상태코드
FROM (
SELECT 장비번호, 장비명
      , (SELECT 변경일자 || LPAD(변경순번, 4) || 상태코드
FROM 상태변경이력
WHERE 장비번호 = P.장비번호
ORDER BY 변경일자 DESC, 변경순번 DESC
FETCH FIRST 1 ROWS ONLY) 최종이력
FROM 장비 P
WHERE 장비구분코드 = 'A001'
);
  • 페이징 처리에 활용할 때 윈도우 함수를 쓰기도 한다.
  • 하지만 쓰지 않는 경우도 자주 발생한다. 따라서 index/index_desc hint를 자주 써야 한다.
  • sort 생략 가능한 index가 없으면 top n stopkey가 아니라 top n sort가 작동해버린다. 
    SELECT 변경일자, 변경순번, 상태코드
FROM (
SELECT 변경일자, 변경순번, 상태코드
      , ROW_NUBMER() OVER(ORDER BY 변경일자, 변경순번) NO
FROM 상태변경이력
WHERE 장비번호 = :eqp_no)
WHERE NO BETWEEN 1 AND 10;
  • 장비번호가 특정번호일 때는 index 조회가 효과적이다.
  • 하지만 전체에 관해 조회할 때는 index를 쓰면 random access에 따른 손해가 크다.
  • 이럴 땐 window function을 쓰는 게 더 나은 선택이다. 
    SELECT P.장비번호, P.장비명
    , H.변경일자 AS 최종변경일자
    , H.변경순번 AS 최종변경순번
    , H.상태코드 AS 최종상태코드
FROM 장비 P
    , (SELECT 장비번호, 변경일자, 변경순번, 상태코드
            , ROW_NUMBER() OVER(PARTITION BY 장비번호
                                ORDER BY 변경일자 DESC, 변경순번 DESC) RNUM
      FROM 상태변경이력) H
WHERE H.장비번호 = P.장비번호
AND H.RNUM = 1;