[1] 인덱스 유지 비용

(1) 테이블 데이터 변경시

변경할 인덱스 레코드를 찾아가는 비용 + Redo, Undo를 생성하는 비용이 발생한다.

1. Update 수행시

• 테이블 레코드는 직접 변경하지만 인덱스 레코드는 정렬 상태를 유지하기 위해 Delete & Insert 방식으로 처리한다.
• Undo 레코드도 2개씩 기록된다.
  • 따라서 변경 컬럼과 관련된 인덱스 개수에 따라 Update 성능이 좌우된다.

2. Insert나 Delete 문일 때

• 인덱스 모두에 변경을 가하므로 총 인덱스 개수에 따라 성능이 크게 달라진다.

3. 대량의 데이터를 입력/수정/삭제할 때

• 인덱스 개수가 DML 성능에 큰 영향을 미치므로 인덱스를 모두 Drop하거나 Unusable 상태로
  변경한 다음 작업하는 것이 빠를 수 있다.

[2] Insert 튜닝

(1) Oracle Insert 튜닝

1. 일반적인 힙 구조 테이블에서의 데이터 입력 방법

• 데이터 입력시 빈 공간을 가진 블록리스트를 관리하는 Freelist로 부터 순서 없이 블록을 할당받아 무작위로 값을 입력하는 방식
• 대량의 데이터 입력시 이 방식은 비효율적이다.
• 일반적인 트랜잭션을 처리할 때는 빈 공간부터 찾아 채워 나가는 이 방식이 효율적

Freelist

• HWM(High-Water Mark) 아래쪽에 위치한 블록 중 어느 정도(테이블에 지정한 pctfree와 pctused 파라미터에 의해 결정됨) 빈 공간을 가진 블록 리스트를 관리하는 자료구조
• Freelist에서 할당받은 블록을 버퍼 캐시에서 찾아보고, 없으면 데이터 파일에서 읽어 캐시에 적재한 후에 데이터를 삽입한다.
• 대량의 데이터 입력시 이 방식은 비효율적이다.
  • 빈 블록은 얼마 지나지 않아 모두 채워지고 이후부터는 순차적으로 뒤쪽에만 데이터를 쌓게 될 텐데도 건건이 Freelist를 조회하면서 입력하기 때문이다.

2. Direct Path Insert

Freelist를 거치지 않고 HWM 바깥 영역에, 그것도 버퍼 캐시를 거치지 않고 데이터 파일에 곧바로 입력하는 방식이다.

• 대량의 데이터 입력시 효율적
• Undo 데이터를 쌓지 않는 점도 속도 향상의 주요인이다.

1) Oracle 에서 Direct Path Insert 방식으로 데이터를 입력하는 방법

• insert select 문장에 /*+ append */ 힌트 사용
• 병렬 모드로 insert
• direct 옵션을 지정하고 SQL*Loader(sqlldr)로 데이터를 로드
• CTAS(create table … as select) 문장을 수행

2) Direct Path Insert 방식으로 데이터 입력시 주의할 점

• Exclusive 모드 테이블 Lock이 걸린다.
  • 작업이 수행되는 동안 해당 테이블에 DML 수행 불가.
• 따라서 트랜잭션이 빈번한 주간에 이 옵션을 사용하는 것은 절대 금물
• nologging 상태에서 입력한 데이터는 장애 발생시 복구 불가하다.

3) nologging 모드 Insert하기

alter table t NOLOGGING;

• Redo 로그까지 최소화(데이터 딕셔너리 변경사항만 로깅)되므로 더 빠르게 insert한다.
• logging 상태에서 입력한 데이터는 장애가 발생했을 때 복구가 불가능하다.
• 이 옵션을 사용해 데이터를 insert한 후에는 곧바로 백업을 실시해야 한다.
• 또는 언제든 재생 가능한 데이터를 insert할 때만 사용한다.
• 예를 들어 배치 프로그램에서 중간 단계의 임시 테이블을 만들 때가 대표적! DW 시스템에 읽기 전용 데이터를 적재할 때도 유용!
  • DW성 데이터는 OLTP로부터 언제든 재현해 낼 수 있기 때문이다.

[3] Update 튜닝

(1) Truncate & Insert 방식 사용

• 대량의 데이터를 아래와 같이 일반 Update문으로 갱신하면 상당히 오랜 시간이 소요
• Delete문일 때도 마찬가지이다..

1. 일반 update , delete문 수행시 처리 비용

• 테이블 데이터를 갱신하는 본연의 작업
• 인덱스 데이터까지 갱신
• 버퍼 캐시에 없는 블록을 디스크에서 읽어 버퍼 캐시에 적재한 후에 갱신
• 내부적으로 Redo와 Undo 정보 생성
• 블록에 빈 공간이 없으면 새 블록 할당(→ Row Migration 발생)

