최적의 베이스캠프, 오라클의 대용량 데이터 관리

김용한
한국오라클 Tech Sales Consulting본부 응용기술팀

기업 경쟁력의 가늠자인 비즈니스 인텔리전스에서 데이터웨어하우스가 차지하는 비중이 적지 않다. 대용량 데이터의 효율적인 관리 없이는 어떤 인텔리전스도 기대할 수 없는 법. 데이터웨어하우스의 현재 활용 상태를 짚어보면서 대용량 데이터를 효과적으로 관리할 수 있는 파티션 테이블과 압축 기법에 대해 상세히 소개한다. 오라클은 대용량 데이터 처리 및 관리에 있어 확실하고 다양한 솔루션을 제공한다.

현시점의 데이터웨어하우스

데이터웨어하우스는 오늘날 경쟁우위를 가늠하는 척도가 되는 비즈니스 인텔리전스 분야에서 기업들이 경쟁할 수 있게 하는 중요한 도구로써 그 능력을 지속적으로 입증하고 있다. 데이터를 얼마나 잘 수집, 관리, 활용하느냐라는 문제는 기업에게 더 이상 선택이 아닌 살아 남기 위한 전략이 되었다.
침체된 경기에도 불구하고 데이터웨어하우징과 비즈니스 인텔리전스는 IT 관련 지출 중 여전히 가장 큰 부분을 차지하고 있다. 이는 한 기업이 가지고 있는 정보가 직접적으로든 간접적으로든 그 회사의 수입 및 지출에 영향을 끼치며, 그 정보를 가능하게 해주는 매체로 데이터웨어하우징을 인식하고 있기 때문이다. 이와 더불어 데이터웨어하우징에 대한 ROI(투자효과)가 요구되며, 그 ROI를 확인하고자 대기하는 시간도 매우 짧아졌다. 데이터웨어하우스 프로젝트에 집중해야 하지만, 그렇다고 이 프로젝트가 다른 분야에 투입되어야 할 인력과 자원을 독점해서도 안 된다. 오늘날 데이 타웨어하우징의 현실을 간단히 정리하면, 다음과 같다.

● 여러 개의 복잡한 애플리케이션이 데이터웨어하우스 상에서 운영되며, 다 양한 사용자들이 이를 활용한다.
● 데이터 용량이 폭발적으로 증가하고 있으며, 앞으로도 같은 형태로 증가할 것이다.
● 실시간 데이터가 필요함에 따라 데이터 수급의 지연은 용납할 수 없는 장애 가될것이다.

애플리케이션

데이터웨어하우징은 기업의 수익에 장기적이든 단기적이든 상당한 영향을 끼치기 때문에 매우 중요하다. 오늘날 ROI를 창출해내는 애플리케이션은 대표적으로 고객 관련 애플리케이션(CRM)으로, 이 애플리케이션들은 제한된 마케팅 예산으로 현명하게 대고객 마케팅 활동을 펼칠 수 있음을 보장함으로써 현재 가치가 높거나 앞으로의 가치가 높다고 판단되는 고객을 유치하고 보유할 수 있도록 해준다.
데이터는 사기 탐지, 마케팅 자동화, 인력 관리, 고객 분석 및 관리, 제조 기획 및 분석, 공급망과 고객서비스센터 관련 활동에 사용되며, 한 기업의 실적과 미래의 방향을 나타내주는 주요 성과 지수를 측정하기 위해 다양한 방법으로 분석된다. 고객, 제품, 매출 등 주요 영역에 대한 중앙 데이터는 여러 애플리케이션에서 활용될 것이므로 시스템 상에서 동시사용자를 얼마나 수용할 수 있느냐는 중요한 문제이다.
요약하면, 오늘날 데이터웨어하우스는 특정 용도나 특정 사용자만 이용하는 것이 아니라 기업 전체에서 포괄적으로 활용되고 있다. 데이터웨어하우스에서 나오는 정보는 기업의 거의 모든 전략적/전술적 목표를 달성하기 위한 밑바탕이 되고 있다.

데이터 용량

데이터웨어하우스는 여러 가지 이유로 그 용량이 급격히 커지고 있는데, 가장 큰 이유는 초기 데이터의 활용으로 수익이 증대되고 플랫폼도 그 워크로드를 감당할 수 있게 되면, 데이터 활용 용도가 새롭게 생겨나고, 이에따라 데이터웨어하우스의 요구사항도 점차 늘어나기 때문이다. 효과적인 의사결정을 위해 요약 데이터와 함께 세부 데이터를 활용하는 것이 중요한데, 이 역시 데이터 과부하의 원인이 된다.
오래된 데이터를 삭제하거나 액세스를 제한하기란 거의 불가능하다.
데이터가 계속적으로 데이터웨어하우스에 축적될 것을 미리 예상하고 계획해야 한다. 그리고 우선 과거 내역의 데이터를 웨어하우스에 기본적으로 적재해야함은 말할 것도 없고, 기업들은 자신들의 범주 밖에서 나오는 데이터, 즉 외부 데이터 역시 유용하다는 사실을 깨닫기 시작했다. 내부적으로 생성되는 데이터를 보강하기 위해 외부 데이터를 "구독"하는 것은 이제 더 이상 힘든 일도, 새로운 일도 아니다. 특히 마케팅 부서는 모든 종류의 데이터를 다루는 기술이 상당히 정교해졌으며, 데이터가 많으면 많을수록 더 유리하다.

