SQL 성능 향상 팁

나만 모르고 남들은 다 알고 있던 조건문 성능 향상 팁

나만 모르고 있던 거라 까먹지 않기 위해 기록

Oracle(tm) 위주임

1. 선택형 조건의 NULL 체크

NVL(:A,'*') 또는 DECODE(:A,'','*',:A) 대신 IS NULL OR 로 변형할 것을 권장한다.

SELECT *
  FROM DUMMY
WHERE 1=1
  AND (:A IS NULL OR COL_A = :A)

너무 남용하면 인덱스를 안 타게 되며, 인덱스를 활용하기 위해 선택형 조건을 UNION ALL 로 분리해서 묶어야 하는데 2^조건수로 늘어나게 되므로 설계를 잘 해야 한다.

SELECT *          /* A는 있고 B가 없는 경우 */
  FROM DUMMY
WHERE 1=1
  AND :A IS NOT NULL
  AND COL_A = :A
  AND :B IS NULL
UNION ALL
SELECT *         /* A는 없고 B가 있는 경우 */
  FROM DUMMY
WHERE 1=1
  AND :A IS NULL
  AND :B IS NOT NULL
  AND COL_B = :B
UNION ALL
SELECT *        /* A와 B가 모두 있는 경우 */
  FROM DUMMY
WHERE 1=1
  AND :A IS NOT NULL
  AND :B IS NOT NULL
  AND COL_A = :A
  AND COL_B = :B
UNION ALL
SELECT *        /* A와 B가 모두 없는 경우(여기에서는 생략했지만 기타필수조건만 있는 경우) */
  FROM DUMMY
WHERE 1=1
  AND :A IS NULL
  AND :B IS NULL

2. 일련번호 최대값 가져오기 위한 동일테이블 중복조회

일련번호의 최대값을 가져와서 적용하는 쿼리는 개발자가 보기에 직관적이지만 데이터량이 많아질수록 데이터베이스에게는 부담이 된다.

SELECT *
  FROM DUMMY A
 WHERE A.SEQNO = (SELECT MAX(B.SEQNO)
                    FROM DUMMY B)

ROW_NUMBER()를 활용하면 빨라진다.

SELECT *
  FROM (SELECT COL1, COL2, SEQNO,
               ROW_NUMBER() OVER (PARTITION BY COL1, COL2 ORDER BY SEQNO DESC) RN
          FROM DUMMY
         WHERE 1=1)
 WHERE RN = 1

끝.