Oracle의 기능을 사용해 테이블 결합을 고속화하기

출처 : KACHISORI

 

전회에서는 추출 조건이 없는 결합을 포함한 SQL, 추출 조건이 있는 결합을 포함한 SQL에 대해 실례를 기본으로 최적인 결합 방법을 설명했습니다.

　

이번은 Oracle의 기능인 「Materialized View·뷰」 「BITMAP JOIN INDEX」 「클러스터」를 이용한 결합 처리에 관한 튜닝·테크닉에 대해 설명합니다.

■  Materialized·뷰의 이해

 

결합 처리등을 포함한 복잡한 검색 처리에 퍼포먼스 향상이 요구되고 있는 경우 마테리아라이즈드·뷰(Materialized View：이후, MView)의 이용이 유효합니다. MView에는 크게 2개의 이용 방법이 있습니다. 1개는 리모트·데이타베이스상에 존재하는 데이터를 로컬·데이타베이스상에 정기적으로 카피하는 목적으로 사용되며 snapshot라고도 불립니다. 다른 하나는 로컬·데이타베이스상의 데이터의 집계나 결합 처리를 고속화하기 위해서 사용됩니다. 예를 들면 매일매일의 매상 데이터가 격납된 테이블을 집계하는 MView를 작성해 둔다면 매상 집계 처리를 고속화하는 것이 가능해집니다.

 

그림 1　MView의 이용 방법

　

MView의 기본이 되는 테이블 데이터가 갱신되었을 경우 리프레쉬라는 처리에 의해 MView에 그 변경이 반영됩니다. 리프레쉬에는 그림 2와 같이 매회 모든 데이터를 바꿔 넣는 완전 리프레쉬와 원테이블의 변경 이력을 보관 유지하는 마테리아라이즈드·뷰·로그(Materialized View Log：이후, MView 로그)를 이용해 차분만을 반영하는 고속 리프레쉬가 있습니다.

그림 2　MView의 리프레쉬 동작

 

Oracle9i 이후에서는 로컬·데이타베이스내에 있는 데이터를 집계, 결합한 MView를 작성해 기본이되는 테이블 데이터가 갱신되었을 경우 즉시 반영시키는 것도 가능합니다.

MView를 사용하면 시간이 걸리는 복잡한 검색 처리를 단시간에 처리하는 것이 가능해집니다.이번 테마는 결합이므로, 결합을 포함한 MView에 대해서 그 메리트를 실행 계획, 실행 통계로부터 확인해 보겠습니다.AUTOTRACE 기능의 대해서는 제5회「SQL 튜닝의 기반이 되는 통계 정보」을 참조하삼.

 

그림 3은 MView를 사용하고 있지 않는 경우의 실행 계획과 실행 통계입니다.

그림 3　MView를 사용하고 있지 않는 경우의 실행 계획, 실행 통계

　

실행 계획을 보면 네스티드·루프 결합과 해시 결합의 2개의 결합 처리를 하고 있는 것을 확인할 수 있습니다.또, 액세스 블록수도 108603으로 꽤 많은 것을 알수 있습니다.

 

■ Materialized·뷰의 작성과 평가

 

결합을 포함한 MView를 작성하려면 MView의 SELECT 리스트에 각 테이블의 ROWID를 포함시켜야 합니다. 그 외에도 집계나 결합을 포함한 MView를 작성하려면  전제 조건이 있으므로 「Oracle9i 데이터·도매상·가이드 릴리스 2(9.2)」를 참조하십시요.

 

덧붙여 이번 예에서는 ON COMMIT 옵션을 지정해 윈래테이블의 갱신이 리얼타임에 MView에 반영되도록 작성합니다. 이하는 MView의 작성 방법과 MView를 사용했을 경우의 실행 계획과 실행 통계입니다.

 

CREATE MATERIALIZED VIEW Mview_l_s_n REFRESH fast on commit AS SELECT l.rowid l_rowid,s.rowid s_rowid,n.rowid n_rowid,        n.n_name,l.l_shipdate,l.l_shipmode,s.s_name,s.s_address   FROM lineitem l,supplier s,nation n  WHERE l.l_suppkey=s.s_suppkey    AND s.s_nationkey=n.n_nationkey;

그림 4　MView의 작성 방법

그림 5　MView를 사용했을 경우의 실행 계획, 실행 통계

