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

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

목차 소개 로컬 색인 로컬 색인을 사용한 롤인 온라인 롤아웃 파티션 레벨 REORG XML과 통합하기 질문과 답변 결론 온라인 롤아웃 DB2 9.7.1을 시작으로 DETACH 조작은 온라인 상태에서 수행되며 쿼리가 나머지 테이블 파티션에 동시에 액세스하는 동안 파티션이 분리될 수 있도록 한다. 사실상, UR(Uncommitted Read) 분리 레벨을 사용하여 워크로드를 보고하면 분리될 파티션을 읽게 될 수 있으며 심지어 동시에 처리되지 않고 분리 상태에 있는 파티션도 읽게 될 수 있다. 이러한 조작은 2단계의 분리 조작을 수행하여 완성되며 첫 번째 단계에서는 파티션을 논리적으로 분리하며 시스템에서 백그라운드로 관리하기 때문에 사용자가 개입하지 않아도 되는 두 번째 단계에서는 파티션된 테이블에 대한 기존의 모든 액세스가 완료되었는지 확인한 후, 분리 조작을 비동기적으로 완료한다. 종속 MQT(Materialized Query Table)가 없는 경우에는 첫 번째 단계(ALTER TABLE DETACH PARTITION)가 커미트되고 나서 시스템에서 두 번째 단계를 시작한다. 파티션된 테이블에 스테이징 테이블이나 REFRESH IMMEDIATE MQT와 같은 종속자가 있으면 이 종속자가 SET INTEGRITY를 통해 새로 고쳐진 후에 두 번째 단계가 자동으로 시작된다. DBA 관점에서는 워크로드 보고가 장기간 계속되는 상태에서 롤아웃 조작이 진행된다. 애플리케이션 관점에서는 장기간 계속되는 워크로드 보고가 분리로 인해 중단되지 않고 동시에 완료된다. DETACH 명령이 성공적으로 완료되고 나면 파티션된 테이블에 액세스하더라도 더이상 분리된 파티션을 볼 수 없으며 분리는 비동기적으로 완료되기 때문에 이러한 새로운 액세스 과정이 중단되지 않는다. 물론, 사실상 새로운 온라인 분리 작동에는 분리 레벨이 중요시된다. 다시 말해서, RR(Repeatable Read) 스캐너가 파티션을 읽었지만 아직 커미트하지 않은 경우, 분리 조작은 파티션을 감추기 전에 RR 스캐너가 작업을 완료하기를 기다린다. 비동기 분리 조작에 의해 분리 작업이 완료되고 나면 분리 대상 테이블을 아카이브 또는 삭제하거나 일부 다른 테이블에 첨부할 수 있다. 비동기 분리 작동은 새로운 기능이며 DB2 9.7.1에서만 사용된다. 비동기 분리 작동은 다음과 같은 방법을 사용하여 모니터할 수 있다. LIST UTILITIES SHOW DETAIL 명령 실행 해당 파티션의 SYSCAT.DATAPARTITIONS 카탈로그 뷰의 STATUS 컬럼을 쿼리. 파티션 항목이 계속해서 리턴되고 STATUS가 'D' 또는 'L'이면 비동기 분리는 완료되지 않는다. 분리 대상 테이블은 비동기 분리 조작이 완료된 후에만 사용 가능하게 된다. 후자는 분리가 완료되기를 기다리는 과정을 자동화하는 데 특히 유용하며 이렇게 하면 대상 테이블에서 시퀀스 조치(즉, 삭제 또는 아카이브)를 직렬화하는 데 도움이 된다. 분리 대상 테이블의 사용가능성이 파티션된 테이블의 사용가능성보다 더 중요한 경우에는 파티션된 테이블에 동시에 액세스하지 않는지 확인하여 비동기 분리가 빨리 완료될 수 있게 한다. DB2 9.7.1 부터는 DETACH가 온라인 상태에서 수행되지만 표 2에 표시된 바와 같이 구문은 변경되지 않았다. 표2. 분리단계 단계 설명 예제 1. DETACH 파티션. 두 번째 단계를 시작하려면 이 명령을 커미트해야 한다. 일반적으로 DDL 이후에 즉시 커미트하는 것이 좋다 ALTER TABLE purchaseOrders DETACH PARTITION jan2010 INTO obsolete_data; COMMIT; 2. 시스템에서 시작한 비동기 파티션 분리로 사용자가 개입할 필요 없음. 파티션 레벨 REORG DB2 9.7.1에는 남아 있는 테이블 파티션에 읽기/쓰기 액세스를 허용하면서 ON DATA PARTITION 절을 사용하는 하나의 데이터 파티션이나 색인 파티션을 REORG하는 기능이 도입되었다. 이러한 경우, 지정된 액세스 모드는 재구성될 파티션에만 적용된다. 다음과 같은 경우에는 동일한 테이블로 구성된 다양한 파티션을 대상으로 여러 개의 REORG 명령을 실행하여 다수의 데이터 파티션을 병렬로 재구성할 수 있다. 해당 테이블에는 XML 컬럼 경로 색인 외에 어떠한 글로벌 색인도 없다. 그리고 REORG 명령에서 ALLOW NO ACCESS 모드가 지정되었다. 글로벌 색인이 존재하고 동일한 테이블로 구성된 다수의 파티션을 재구성해야 하는 경우에는 REORG 조작을 하기 전에 모든 글로벌 색인을 삭제하고 REORG 조작이 모두 완료된 후에 글로벌 색인을 다시 작성하는 것이 더욱 효율적일 수 있다. 이렇게 하지 않으면 하나의 파티션에서 한 번에 직렬로 데이터를 재구성해야 할 뿐만 아니라 각 데이터 파티션을 재구성한 후, 모든 글로벌 색인을 여러 번 다시 작성해야 한다. 파티션 레벨 REORG를 사용할 때 다음과 같은 몇 가지 사항을 기억해야 한다. 다수의 파티션을 동시에 재구성하려면 시스템 자원이 충분해야 한다. 파티션된 테이블에 XML 컬럼 경로 색인 외에 글로벌 색인이 있는 상태에서 파티션을 재구성하면 전체 테이블이 오프라인 상태가 된다. REORG INDEX 명령은 ON DATA PARTITION 절을 지원하지 않으며 이 절은 글로벌 색인에만 적용된다. 다음 예제에서는 글로벌 색인이 없는 테이블에서 파티션 레벨 REORG를 실행한다. 표3. 글로벌 색인이 없는 테이블에서의 파티션 레벨 REORG 실행 예제 주석 REORG TABLE purchaseOrders ALLOW READ ACCESS ON DATA PARTITION Apr2010 하나의 파티션(Apr2010)을 읽기 액세스를 허용하면서 재구성하며 남아 있는 모든 파티션에서는 읽기/쓰기가 가능하다 REORG TABLE purchaseOrders ALLOW NO ACCESS ON DATA PARTITION Mar2010; REORG TABLE purchaseOrders ALLOW NO ACCESS ON DATA PARTITION Apr2010; 두 개의 파티션을 동시에 재구성한다. 각 파티션에 대한 액세스는 허용되지 않으며 남아 있는 모든 파티션에서는 읽기/쓰기가 가능하다. REORG INDEXES ALL FOR TABLE purchaseOrders ALLOW WRITE ACCESS ON DATA PARTITION Apr2010; Apr2010 데이터 파티션의 모든 로컬 색인을 재구성한다 XML과 통합하기 이 기능은 DB2 9.7에서 시작되었으며 XML 데이터가 있는 테이블에서도 테이블 파티셔닝을 통해 다양한 혜택을 얻을 수 있다. 해당 파티션의 XML 데이터를 저장하기 위해 데이터 파티션당 하나의 XDA 오브젝트가 작성된다. XML 데이터를 위한 테이블스페이스 배치는 LOGIN IN 절에서 결정되며 파티션 레벨이나 테이블 레벨 또는 이 두 레벨이 조합된 레벨로 지정될 수 있다. 기본적으로 XML 데이터는 해당 데이터 파티션과 같은 테이블스페이스에 저장된다. XML 데이터 전체에서 글로벌 색인이나 로컬 색인을 작성할 수 있다. 그러나 XML 컬럼은 테이블 파티셔닝 키의 일부가 될 수 없기 때문에 모든 고유 XML 값 색인을 글로벌 색인으로서 작성해야 한다. 시스템에서 작성된 XML 영역 색인은 언제나 로컬로 작성되지만 XML 컬럼 경로 색인은 언제나 글로벌로 작성된다. 글로벌 경로 색인을 사용하면 결과적으로 스토리지의 효율성이 개선되고 XQuery 실행에 필요한 경로 검색 시간이 최소화된다. 질문과 답변 이 섹션에서는 분명하지 않을 수도 있는 테이블 파티셔닝에 관한 몇 가지 의견과 자주 질문을 받게 되는 사항에 관해 살펴본다. 질문: CREATE INDEX 문을 사용하여 로컬 색인을 위한 테이블스페이스를 지정할 수 없는 이유는? 답변: 데이터 파티션을 위한 모든 로컬 색인은 파티션된 테이블에 있는 색인과 동일한 색인 오브젝트에 상주한다. 따라서 데이터 파티션의 모든 색인이 포함된 색인 오브젝트가 이동하는 테이블스페이스를 지정할 수 있다고 해도 각 개별 색인을 위한 테이블스페이스를 지정할 수는 없다. 이는 REORG와 같은 유지보수 조작을 개별 로컬 색인에서 수행할 수 없는 이유이기도 하다. 로컬 색인은 자체적으로 오브젝트를 갖고 있는 글로벌 색인과는 달리 테이블스페이스 배치가 더욱 유연하고 개별 글로벌 색인에 대해 REORG를 지원한다. 질문: 로컬 색인을 사용하면서 글로벌 색인을 사용할 때에 비해 잠금 충돌이 더욱 많아졌다(동시성이 다소 악화됨). 그 이유는? 답변: 글로벌 색인 스캔을 사용하면 색인 검색을 통해 파티션에 대한 참조가 존재하는지 판별된다. 파티션은 참조가 발견되는 경우에만 잠긴다. 그러나 로컬 색인 스캔을 사용하면 먼저 파티션을 잠근 다음 색인 검색을 하여 일치되는 색인을 찾아야 한다. 따라서 로컬 색인을 사용하여 액세스하는 경우 모든 쿼리 처리 행에 기여하지 않는 일부 파티션은 여전히 잠기게 된다. 질문: RUNSTATS를 사용하여 개별 파티션의 통계를 수집할 수 있나? 답변: 수집할 수 없다. RUNSTATS는 테이블에서만 수행될 수 있다. 질문: 파티션된 테이블에 있는 색인이 파티션되었는지 어떻게 표시할 수 있나? 답변: DESCRIBE INDEXES FOR TABLE 명령을 사용한다. Index Partitioning 컬럼에 사용 가능한 값은 파티션된 색인의 경우 'P'이고 파티션되지 않은 색인의 경우에는 'N'이며 파티션되지 않은 테이블의 색인인 경우에는 '-'(널)이다. 또한, 테이블 함수 SYSPROC.ADMIN_GET_INDEX_INFO()가 이러한 정보를 리턴한다. 질문: 동일한 색인이 로컬 색인이었을 때보다 글로벌 색인이었을 때 그 크기가 큰 이유는? 답변: 로컬 색인은 각 키 레코드의 rowid에 있는 2바이트 파티션 ID를 저장하지 않는다. 이 때문에 rowid가 6바이트인 대용량 테이블스페이스에서는 색인의 크기가 rowid당 25% 줄어들고 rowid가 4바이트인 일반 테이블스페이스에서는 33%가 줄어든다. 질문: 로컬 색인을 사용하는 것이 유리한 상황은 일반적으로 어떤 경우인가? 답변: 로컬 색인은 일반적으로 글로벌 색인보다 크기가 작다. 일반적으로 스토리지가 줄어들면 I/O가 감소하여 성능이 향상된다. 이러한 요인이 로컬 색인에 유리하다고 할 수 있다. 또한, 로컬 색인은 데이터 롤인과 데이터 롤아웃 조작을 효율화하는 데 중요한 역할을 하며 REORG와 같은 파티션 레벨 조작을 가능하게 한다. 질문: 글로벌 색인을 사용하는 것이 유리한 상황은 일반적으로 어떠한 경우인가? 답변: 글로벌 색인을 사용하면 각 색인에 맞게 테이블스페이스를 유연하게 지정할 수 있다. 중요한 특정 색인이 포함된 테이블스페이스만 백업해야 하는 경우에는 이렇게 하는 것이 유용할 수 있다. 글로벌 색인을 사용하면 색인 스캔을 하더라도 순서 특성이 유지되며 이점이 로컬 색인보다 유리하다고 할 수 있다. 대부분의 경우에 로컬 색인은 파티션 간에 순서 특성을 유지하지 않기 때문에 로컬 색인을 사용할 경우에는 따로 정렬을 해야 한다. 다시 말해서 PurcaseOrders 테이블은 po_date로 파티션되고 po_customer에 로컬 색인이 있다. 색인을 스캔한 후에는 아래에 있는 쿼리의 결과를 다시 정렬해야 한다. SELECT po_id, po_customer FROM purchaseOrders WHERE po_date > '2/15/2010' AND po_date < '4/15/2010' ORDER BY po_customer; 그러나 쿼리 엑세스가 하나의 파티션으로 제한되었기 때문에 다음 쿼리는 추가로 정리할 필요가 없다. SELECT po_id, po_customer FROM purchaseOrders WHERE po_date > '2/15/2010' AND po_date < '2/25/2010' ORDER BY po_customer; 질문: 데이터 파티션이나 색인 파티션을 재구성해야 하는지 어떻게 알 수 있나? 답변: REORGCHK 명령을 실행한다. 이 명령을 실행하면 각 파티션에 대한 색인과 데이터 통계가 출력되고 어떤 파티션을 재구성해야 하는지 제시된다. 질문: 행이 상주하는 데이터 파티션을 어떻게 표시하나? 답변: DATAPARTITIONNUM 함수가 행이 상주하는 데이터 파티션의 시퀀스 번호(SYSDATAPARTITIONS.SEQNO)를 리턴한다. 질문: 파티션된 테이블(또는 원하는 임의의 테이블)의 테이블 크기와 색인 크기는 어떻게 계산하나? 답변: 테이블 함수 ADMIN_GET_TAB_INFO_V97과 ADMIN_GET_INDEX_INFO는 각각 테이블 크기와 색인 크기를 리턴한다. 질문: 로컬 색인 파티션과 데이터 파티션이 각 파티션의 동일한 테이블스페이스에 있을 수 있나? 답변: 그렇다. 사실상 이러한 상황이 기본적인 경우이며 대부분의 고객이 이렇게 선택한다. 다음과 같은 경우에는 로컬 색인이 연관된 데이터 파티션과는 다른 테이블스페이스에 있게 된다. CREATE TABLE 문에서 파티션 레벨 INDEX IN 절이 지정되거나 새로운 파티션을 위해 ALTER TABLE ADD PARTITION 문에서 INDEX IN 절이 지정되거나 첨부될 데이터 파티션에서 데이터와 분리된 별도의 테이블스페이스에 색인이 있는 경우 결론 DB2 9.7에서 시작된 로컬 색인 덕택에 데이터 롤인 및 데이터 롤아웃과 같은 일반적인 데이터 웨어하우징이 상당히 개선되었다. 로컬 색인을 활용하면 롤인 과정에서 새로 첨부된 데이터를 더욱 신속하게 표시할 수 있을 뿐만 아니라 사실상 활성 로그 스페이스를 사용하지 않게 된다. 로컬 색인을 지원하게 되면서 테이블 파티셔닝을 사용하여 파티션의 독립성을 개선하는 기능이 큰 진전을 보게 되었다. 파티션이 독립되면 사용가능성이 개선되어 파티션의 서브세트에서 REORG와 같은 유지보수 조작을 진행하는 동안 나머지 테이블에 액세스할 수 있게 된다. 일반적인 웨어하우스 환경에서는 최신 데이터는 자주 변경되는 반면, 기록 데이터는 다소 정적이다. 이 때문에 파티션 레벨 REORG는 활성 데이터만을 재구성하는 경우에 유용하다. 온라인 분리를 이용하면 시간이 오래 걸리는 쿼리 보고를 롤아웃 조작과 함께 동시에 실행할 수 있으며 데이터 웨어하우스의 사용가능성을 개선할 수 있다. 출처 : http://www.ibm.com/developerworks/kr/library/tutorial/dm-1005db2embeddedmaint/index.html