1999년 12월 31일 이전 주문 데이터의 상태코드를 모두 변경하는 일반 Update문

   update 주문 set 상태코드 = '9999'
   where 주문일시 < to_date('20000101', 'yyyymmdd')

1999년 12월 31일 이전 주문 데이터의 상태코드를 모두 지우는 일반 Delete문

   delete from 주문
   where 주문일시 < to_date('20000101', 'yyyymmdd');

2. 임시 테이블에 데이터를 옮기고 데이터를 일괄 삭제

• 대량의 데이터를 Update,Delete 할 때 아래와 같이 임시 테이블에 데이터를 옮기고 데이터를 일괄 삭제하여 처리하는 것이 빠를 수 있다.

순서

1. 대상테이블의 데이터로 temp 테이블 생성'
2. 대상테이블의 제약조건 및 인덱스 삭제
3. 대상테이블 truncate
4. temp 테이블에 있는 원본 데이터를 update 할 값으로 수정하여 대상테이블에 insert
5. 대상테이블에 제약조건 및 인덱스 생성

update 튜닝 예시

-- 대상테이블의 데이터로 temp 테이블 생성
create table 주문_임시 as
select * from 주문;

-- 대상테이블의 제약조건 및 인덱스 삭제
alter table emp drop constraint 주문_pk;  

-- 대상테이블 truncate
truncate table 주문;

-- temp 테이블에 있는 원본 데이터를 update 할 값으로 수정하여 대상테이블에 insert
insert into 주문(고객번호, 주문일시,상태코드) 
select 고객번호, 주문일시, ,
	(case when 주문일시 >= to_date('20000101', 'yyyymmdd')
         ]then '9999' else status end) 상태코드
from 주문_임시;

-- 상테이블에 제약조건 및 인덱스 생성
alter table 주문 add constraint 주문_pk primary key(고객번호, 주문일시); 
create index 주문_idx1 on 주문(주문일시, 상태코드); 

delete 튜닝 예시

   create table 주문_임시
   as select *
   from 주문
   where 주문일시 >= to_date('20000101', 'yyyymmdd');
   
   alter table emp drop constraint 주문_pk;
   drop index 주문_idx1;

   truncate table 주문;
   insert into 주문 select * from 주문_임시;

   alter table 주문 add constraint 주문_pk primary key(고객번호, 주문일시);
   create index 주문_idx1 on 주문(주문일시, 상태코드);

3. 손익분기점

• 인덱스가 하나도 없는 상태에서 테스트해 봐도 대략 20% 수준에서 손익분기점이 결정된다.
• 만약 인덱스까지 여러 개 있다면 손익분기점은 더 낮아진다.
• Oracle의 경우 위 CTAS 문장에 nologging 옵션을 사용하고서 Insert 문장에 append 힌트까지 사용하면 손익분기점은 더 낮아진다.

(2) 조인을 내포한 Update 튜닝

• 조인을 내포한 Update 문을 수행할 때는 Update 자체의 성능보다 조인 과정에서 발생하는 비효율 때문에
  성능이 느려지는 경우가 더 많음

1. 전통적인 방식의 Update문

• 다른 테이블과 조인을 통해 Update를 수행할 때, 아래와 같이 일반적인 Update문을 사용하면 비효율이 발생
  • Update를 위해 참조하는 테이블을 2번 액세스해야 하기 때문이다.

update 고객
   set (최종거래일시, 최근거래금액) =
         ( select max(거래일시), sum(거래금액)
         from 거래
         where 고객번호 = 고객.고객번호
         and 거래일시 >= trunc(add_months(sysdate,-1))
         )
   where exists (
   		select 'x'
         from 거래
         where 고객번호 = 고객.고객번호
         and 거래일시 >= trunc(add_months(sysdate,-1))
         );

• 위 Update를 위해서는 기본적으로 거래 테이블에 [고객번호+거래일시] 인덱스가 있어야 한다.
• 인덱스가 그렇게 구성돼 있어도 고객 수가 많고 고객별 거래 데이터가 많다면 위 쿼리는 결코 빠르게 수행될 수 없다.
  • Random 액세스 방식으로 조인을 수행하기 때문이다.
  • 그럴 때는 서브쿼리에 unnest와 함께 hash_sj 힌트를 사용해 해시 세미 조인(Semi Join) 방식으로 유도하는 것이 효과적이다.
• 해시 세미 조인 방식으로 수행시 Random 액세스는 상당히 줄일 수 있지만 거래 테이블을 2번 액세스하는 비효율은 여전히 남는다.
  • 이 문제를 해결하기 위한 확장 Update 문장이 DBMS마다 조금씩 다른 형태로 제공된다.

2. 문제를 해결하기 위한 확장 Update 문장 (Oracle)

