TunA

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

테스트를 위해 아래의 테이블을 생성하자. 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;

-- 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 |
----------------------------------------------------------------

-- 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 |
----------------------------------------------------