SQL
DA, SQL, DB보안 등 실무자를 위한 위한 DB기술 바이블!
테이블 데이터를 변경하면 관련된 인덱스에도 변경이 발생한다. 변경할 인덱스 레코드를 찾아가는 비용에 Redo, Undo를 생성하는 비용까지 더해지므로 인덱스 개수가 많을수록 DML 성능이 나빠지는 것은 당연하다. Update를 수행할 때, 테이블 레코드는 직접 변경하지만 인덱스 레코드는 Delete & Insert 방식으로 처리된다. 인덱스는 항상 정렬된 상태를 유지해야 하기 때문이며, 인덱스 유지를 위한 Undo 레코드도 2개씩 기록된다. 따라서 변경 칼럼과 관련된 인덱스 개수에 따라 Update 성능이 좌우된다. Insert나 Delete 문일 때는 인덱스 모두에(Oracle에서는 인덱스 칼럼이 모두 Null인 경우는 제외) 변경을 가해야 하므로 총 인덱스 개수에 따라 성능이 크게 달라진다. 이처럼 인덱스 개수가 DML 성능에 큰 영향을 미치므로 대량의 데이터를 입력/수정/삭제할 때는 인덱스를 모두 Drop하거나 Unusable 상태로 변경한 다음에 작업하는 것이 빠를 수 있다. 인덱스를 재생성하는 시간까지 포함하더라도 그냥 작업할 때보다 더 빠를 수 있기 때문이다. Insert 속도를 향상시키는 방법에 대해 Oracle부터 살펴보자. IOT(index-organized table)는 정해진 키(Key) 순으로 정렬하면서 값을 입력하는 반면, 일반적인 힙 구조 테이블(heap-organized table)은 순서 없이 Freelist로부터 할당받은 블록에 무작위로 값을 입력한다. Freelist는 HWM(High-Water Mark) 아래쪽에 위치한 블록 중 어느 정도(테이블에 지정한 pctfree와 pctused 파라미터에 의해 결정됨) 빈 공간을 가진 블록 리스트를 관리하는 자료구조다. Freelist에서 할당받은 블록을 버퍼 캐시에서 찾아보고, 없으면 데이터 파일에서 읽어 캐시에 적재한 후에 데이터를 삽입한다. 일반적인 트랜잭션을 처리할 때는 빈 공간부터 찾아 채워 나가는 위 방식이 효율적이다. 하지만, 대량의 데이터를 Bulk로 입력할 때는 매우 비효율적이다. 빈 블록은 얼마 지나지 않아 모두 채워지고 이후부터는 순차적으로 뒤쪽에만 데이터를 쌓게 될 텐데도 건건이 Freelist를 조회하면서 입력하기 때문이다. Freelist를 거치지 않고 HWM 바깥 영역에, 그것도 버퍼 캐시를 거치지 않고 데이터 파일에 곧바로 입력하는 Direct Path Insert 방식을 사용하면 대용량 Insert 속도를 크게 향상시킬 수 있다. 이 방식을 사용할 때 Undo 데이터를 쌓지 않는 점도 속도 향상의 주요인이다. 사용자가 커밋할 때만 HWM를 상향 조정하면 되기 때문에 Undo 데이터가 불필요하다. 아래는 Oracle에서 Direct Path Insert 방식으로 데이터를 입력하는 방법이다. Oracle에서 아래와 같이 테이블 속성을 nologging으로 바꿔주면 Redo 로그까지 최소화(데이터 딕셔너리 변경사항만 로깅)되므로 더 빠르게 insert 할 수 있다. 이 기능은 Direct Path Insert 일 때만 작동하며, 일반 insert문을 로깅하지 않도록 하는 방법은 없다. 주의할 것은, Direct Path Insert 방식으로 데이터를 입력하면 Exclusive 모드 테이블 Lock이 걸린다는 사실이다. 즉, 작업이 수행되는 동안 다른 트랜잭션은 해당 테이블에 DML을 수행하지 못하게 된다. 따라서 트랜잭션이 빈번한 주간에 이 옵션을 사용하는 것은 절대 금물이다. nologging 상태에서 입력한 데이터는 장애가 발생했을 때 복구가 불가능하다는 사실도 반드시 기억하기 바란다. 이 옵션을 사용해 데이터를 insert한 후에는 곧바로 백업을 실시해야 한다. 또는 언제든 재생 가능한 데이터를 insert할 때만 사용해야 한다. 예를 들면, 배치 프로그램에서 중간 단계의 임시 테이블을 만들 때가 대표적이다. DW 시스템에 읽기 전용 데이터를 적재할 때도 유용하다. DW성 데이터는 OLTP로부터 언제든 재현해 낼 수 있기 때문이다. 물론 가용성 요건과 운영 환경이 시스템마다 다르므로 상황에 맞게 적용하기 바란다. SQL Server에서 최소 로깅 기능을 사용하려면, 우선 해당 데이터베이스의 복구 모델(Recovery model)이 ‘Bulk-logged’ 또는 ‘Simple’로 설정돼 있어야 한다. 첫 번째로, 아래와 같이 파일 데이터를 읽어 DB로 로딩하는 Bulk Insert 구문을 사용할 때, With 옵션에 TABLOCK 힌트를 추가하면 최소 로깅 모드로 작동한다. 두 번째로, Oracle CTAS와 같은 문장이 select into 인데, 복구 모델이 ‘Bulk-logged’ 또는 ‘Simple’로 설정된 상태에서 이 문장을 사용하면 최소 로깅 모드로 작동한다. 세 번째로, SQL Server 2008 버전부터 최소 로깅 기능을 일반 Insert문에서 활용할 수 있게 되었다. 힙(Heap) 테이블에 Insert할 땐 아래와 같이 간단히 TABLOCK 힌트를 사용하면 된다. 이때, X 테이블 Lock 때문에 여러 트랜잭션이 동시에 Insert 할 수 없게 된다는 사실을 기억하기 바란다. B*Tree 구조 테이블(클러스터형 인덱스)에 Insert 할 때도 최소 로깅이 가능한데, 가장 기본적인 전제 조건은 소스 데이터를 목표 테이블 정렬(클러스터형 인덱스 정렬?? 데이터베이스의 복구 모델(Recovery model)은 ‘Bulk-logged’ 또는 ‘Simple’로 설정돼 있어야 한다. 최소 로깅을 위해 필요한 다른 조건은 다음과 같다. 위 조건에서 보듯, B*Tree 구조 테이블에 최소 로깅 모드로 Insert 할 때는 TABLOCK 힌트가 반드시 필요하지 않다. 따라서 입력하는 소스 데이터의 값 범위가 중복되지 않는다면 동시 Insert도 가능하다. 아래는 B*Tree 구조 테이블에 최소 로깅 모드로 Insert하는 예시다. 목표 테이블 정렬 순서와 같게 하려고 order by 절을 사용한 것을 확인하기 바란다. SQL Server에서도 최소 로깅 작업을 수행한 다음에는 차등(Differential) 백업을 수행해 줘야 한다는 사실을 기억하자. 아래는 1999년 12월 31일 이전 주문 데이터의 상태코드를 모두 변경하는 Update문이다. 대량의 데이터를 위와 같이 일반 Update문으로 갱신하면 상당히 오랜 시간이 소요될 수 있다. 다음과 같은 이유 때문이며, Delete문일 때도 마찬가지다. 따라서 대량의 데이터를 갱신할 때는 Update문을 이용하기보다 아래와 같이 처리하는 것이 더 빠를 수 있다. 인덱스가 하나도 없는 상태에서 테스트해 봐도 대략 20% 수준에서 손익분기점이 결정되는 것을 알 수 있고, 만약 인덱스까지 여러 개 있다면 손익분기점은 더 낮아진다. Oracle의 경우 위 CTAS 문장에 nologging 옵션을 사용하고서 Insert 문장에 append 힌트까지 사용하면 손익분기점은 더 낮아진다. 아래는 1999년 12월 31일 이전 주문 데이터의 상태코드를 모두 지우는 Delete문이다. 대량의 데이터를 Delete 할 때도 아래와 같이 처리하는 것이 빠를 수 있다. 조인을 내포한 Update 문을 수행할 때는 Update 자체의 성능보다 조인 과정에서 발생하는 비효율 때문에 성능이 느려지는 경우가 더 많다. 그 원인과 튜닝 방안에 대해 살펴보자. 다른 테이블과 조인을 통해 Update를 수행할 때, 아래와 같이 일반적인 Update문을 사용하면 비효율이 발생한다. Update를 위해 참조하는 테이블을 2번 액세스해야 하기 때문이다. 위 Update를 위해서는 기본적으로 거래 테이블에 [고객번호+거래일시] 인덱스가 있어야 한다. 인덱스가 그렇게 구성돼 있어도 고객 수가 많고 고객별 거래 데이터가 많다면 위 쿼리는 결코 빠르게 수행될 수 없는데, Random 액세스 방식으로 조인을 수행하기 때문이다. 그럴 때는 서브쿼리에 unnest와 함께 hash_sj 힌트를 사용해 해시 세미 조인(Semi Join) 방식으로 유도하는 것이 효과적이다. 해시 세미 조인 방식으로 수행하면 Random 액세스는 상당히 줄일 수 있지만 거래 테이블을 2번 액세스하는 비효율은 여전히 남는다. 이 문제를 해결하기 위한 확장 Update 문장이 DBMS마다 조금씩 다른 형태로 제공되는데, 지금부터 이에 대해 살펴보자. SQL Server에서는 아래와 같은 확장 Update문을 활용함으로써 방금 설명한 비효율을 쉽게 제거할 수 있다. Oracle에서는 아래와 같이 수정 가능 조인 뷰(Updatable Join View)를 활용할 수 있다. 위 update 문에 사용된 bypass_ujvc 힌트에 대해서는 설명이 필요하다. ‘조인 뷰’는 from절에 두 개 이상 테이블을 가진 뷰를 말하며, 조인 뷰를 통해 원본 테이블에 입력, 수정, 삭제가 가능하다. 여기에 한가지 제약사항이 있는데, 키-보존 테이블에만 입력, 수정, 삭제가 허용된다는 사실이다. 키-보존 테이블(Key-Preserved Table)이란, 조인된 결과집합을 통해서도 중복 없이 Unique하게 식별이 가능한 테이블을 말한다. 이를 위해선 Unique한 집합과 조인되어야 하는데, 옵티마이저는 조인되는 테이블에 Unique 인덱스가 있는지를 보고 Unique 집합 여부를 판단한다. 결국, Unique 인덱스가 없는 테이블과 조인된 테이블에는 입력, 수정, 삭제가 허용되지 않는다. 방금 본 Update문이 제대로 수행되려면 고객 테이블이 키-보존 테이블이어야 한다. 그런데 거래 데이터를 집계한 인라인 뷰에 Unique 인덱스가 존재할 수 없으므로 Oracle은 고객 테이블을 키-보존 테이블로 인정하지 않는다. 고객번호로 group by한 집합의 고객번호에 중복 값이 있을 수 없다는 사실을 옵티마이저도 충분히 인지할 수 있는데도 말이다. 집합적으로 Unique성이 보장됨에도 불구하고 Unique 인덱스를 찾을 수 없다는 이유로 옵티마이저가 필요 이상의 제약을 가한 셈인데, 다행히 이를 피해갈 수 있는 bypass_ujvc 힌트가 제공된다. 참고로, 이 힌트는 ‘Bypass Updatable Join View Check’를 축약해 만든 것이다. 이 힌트는 Update를 위해 참조하는 집합에 중복 레코드가 없음이 100% 보장될 때만 사용할 것을 당부한다. 10g부터는 바로 이어서 설명할 Merge Into 구문을 활용하는 것이 바람직하다. merge into문을 이용하면 하나의 SQL 안에서 insert, update, delete 작업을 한번에 처리할 수 있다. 이 기능은 Oracle 9i부터 제공되기 시작했고, delete 작업까지 처리할 수 있게 된 것은 10g부터다. SQL Server도 2008 버전부터 이 문장을 지원하기 시작했다. merge into는 기간계 시스템으로부터 읽어온 신규 및 변경분 데이터를 DW 시스템에 반영하고자 할 때 사용하면 효과적이다. 아래는 merge문을 이용해 insert, update를 동시에 처리하는 예시다. Oracle 10g부터는 아래와 같이 update와 insert를 선택적으로 처리할 수 있다. 이 확장 기능을 통해 Updatable Join View 기능을 대체할 수 있게 되었다. 앞에서 bypass_ujvc 힌트를 사용했던 update 문장을 예로 들면, 아래와 같이 merge문으로 처리를 할 수 있게 되었다.DML 튜닝
1. 인덱스 유지 비용
2. Insert 튜닝
가. Oracle Insert 튜닝
나. SQL Server Insert 튜닝
3. Update 튜닝
가. Truncate & Insert 방식 사용
나. 조인을 내포한 Update 튜닝