[1] 인덱스 유지 비용

• 테이블 데이터 변경시 관련 인덱스 변경이 발생한다.
  • Update 수행시
    • 테이블 레코드는 직접 변경하지만 인덱스 레코드는 정렬 상태를 유지하기 위해 Delete & Insert 방식으로 처리되며 Undo 레코드도 2개씩 기록된다.
    • 따라서 변경 컬럼과 관련된 인덱스 개수에 따라 Update 성능이 좌우된다.
  • Insert나 Delete 문일 때
    • 인덱스 모두에 변경을 가하므로 총 인덱스 개수에 따라 성능이 크게 달라진다.
    • 인덱스 개수가 DML 성능에 큰 영향을 미치므로 대량의 데이터를 입력/수정/삭제할 때는 인덱스를 모두 Drop하거나 Unusable 상태로 변경한 다음 작업하는 것이 빠를 수 있다.
• ⭐️변경할 인덱스 레코드를 찾아가는 비용 + Redo, Undo를 생성하는 비용

[2] Insert 튜닝

(1) Oracle Insert 튜닝

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

• 데이터 입력시 빈 공간을 가진 블록리스트를 관리하는 Freelist로 부터 순서 없이 블록을 할당받아 무작위로 값을 입력한다.

  ✅ Freelist

  • HWM(High-Water Mark) 아래쪽에 위치한 블록 중 어느 정도 빈 공간을 가진 블록 리스트를 관리하는 자료구조이다.
  • 테이블에 지정한 pctfree와 pctused 파라미터에 의해 결정된다.

• 대량의 데이터 입력시 이 방식은 비효율적이다.

• 일반적인 트랜잭션을 처리할 때는 빈 공간부터 찾아 채워 나가는 이 방식이 효율적이다.

• Freelist에서 할당받은 블록을 버퍼 캐시에서 찾아보고, 없으면 데이터 파일에서 읽어 캐시에 적재한 후에 데이터를 삽입한다.

• 대량의 데이터 입력시 이 방식은 비효율적이다.

  • 빈 블록은 얼마 지나지 않아 모두 채워지고 이후부터는 순차적으로 뒤쪽에만 데이터를 쌓게 될 텐데도 건건이 Freelist를 조회하면서 입력하기 때문이다.

2. Direct Path Insert

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

• insert select 문장에 /+ append / 힌트를 사용한다.
• 병렬 모드로 insert한다.
• direct 옵션을 지정하고 SQL*Loader(sqlldr)로 데이터를 로드한다.
• CTAS(create table … as select) 문장을 수행한다.
  • Oracle의 경우 CTAS 문장에 nologging 옵션을 사용하면 손익분기점은 더 낮아진다.

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

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

• Freelist를 거치지 않고 HWM 바깥 영역에, 그것도 버퍼 캐시를 거치지 않고 데이터 파일에 곧바로 입력하는 방식이다.
• 대량의 데이터 입력시 효율적이다.
• Undo 데이터를 쌓지 않는 점도 속도 향상의 주요인이다.

  3. nologging 모드 Insert

 alter table t NOLOGGING;

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

[3] Update 튜닝

(1) Truncate &Insert 방식 사용

• 대량의 데이터를 일반 Update문으로 갱신하면 상당히 오랜 시간이 소요된다.
  Delete문일 때도 마찬가지이다.
• 발생하는 비용
  • 테이블 데이터를 갱신하는 본연의 작업
  • 인덱스 데이터까지 갱신
  • 버퍼 캐시에 없는 블록을 디스크에서 읽어 버퍼 캐시에 적재한 후에 갱신
  • 내부적으로 Redo와 Undo 정보 생성
  • 블록에 빈 공간이 없으면 새 블록 할당(→ Row Migration 발생)
• 대량의 데이터를 갱신할 때는 Update문을 이용하기보다 아래와 같이 처리한다.
  • 1. 대상테이블의 데이터로 temp 테이블 생성
  • 2. 대상테이블의 제약조건 및 인덱스 삭제
  • 3. 대상테이블 truncate
  • 4. temp 테이블에 있는 원본 데이터를 update 할 값으로 수정하여 대상테이블에 insert
  • 5. 대상테이블에 제약조건 및 인덱스 생성

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

• 조인을 내포한 Update 문을 수행할 때는 Update 자체의 성능보다 조인 과정에서 발생하는 비효율 때문에 성능이 느려지는 경우가 더 많다.
• 전통적인 방식의 Update문
  • 다른 테이블과 조인을 통해 Update를 수행할 때, 아래와 같이 일반적인 Update문을 사용하면 비효율이 발생한다.
  • Update를 위해 참조하는 테이블을 2번 액세스해야 하기 때문이다.

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

• Oracle 수정 가능 조인 뷰 활용
  • ‘조인 뷰’는 from절에 두 개 이상 테이블을 가진 뷰이다.
  • 조인 뷰를 통해 원본 테이블에 입력, 수정, 삭제가 가능하다.
  • ⭐️ 여기에 한가지 제약사항이 있는데, 키-보존 테이블에만 입력, 수정, 삭제가 허용된다.
• 키-보존 테이블(Key-Preserved Table)
  • 조인된 결과집합을 통해서도 중복 없이 Unique하게 식별이 가능한 테이블
  • Unique한 집합과 조인되어야 한다.
  • 옵티마이저는 조인되는 테이블에 Unique 인덱스가 있는지를 보고 Unique 집합 여부를 판단하며 즉, Unique 인덱스가 없는 테이블과 조인된 테이블에는 입력, 수정, 삭제가 허용되지 않는다.

[예시] 고객번호로 group by한 집합의 고객번호에 중복 값이 있을 수 없다는 사실을 옵티마이저도 충분히 인지할 수 있는데도 불구하고 키-보존 테이블로 인식을 안 하는 경우

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 최종거래일시 = 거래일시 , 최근거래금액 = 거래금액

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

     merge into 테이블 A -- 수정 대상 테이블
     using (테이블) B
     on    (A.~~ = B.~~) -- 두 테이블 관계 조건절
     when matched then update set A.~~ = B.~~ , A.~~ = B.~~;

• 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.거래금액;