부하가 높은 결합 처리는 MView 작성시에 완료하므로 결합이 끝난 상태로 데이터가 보관 유지되고 있기 때문에 MView의 색인 스캔만으로 결과를 돌려줍니다. 이 때문에 그림 3과 그림 5의 실행 계획 실행 통계를 비교하면 실행 계획내의 결합의 유무, 액세스 블록수가 큰폭으로 감소하고 있는 것을 확인할 수 있습니다. 다만, ON COMMIT 옵션을 지정해 MView를 이용하는 경우 원래 테이블에 대한 갱신 처리의 퍼포먼스가 열화 할 우려가 있으므로 충분한 테스트를 실시한 위에 사용할 것을 권장합니다.

 

■ Materialized·뷰에서 쿼리-리라이트 기능의 사용

　

방금전의 예에서는 FROM구로 MView의 이름을 직접 지정했지만 쿼리 리라이트 기능을 이용하면 SQL를 변경하지 않고  자동적으로 테이블의 참조를 MView에의 참조에 옮겨놓는 것이 가능해집니다. 쿼리-리라이트 기능을 이용하기 위해서는 이하의 설정이 필요합니다.

1. SQL를 실행하는 유저는 QUERY REWRITE 권한을 보관 유지한다
   (다른 유저가 소유하는 테이블을 참조하는 경우 GLOBAL QUERY REWRITE 권한이 필요)
   　

2. 코스트 베이스의 어프로치를 이용해야 한다.
   　

3. 초기화 파라미터 「QUERY_REWRITE_ENABLED=TRUE」의 설정
   　

4. MView가 쿼리-리라이트가 가능하게 설정되어야 한다.
   이하의 SQL로 REWRITE_ENABLED열이 Y일 필요가 있습니다.
   　
   

   SQL> SELECT mview_name,rewrite_enabled FROM user_mviews   2  WHERE mview_name='MVIEW_L_S_N'; MVIEW_NAME                     R ------------------------------ - MVIEW_L_S_N                    Y

   
   MView에서 쿼리-리라이트 가능하게 변경하려면 이하의 SQL를 실행합니다
   　
   

   ALTER MATERIALIZED VIEW Mview_l_s_n ENABLE QUERY REWRITE;

   
   　

5. 초기화 파라미터 「QUERY_REWRITE_INTEGRITY」의 값의 적절히 설정
   각 파라메터의 의미에 대해서는 아래를 참고하세요
   　
   

   파라미터치	의미
   ENFORCED （디폴트）	MView와 참조 기본의 마스터 테이블과의 데이터 정합성이 확인되었을떄 리라이트를 한다
   TRUSTED	데이터 관계의 정합성은 유저에게 의존하고 있고 기본적으로 ENFORCED와 같고, 디멘션을 이용할 경우에 설정한다
   STALE_TOLERATED	MView와 참조기본 마스터테이블과의 데이터 정합성이 확인 되지 않더라도 리라이트를 한다

쿼리-리라이트 기능을 이용하기 위해선 상기 설정을 실시해 방금전 실행한 결합을 포함한 SQL를 실행합니다.

 

그림 6　쿼리-리라이트 기능을 사용했을 경우의 실행 계획, 실행 통계

SQL는 기본 테이블 「lineitem」 「supplier」 「nation」의 결합 처리를 지정해 있습니다만 실행 계획을 보면 MView가 참조되고 있는 것을 확인할 수 있습니다. 이와 같이 쿼리-리라이트 기능을 이용하면 어플리케이션의 SQL를 변경하지 않고  MView를 이용하는 것이 가능하게 됩니다. 다만 기본 테이블에 대한 갱신 빈도나, 최신 데이터를 참조할 필요가 있는지 등을 충분히 검토하고  테스트를 실시하고 나서 이용해야 할 것입니다.

 

■ BITMAP JOIN INDEX의 사용

 

결합을 포함한 SQL이 실행될 때 결합 처리에 시간이 걸리는 경우가 매우 많이 있습니다. 결합 처리를 배제할 수 있으면 당연히 그 만큼 시간을 단축할 수 있으므로 SQL의 퍼포먼스 향상을 기대할 수 있습니다. Oracle9i로부터 BITMAP JOIN INDEX를 이용해 결합 처리의 배제가 가능하게 되었습니다. BITMAP JOIN INDEX는 그 이름대로, 그림 7과 같은 이미지로 데이터를 Bitmap로 관리하고 있습니다.

그림 7　Bitmap Index 이미지

　

