기본 DML튜닝 - 2

3. Array Processing 활용

• 실무에서 절차적 프로그래밍을 One SQL로 구현하는것은 복잡한 업무로직 때문에 쉽지않다.
• 그럴경우 Array Processing활용하면 Call부하를 획기적으로 줄일 수 있다.

  --SQL수행예제
  declare
  cursor c is select * from source;
  type typ_source is table of c%rowtype;
  l_source typ_source;
  
  l_array_size number default 10000;
  
  procedure insert_target(p_source in typ_source) is
  begin
  forall i in p_source.first..p_source.last
  insert into target values p_source(i);
  end insert_target;
  
  begin
  open c;
  loop
  fetch c bulk collect into l_source limit l_array_size;
  
  insert_target(l_source);
  exit when c%notfound;
  end loop;
  
  close c;
  
  commit;
  end;
  -자바는 PreparedStatement의 addBatch, executeBatch를 활용해 수행가능

• 수행할 업무를 배열에 담아 한번에 콜하므로 전체 100만번 호출을 백번호출로 줄였을때 Call부하 감소에 큰도움

4. 인덱스 및 제약 해제를 통한 대략 DML튜닝

- 한줄요약 : 대량 입출력시 nologging, 인덱스해제, pk해재후 입력한다음 생성하면 성능향상효과가있다

• 앞서 설명했듯 인덱스와 무결성제약조건은 DML성능에 큰 영향
• OLTP환경에선 이들기능을 해제할 순 없지만
• 동시 트랜잭션없이 대량 데이터를 적재하는 배치프로그램에서는 이들기능을 해제하여 큰성능개선 효과
• CASE
  • PK와 인덱스가있는테이블에 1000만건 입력
  • PK제약과 인덱스 해제(PK DROP, 일반인덱스 Unusable) 후 100만건 입력

    -- 인덱스가 Unusable상태에서 데이터 입력하려면
    -- 기본값이 true
    alter session set skip_unusable_indexes = true;

  • PK제약과 인덱스 해제2 - PK제약에 Non-Unique인덱스를 사용
    PK제약 비활성화(disable)하고, 인덱스를 Unusable
    작업마치고 PK 인덱스 재생성, PK제약 활성화

5. 수정가능 뷰

전통적방식의 update

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

• 아래와같이 고칠 수 있다

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

• 위방식도 비효율은있다, 한달이내 고객별거래데이터를 두번조회, 총고객수와 한달이내 거래고객수에 따라 성능 좌우
• 총 고객 수가 아주 많다면 Exists서브쿼리를 아래와 같이 해시 세미조인으로 유도하는것을 고려

  update 고객 c
  set (최종거래일시, 최근거래횟수, 최근거래금액) =
     (select max(거래일시), count(*), sum(거래금액)
        from 거래
       where 고객번호 = c.고객번호
         and 거래일시 >= trunc(add_months(sysdate, -1)))
  where exists (select /*+ unnest hash_sj*/'x' from 거래
                where 고객번호 = c.고객번호
                  and 거래일시 >= trunc(add_months(sysdate, -1)))
                             

• 만약 한달이내 거래를 발생시킨 고객이많아 update발생량이 많다면 아래와같이 변경 고려
  but 모든 고객 레코드에 LOCK이걸리는것과, 이전 같은 값으로 갱신되는 비중이 높을수록 Redo로그발생량이 증가해 오히려 비효율적일수있음.

  update 고객 c
  set (최종거래일시, 최근거래횟수, 최근거래금액) =
   (select nvl(max(거래일시), c.최종거래일시)
          ,decode(count(*), 0, c.최근거래횟수, count(*))
          ,nvl(sum(거래금액), c.최근거래금액)
      from 거래
     where 고객번호 = c.고객번호
       and 거래일시 >= trunc(add_months(sysdate, -1)))

• 이처럼 전통적 update문으로는 비효율 완전 해소할 수 없다

수정가능 조인 뷰

• 조인뷰 : FROM절에 두 개 이상 테이블을 가진 뷰
• 입력, 수정, 삭제가 허용되는 조인 뷰
• 1쪽 집합과 조인하는 M쪽 집합에만 입력, 수정, 삭제가 허용
• 키-보존테이블, 비 키보존테이블 검색해서 추가 커멘트할것.

6. MERGE문 활용

• UPSERT라고도 부른다 (UPDATE+INSERT)
• ON절에 기술한 조인문 외에 아래쪽에 WHERE절로 추가로 조건절을 기술할 수 있다.
• 이미 저장된 데이터를 조건에 따라 지우는 기능도 제공
  조인에 성공한 데이터만 삭제할 수 있다