실시간 지원

많으면 많을수록, 빠르면 빠를수록 좋다. 이러한 덕목을 의미하는 "실시간"이라는 단어는 오늘날 데이터 웨어하우징의 새로운 유행이 되고 있다. 야간 로딩도 좋지만, 하루에 몇 차례씩 로딩할 수 있다면 더욱 좋을 것이다.
무엇보다 ‘실시간’ 로딩이 가장 좋다. 데이터웨어하우스는 오늘날 운영 시스템이 제공해주지 못하는 다양한 ‘운영’ 기능을 흡수하고 있다. 현대적이고 지원이 용이한 데이터웨어하우스로 문제를 해결하는 것이 운영 시스템을 바꾸는 것보다 훨씬 쉬울 경우가 많다. 하지만 그렇다고 해서 추가된 성능으로 인해 시스템의 운영이 방해 받는 것은 아니다.
이제 사용자들은 요약된 과거 데이터도 필요로 하지만, 실시간 데이터도 필요로 하고 있다. 비즈니스 이벤트가 일어남과 동시에 비즈니스 유저들에게 중요한 정보를 제공하는 것이 중요하다. 이를 통해 비즈니스 유저들은 비즈니스 실적을 향상시키는 데 더욱 주력할 수 있기 때문이다. 트랜잭션 시스템이 생성하는 실시간 데이터와 데이터웨어하우스에 저장되어있는 과거 시간별 정보를 혼합하여 전달함으로써 막대한 가치를 창출할 수 있는 정보를 제공해주어야 한다.
데이터웨어하우스를 위한 추출, 변환 및 로딩 시스템을 구축할 때, 실시간이 아닌 시스템의 경우, 데이터웨어하우스는 실시간으로 데이터를 통합할 수 있도록 설계되어야 한다.
만약 이러한 실시간 서비스를 제공하는 데이터웨어하우스가 한번에 구축되기 힘들 경우, 단계별 실시간 서비스를 구현하고 통합하는 방향으로설계해야 한다.

최근 데이터웨어하우스 구축 추세

최근 들어 기업에서는 데이터웨어하우스 리모델링, 데이터 통합, 시스템 고도화, 데이터 정비라는 명목 하에 여러 프로젝트들이 진행되고 있다. 물론 신규 프로젝트로서의EDW도 있지만, 2000년을 전후하여 DW, DBM, CRM의 타이틀을 가지고 활발하게 진행되었던 여러 시스템들의 보완 및 진화가 필요하기 때문일 것이다. 왜냐하면 데이터웨어하우스를 구축했다고 해서, 그것만으로 기업의 데이터 활용 방식이 크게 개선되는 것이 아니라, 오히려 데이터를 인식하고 새로운 체계를 만드는 첫 단계로 보아야 하 기 때문이다.
또한 언제부턴가 데이터웨어하우스는 테라바이트 규모에 속속 이르고 있다. 이러한 데이터의 증가로 인해 대용량 데이터의 관리와 더불어, 시스템의 고가용성은 매우 중요한 요소가 되었으며, 더구나 실시간 혹은 액티브 분석이 필요한 현재의 환경에서는 그 중요성이 더욱 크다고 할 수 있다.
비즈니스 측면에서는 기존의 구축경험을 바탕으로 신속한 개발과 전개가 요구되고 있으며, 다양한 장비에서 웹을 통한 데이터의 분석과, 비즈니스 애널리스트의 증가로 인한 분석 데이터의 증가, 그리고 전사 관점에서의 싱글 뷰를 구현하기 위한 통합 관점에서의 리모델링이 요구되고 있다.
최근 데이터웨어하우스 구축추세를 정리해 보면 다음과 같다.

● 전사적 데이터의 통합
- 이미 구축된 주제별 데이터웨어하우스의진화
- 통합 관점에서의 리모델링
- 데이터의 표준화와 품질 고려

● CRM고려
- 고객 관점 싱글 뷰 구현
- 채널 및 고객접촉이력의 통합

● KPI(주요성과지표)와연계
- 사용자 요건을 비즈니스 목표를 수행하기 위한 지표 관점에서 정의

● Near Real Time
- 거의 실시간으로 데이터를 통합

