DATOR


통계정보 부분 범위 처리 SQL튜닝 사례


 대용량 데이터 조회의 경우 대부분 통계정보를 많이 사용한다.
 통계 정보의 경우는 일반적으로 부분 범위 처리가 어렵다. 통계를 내기 위해서는 해당 범위의 모든 데이터를 연산 후 그 데이터를 합해야 하는 경우가 많기 떄문이다.
 때문에 통계 정보의 경우 배치 작업을 통해 미리 연산 된 결과를 저장해 두는 테이블을 두는 것이 일반적이다.
 이번 예제에서는 아래와 같은 일반적인 부서-< 사원 데이터 모델을 기본으로 하여 통계정보의 부분 범위 처리 및 클러스터 적용을 통해 성능 향상을 알아보도록 한다.

170E50454EC732B027DF41

부서와 사원의 관계는 일반적으로 위와 같이 부서와 사원의 1:M 관계 이다.

먼저 위의 데이터 모델링을 토대로  예제 데이터를 생성해 보도록 한다.



 CREATE TABLE BIG_DEPTARTMENT AS
SELECT
  TO_NUMBER(DEPARTMENT_ID || LEV) AS DEPARTMENT_ID
  , DEPARTMENT_NAME || LEV AS DEPARTMENT_NAME
  , MANAGER_ID
  , LOCATION_ID
FROM HR.DEPARTMENTS,
(SELECT LEVEL LEV FROM DUAL CONNECT BY LEVEL <= 100)
;

CREATE UNIQUE INDEX PK_IDX_BIG_DEPTARTMENT ON BIG_DEPTARTMENT (DEPARTMENT_ID);

ALTER TABLE BIG_DEPTARTMENT
  ADD CONSTRAINT PK_DEPARTMENT_ID PRIMARY KEY(DEPARTMENT_ID)
;

CREATE UNIQUE INDEX IDX_BIG_DEPT_RNUM ON BIG_DEPTARTMENT (RNUM);

CREATE TABLE BIG_EMPLOYEE AS
  SELECT
    EMPLOYEE_ID
      , FIRST_NAME
      , LAST_NAME
      , EMAIL
      , PHONE_NUMBER
      , HIRE_DATE
      , JOB_ID
      , SALARY
      , COMMISSION_PCT
      , MANAGER_ID
      , (SELECT DEPARTMENT_ID FROM BIG_DEPTARTMENT B WHERE A.RNUM = B.RNUM) AS DEPARTMENT_ID
      , A.RNUM
    FROM (SELECT
      EMPLOYEE_ID
      , FIRST_NAME
      , LAST_NAME
      , EMAIL
      , PHONE_NUMBER
      , HIRE_DATE
      , JOB_ID
      , SALARY
      , COMMISSION_PCT
      , MANAGER_ID
      -- , DEPARTMENT_ID
      --, (SELECT DEPARTMENT_ID FROM BIG_DEPTARTMENT WHERE RNUM = FLOOR(DBMS_RANDOM.VALUE(1, 2701))) AS DEPARTMENT_ID
      , FLOOR(DBMS_RANDOM.VALUE(1, 2701)) AS RNUM
    FROM HR.EMPLOYEES,
    (SELECT LEVEL LEV FROM DUAL CONNECT BY LEVEL <= 100000)
    ) A
;

CREATE INDEX IDX_BIG_DEPTARTMENT ON BIG_EMPLOYEE (DEPARTMENT_ID, SALARY);

* 예제 생성을 위해서는 오라클 생성 시 테스트 계정인 HR계정의 SELECT권한이 있어야 합니다.


 예제 데이터 대로이면 부서는 100배 곱해져 2700개의 부서가 생성되고, 사원은 10700000건의 데이터가 생성 될 것이다.
 일반적으로 정보를 보고자 할 때 한 페이지에 2700개의 데이터를 보고자 할 때 한 화면에 모든 데이터를 보여주지 않는 다.

 하지만 천만건이 넘는 위의 데이터를 가지고 통계를 낸 후 페이징을 하는 것은 무리다.
 때문에 아래와 같이 배치 프로그램을 통해서 미리 아래와 같이 계산을 해두고 보는 것이 일반적이다.