이 예에서 실행하고 있는 SQL은 결합 조건 이외에 DNAME열에 WHERE 조건이 존재하는 것을 전제로 하고 있습니다. BITMAP JOIN INDEX를 이용하면 DNAME열의 조건에 일치하는 EMP테이블의 ROWID를 취득하는 것이 가능하므로 DEPT테이블 과의 결합 처리가 배제 가능해집니다.

　

그러면, 구체적으로 BITMAP JOIN INDEX를 이용했을 경우와 그렇지 않은 경우의 차이에 대해서, 실행 계획을 봅시다.

＜BITMAP JOIN INDEX를 이용하지 않는 경우＞
디멘션표에 해당되는 ORDERS테이블, PART테이블의 키열에는 프라이머리 키를 작성하고  WHERE구의 추출 조건열에도 색인을 작성합니다.

그림 8　BITMAP JOIN INDEX를 사용하지 않는 경우의 실행 계획

＜BITMAP JOIN INDEX를 이용한 SQL＞
처음에 이하의 SQL로 BITMAP JOIN INDEX를 작성합니다.

그림 9　BITMAP JOIN INDEX를 사용했을 경우의 실행 계획

그림 8과 그림 9의 실행 계획을 비교하면 BITMAP JOIN INDEX를 작성함으로 BITMAP JOIN INDEX에의 레인지 스캔을 해 결합 처리가 배제되고 있는 것을 확인할 수 있습니다. 이와 같이 BITMAP JOIN INDEX를 이용해 결합 처리를 배제할 수 있습니다만, SELECT 리스트에 디멘션표의 열이 포함되어 있으면 결합 처리가 필요하게 됩니다. 이것은, BITMAP JOIN INDEX가 돌려주는 ROWID는 색인을 작성한 팩트테이블이 되기 때문입니다. 그렇기 때문에 SELECT 리스트를 참조해 결합이 완전하게 배제할 수 있는지 확인할 필요가 있습니다.

 

또 단일 Bitmap 인덱스와 같이 갱신시의 락 단위는 레코드가 아니고, Bitmap 세그먼트(segment) 단위가 됩니다. 그러므로 OLTP계와 같은 갱신 트랜잭션(transaction)가 다수 실행되는 환경에서는 BITMAP JOIN INDEX의 사용에는 충분한 테스트가 필요합니다.

 

 

■  클러스터의 이용

 

별로 친숙하지 않은 분도 있으시겠습니다만 Oracle에는 클러스터라고 하는 오브젝트가 존재합니다. 클러스터란 결합 처리의 퍼포먼스를 향상시키기 위해서 사용되는 데이터 구조로 복수의 테이블을  결합한 상태로 데이타베이스에 격납합니다. 구체적으로는, 그림 10과 같이 동일한 키치(클러스터·키)를 가지는 복수의 테이블의 데이터가 정리되어 같은 데이터 블록에 격납됩니다. 키치가 결합열일 경우  같은 블록에 복수테이블의 데이터가 격납되고 있으므로 I/O감소에 의한 퍼포먼스 향상을 기대할 수 있습니다.

그림 10　클러스터의 구조

　

클러스터는 결합 처리를 최적화하기 위한 특수한 구조이며 클러스터내의 단일의 테이블에 대한 검색 처리나 클러스터내의 테이블에 대한 갱신 처리 등은 통상의 테이블에 비해 늦어집니다. 그러므로  결합 처리의 퍼포먼스가 요구되는 테이블이 반드시 결합해 검색되는 경우에 한해서 사용을 검토해야 합니다.

 

＜통상 테이블의 SQL＞

그림 11　통상표의 경우의 실행 계획, 실행 통계

＜클러스트한 SQL＞
비교하기 위해서 PART 테이블 ,PARTSUPP 테이블을 바탕으로 이하의 SQL로 클러스터를 작성합니다.

그림 12　클러스터를 사용했을 경우의 실행 계획, 실행 통계図     그림 12의 실행 계획은 「T_PARTSUPP」테이블이 클러스터에 액세스 하고 있는 것을 확인할수  있습니다. 또 실행 통계로부터 키치를 동일 블록에 격납하는 클러스터가 액세스 블록수가 감소하고 있는 것을 확인할 수 있습니다.

◇

 

이번 회에서는 Oracle의 기능을 이용한 결합 처리의 튜닝·테크닉을 해설했습니다. 다음회에서는 갱신 처리의 튜닝 수법에 대해 설명합니다.