이러한 구축요건을 충족하기 위해서는 체계적인 방이 필요하지만, 기업 의사결정의 원천이 되는 것은 데이터이므로, 대용량 데이터의 처리 및 관리 방안에 대한 복안을 가지고 있어야 한다.
오라클은 대용량 데이터 처리 및 관리에 있어 확실하고 다양한 솔루션을 제공하고 있으며, 입증된 기술 및 노하우를 바탕으로 대용량 시스템 구축을 가능하게 한다.
그러면, 대용량 테이블 관리를 위한 파티션 테이블(partitioned table)의 활용방안과, 저장공간의 효율적인 활용을 위한 압축(compress)에대해 상세히 살펴보도록 하자.

파티션 테이블의 활용

테이블 파티션 방법에는 여러 가지가 있다. 크게 Range, Hash, List 방식을 각각 독립적으로 사용할 수 있으며, 이들의 조합에 의한 방식인 Composite 방식(Range + Hash, Range + List)도 가능하다.
이러한 방식들은 테이블의 업무적 특성과, 관리방안, 그리고 수행속도를 고려하여 선정하여야 하며, 분할되는 파티션의 크기와 개수 또한 정렬 작업 또는 그루핑작업, 조인작업의 수행속도와 밀접한 관계가 있으므로, 시스템 구성(CPU 개수, 활용 메모리 크기 등)과 병렬처리를 고려한 설계가 필요하다.

파티션 방식의 선정

기본적으로, 업무 테이블의 유형은 마스터 테이블과 트랜잭션 성격의 테이블, 그리고 코드 정보를 포함하는 테이블로 나눌 수 있다.
마스터 테이블은 대부분 기간 개념이 없는 성격의 테이블로서, 고객/회원/원장 등을 예로 들 수 있으며, 트랜잭션 테이블은 기간 개념이 포함된 거래관련 테이블이 해당된다. 중요한 것은, 마스터 테이블과 트랜잭션 테이블은 항상 연관된 관계를 가지고, 조인 오퍼레이션을 수반하게되므로, 물리적으로 연관되는 기준(예를 들어, 파티션 방식, 파티션 키, 파티션 개수 등)을 동일하게 또는 일관성 있게 부여함으로써, 질의 작업의 효율성을 높일 수 있다는 것이다.

● 마스터 테이블
Hash 또는 List 파티션 방식을 사용한다.
Hash 파티션 사용 경우나, 업무적으로 특별한 구분 항목이 없는 경우에, 특정 레인지 파티션을 균등하게 분할하기 위해 사용하며, 균등한 분할을 위한 파티션 키(Hash Key)의 선정과, 시스템 현황에 따른 파티션 크기 및 개수 선정이 중요하다.
List 파티션 사용 경우, 특정 항목에 의해 업무적으로 명확히 구분되는 성격 의 테이블로서, 지역/부서 항목을 포함한 경우가 대표적이라할수있다.

● 트랜잭션 테이블
기간 개념의 테이블로서 Range 또는 R+H, R+L 방식을 사용한다.

● 고려사항
주의할 점은 마스터 테이블과 트랜잭션 테이블은 항상 연관 관계가 있으므로, 일관성 있는 파티션방식이 선택되어야 된다. 즉, 마스터테이블이Hash 방식이면, 트랜잭션 테이블도R+H 방식으로 구성해야 하며, List방식이면 R+L방식으로 구성한다.

업무영역에 따른 파티션 구성

테이블 파티션은 대용량 테이블의 관리의 용이성, 가용성, 수행속도측면을 고려하여 구성된다. 그러나 모든 테이블을 잘게 분할하는 것만이 해결책은 아니며, 상황에 따라 성격에 맞는 파티션 방안을 적용한다.
데이터웨어하우스 시스템의 경우, 크게 Staging 영역, Operational Data Store 영역, Data Warehouse 영역, Data Mart 영역으로 구분되며, 그에 따른 데이터 역시 사용용도가 틀려진다.
Staging/ODS 영역의 경우는, Data Warehouse, Data Mart 부분에 데이터를 공급하는 것이 목적이고, Data Warehouse, Data Mart 영역은 정보의 분석이 목적이므로 테이블의 사용용도가 Staging/ODS와 틀리며, 따라서 파티션 방식도 구분하여 적용한다.

● Staging 및ODS 영역
원시 데이터가 집적되는 곳이므로, 데이터 로딩을 고려하고, 테이블간의 조인보다는 관리 차원의 파티션 방안을 고려한다. 따라서, 기간별 관리측면의 Range 방식을 주로 사용하며, 초기 데이터가 대용량이라면, Hash 방식을 사용하여 분할한다. 동시작업보다는 단계별 벌크 작업이 예상되므로, 단위 작업시 작업 세션에 충분한 사용자 메모리(sort area, hash join area)를 할당하여 작업한다.