CREATE TABLE EMPLOYEE_SALARY_INFO AS
SELECT
  DEPARTMENT_NAME
  , SUM_SAL
  , AVG_SAL
  , MIN_SAL
  , MAX_SAL
FROM
  (SELECT DEPARTMENT_ID,
    SUM(SALARY) AS SUM_SAL
    , ROUND(AVG(SALARY), 2) AS AVG_SAL
    , MIN(SALARY) AS MIN_SAL
    , MAX(SALARY) AS MAX_SAL
  FROM BIG_EMPLOYEE
  GROUP BY DEPARTMENT_ID
  ORDER BY DEPARTMENT_ID DESC) A, BIG_DEPTARTMENT B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID;

 이미 일반적인 형태라 이렇게 별도의 계산 테이블을 두는 것이 큰 문제는 없지만, 아래와 같이 불편한 점이 없지 않아 있는 것이 사실이다.

1. 별도의 배치 프로그램이 있어야한다.
 데이터를 일정한 시간에 배치 프로그램으로 데이터를 변환해야 하기에 별도의 프로그램이 필요하고, 또 배치 프로그램 이후에 작업에 대한 통계는 불가능 하다.
2. 추가 데이터가 필요할 시 스키마 변경이 필요하다.
 현업이 추가로 보고자 하는 데이터가 있을 시 스키마를 변경하여야 한다.
 예를 들어 위와 같은 스키마에서 급여 정보에 평균 커미션 정보와 커미션 합계 정보가 필요하다고 하면 아래와 같이 SQL이 변경될 것 이고, 그에 따른 테이블 변경이 필요할 것 이다.

CREATE TABLE EMPLOYEE_SALARY_INFO AS
 SELECT
  DEPARTMENT_NAME
  , SUM_SAL
  , AVG_SAL
  , MIN_SAL
  , MAX_SAL
  , AVG_COM
  , SUM_COM
FROM
  (SELECT DEPARTMENT_ID,
    SUM(SALARY) AS SUM_SAL
    , ROUND(AVG(SALARY), 2) AS AVG_SAL
    , MIN(SALARY) AS MIN_SAL
    , MAX(SALARY) AS MAX_SAL
    , AVG(COMISSION_PCT) AS AVG_COM
    , SUM(COMISSION_PCT) AS SUM_COM
  FROM BIG_EMPLOYEE
  GROUP BY DEPARTMENT_ID
  ORDER BY DEPARTMENT_ID DESC) A, BIG_DEPTARTMENT B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID;

 물론  추가 개발을 감수 하고라도 정보가 필요하고, 다른 방법이 없다면 이런 방식의 개발을 하는 것도 틀린 것은 아니다.
 하지만 조금 더 생각해서 위와 같은 추가 비용을 절감할 방법이 있다면 그 방법이 더 좋은 방법일 것 이다.
 이제 그 방법에 대해 알아보도록 한다.
 
 모델링 관점에서 보았을 때 사원과 부서의 관계는 1:M이다. 
 위의 쿼리에서 페이징을 한다고 하였을  시 사원 테이블을 기준으로 생각하여 페이징을 하는 것은 불가능 하다. 정확히 말하면 가능은 하지만, 성능 상 무리가 있기 때문에 온라인에서 페이지 당윈로 보고자 하는 것은 무리이다. 보고자 하는 데이터는 데이터의 원본 데이터가 아닌 GROUP BY를 이용한 급여 통계 정보이기 때문에 모두 읽고 통계를 내기 전에는 페이징을 하기 위한 ROWNUM을 통계정보 레벨로 받아올 수 없다.
 하지만 여기서 주목해야 할 부분이 있다.
 통계의 기준이 되는 부서ID(DEPARTMENT_ID)컬럼이 부서 테이블의 FK라는 점이다.
 부서 테이블의 부서ID는 PK이기 때문에 각각 부서별로 페이징을 하는 것이 가능하다.
 이 점을 잘 이용하면 위와 같은 업무에서 부분 범위 처리가 가능하다.
 아래의 쿼리를 살펴보도록 한다.

