SQL 튜닝 - DML 튜닝

인덱스 유지비용

테이블 데이터를 변경하면 인덱스에도 변경이 발생합니다. 변경할 인덱스 레코드를 찾아가는 비용에 변경을 위한 REDO, UNDO 를 생성하는 비용까지 더해지므로 하나의 테이블에 연관된 인덱스가 많을수록 DML 성능이 저하됩니다.

 

UPDATE를 수행할 때, 테이블 레코드는 직접 변경하지만, 인덱스는 항상 정렬된 상태를 유지해야 하므로 DELETE & INSERT 방식으로 처리합니다. 추가적으로 인덱스 유지를 위한 UNDO 레코드 또한 2개씩 기록됩니다. 따라서 변경칼럼과 관련된 인덱스의 수에 따라 UPDATE 성능이 좌우됩니다.

 

INSERT나 DELETE는 관련된 인덱스 전체에 변경을 가해야하므로 총 인덱스 개수에 따라 성능이 크게 달라집니다. 대량의 데이터를 입력, 수정, 삭제할 때는 인덱스를 모두 DROP 하거나 UNUSABLE 상태로 변경한 다음 작업하는 것이 빠릅니다. 인덱스를 재생성을 감수하더라도 속도가 더 빠를 수 있습니다.

 

ORACLE INSERT 튜닝

 

DIRECT PATH INSERT

 

IOT(INDEX ORGANIZED TABLE) 은 정해진 키 순으로 정렬하면서 값을 입력합니다. 반면, 일반적인 힙 구조의 테이블은 FREELIST로 부터 할당받은 블록에 무작위로 값을 입력합니다.

 

- 데이터를 입력할 수 있는 블록을 FREELIST에서 찾습니다.
- FREELIST에서 할당받은 블록을 버퍼 캐시에서 찾아보고, 없으면 데이터 파일에서 읽어 캐시에 적재한 후에 INSSERT 데이터를 삽입한다.
- INSERT 내용을 UNDO 세그먼트에 기록합니다.
- INSERT 내용을 REDO 세그먼트에 기록합니다.

 

일반적인 트랜젝션을 처리할 때는 빈 공간부터 채워나가는 것이 효율적입니다. 하지만 대량의 데이터를 입력할때는 매우 비효율적입니다. 빈 블록이 모두 채워지면 다음 데이터 부터는 순차적으로 뒤쪽을 채워나감에도 건건이 FREELIST를 조회하면서 입력하기 때문입니다.

 

DIRECT PATH INSERT는 FREELIST 탐색과 캐시탐색을 거치지 않고 HWM 바깥 영역에 데이터 파일을 순차적으로 직접입력합니다. 이 방식을 사용하면 UNDO 데이터를 쌓지않기 때문에 더욱 속도가 빠릅니다. 설정에 따라 REDO 로깅도 하지않을 수 있습니다.

 

아래는 ORACLE 에서 DIRECT PATH INSERT 방식으로 데이터를 입력하는 방법입니다.

 

- INSERT SELECT 문장에 /+append/ 힌트 사용
- 병렬 모드로 INSERT
- direct 옵션을 지정하고 SQL*LOADER로 데이터를 로드
- CTAS(CREATE TABLE ... AS SELECT) 문장을 수행

 

NOLOGGING 모드 INSERT

 

 

ORACLE에서 테이블 속성을 NOLOGGING으로 바꾸면 로그기록이 최소화되어 INSERT 속도가 증가합니다. 이 기능은 DIRECT PATH INSERT 일 때만 작동하며. 일반 INSERT 문을 로깅하지 않는 방법은 없습니다.

 

 

ALTER TABLE T NOLOGGING;

 

DIRECT PATH INSERT 방식으로 데이터를 입력하면 EXCLUSIVE 모드 테이블 LOCK이 걸려 INSERT가 완료되기 전까지 다른 트랜잭션은 DML을 수행하지 못하게 됩니다. 따라서 트랜잭션이 빈번한 주간에는 이 모드를 사용해선 안됩니다.

 

NOLOGGING 상태에서 입력한 데이터는 장애가 발생하면 복구가 불가합니다. 곧바로 백업하거나 언제든 재생 가능한 데이터를 INSERT 할때만 사용해야합니다.

 

SQL SERVER INSERT

 

최소로깅

 

SQL SERVER에서 최소로깅 기능을 사용하려면, 해당 데이터베이스의 복구 모델이 BULK-LOGGED 또는 SIMPLE 로 설정돼 있어야합니다.

 

ALTER DATABASE SQLPRO SET RECOVERY SIMPLE

 