● Data Warehouse 및 Data Mart 영역
데이터의 가공,변환,생성에 따른 대용량 배치작업이 예상되며, 데이터 정렬 및 그루핑 작업에서 사용자 메모리 부족으로 인한 물리적 I/O 발생을 최소화하기 위한 방안이 필요하다. 따라서, 다양한 질의 및 대량의 정렬작업을 메모리에서 수용할 수 있는 파티션 방안이 필요하며, 병렬성을 고려한 파티션 크기 및 개수의 선정을 고려한다.
새로운 월의 추가, 또는 과거정보의 정리를 위한 파티션을 구성한다.

마스터 테이블은 Hash 파티션이 예상되며, 이와 관련된 트랜잭션 테이블을 Range + Hash 방식으로 구성하며, 마스터 테이블의 Hash 파티션의 개수와 상응하는 방식으로 트랜잭션 테이블을 구성한다.

파티션 개수 선정

기간 개념의 Range 파티션 경우, 사실적인 기간 정보에 의해 파티션이 결정되지만, Hash 파티션 경우는 설계자가 서브파티션의 개수를 정해야 한다. 이 서브파티션의 개수는 관리 및 수행속도와 관계가 있으며, 특히 대량의 정렬(sort) 작업과 밀접한 관계가 있다.

파티션 테이블의 정렬

만일 대용량 테이블(예를 들어 수십~수백 기가바이트)이 파티션으로 구성되어 있지 않다고 가정하면, 메모리 정렬 작업을 하기 위해서는 그 크기에 상응하는 메모리가 필요할 것이다. 따라서 이러한 상황에서는 일정량의 데이터만 메모리 처리가 되고 그 이후는 템포러리 세그먼트(temporary segment)를 생성하여 정렬 작업을 하게 되므로 당연히 I/O에 의한 부하가 발생하게 된다.
그러나, 파티션 테이블이라면 상황이 크게 달라진다. 예를 들어, Large 테이블의 크기가 60GB이고 월별로 파티션되어 있고, 1년치의 데이터가 존재한다면, 단순계산을 하면 월별 파티션의 크기는 대략 5GB로 산정될 것이다. 월별로 파티션되어 있다는 의미는 테이블 전체를 생각할 때 월별로 파티션이 순차적으로 정렬되어 있음을 알 수 있다.
따라서, 이 테이블에 대한 정렬 작업은 각 단위 월 데이터에 대한 정렬만 이루어진다면, 테이블 전체에 대한 정렬은 이미 파티션 키에 의해 데이터 부분이 구분되어 있으므로 필요없게 된다.
위의 두 가지 상황을 비교해 보면,
● 파티션되지 않은 테이블은 데이터의 정렬을 위해 총 60GB의 데이터를 한번에 해결해야 하며
● 파티션 테이블은 데이터 정렬을 위해 5GB의 월 단위 데이터를 12번 수행하면 된다
는 결론이 나온다.
이 두 가지 경우 서로 하는 일은 비슷하지만, 가장 큰 차이는 첫 번째 경우는 한번에 데이터를 처리하고 있지만, 두 번째 경우는 대용량 작업을 여러 개의 파티션 단위로 처리함을 알 수 있다.
파티션 테이블인 경우를 조금 더 생각해보면, 파티션의 단위를 늘림으로써 단위 작업 수는 늘어나지만, 한번에 처리하게 되는 데이터의 양은 상대적으로 줄어든다는 결론이 나온다. 즉, 대용량 테이블도 디스크 정렬 없이 또는 최소화하여 수행할 수 있다는 것이다. 순수하게 메모리만을 사용하게 된다면 작업횟수와는 관계 없이 물리적인 I/O를 일으키는 디스크 정렬과 비교해볼 때 파티션 테이블이 수행속도 측면에서나 디스크 스토리지 절약에서 엄청난 효과가 있다고 할 수 있다. 따라서, 업무적 특성에 따른 파티션 기준을 고려하되, 대량의 작업을 메모리에서 소화할 수 있는 파티션 단위의 크기를 산정한 후, 서브파티션 개수를 결정하는 것이 좋다.
그리고, 이렇게 결정된 파티션 개수는 마스터 테이블, 트랜잭션 테이블과 동일하게 또는 일관성(배수형태) 있게 적용한다.

템포러리 영역의 사용

메모리를 무한정 지정할 수는 없으므로, 정렬 작업시 템포러리 세그먼트를 사용하게 된다. 즉, 다시 말하면, 가장 큰 테이블을 기준으로 메모리 및 템포러리 영역을 설계하면 좋지만, 데이터의 볼륨을 고려한다면 현실적이지 못하므로, 이러한 설계의 관점은 어떻게 하면 대용량 작업을 메모리 정렬로 유도할 수 있는지와 템포러리 영역을 무한정 크게 생성하지 않고 활용할 수 있는지를 고민해야 한다.
템포러리 영역의 크기 산정은 앞에서 설명한 바와 같이 테이블 파티션을 어떻게 하는가에 의해 결정할 수 있다. 따라서 업무성격을 감안한 테이블 파티션 키의 선정과, 경우에 따라서는 대용량 테이블의 분산을 위한 파티션 키의 선정으로 대용량 테이블에 대한 템포러리 영역사용(크기산정)을 설계할 수 있다.
또한 템포러리 영역은 데이터 영역과 I/O가 같이 발생하므로 물리적인 디스크 배치를 고려한 설계도 수행속도를 판가름하는 중요한 요소이다.

