테이블의 결합을 연구하는 튜닝·테크닉

출처 : KACHISORI

 

전회에서는 색인에 관한 튜닝·테크닉을 설명했습니다. 이번회에선 테이블 결합에 관한 퍼포먼스 향상 방법을 생각해 보겠습니다. 결합 처리는 정규화된 데이타베이스에서는 반드시 필요한 처리이며, 퍼포먼스의 문제가 되기 쉬운 부분이기도 합니다. 여기에서는 참조하는 테이블의 건수, 추출 조건의 유무등을 변경해, 결합의 종류에 의한 처리 시간, 액세스 블록수등이 어떻게 바뀌는지, 실례를 들어 설명해 갈 것입니다. 결합의 종류에 대해서는 제3회 「SQL 튜닝의 필수 지식을 총 자리등 있어(후편)」를 참조해 주세요.

 

■ 커다란 테이블에 대부분의 데이터를 결합하는 경우

우선 집계 처리 등에 대표되는 테이블의 대부분의 레코드를 취득하는 결합 처리를 예로 결합 방법에 따라 어떠한 차이가 발생하는지를 알아보겠습니다.

이하의 예에서는 데이터 건수가 약 600만건의 「LINEITEM」테이블과 1만건의 「SUPPLIER」테이블을 결합하고 약 600만건 전레코드를 취득하는 검색을 실행하고 있습니다. 디멘션표(부모 테이블)에 해당되는 「SUPPLIER」테이블의  「s_suppkey」열에 「PK_SUPPLIER」주키 색인이, 「LINEITEM」표의 「l_suppkey」열에 「FK_SUPPKEY」색인이 작성되어 있습니다.

결합 처리가 「네스티드·루프 결합」 「소트/머지 결합」 「해시 결합」이 되도록 SQL에 「USE_NL」 「USE_MERGE」 「USE_HASH」의 각 힌트를 지정해, 실행 계획, 실행 통계를 확인해 갈 것입니다. 실행 통계의 견해에 관해서는, 제5회 「SQL 튜닝의 기반이 되는 통계 정보　Page 3」을 참조하세요

 

네스티드·루프 결합

그림 1에서는 「USE_NL」힌트와 「ORDERED」힌트를 사용해 FROM구로 지정한 순서로 네스텟드·루프 결합을 하도록 옵티마이져에 지시하고 있습니다.

그림 1　네스티드·루프 결합의 경우

　

실행 계획을 보면 「LINEITEM」테이블이 외부테이블(구동테이블)로  풀테이블 스캔되어 1 레코드씩 꺼낼 때 마다 「PK_SUPPLIER」색인을 사용해 「SUPPLIER」테이블과 네스티드·루프 결합되고 있는 것을 알수 있습니다.

　

그럼 외부테이블을 「SUPPLIER」테이블로 변경했을 경우에는 어떻게 될까요? 트레이스 출력 결과를 봅시다.

그림 2　외부테이블을 「SUPPLIER」로 했을 경우

　

 FROM구의 차례를 변경하여「SUPPLIER」테이블이 외부테이블로서 선택되어 풀테이블 스캔되어 1 레코드씩 꺼내, 「FK_SUPPKEY」색인을 사용해 내부테이블인 「LINEITEM」테이블과 결합하는 네스티드·루프 결합을 하고 있습니다. 외부테이블에 건수의 적은 「SUPPLIER」표를 선택하고 있음에도 불구하고 처리 시간은 4만 2431초( 약 11시간 50분 )로 큰폭 늘어났습니다.

　

그림 1의 케이스와의 처리 시간의 차이는, 600만건이라고 하는 대량의 데이터를 풀테이블 스캔해 멀티 블록 읽기로 취득해 오는 경우와 색인 스캔에 의한 싱글·블록 읽기로 취득한 경우의 효율차에 의해 벌어지고 있습니다.

 

소트/머지 결합

다음에 그림 3의 「USE_MERGE」힌트를 사용한 소트/머지 결합의 결과를 봅시다. 열에 색인이 존재하므로 NOT NULL 제약이 존재하는 경우 색인 스캔을 실시함으로 소트 처리를 스킵 할 수 있습니다.「SUPPLIER」테이블은 건수도 적기 때문에 「PK_SUPPLIER」색인을 사용한 풀색인 스캔을 해 소트 처리가 스킵 되고 있습니다. 한편, 「LINEITEM」테이블은 건수가 많기 때문에 색인을 사용하지 않고  풀테이블스캔을 하고 있습니다. 강제적으로 색인을 사용시켰을 경우, 그림 2의 경우와 같이 매우 긴 시간이 걸리게 됩니다.

그림 3　소트/머지 결합의 경우

　