첫번째로, 파일 데이터를 읽어 DB로 로딩하는 BULK INSERT 구분을 사용할때 WITH 옵션에 TABLOCK 힌트를 추가하면 최소 로깅모드로 동작합니다.

 

BULK INSERT ADVENTUREWORKS.SALES.SALESORDERDETAIL
FROM "C:\ORDERS\LINEITEM.TXT"
WITH
(
  DATEFILETYPE = 'CHAR'
  FIELDTERMINATOR = '|',
  ROWTERMINATOR = '|\n',
  TABLOCK
)

 

두 번째로, ORACLE CREATE TABLE AS ... 와 같은 문장인 SELECT INTO 문을 사용합니다. 복구 모델이 BULK-LOGGED 또는 SIMPLE로 설정한 상태에서 이 문장을 사용하면 최소로깅 모드로 작동합니다.

 

SELECT * INTO TARGET FROM SOURCE;

 

세 번째로, SQL SERVER 2008 버전부터 최소로깅 기능을 일반 INSERT 문에 활용할 수 있습니다. 한 테이블에 INSERT 할땐 다음과 같이 간단히 TABLOCK 힌트를 사용합니다. 단, LOCK이 발생하기 때문에 여러 트랜잭션이 동시에 INSERT 할 수 업습니다.

 

INSESRT INTO T_HEAP WITH(TABLOCK) SELECT * FROM T_SOURCE

 

B*TREE 구조 테이블에 INSERT 할때도 최소 로깅이 가능합니다. 전제조건은 소스 데이터가 목표 테이블의 정렬과 동일해야하고 복구 모델이 BULK-LOGGED 또는 SIMPLE로 설정되야 합니다.

 

최소로깅을 위해 필요한 조건은 다음과 같습니다.

 

- 비어 있는 BTREE 구조에서 TABLOCK 힌트 사용
- 비어 있는 BTREE 구조에서 TF-610 활성화
- 비어 있지 않은 B*TREE 구조에서 TF-610 을 활성화하고 새로운 키 범위만 입력

 

위 조건에서 보듯 B*TREE 구조 테이블에 최소로깅 모드로 INSERT 할때는 TABLOCK 힌트가 필요하지 않기 때문에 소스 데이터의 값 범위가 중복되지 않는 한 동시 INSERT 가 가능합니다.

 

USE SQLPRO
GO

ALTER DATABASE SQLPRO RECOVERY SIMPLE

DBCC TRANCEON(610);

INSERT INTO T_IDX
SELECT * FROM T_SOURCE
-- T-IDX 테이블의 클러스터형 인덱스 키 순 정렬
ORDER BY COL1

 

최소로깅 작업 후에는 차등 백업을 수행해야합니다.

 

UPDATE 튜닝

 

TRUNCATE & INSERT 방식 사용

 

아래는 특정 시점 이후의 데이터의 특정 칼럼값을 모두 변경하는 UPDATE 문입니다.

 

UPDATE 주문 SET 상태코드 = '9999' WHERE 주문일시 < to_date("20000101", 'yyyymmdd')

 

대량의 데이터를 일반 UPDATE 문으로 갱신하면 오랜 시간이 소요됩니다. 이유는 다음과 같습니다.

 

- 테이블의 데이터를 갱신하는 작업
- 인덱스 데이터를 갱신하는 작업
- 버퍼 캐시에 없는 블록을 디스크에서 읽어 캐싱한 후 갱신하는 작업
- REDO & UNDO 생성
- 블록에 빈 공간이 없으면 새 블록 할당 후 ROW MIGRATION 발생

 

따라서 대량 데이터를 갱신할 때는 TRUNCATE 후 다시 INSERT 하는 것이 더 효과적입니다.

 

CREATE TABLE ORDER_TEMP AS SELECT * FROM ORDER;

TRUNCATE TABLE ORDER;

INSERT INTO ORDER(C_NUM, O_DATE, S_CODE)
SELECT C_NUM, O_DATE,
(CASE WHEN O_DATE >= TO_DATE('20000101', 'YYYYMMDD') THEN '9999' ELSE STATUS END) S_CODE
FROM ORDER_TEMP;

ALTER TABLE ORDER ADD CONSTRAINT ORDER_PK PRIMARY KEY(C_NUM, O_DATE);
CREATE INDEX ORDER_IDX1 ON ORDER(O_DATE, S_CODE);

 

대량의 데이터를 DELETE 하는 작업도 임시 백업 테이블 생성 후 삭제되지않은 데이터를 백업하고 기존 테이블을 통체로 삭제 후 백업해는 방식이 더 효율적입니다.

 

DELETE FROM ORDER WHERE O_DATE < TO_DATE('20000101', 'YYYYMMDD')