Rolling Window Operation

<그림 1>은 파티션 테이블을 활용한 Rolling Window Operation의예제이다. SALES_FACT라는 팩트 테이블이 있고, 5월 데이터의 신규월 추가작업이 진행된다고 할 때, 직접 팩트 테이블에 작업을 하는 것이 아니라, TEMP_SALES라는 임시 테이블에 신규월 작업을 우선하여 진행한다. 그 후 작업이 완료되면 Partition Exchange 기능을 활용하여 실제 팩트 테이블인 SALES_FACT의 5월 파티션에 완료된 TEMP_SALES 데이터를 교환 하는 것으로 신규월 작업을 완료하게 된다. 이렇게 하면 SALES_FACT 테이블은 월 작업을 고려한 다운타임 없이 무정지 상태로 팩트 테이블을 운영할 수 있다.



또한 과거자료 정리시 반대의 방법으로 Partition Exchange 기능을 사용하면, 기존의 SALES_FACT에 있던 데이터를 템포러리 테이블로 손쉽게 교환할 수 있다. 이러한 방법으로 과거자료의 정리 또는 백업을 계획된 다운타임 없이 진행할 수 있으며, 경우에 따라 마감된 읽기전용 파티션을 압축할 수 있다.

속도 향상과 공간 절약을 위한 데이터 압축

대부분 일반적 의사결정 시스템에서 몇몇의 대용량 테이블에는 거대한 데이터가 존재하게 되며, 이러한 디스크 공간의 요구는 매우 빠르게 증가되고 있다. 따라서 데이터웨어하우스의 크기는 얼마 안되어 100테라바이트까지 넘보게 될 것이다.
이러한 디스크 용량 문제를 해결하기 위해, 오라클에서는 Oracle9i Database Release 2 버전에서 테이블 압축 기법을 소개하였으며, 테이블 내 중복된 데이터의 값(value)을 제거함으로써 데이터 압축을 실행한다.
따라서 디스크 스토리지 활용 측면과 질의 작업시 읽기 블록(read block)을 줄임으로써 수행속도 향상을 기대할 수 있게 되었다.
그러면, 테이블 압축의 구현과 적용 기법을 예제를 통하여 살펴보고, 테이블 압축을 통해 얻을 수 있는 효과에 대해 알아보자.

압축 작동 원리

<그림 2>는 압축 블록과 비압축 블록에서의 데이터 저장 방식을 나타낸 것이다. 압축 작업은 데이터베이스 블록 레벨에서 구현되며, 테이블이 Compress로 정의되면, 데이터베이스는 각 블록에 여러 중복된 값들에 대한 싱글 카피를 만들 공간을 확보하게 되는데, 이를 "심볼 테이블(symbol table)"이라고 부른다.
이 심볼 테이블에는 중복된 값들이 들어 있으며, 이렇게 정의된 값들은 그 값 자신이 아닌, 심볼 테이블과 연결된 포인터를 로우에 가지고 있게 됨으로써 압축을 구현하게 된다.
테이블 압축은 사용자나 애플리케이션에 전혀 영향을 미치지 않으며, 따라서 개발자는 이전과 똑같은 방법으로 테이블에 액세스할 수 있으며(그 테이블이 압축된 것이든 압축되지 않은 것이든 상관없이), SQL 질의 문장도 전혀 변경사항이 없다. 테이블 압축의 정의는 DBA 또는 아키텍트가 주로 정의 및 관리해야 한다.

압축 테이블 생성 방법

압축 테이블을 생성하기 위해서는 CREATE TABLE 문장에 COMPRESS 키워드를 사용하며, COMPRESS 키워드는 언제라도 테이블에 압축 포맷이 가능하게 해준다.
다음은 CREATE TABLE COMPRESS 문장이다.

CREATE TABLE SALES_HISTORY_COMP (
PART_ID VARCHAR2(50) NOT NULL,
STORE_ID VARCHAR2(50) NOT NULL,
SALE_DATE DATE NOT NULL,
QUANTITY NUMBER(10,2) NOT NULL
)
COMPRESS
;

다른 방법으로는, ALTER TABLE 문장을 사용하여 이미 존재하는 테이블에 대한 압축 속성을 정의할 수 있다.

ALTER TABLE SALES_HISTORY_COMP COMPRESS;

테이블이 COMPRESS로 정의되면, USER_TABLES라는 데이터 딕셔너리를 통하여 확인할 수 있다.

