여부 속성으로 인한 성능 저하 사례 #2

여부 속성으로 인한 두 번째 성능 저하 사례를 살펴보자.

테스트를 위해 아래의 테이블을 생성하자. t1 테이블을 고객별 상품 조회 이력 테이블로 가정하자.

-- 1-1
DROP TABLE t1 PURGE;

CREATE TABLE t1 AS
SELECT 1 AS id
     , ROWNUM AS cd
     , DATE '2050-01-01' - NUMTODSINTERVAL (ROWNUM, 'MINUTE') AS dt
     , 'N' AS del_yn
  FROM XMLTABLE ('1 to 144000');

ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (id, cd, dt);

-- 1-2
UPDATE t1
   SET del_yn = 'Y'
 WHERE id = 1
   AND dt < DATE '2049-12-31' - 30;

COMMIT;

고객이 2050-01-01에 로그인하면 아래 쿼리가 수행된다. 30일 이전의 상품 조회 이력을 논리적으로 삭제하는 처리다.

-- 2
UPDATE t1
   SET del_yn = 'Y'
 WHERE id = 1
   AND dt < DATE '2050-01-01' - 30;

UPDATE 문을 SELECT 문으로 변경하여 실행계획을 확인해보면 갱신 대상이 10만건 이상인 것을 확인할 수 있다.^[각주:1]

-- 3
SELECT del_yn
  FROM t1
 WHERE id = 1
   AND dt < DATE '2050-01-01' - 30;

----------------------------------------------------------------
| Id  | Operation                   | Name  | A-Rows | Buffers |
----------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |    100K|    1045 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |    100K|    1045 |
|*  2 |   INDEX RANGE SCAN          | T1_PK |    100K|     623 |
----------------------------------------------------------------

업무적으로 과거 데이터는 이미 논리 삭제가 된 상태이므로 아래와 같이 del_yn = 'N' 조건을 추가하여 갱신 대상을 줄일 수 있다.^[각주:2] 불필요한 갱신을 방지함으로써 로깅 부하가 감소했지만 블록 I/O는 여전히 높다.

-- 4
SELECT del_yn
  FROM t1
 WHERE id = 1
   AND dt < DATE '2050-01-01' - 30
   AND del_yn = 'N' -- 추가
;

----------------------------------------------------------------
| Id  | Operation                   | Name  | A-Rows | Buffers |
----------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   1440 |     848 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |   1440 |     848 |
|*  2 |   INDEX RANGE SCAN          | T1_PK |    100K|     524 |
----------------------------------------------------------------

아래와 같이 인덱스를 생성하자. NDV과 무관하게 등호(=)로 입력된 del_yn 칼럼이 범위로 입력된 dt 칼럼보다 앞쪽에 위치해야 한다.

-- 5
CREATE INDEX t1_x1 ON t1 (id, del_yn, dt);

인덱스가 생성되었습니다.

쿼리를 다시 수행하면 블록 I/O가 9로 감소한 것을 확인할 수 있다.

-- 6
SELECT del_yn
  FROM t1
 WHERE id = 1
   AND dt < DATE '2050-01-01' - 30
   AND del_yn = 'N';

----------------------------------------------------
| Id  | Operation        | Name  |A-Rows | Buffers |
----------------------------------------------------
|   0 | SELECT STATEMENT |       |  1440 |       9 |
|*  1 |  INDEX RANGE SCAN| T1_X1 |  1440 |       9 |
----------------------------------------------------

1. 일전에 언급한 시한폭탄 유형이다. [본문으로]
2. 1분에 1개의 상품을 조회하고 1일에 1회 로그인한다고 가정하면 갱신 대상이 최대 1440건이다. [본문으로]