DB2 웨어하우스의 테이블 파티셔닝 기능 해부(1)

DB2 웨어하우스의 테이블 파티셔닝 기능 해부(1) * 출처: http://www.kdug.kr 쿼리 | 2010-08-10 13:29:50

요약 IBM® DB2® for Linux®, UNIX® and Windows® V9.7에서는 파티션 테이블에서의 데이터와 REORG 색인 지원, 파티션 분할된 색인(로컬 색인), XML과의 통합 및 DETACH를 사용한 온라인 롤아웃과 같은 몇 가지 주요 테이블 파티셔닝 기능이 개선되었습니다. 이 기사에서는 데이터 웨어하우스 환경에서 이러한 애플리케이션의 기능을 살펴보고 파티션 분할된 색인을 활용하여 활성 로그 공간을 줄이면서 ATTACH를 사용하는 데이터 롤인을 어떻게 신속하게 크기순으로 정렬할 수 있는지 확인합니다. 또한, 이 기사에는 테이블 파티셔닝과 관련된 몇 가지 일반적인 질문사항을 다룬 섹션이 포함되어 있습니다. 목차 소개 로컬 색인 로컬 색인을 사용한 롤인 온라인 롤아웃 파티션 레벨 REORG XML과 통합하기 질문과 답변 결론 소개 테이블 파티셔닝(데이터 파티셔닝이나 범위 파티셔닝이라고도 함)은 DB2 for Linux, UNIX, and Windows in Version 9.1에서 도입되었다. 초기 릴리스에서는 파티션되지 않은 색인(글로벌 색인)을 지원했으며 이 색인은 파티션 제거 기능과 결합되면 대부분의 액세스 과정에서 제대로 작동했다. 그러나 데이터 롤인 및 데이터 롤아웃, 글로벌 색인의 유지보수와 같은 일반적인 데이터 관리 조작을 수행하려면 시간이 많이 걸렸다. 이 때문에 롤인된 데이터를 사용하려면 시간이 지연되었을 뿐만 아니라 대용량 활성 로그 공간을 사용해야 했다. DB2 V9.7에서는 로컬 색인을 도입하여 데이터 롤인 조작을 효율화했다. 이 기사에서는 롤인 조작이 더욱 효과적으로 수행될 수 있도록 도움을 주는 로컬 색인을 활용하여 데이터 롤인에 대한 베스트 프랙티스를 살펴본다. 새 데이터를 주기적으로 롤인하는 동안에는 스토리지에 저장하기 위해 기존 데이터를 자주 롤아웃하거나 삭제하여 스토리지를 비워야 한다. 운영 데이터 저장소로서 사용이 점차 증가하고 잇는 데이터 웨어하우스를 사용하면서 현재는 더욱 많은 비즈니스 사용자가 롤인 및 롤아웃 오퍼레이션이 수행되는 동안에도 데이터 웨어하우스에 있는 데이터를 매일 24시간 이용할 수 있을 것으로 기대한다. 파티션된 테이블의 사용가능성을 개선하는 작업은 DB2 9.7.1에서 처음으로 이루어졌으며 가장 일반적인 액세스 조작을 온라인에서 롤아웃을 작성(ALTER TABLE DETACH PARTITION)하여 처리함으로써 이러한 개선을 이루었다. 이 기사에서는 새로운 2단계 분리 작동을 설명하고 이와 관련된 일부 FAQ를 살펴본다. 오늘날에는 비즈니스에 사용되고 있는 평균 데이터 웨어하우스의 크기가 폭발적으로 늘어나고 있다. 100TB 이상의 데이터 웨어하우스를 자주 볼 수 있다. 이렇게 데이터 용량이 증가한데는 비즈니스상의 요구로부터 다양한 법규정 및 회계 표준에 이르는 다양한 요구사항에 기인한다. 그러나 이러한 사실은 자주 변경되는 최신 데이터만 해당하며 기록 데이터는 변화없이 일정하다. 이 때문에 전체 테이블 대신 테이블 데이터의 서브세트를 대상으로 유지보수를 수행하여 운영 시간을 줄이고 정적 데이터에 불필요한 조작을 하지 않도록 해야 한다. DB2 9.7.1에서는 DB2 9.7에서 로컬 색인을 지원하면서 제공된 파티션 독립성을 활용하여 파티션 레벨의 데이터와 색인 재구성을 지원하게 되었다. 로컬 색인 로컬 색인은 로컬 색인에서 기초로 하는 테이블과 같은 방식으로 파티션된다. 각 색인 파티션은 해당 데이터 파티션에서만 행을 색인한다. 사용자가 작성한 색인과 시스템에서 작성된 색인은 대부분 기본적으로 로컬 색인이나 파티션된 색인으로 작성된다. 고유 색인은 이러한 규칙에 해당되지 않으며 고유 색인에는 색인 키 컬럼에 테이블 파티셔닝 키가 포함되지 않는다. 고유 색인은 글로벌 색인으로 작성되어야 한다. 이러한 경우, 기본 키나 고유 색인 제한조건을 강제로 시행하기 위해 시스템에서 작성된 고유 색인은 글로벌로 작성된다. 이러한 제한이 없으면 범위가 다양한 파티션간에 고유성을 유지보수하는 것이 실용성이 없어지게 된다. 테이블 파티셔닝이 데이터베이스 파티셔닝 기능(DPF)과 결합되면 고유 색인 키에 데이터베이스 파티셔닝(또는 분산) 키와 테이블 파티셔닝 키가 포함되어야 한다. 각 데이터 파티션의 색인 파티션은 다양한 테이블스페이스에 있을 수 있다. 테이블스페이스는 CREATE TABLE 문의 INDEX IN 절을 사용하여 지정할 수 있다. 테이블 파티션의 메타데이터는 SYSCAT.DATAPARTITIONS 뷰를 쿼리하여 얻을 수 있는 반면에 색인 파티션의 메타데이터는 DB2 9.7에서 도입된 SYSCAT.INDEXPARTITIONS 카탈로그 뷰를 쿼리하여 얻을 수 있다. 다음 예제는 해당 테이블의 로컬 및 글로벌 색인을 위한 테이블스페이스 스펙을 나타낸다 예제: 파티션된 테이블과 파티션된 색인 작성하기 CREATE TABLE purchaseOrders ( po_id INT, po_date DATE, po_customer CHAR(200), po_item VARCHAR(20),po_quantity INT, po_price DECFLOAT ) INDEX IN global_index_tbsp PARTITION BY (po_date) (PARTITION jan2010 STARTING ('1/1/2010') ENDING '1/31/2010' IN jan2010_data_tbsp INDEX IN jan2010_index_tbsp, PARTITION feb2010 STARTING ('2/1/2010') ENDING '2/28/2010' IN feb2010_data_tbsp INDEX IN feb2010_index_tbsp, PARTITION mar2010 STARTING ('3/1/2010') ENDING '3/31/2010' IN mar2010_data_tbsp INDEX IN mar2010_index_tbsp); CREATE INDEX purchaseOrdersIndex1 ON purchaseOrders(po_customer) PARTITIONED; 로컬 색인을 사용하면 데이터 롤인과 롤아웃을 효율화할 수 있다. 롤인의 경우, 연결될 테이블에 있는 색인이 새 파티션의 로컬 색인이 되도록 활용된다. (이점은 다음 섹션에서 자세히 살펴본다.) 롤아웃의 경우, 분리될 파티션에 있는 로컬 색인이 분리된 독립형 테이블의 색인이 된다. 로컬 색인을 사용한 롤인 DB2에서의 롤인은 ATTACH를 사용하며 두 단계의 조작으로 수행된다. 1. 먼저, ALTER TABLE ATTACH PARTITION 명령에 의해 기존 테이블이 새로운 데이터 파티션으로 파티션된 테이블에 통합된다. 2. 그런 다음, SET INTEGRITY 명령이 무결성 처리를 초기화하고 글로벌 색인을 유지보수한다. 새로 추가된 데이터는 두 번째 단계가 정상적으로 완료된 후에만 볼 수 있다. 글로벌 색인이 있는 상태에서 SET INTEGRITY를 수행하려면 글로벌 색인을 점진적으로 유지보수하기 위해 상당한 양의 활성 로그 공간이 필요하며 또한, 새로 추가된 데이터 양에 비례하여 실행하는 데 오랜 시간이 필요할 수 있다. 그림 1에는 DB2 9.7 이전 버전에서 글로벌 색인을 사용하여 롤인 조작을 하는 과정이 표시되어 있다. 그림 1. 글로벌 색인을 사용한 롤인 로컬 색인을 사용하면 롤인에 걸리는 시간이 크기에 따라 감소된다. 파티션된 테이블에 있는 기존 로컬 색인과 일치하는 모든 색인(첨부될 테이블에 있는)을 활용할 수 있다. 데이터 롤인 속도 개선과 관련된 베스트 프랙티스에서는 롤인하기 전에 파티션된 테이블에 있는 모든 로컬 색인과 정확히 일치하는 색인이 첨부될 테이블에 있는지 확인한다. 그림 2에는 이러한 베스트 프랙티스가 표시되어 있다. 그림 2. 로컬 색인을 사용한 롤인 이러한 베스트 프랙티스를 따르지 않더라도 일반적으로 로컬 색인만 있는 경우에는 여전히 롤인에 걸리는 시간이 상당히 줄어든다. 자세히 말하자면 파티션된 대상 테이블의 로컬 색인과 일치하는 색인이 첨부될 테이블에 없으면 새 파티션의 로컬 색인 오브젝트가 잘못됨으로 표시되며 이 파티션의 로컬 색인은 SET INTEGRITY 과정에서 작성된다. 이 로컬 색인은 새로 첨부된 파티션에서만 작성되고 매우 제한된 로깅을 필요로 하기 때문에 글로벌 색인과 비교하여 여전히 SET INTEGRITY가 상당히 빠르게 실행된다. 표1에는 롤인에 대한 베스트 프랙티스가 요약되어 표시되어 있다. 표 1. 롤인에 대한 베스트 프랙티스 단계 설명 예제 주석 1. 대상 테이블에 있는 모든 로컬 색인과 일치하지 않는 색인을 원본 테이블에서 삭제 DROP INDEX srcidx_po_price 빠른 DDL 2. 대상 테이블에 있는 로컬 색인과 일치시키기 위해 원본 테이블에서 누락된 색인을 작성 CREATE INDEX srcidx_po_customer ON Apr2010(po_customer) AND는 비교적 부담이 덜하고 대상 테이블에 영향을 주지 않음 3. ATTACH 파티션 ALTER TABLE purchaseOrders ATTACH PARTITION Apr2010 STARTING '4/1/2010' ENDING '4/30/2010' FROM Apr2010 빠른 DDL 4. SET INTEGRITY SET INTEGRITY FOR purchaseOrders IMMEDIATE CHECKED 새 테이블 데이터를 간단히 스캔하여 모든 행이 범위 내에 있는지 검사하고 제약조건이 있으면 이를 강제로 시행 출처 : http://www.ibm.com/developerworks/kr/library/tutorial/dm-1005db2embeddedmaint/index.html