SELECT TABLE_NAME, COMPRESSION FROM USER_TABLES;
TABLE_NAME COMPRESSION
---------------------- ---------------------
SALES_HISTORY DISABLED
SALES_HISTORY_COMP ENABLED

또한 COMPRESS 속성은 테이블스페이스 레벨에서도 정의할 수 있으며, 테이블스페이스 생성시(CREATE TABLESPACE) 또는 그 이후 (ALTER TABLESPACE)를 활용하여 정의할 수 있다.
COMPRESS 속성은 스토리지 파라미터의 상속을 받게 된다. 즉 테이블은 테이블스페이스에 생성되기 때문에, 테이블스페이스가 COMPRESS 속성을 가지고 있다면, 당연히 그 속성을 상속 받게 된다. 물론, 테이블스페이스의 COMPRESS 속성에 상관없이, 원하는 테이블은 Compress 또는 UNCompress가 가능하다.
테이블스페이스가 COMPRESS인지를 확인하려면, DBA_TABLESPACES 데이터 딕셔너리의 DEF_TAB_COMPRESSION 칼럼을 통해 알아볼 수 있다.

SELECT TABLESPACE_NAME,
DEF_TAB_COMPRESSION
FROM DBA_TABLESPACES;
TABLESPACE_NAME DEF_TAB_COMPRESSION
---------------------------- ----------------------------------
DATA_TS_01 DISABLED
INDEX_TS_01 DISABLED

압축 테이블로의 데이터 로딩

앞에서와 같은 COMPRESS 속성의 정의는 단지 데이터 딕셔너리 세팅이며, 테이블에 데이터를 로드, 삽입, 이전하기 전까지는 데이터 압축은 진행 되지 않는다.
따라서 실제로 테이블 압축을 적용하기 위해서는, 다음과 같은 벌크 로드 또는 벌크 삽입 오퍼레이션을 사용하여 압축을 진행한다.

● Direct Path SQL*Loader
● Serial INSERT with an APPEND hint
● Parallel INSERT
● CREATE TABLE ... AS SELECT

Direct Path SQL*Loader 방법은 플랫파일을 테이블에 로드하는 아주 편리한 유틸리티이며, 대상 테이블이 COMPRESS 속성인 경우, 데이터 로딩과 동시에 압축이 가능하다.

$sqlldr scott/tiger@mydb control=sales_history.ctl direct=true

만일 입력 데이터가 테이블인 경우, INSERT with an APPEND hint나 Parallel INSERT 방법을 사용하여 압축할 수 있다.
예를 들어, 압축되지 않은 스테이징 테이블이SALES_HISTORY 라면, Serial INSERT 방법을 사용하여 다음과 같이 SALES_HISTORY_COMP 테이블에 압축된 데이터를 저장할 수 있다.

INSERT /*+ APPEND */
INTO SALES_HISTORY_COMP
SELECT * FROM SALES_HISTORY;

또 다른 방법으로, Parallel INSERT를 사용하여 압축할 수 있는데, 이때 ALTER SESSION ENABLE PARALLEL DML문을 사용하여 Parallel DML을 사용할 수 있게 세션을 변경해 주어야 한다.

ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+PARALLEL(SALES_HISTORY_COMP,4)*/
INTO SALES_HISTORY_COMP
SELECT * FROM SALES_HISTORY;

또한 CREATE TABLE ... AS SELECT 문장을 사용하여 다음과 같이 테이블의 생성과 동시에 압축할 수도 있다.

CREATE TABLE SALES_HISTORY_COMP
COMPRESS
AS SELECT * FROM SALES_HISTORY;

주의할 점은, 테이블이 COMPRESS 속성으로 정의되어 있다 하더라도, 위에서 사용한 정확한 로딩 또는 삽입 방법을 사용하여야 압축할 수 있다는 것이다. 만일 Conventional Path SQL*Loader 또는 Regular INSERT 문장을 사용한다면 데이터 압축은 수행되지 않는다.

압축이 적합한/부적합한 경우

Online Transaction Processing(OLTP) 시스템에서는 일반적으로 일반 삽입(insert)을 사용하는데, 결론적으로 이러한 테이블에서는 압축의 이점을 찾을 수 없다. 테이블 압축은 한번 로드된 후 여러 번 읽혀지는 읽기전용 성격의 테이블에 유용하며, 따라서OLTP 시스템보다는 데이터웨어하우스 또는 데이터 백업관련 시스템에 적합하다고 할 수 있다.
압축이 부적합한 경우를 예로 들면, 압축 테이블에서 데이터 업데이트는 압축되지 않은 로우가 발생할 수 있으므로, 압축의 목적에 부합되지 않는다. 따라서 업데이트가 빈번한 테이블이라면, 압축 대상이 아니라고 할 수 있다.
압축 테이블에서 로우의 삭제를 생각해 볼 때, 데이터베이스는 삭제가 수행되면 그만큼의 빈 공간을 확보하게 된다. 이러한 공간은 차후의 삽입 수행에 재사용되게 되는데, 만일 Conventional Mode로 삽입되었다면 압축이 안 되었을 것이고, 원하지 않는 상황으로 공간사용이 될 가능성이 커진다.
따라서, 많은 데이터의 삭제와 삽입이 연속적으로 이루어지면, 단편화 현상이 생겨 그만큼 공간을 활용하지 못하므로, 압축으로 절약된 공간보다 많은 손실을 볼 수도 있다. 따라서 이러한 테이블도 압축 대상으로 부적합하다.