SELECT
  DEPARTMENT_NAME
  , SUM(SALARY) AS SUM_SAL
  , ROUND(AVG(SALARY), 2) AS AVG_SAL
  , MIN(SALARY) AS MIN_SAL
  , MAX(SALARY) AS MAX_SAL
FROM
(
  SELECT DEPARTMENT_ID, DEPARTMENT_NAME FROM
   (SELECT /*+ NO_MERGE INDEX_DESC(D PK_IDX_BIG_DEPTARTMENT) */
      DEPARTMENT_ID, DEPARTMENT_NAME, ROWNUM RNUM     
    FROM BIG_DEPTARTMENT D
    WHERE ROWNUM <= 20
    AND DEPARTMENT_ID > 0
    ORDER BY DEPARTMENT_ID DESC
    )
    WHERE RNUM > 0
  ) A, BIG_EMPLOYEE B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
GROUP BY DEPARTMENT_NAME
;

12380A3F4ECA42373099F5

13036D464ECA436812977E

 Rows     Row Source Operation
-------  ---------------------------------------------------
     20  HASH GROUP BY (cr=248 pr=0 pw=0 time=97790 us)
  79826   NESTED LOOPS  (cr=248 pr=0 pw=0 time=54 us)
     20    VIEW  (cr=4 pr=0 pw=0 time=116 us)
     20     COUNT STOPKEY (cr=4 pr=0 pw=0 time=82 us)
     20      TABLE ACCESS BY INDEX ROWID BIG_DEPTARTMENT (cr=4 pr=0 pw=0 time=73 us)
     20       INDEX RANGE SCAN DESCENDING PK_IDX_BIG_DEPTARTMENT (cr=2 pr=0 pw=0 time=24 us)(object id 54497)
  79826    INDEX RANGE SCAN IDX_BIG_EMP_DEPT (cr=244 pr=0 pw=0 time=140 us)(object id 54496)

0.219초만에 20건의 데이터를 리턴하였다.

 위의 쿼리를 설명하자면 이렇다.
1. 먼저 부서 테이블을 페이징해서 부분 범위 처리한다. 2700건 밖에 되지 않는 소규모 테이블이기 때문에 이 테이블을 읽어 오는 부하는 아주 미미하다.
2. 부서 테이블에서 페이징해서 걸러진 데이터와 사원 테이블 간의 조인을 한다.
20건으로 페이징을 끈었기 때문에 사원 테이블에서는 20건의 부서 정보에 해당하는 데이터만 읽어 오면 된다. 부서당 데이터는 평균 4000건 정도로 천만건의 데이터를 모두 읽어올 필요 없이 약 8만건의 데이터만 가져온다.
 이 때 통계 쿼리에서 사용되는 컬럼인 DEPARTMENT_ID와 SALARY컬럼 모두 인덱스인 IDX_BIG_EMP_DEPT에 포함된 컬럼이기 때문에 테이블을 읽을 필요가 없다. 인덱스는 데이터 블럭크기도 작고, 컬럼의 순서대로 정렬되어 있기 때문에 위와 같은 쿼리에서 비효율이 없다.

 통계정보는 일반적으로 모든 정보를 GROUP BY로 묶어 주어야 한다는 생각이 있기 때문에 이러한 부분 범위가 가능한 업무에서도 별도의 통계 테이블을 만들어야 한다는 잘 못 된 인식이 있지만, 이번 예제를 통해 실무에서 잘 활용한다면 불 필요한 통계 테이블을 더 줄일 수 있을 것이다.

Tag :

Leave Comments