계층 코드와 예상 카디널리티

그룹, 본부, 부서로 구성된 부서 테이블은 계층 코드를 가지기 마련이다. 부서 테이블의 계층 코드를 사원 테이블에 상속하고, 조건으로 사용하는 경우 예상 카디널리티가 1로 계산되어 쿼리의 성능이 저하될 수 있다.

테스트를 위해 아래와 같이 테이블을 생성하자. 사월 테이블의 부서는 부서 테이블의 상속 속성이며, 그룹과 본부는 3정규형을 위배한 반정규화 속성이다. 매출 테이블은 조건 조건에 해당하는 인덱스가 없는 것으로 가정하자.

-- 1
DROP TABLE 사원 PURGE;
DROP TABLE 매출 PURGE;

CREATE TABLE 사원 AS
SELECT ROWNUM AS 사번
     , a.c1 AS 그룹
     , a.c1 || b.c1 AS 본부
     , a.c1 || b.c1 || c.c1 AS 부서
  FROM (SELECT LPAD (ROWNUM, 2, '0') AS c1 FROM XMLTABLE ('1 to  10')) a
     , (SELECT LPAD (ROWNUM, 2, '0') AS c1 FROM XMLTABLE ('1 to  10')) b
     , (SELECT LPAD (ROWNUM, 2, '0') AS c1 FROM XMLTABLE ('1 to  10')) c
     , (SELECT LPAD (ROWNUM, 2, '0') AS c1 FROM XMLTABLE ('1 to 100')) d;

CREATE TABLE 매출 AS
SELECT ROWNUM AS 사번, DATE '2000-01-01' + ROWNUM - 1 AS 날짜, 1000 AS 금액
  FROM XMLTABLE ('1 to 100000');

ALTER TABLE 사원 ADD CONSTRAINTS 사원_PK PRIMARY KEY (사번);
-- ALTER TABLE 매출 ADD CONSTRAINTS 매출_PK PRIMARY KEY (사번, 날짜);

CREATE INDEX 사원_x1 ON 사원 (그룹);
CREATE INDEX 사원_x2 ON 사원 (본부);
CREATE INDEX 사원_x3 ON 사원 (부서);
CREATE INDEX 매출_x1 ON 매출 (날짜);

EXEC DBMS_STATS.GATHER_TABLE_STATS ('TUNA', '사원');
EXEC DBMS_STATS.GATHER_TABLE_STATS ('TUNA', '매출');

아래는 사원 테이블을 조회한 결과다. 본부, 부서, 팀 코드는 계층 구조를 가진다.

--2
SELECT * FROM 사원 WHERE 사번 = 1;

사번 그룹 본부 부서
---- ---- ---- ------
  1  01   0101 010101

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

아래 쿼리는 그룹, 본부, 부서에 해당하는 사원의 매출 테이블 정보를 조회한다. 비효율적인 실행 계획으로 인해 7.72초가 소요되었고, 55,411개의 블록 I/O가 발생했다.

-- 3
VAR v1 VARCHAR2(32)
VAR v2 VARCHAR2(32)
VAR v3 VARCHAR2(32)
VAR v4 VARCHAR2(32)

EXEC :v1 := '01'
EXEC :v2 := '0101'
EXEC :v3 := '010101'
EXEC :v4 := '2000-01-01'

SELECT SUM (b.금액)
  FROM 사원 a
     , 매출 b
 WHERE a.그룹 = :v1
   AND a.본부 = :v2
   AND a.부서 = :v3
   AND b.사번 = a.사번
   AND b.날짜 >= TO_DATE (:v4, 'YYYY-MM-DD');

---------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |        |      1 |        |      1 |00:00:07.72 |   55411 |
|   1 |  SORT AGGREGATE                |        |      1 |      1 |      1 |00:00:07.72 |   55411 |
|   2 |   NESTED LOOPS                 |        |      1 |      1 |    100 |00:00:07.72 |   55411 |
|   3 |    NESTED LOOPS                |        |      1 |    900 |     10M|00:00:02.36 |   26411 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| 사원   |      1 |      1 |    100 |00:00:00.01 |       3 | -- !
|*  5 |      INDEX RANGE SCAN          | 사원_X3|      1 |    100 |    100 |00:00:00.01 |       2 |
|*  6 |     INDEX RANGE SCAN           | 매출_X1|    100 |    900 |     10M|00:00:01.50 |   26408 |
|*  7 |    TABLE ACCESS BY INDEX ROWID | 매출   |     10M|      1 |    100 |00:00:03.72 |   29000 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter(("A"."본부"=:V2 AND "A"."그룹"=:V1))
   5 - access("A"."부서"=:V3)
   6 - access("B"."날짜">=TO_DATE(:V4,'YYYY-MM-DD'))
   7 - filter("B"."사번"="A"."사번")

아래는 사원 테이블의 칼럼 통계 정보다. 그룹, 본부, 부서 열을 등호(=)로 조회하면 카디널리티가 1(=100000 * 1/10 * 1/100 * 1/1000)로 계산된다. 예상 카디널리티가 1이므로 매출 테이블을 한번만 액세스할 수 있을 것으로 판단하여 해시 조인이 아닌 NL 조인을 선택한 것이다.^[각주:1]

-- 4
SELECT column_name, num_distinct, density, low_value, high_value, num_buckets
  FROM user_tab_columns
 WHERE table_name = '사원';