기존 비압축 테이블의 압축

압축되지 않은 테이블인 경우, ALTER TABLE ... MOVE 문장을 사용하여 압축할 수 있다. 비압축 테이블이 SALES_HISTORY_TEMP라면 다음과 같이 사용한다.

ALTER TABLE SALES_HISTORY_TEMP
MOVE COMPRESS;

마찬가지로, ALTER TABLE ... MOVE NOCOMPRESS 문장을 사용하여 압축할 수도 있다.

ALTER TABLE SALES_HISTORY_TEMP
MOVE NOCOMPRESS;

Materialized View의압축

압축 테이블과 비슷한 방법으로 Materialized View도 압축할 수 있다.

CREATE MATERIALIZED VIEW MV_SALES_COMP
COMPRESS
AS SELECT P.PART_NAME, H.STORE_ID, H.SALE_DATE, H.QUANTITY
FROM SALES_HISTORY H, PARTS P
WHERE P.PART_ID = H.PART_ID;

Materialized View는 여러 테이블의 조인을 기본으로 하고, 반복된 값을 많이 가지므로 압축의 대상이 될 수 있다. ALTER MATERIALIZED VIEW 문장을 통하여 Compress 속성을 정의한다.

ALTER MATERIALIZED VIEW MV_SALES COMPRESS;

위의 문장을 사용하면, Materialized View의 다음 번 리프레시 작업부터 압축이 적용된다.

파티션 테이블의 압축

파티션 테이블에서는 다양한 압축 방법을 사용할 수 있다. 즉 테이블 레벨 또는 파티션 레벨에서 압축을 정의할 수 있다. 예를 들어, <리스트 1>과 같이 하면, COMPRESS 속성이 테이블 레벨에서 정의되므로, 4개 모든 파티션이 압축된다.

<리스트 1> 모든 파티션의 압축
CREATE TABLE SALES_PART_COMP (
PART_ID VARCHAR2(50) NOT NULL,
STORE_ID VARCHAR2(50) NOT NULL,
SALE_DATE DATE NOT NULL,
QUANTITY NUMBER(10,2) NOT NULL
)
COMPRESS
PARTITION BY RANGE (SALE_DATE) (
PARTITION SALES_Q1_03 VALUES LESS THAN (TO_DATE("01-APR-2003","DD-MON-YYYY")),
PARTITION SALES_Q2_03 VALUES LESS THAN (TO_DATE("01-JUN-2003","DD-MON-YYYY")),
PARTITION SALES_Q3_03 VALUES LESS THAN (TO_DATE("01-OCT-2003","DD-MON-YYYY")),
PARTITION SALES_Q4_03 VALUES LESS THAN (TO_DATE("01-JAN-2004","DD-MON-YYYY"))
) ;

다른 방법으로 파티션 레벨에서 Compress 속성을 정의할 수 있다.
<리스트 2>와 같이 하면 특정 파티션은 압축하고 나머지 파티션은 비압축 상태를 유지할 수 있다.

<리스트 2> 특정 파티션만 압축
CREATE TABLE SALES_PART_COMP (
PART_ID VARCHAR2(50) NOT NULL,
STORE_ID VARCHAR2(50) NOT NULL,
SALE_DATE DATE NOT NULL,
QUANTITY NUMBER(10,2) NOT NULL
)
PARTITION BY RANGE (SALE_DATE) (
PARTITION SALES_Q1_03
VALUES LESS THAN (TO_DATE("01-APR-2003","DD-MON-YYYY")) COMPRESS,
PARTITION SALES_Q2_03
VALUES LESS THAN (TO_DATE("01-JUN-2003","DD-MON-YYYY")) COMPRESS,
PARTITION SALES_Q3_03
VALUES LESS THAN (TO_DATE("01-OCT-2003","DD-MON-YYYY")),
PARTITION SALES_Q4_03
VALUES LESS THAN (TO_DATE("01-JAN-2004","DD-MON-YYYY"))
) ;

