SQL 부분 범위 처리, 전체 범위 처리

보통 최대값, 최소값, 합계, 평균 등을 구할 때 전체 범위를 모두 스캔해서 연산하게 되기 때문에 부하가 커진다.

그중 최대값ㅡ 최소값을 구할 때 를 살펴보자.


전체범위에서 최대값을 구하는 방법

SELECT MAX(ABOL_DT)
FROM HR.EMP_2

이렇게 전체 범위에서 값을 구하면 부하가 커지게 된다. 이럴 때 부분 범위 처리를 사용하면 부하를 상당히 줄일 수 있다.


부분범위에서 최대값 구하는 방법

SELECT /*+ INDEX_DESC (ABC IX_EMP_02) */ A.ABOL_DT
FROM HR.EMP_2 ABC
WHERE ABOL_DT >= TO_DATE('19000101', 'YYYYMMDD')
AND ROWNUM = 1

여기서 자세히 봐야할 것은 ABOL_DT에 인덱스(IX_EMP_02)가 걸려 있다는 것과, 또 이 인덱스를 DESC로 내림차순으로 정렬 힌트를 주었고, ROWNUM으로 하나만 가져오는 모습이다.
WHERE절을 주지 않으면 인덱스를 중심으로 정렬하지 않아 최대값을 구하지 못할 수 있다.




특정 컬럼에서 최대값을 구하는 방법

SELECT /*+ INDEX_DESC (ABC IX_EMP_03) */ 
      NVL(MAX(ABC.ID),0) + 1
FROM HR.EMP_2 ABC
WHERE ID = ABC.ID
AND ROWNUM = 1

마찬가지로 부분범위를 사용해서 특정 컬럼에서 최대값 + 1을 구하는 방법이다.
해당 컬럼에 인덱스를 걸어주고 DESC로 가져왔고 힌트를 주었고 WHERE 절을 통해 인덱스를 사용하도록 유도했으며, ROWNUM을 사용해 1개만 최대값 하나만 가져오기를 수행했다.





인덱스의 중요성을 확인할 수 있는 예제를 살펴보자.

/* 5만건 정도의 더미 테이블 생성 */
CREATE TABLE HR.MRR_FRM_CLNINFO AS
SELECT ROUND(DBMS_RANDOM.VALUE(1,50), 0) FRMCLN_REP_KEY,
       ROUND(DBMS_RANDOM.VALUE(1,1000000),0) FRMCLN_KEY
       FROM DUAL
       CONNECT BY LEVEL <= 50000;

/* FRMCLN_REP_KEY에만 인덱스를 생성한다. */
CREATE INDEX IX_MRR_FRM_CLNINFO_04 ON HR.MRR_FRM_CLNINFO(FRMCLN_REP_KEY);



/* FRMCLN_REP_KEY가 1인 것들 중 FRMCLN_KEY의 최대값을 구하는 쿼리 */
SELECT MAX(FRMCLN_KEY)
FROM HR.MRR_FRM_CLNINFO WHERE FRMCLN_REP_KEY = 1

먼저 5만건 정도의 테이블을 만든 뒤 특정 조건에서 또다른 특정 컬럼의 최대값을 구하는 쿼리를 날리면 인덱스는 처음 특정 조건을 건 컬럼에만 생성되어 있으므로 상당히 느리게 동작한다.

이를 개선하려면 아래와 같이 처리한다.

/* 기존 인덱스를 제거 */
DROP INDEX IX_MRR_FRM_CLNINFO_04;

/* FRMCLN_REP_KEY, FRMCLN_KEY 두 개 모두를 인덱스로 잡는다 */
CREATE INDEX HR.IX_MRR_FRM_CLNINFO_04 ON HR.MRR_FRM_CLNINFO (FRMCLN_REP_KEY, FRMCLN_KEY);

/* FRMCLN_REP_KEY가 1인 것들 중 FRMCLN_KEY의 최대값을 구하는 쿼리를 다시 테스트해본다 */
SELECT MAX(FRMCLN_KEY)
FROM HR.MRR_FRM_CLNINFO WHERE FRMCLN_REP_KEY = 1

별거 아닌 것 같지만, 인덱스가 있느냐 없느냐에 따라서 상당한 성능 차이를 가져온다.



이번에는 게시판에서 페이지 단위로 게시물을 가져오는 처리를 살펴보자.

SELECT B.*
FROM (SELECT /*+ INDEX_DESC(A IX_T_EMP01) */ A.*, ROWNUM R
      FROM HR.T_TEMP A
      WHERE EMPLOYEE_ID > '0'  AND FIRST_NAME like '%on%'
            AND ROWNUM <= 10 * 4) B
WHERE R >10 * 3

핵심은 인덱스로 처리한 다는 것과, 인덱스를 강제로 사용하게 하기위해 WHERE절에 무의미한 EMPLOYEE_ID > '0' 을 삽입했다는 것이다.
또 페이지 처리를 위해 서브쿼리를 사용해서 ROWNUM을 활용했다.
검색을 사용하는 상황을 보이기 위해 LIKE도 한번 넣어봤다. 



1:M 관계에서 조인 수를 줄이는 처리
불필요하게 조인을 하게될 경우 게시물이 많아지면 다소 퍼포먼스에 영향을 준다.
이때에는 아래와 같이 처리하는 것이 바람직하다.

-- 아래와 DISTINCT를 사용할 경우 반드시 SORT이 수반되고 퍼포먼스가 떨어지게 된다.
SELECT DISTINCT A.DEPARTMENT_ID, A.DEPARTMENT_NAME
FROM HR.DEPARTMENTS A, HR.EMPLOYEES B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
ORDER BY A.DEPARTMENT_ID

-- 따라서 아래와 같이 EXISTS 를 활용해서 한 개라도 존재하는 경우를 찾아준다.
SELECT A.DEPARTMENT_ID, A.DEPARTMENT_NAME
FROM HR.DEPARTMENTS A
WHERE EXISTS (SELECT 'X'
              FROM HR.EMPLOYEES B
              WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID)
ORDER BY A.DEPARTMENT_ID