COLUMN_NAME NUM_DISTINCT DENSITY LOW_VALUE    HIGH_VALUE   NUM_BUCKETS
----------- ------------ ------- ------------ ------------ -----------
사번              100000 0.00001 C102         C30B                   1
그룹                  10 0.10000 3031         3130                   1
본부                 100 0.01000 30313031     31303130               1
부서                1000 0.00100 303130313031 313031303130           1

4 행이 선택되었습니다.

아래 쿼리는 부서 조건만 입력했다. 예상 카디널리티가 100으로 계산되어 해시 조인으로 조인된 것을 확인할 수 있다. 0.1초가 소요되었고, 559개의 블록 I/O가 발생했다.

-- 5
SELECT SUM (b.금액)
  FROM 사원 a
     , 매출 b
 WHERE a.부서 = :v3
   AND b.사번 = a.사번
   AND b.날짜 >= TO_DATE (:v4, 'YYYY-MM-DD');

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |      1 |        |      1 |00:00:00.10 |     559 |          |
|   1 |  SORT AGGREGATE               |        |      1 |      1 |      1 |00:00:00.10 |     559 |          |
|*  2 |   HASH JOIN                   |        |      1 |    100 |    100 |00:00:00.10 |     559 | 1658K (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID| 사원   |      1 |    100 |    100 |00:00:00.01 |       3 |          | -- !
|*  4 |     INDEX RANGE SCAN          | 사원_X3|      1 |    100 |    100 |00:00:00.01 |       2 |          |
|   5 |    TABLE ACCESS BY INDEX ROWID| 매출   |      1 |   5000 |    100K|00:00:00.05 |     556 |          |
|*  6 |     INDEX RANGE SCAN          | 매출_X1|      1 |    900 |    100K|00:00:00.02 |     266 |          |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("B"."사번"="A"."사번")
   4 - access("A"."부서"=:V3)
   6 - access("B"."날짜">=TO_DATE(:V4,'YYYY-MM-DD'))

쿼리를 수정하기 어렵다면 통계 정보 설정으로 성능을 개선할 수 있다. 아래와 같이 계층이 코드가 아닌 경우를 감안하여 본부와 부서 칼럼에 대한 통계 정보를 설정하자.

-- 6
DECLARE
    l_distcnt    NUMBER;
    l_density    NUMBER;
    l_nullcnt    NUMBER;
    l_avgclen    NUMBER;
BEGIN
    DBMS_STATS.SET_COLUMN_STATS (
        ownname              => 'TUNA'
      , tabname              => '사원'
      , colname              => '본부'
      , distcnt              => '10'
      , density              => '0.1'
      , no_invalidate        => FALSE
      , force                => TRUE
    );


    DBMS_STATS.SET_COLUMN_STATS (
        ownname              => 'TUNA'
      , tabname              => '사원'
      , colname              => '부서'
      , distcnt              => '10'
      , density              => '0.1'
      , no_invalidate        => FALSE
      , force                => TRUE
    );
END;
/

PL/SQL 처리가 정상적으로 완료되었습니다.

그룹, 본부, 부서 열을 등호(=)로 조회하면 카디널리티가 100(= 100000 * 1/10 * 1/10 * 1/10)으로 계산된다.

-- 7
SELECT column_name, num_distinct, density, low_value, high_value, num_buckets
  FROM user_tab_columns
 WHERE table_name = '사원';

COLUMN_NAME NUM_DISTINCT  DENSITY LOW_VALUE    HIGH_VALUE   NUM_BUCKETS
----------- ------------ -------- ------------ ------------ -----------
사번              100000  0.00001 C102         C30B                   1
그룹                  10  0.10000 3031         3130                   1
본부                  10  0.10000 30313031     31303130               1
부서                  10  0.10000 303130313031 313031303130           1

4 행이 선택되었습니다.

3번 쿼리를 다시 수행해보면 5번 쿼리처럼 해시 조인으로 수행되는 것을 확인할 수 있다.

-- 8
SELECT SUM (b.금액)
  FROM 사원 a
     , 매출 b
 WHERE a.그룹 = :v1
   AND a.본부 = :v2
   AND a.부서 = :v3
   AND b.사번 = a.사번
   AND b.날짜 >= TO_DATE (:v4, 'YYYY-MM-DD');

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |      1 |        |      1 |00:00:00.16 |     611 |          |
|   1 |  SORT AGGREGATE               |        |      1 |      1 |      1 |00:00:00.16 |     611 |          |
|*  2 |   HASH JOIN                   |        |      1 |    100 |    100 |00:00:00.16 |     611 | 1657K (0)|
|*  3 |    TABLE ACCESS BY INDEX ROWID| 사원   |      1 |    100 |    100 |00:00:00.02 |      55 |          | -- !
|*  4 |     INDEX RANGE SCAN          | 사원_X3|      1 |  10000 |  10000 |00:00:00.01 |      21 |          |
|   5 |    TABLE ACCESS BY INDEX ROWID| 매출   |      1 |   5000 |    100K|00:00:00.06 |     556 |          |
|*  6 |     INDEX RANGE SCAN          | 매출_X1|      1 |    900 |    100K|00:00:00.02 |     266 |          |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("B"."사번"="A"."사번")
   3 - filter(("A"."본부"=:V2 AND "A"."부서"=:V3))
   4 - access("A"."그룹"=:V1)
   6 - access("B"."날짜">=TO_DATE(:V4,'YYYY-MM-DD'))

1. PGA를 사용하지 않을 수 있기 때문이다. [본문으로]