Oracle 수정 가능 조인 뷰 활용

• ‘조인 뷰’는 from절에 두 개 이상 테이블을 가진 뷰
• 조인 뷰를 통해 원본 테이블에 입력, 수정, 삭제가 가능
• 조인 뷰에서의 한가지 제약사항 : 키-보존 테이블에만 입력, 수정, 삭제가 허용된다.

update /*+ bypass_ujvc */
     (select c.최종거래일시, c.최근거래금액, t.거래일시, t.거래금액
      from (select 고객번호, max(거래일시) 거래일시, sum(거래금액) 거래금액
          		from 거래
                where 거래일시 >= trunc(add_months(sysdate,-1))
               group by 고객번호) t , 고객 c
      where c.고객번호 = t.고객번호 )
set 최종거래일시 = 거래일시 , 최근거래금액 = 거래금액 ; 

• 키-보존 테이블(Key-Preserved Table)
  • 조인된 결과집합을 통해서도 중복 없이 Unique하게 식별이 가능한 테이블
  • Unique한 집합과 조인되어야 함
  • 옵티마이저는 조인되는 테이블에 Unique 인덱스가 있는지를 보고 Unique 집합 여부를 판단
  • 결국, Unique 인덱스가 없는 테이블과 조인된 테이블에는 입력, 수정, 삭제가 허용되지 않는다.

Update문이 제대로 수행되려면 고객 테이블이 키-보존 테이블이어야 한다

     drop table t1;
     create table t1(c1 number, c2 varchar2(1));

     insert into t1
     select rownum , 'x'
     from dual
     connect by level <= 10;

     create unique index idx1_t1 on t1(c1);
     -- t1 테이블의 자식 테이블인 t2 생성
     drop table t2;
     create table t2(c1 number, c2 number, c3 varchar2(1));
     insert into t2
     select mod(rownum,10) + 1 , rownum , 'x'
     from dual
     connect by level <= 100 ;

     create unique index idx1_t2 on t2(c1, c2);

     -- t1 컬럼 수정 => 키보존 테이블이 아니므로 수정 불가
     SQL> update (select t1.c1, t1.c2, t2.c3
                 from   t1 , t2
                 where  t1.c1 = t2.c1 )
          set    c2 = 'A' ;
         
     6행에 오류:
     ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다.

     -- t2 컬럼 수정 => 키보존 테이블이므로 수정 가능
     SQL> update (select t1.c1, t1.c2, t2.c3
                 from   t1 , t2
                 where  t1.c1 = t2.c1 )
          set    c3 = 'A' ;
          
100 행이 갱신되었습니다.

-- t1 테이블 인덱스를 제거해서 M : N 관계로 만든 후 t2 컬럼 수정 => 수정 불가
SQL> drop index idx1_t1;
인덱스가 삭제되었습니다.

SQL> update (select t1.c1, t1.c2, t2.c3
            from   t1 , t2
            where  t1.c1 = t2.c1 )
     set    c3 = 'A' ;
     
6행에 오류:
ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다

• 방금 본 Update문이 제대로 수행되려면 고객 테이블이 키-보존 테이블이어야 한다
• 그런데 거래 데이터를 집계한 인라인 뷰에 Unique 인덱스가 존재할 수 없으므로 Oracle은 고객 테이블을
  키-보존 테이블로 인정하지 않는다.
• 고객번호로 group by한 집합의 고객번호에 중복 값이 있을 수 없다는 사실을 옵티마이저도 충분히 인지할 수 있는데도 말이다.

bypass_ujvc 힌트

• 집합적으로 Unique성이 보장됨에도 불구하고 Unique 인덱스를 찾을 수 없다는 이유로 옵티마이저가 필요 이상의 제약을 가한 셈인데, 다행히 이를 피해갈 수 있는 bypass_ujvc 힌트가 제공된다.
• 이 힌트는 ‘Bypass Updatable Join View Check’를 축약해 만든 것이다.
• 이 힌트는 Update를 위해 참조하는 집합에 중복 레코드가 없음이 100% 보장될 때만 사용할 것을 당부한다.
• 10g부터는 바로 이어서 설명할 Merge Into 구문을 활용하는 것이 바람직하다.

Oracle Merge 문

• merge 문 : insert, update, delete 작업을 한번에 처리가 가능하다.
• Updatable Join View 기능을 대체한다.

     merge into 고객 c
     using (select 고객번호 , max(거래일시) 거래일시 , sum(거래금액) 거래금액
            from   거래
            where  거래일시 >= trunc(add_months(sysdate, -1))
            group by 고객번호) t
     on    (c.고객번호 = t.고객번호)
     when matched then update set c.최종거래일시 = t.거래일시 , c.최근거래금액 = t.거래금액;