-- 대량의 데이터를 DELETE 할 때도 다음과 같이 처리하는 것이 빠를 수 있다.
CREATE TABLE ORDER_TEMP AS SELECT * FROM ORDER
WHERE 주문일시 >= TO_DATE('20000101', 'YYYYMMDD')

alter table emp drop constraint order_pk;
drop index order_idx1;
truncate table order;

insert into order
select * from order_temp;

alter table order add constraint order_pk primary key(c_num, o_date)
create index order_idx1 on order(o_date, s_code);

 

조인을 내포한 UPDATE 튜닝

 

조인을 내포한 UPDATE 문을 수행할 때는 UPDATE 자체의 성능보다는 조인 과정에서 발생하는 비효율로 인해 느려지는 경우가 더 많습니다.

전통적인 방식의 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 문을 효율적으로 실행하기 위해서는 고객번호 + 거래일시가 포함된 인덱스가 필요합니다. 하지만 대상 데이터가 많다면 조인 튜닝을 통해 엑세스되는 데이터 양 자체를 줄이는 것이 더 효과적입니다. 서브쿼리 unnesting과 hash_sj 힌트를 통해 해시 세미조인을 유도하는 것이 더 효과적입니다.

 

ORACLE 수정가능 조인뷰 활용

 

조인 뷰는 FROM 절에 2개 이상의 테이블이 있는 뷰를 말합니다. 조인 뷰를 통해 원본 테이블에 입력, 수정, 삭제가 가능합니다. 한 가지 제약사항은 키-보존 테이블에만 입력,수정,삭제를 허용한다는 것입니다.

 

키-보존 테이블이란, 조인된 결과집합의 각 레코드가 UNIQUE 하게 식별 가능한 테이블을 말합니다. 옵티마이저는 이를 위해 조인하는 테이블에 UNIQUE 인인덱스가 있는지, 조인 키 칼럼으로 GROUP BY 했는지 등을 확인해 유일성을 판단합니다. 키-보존 테이블이 아닌 경우 입력, 수정, 삭제를 허용하지 않습니다.

 

GROUP BY 한 집합은 유일성을 보장하지만 ORACLE 11G 까지는 이를 키-보존 테이블로 인정하지 않았습니다. 하지만 12G 부터는 이를 인정해 키-보존 테이블로 인식합니다.

 

다음과 같은 형태의 조인 뷰는 고객 테이블의 고객번호 칼럼에 UNIQUE 인덱스가 있으면 버전에 상관없이 수정이 가능합니다.

 

UPDATE
(
  SELECT T.주문연락처, T.배송지주소, C.고객연락처, T.고객주소
  FROM 배송 T, 고객 C
  WHERE C.고객번호 = T.고객번호
  AND 거래일시 >= TRUNC(SYSDATE)
  AND T.상태코드 = 'INVLD'
)
SET 주문연락처 = 고객연락처, 배송지주소 = 고객주소

 

ORACLE MERGE 문 활용

 

MERGE INTO 문을 이용하면 하나의 SQL안에서 INSERT, UPDATE, DELETE 작업을 한 번에 처리할 수 있습니다.
아래는 MERGE 문을 이용해 INSERT와 UPDATE를 동시에 처리하는 예시입니다.

 

MERGE INTO 고객 T USING 고객변경분 S ON (T.고객번호 = S.고객번호)
WHEN MATCHED THEN UPDATE 
    SET T.고객번호 = S.고객번호, T.고객명 = S.고객명, T.이메일 = S.이메일
WHEN NOT MATCHED THEN INSERT (고객번호, 고객명, 이메일, 전화번호, 거주지역, 주소, 등록일시) VALUES
    (S.고객번호, S.고객명, S.이메일, S.전화번호, S.거주지역, S.주소, S.등록일시);

 

10G 이상부터는 UPDATE와 INSERT를 선택적으로 처리할 수 있습니다.

 

MERGE INTO 고객 t USING 고객변경분 S ON (t.고객번호 = s.고객번호)
WHEN MATCHED THEN UPDATE
  SET T.고객번호 = S.고객번호, T.고객명 = S.고객명, T.이메일 = S.이메일;

MERGE INTO 고객 t USING 고객변경분 S ON (t.고객번호 = s.고객번호)
WHEN NOT MATCHED THEN INSERT
(고객번호, 고객명, 이메일, 전화번호, 거주지역, 주소, 등록일시) VALUES
(S.고객번호, S.고객명, S.이메일, S.전화번호, S.거주지역, S.주소, S.등록일시);

 

이 기능을 이용해 키-보존 테이블 업데이트 제약을 회피할 수 있습니다.

 

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