실행 통계의 「query」의 값을 보면 네스트드·루프 결합(그림 1)의 약 1200만 블록에 비해 소트/머지 결합에서는 약 12만 블록으로 큰폭으로 감소하고 있습니다. 이것은 소트/머지 결합의 경우 각 테이블의 블록은 풀테이블 스캔 또는 색인 풀 스캔에 의해서 한 번만 읽히기 때문입니다 (색인 스캔의 경우 동일한 블록이 몇번이나 읽히게 됩니다). 다만, 소트/머지 결합에서는, 「disk」의 값이 증가하고 있어, 「query」의 값을 웃돌고 있습니다. 이것은 디스크 소트^注1 을 한 것을 나타내고 있습니다.「disk」의 값 가운데 어느 정도의 블록이 디스크 소트 처리로 읽혔는가는, v$sesstat 뷰의 「physical reads direct」의 값에 의해 확인할 수 있습니다. 이 예에서는, 「physical reads direct」에 의해 약 20만 블록이 읽히고 있어 실제로 읽힌 블록의 총수는, 약 32만(12만＋20만) 블록 입니다.되고 있습니다.

 

注1：디스크 소트에 의한 읽기 「query」의 값은 버퍼·캐쉬로부터 읽힌 블록수, 「disk」의 값은 그 중 무슨 블록이 디스크로부터 읽혔는지를 나타내기 위함으로 통상은 disk의 값이 query의 값을 넘지 않습니다. 그러나 디스크 소트 처리나 일부의 패러렐 처리로 버퍼·캐쉬를 경유하지 않고 읽어들이는 처리(이것을 Direct Path Read라고 부릅니다)를 했을 경우, 「query」의 값에는 카운트 되지 않기 때문에 「disk」의 값이 커지는 경우가 있습니다.

해시 결합

마지막으로 그림 4의 「USE_HASH」힌트를 사용한 해시 결합의 결과를 봅시다.

그림 4　해시 결합의 경우

　

「query」의 값은 그림 3의 소트/머지 결합의 블록수 약 32만 블록에 비해 해시 결합은 약 50만 블록으로 많습니다만 「elapsed」 「disk」의 값은 해시 결합이 꽤 작은것을 알수 있습니다.  해시 결합은 소트/머지 결합과 같은 소트 처리는 행해지지 않고 결합 조건열인 「SUPPLIER」테이블의 「s_suppkey」열을 메모리상의 해시표에 전개합니다.그 후, 다른「LINEITEM」테이블의 「l_suppkey」열을 스캔 해, 작성된 해시표와 결합하고 결과를 돌려줍니다.

 

결합 방법별의 퍼포먼스 평가

그림 5는 각 결합 방법에 따르는 실행 시간, 액세스 블록수의 결과를 그래프로 한 것입니다.

그림 5　결합 방법별의 실행 시간과 액세스 블록수

　

3개의 결합 방법의 처리 시간을 비교해 보면 해시 결합의 경우가 가장 처리 시간이 빠릅니다. 네스티드·루프 결합은 내부테이블 색인 스캔을 하지만 색인 스캔은 테이블의 대부분의 레코드를 취득하는 처리에서는 풀테이블 스캔 이상으로 시간이 걸립니다. 그 때문에 테이블의 대부분을 결합하는 처리에는 바람직하지 않다고 말할 수 있습니다. 또한 소트/머지 결합은 쌍방의 테이블을 풀테이블 스캔에 의한 멀티 블록 읽기를 실시하는 점에서는 해시 결합과 같습니다만 그 후 소트 처리라고 하는 부하의 높은 처리를 실시해야 하므로  처리에 시간이 오래 걸립니다.

이러한 것으로 건수가 많은 테이블의 대부분의 데이터를 취득하는 등의 결합을 실시하는 경우에는 해시 결합이 우수한 것을 알수 있습니다.

 

■ 외부테이블(구동테이블)에 추출 조건이 있는 결합을 포함한 SQL를 실행한다

다음으로 WHERE 조건에 의해 어느 정도 걸러낸 데이터를 결합하는 경우에 대해서 보기로 하겠습니다. 여기에서는 「LINEITEM」테이블의 「l_orderkey」열(주키의 일부)을 검색 조건으로 지정해, 결합 방법에 따라 어떠한 차이가 나오는지를 확인하겠습니다.

그림 6은 검색 조건을 추가한 SQL에 힌트문으로 네스티드 ·루프 결합의 사용을 옵티마이져에 지시하고 있습니다.

 

그림 6　검색 조건을 추가한 네스티드·루프 결합의 경우

　