이 경우 두개의 파티션(SALES_ Q1_03과 SALES_Q2_03)은 압축되고, 나머지는 압축되지 않는다. 이렇게 볼 때 Compress 속성은 테이블 레벨보다 파티션 레벨이 우선한다는 것을 알 수 있다. 예제와 마찬가지로 만일 파티션에 Compress 속성이 기술되지 않았다면, 그 파티션은 테이블 레벨의 Compress 속성을 상속받았기 때문에, SALES_Q3_03과 SALES_Q4_03 두 개의 파티션은 디폴트 값인NOCOMPRESS 속성을 갖게 된다.
파티션 테이블은 압축 기능을 사용함으로써 유용한 장점을 갖게 되는데, 파티션별로 DML 오퍼레이션을 진행할 수 있다는 점이다.
예를 들어, 위의 예제에서 Sales 데이터는 SALES_DATE로 파티션되어 있으며, Sales History 가 4개의 분기로 파티션되어 분리되어 있다.
여기서, Sales 데이터의 Q1과 Q2는 마감이 되었다고 한다면, 더 이상의 변경이 없으므로 SALES_Q1_03과 SALES_Q2_03 파티션은 압축을 적용하였고, Q3와Q4는 아직 마감 전이므로 비압축 상태로 정의되었다.
만일, Q3가 마감 시점이 된다면 읽기전용이 될 것이므로 ALTER TABLE ... MOVE PARTITION 명령을 사용하여 압축하면 된다.

ALTER TABLE SALES_PART_COMP
MOVE PARTITION SALES_Q3_03 COMPRESS;

파티션의 압축 정보를 확인하려면, USER_TAB_PARTITIONS 딕셔너리 뷰를 사용하여 확인할 수 있다.

SELECT TABLE_NAME, PARTITION_NAME,
COMPRESSION
FROM USER_TAB_PARTITIONS;

TABLE_NAME PARTITION_NAME COMPRESSION
-------------------------- ------------------------ --------------------
SALES_PART_COMP SALES_Q1_03 ENABLED
SALES_PART_COMP SALES_Q2_03 ENABLED
SALES_PART_COMP SALES_Q3_03 ENABLED
SALES_PART_COMP SALES_Q4_03 DISABLED

테이블 압축의 장점

테이블 압축의 장점으로는 스토리지 절약을 가장 먼저 들 수 있다. 이러한 공간활용과 더불어 질의 수행속도의 향상도 기대할 수 있다. 다음은 두 개의 테이블 비압축 테이블(SALES_HISTORY)과 압축 테이블(SALES_HISTORY_COMP)을 비교한 예제인데, 압축 테이블인 경우, 비압축 테이블보다 적은수의 블록수를 가지고 있는것을 볼 수 있다. 이것은 I/O에 밀접한 관계가 있는 질의에서 수행속도 향상을 기대할 수 있으며, SQLTRACE/TKPROF 리포트의 결과에서 볼 수 있듯이 물리적 또는 논리적 I/O가 적은 쪽이 좋은 성능을 보인다는 것을 확인할 수 있다.

ANALYZE TABLE SALES_HISTORY COMPUTE STATISTICS;
ANALYZE TABLE SALES_HISTORY_COMP COMPUTE STATISTICS;
SELECT TABLE_NAME, BLOCKS, NUM_ROWS, COMPRESSION
FROM USER_TABLES
WHERE TABLE_NAME LIKE "SALES_HIST%";

TABLE_NAME BLOCKS NUM_ROWS COMPRESSION
----------------------- ----------- ------------------ --------------------
SALES_HISTORY 12137 1000000 DISABLED
SALES_HISTORY_COMP 6188 1000000 ENABLED

TKPROF results of the query on the uncompressed table:

SELECT SALE_DATE, COUNT(*) FROM SALES_HISTORY GROUP BY SALE_DATE;

call count cpu elapsed disk query current rows
------- -------- ----- ----------- ------ ------- --------- -------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 5.22 13.76 10560 12148 0 1
------- -------- ----- ----------- ------ ------- --------- -------
total 4 5.22 13.78 10560 12148 0 1

TKPROF results of the query on the compressed table:

SELECT SALE_DATE, COUNT(*) FROM SALES_HISTORY_COMP GROUP BY SALE_DATE;

call count cpu elapsed disk query current rows
------- -------- ----- ----------- ------ ------- --------- -------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 5.27 7.20 6082 6091 0 1
------- -------- ----- ----------- ------ ------- --------- -------
total 4 5.27 7.20 6082 6091 0 1

대용량 데이터 처리의 큰 일꾼

지금까지 오라클 파티션 테이블의 활용방안 및 테이블 압축에 대한 개념 및 예제를 살펴보았다. 이제 데이터는 더 이상 보고서를 채우는 문자가 아니며 데이터웨어하우스는 업무처리 시간을 단축하고 필요한 결과를 도출하는 매우 유용한 전략적인 시스템으로서의 역할을 해야 한다. 그러기 위해 오라클 파티션 테이블 기능과 테이블 압축 기능은 대용량 데이터 처리를 위한 최적의 솔루션을 제공할 것이다.

제공 : DB포탈사이트 DBguide.net