그림 6의 실행 통계에서 보듯이 검색 조건 추가한 것으로 대상이 되는 건수가 감소했으며 또 색인이 효율적으로 사용되어 그림 1과 비교하면 실행 시간, 액세스 블록수가 크게 감소하고 있는 것을 확인할 수 있습니다.

 

그림 7에서는 힌트문으로 소트/머지 결합의 사용을 옵티마이져에 지시하고 있습니다.

그림 7　검색 조건을 추가한 소트/머지 결합의 경우

　

그림 7의 실행 통계에서 보듯 「query」의 값은 네스티드·루프 결합에 비해 작지만 실행 시간은 되려 오래 걸렸습니다. 이것은 「LINEITEM」테이블의 결합 조건열인 「l_suppkey」에 색인이 작성되어 있지 않으므로 이 테이블에 대한 소트 처리가 발생하고 있기 때문입니다.

 

그림 8은 힌트문으로 해시 결합의 사용을 옵티마이져에 지시하고 있습니다.

 

그림 8　검색 조건을 추가한 해시 결합의 경우

　

그림 9는 각 결합 방법에 따르는 실행 시간, 액세스 블록수의 결과를 그래프로 한 것입니다.

그림 9　결합 방법별의 실행 시간과 액세스 블록수

　

그림 9의 결과로 액세스 블록수에서는 해시 결합이 제일 적습니다만 실행 시간은 네스티드·루프 결합이 제일 빠른 것을 알수 있습니다. 이와 같이 외부테이블(구동테이블)에 추출 조건이 있는 결합의 경우에는 네스텟드·루프 결합이 가장 적합한 결합 방법입니다.

 

 

■ 내부테이블에 걸러내는 조건이 있는 결합을 포함한 SQL를 실행

그러면 이번에는 내부테이블에 건수를 걸러내는 조건이 있는 경우에 대해 보겠습니다. 위의 「외부테이블(구동테이블)에 추출 조건이 있는 결합을 포함한 SQL」에서 사용한 SQL을 FROM구로 지정한 테이블의 순서를 바꿔 생각해 봅시다. 그 차이를 제일 확인하기 쉬운 네스티드·루프 결합의 경우에 대해 설명합니다.

 

그림 10은 「LINEITEM」테이블을 , 그림 11은 「SUPPLIER」테이블을 각각 외부테이블로 네스티드 루프 결합하는 것을 옵티마이져에 지시하고 있습니다.

 

그림 10　「LINEITEM」테이블로 걸러낸 결과를 「SUPPLIER」테이블에 결합했을 경우

그림 11　「SUPPLIER」테이블로 걸러낸 결과를 「LINEITEM」테이블에 결합했을 경우

　

그림 10, 그림 11의 실행 통계를 비교하면, 내부테이블에 걸러내는 조건이 있었을 경우가 실행 시간, 액세스 블록수 모두 매우 많아지고 있는 것을 확인할 수 있습니다.

　

그림 12는 추출 조건을 외부테이블, 내부테이블에서 실시했을 경우의 실행 시간, 액세스 블록수의 결과를 그래프로 한 것입니다.

 

그림 12　추출을 실시하는 테이블별 실행 시간과 액세스 블록수

　

 

그림 12의 결과로 네스티드·루프 결합의 경우 적절한 테이블을 외부테이블로 하는 것이 매우 중요하다라고 하는 것을 알수 있습니다. 외부테이블을 결정할 때는, 테이블의 전체의 레코드 건수로 판단하는 것이 아니라 결합 조건 이외의 WHERE구로 보다 좁혀지는 테이블을 외부테이블로 해야 한다라고 것을 주의해야 합니다.

 

■ 정리

　표 1은 실행되는 검색의 패턴별로 어느 결합 방법이 좋은가를 매트릭스표로 한 것입니다

결합하는 테이블 패턴	결합 방식
	네스티드 루프 결합	소트/머지 결합	해시 결합
건수가 많은 테이블끼리를 결합해 전레코드 출력한다	부적합	결과를 결합열로 소트 해 출력하는 경우에 유효.쌍방의 결합열에 NOT NULL 제약이 지정되어 있어 색인이 존재하는 경우, 매우 효율적	시스템·자원에 여유가 있는 경우에는적절
다른 테이블의 걸러지는 조건을 지정해 테이블을 결합해 레코드를 출력한다	기준으로서 색인을 사용해 테이블의 15%이내의 추출이면 최적	부적합	기준으로서 색인을 사용해 표의 15%이상의 추출로 게다가 등가 조건이 있으면 사용을 검토
표 1　결합 방식과 검색 패턴 일람

　

표 1로 알 수 있듯이 결합 조건이 등가 조건이 아니므로 해시 결합을 실시할 수 없는 경우 이외는, 대량의 결합 처리에서는 우선 해시 결합을 검토해야 합니다.