클러스터링 팩터

개요

본 장에서는 클러스터링 팩터가 좋은지 판단 근거와 개선하는 방법을 알아보자

클러스터링 팩터란 수직적 탐색 -> 수평적 탐색을 통해서 찾은 인덱스의 rowid 들이 실제 테이블 블럭에 모여있는 정도를 뜻한다.

index range scan 을 통해서 찾은 블록이 소량임에도 불구하고, 클러스터링 팩터가 좋지 않아 table access by rowid 블록수가 매우 큰 경우가 있다. 즉, 테이블 랜덤 액세스가 많아진 상황이라고 할 수 있는데, 이때 옵티마이저는 비용을 확인하여 인덱스 대신에 table full scan 을 사용할 수 도 있다.

1. 클러스터링 팩터 상태 파악

1) 올바른 히스토그램 정보 생성

• 옵티마이저가 최적의 판단을 하기 위해서

BEGIN 
  DBMS_STATS.GATHER_TABLE_STATS(ownname       => USER
                               ,tabname       => 'CUSTOMERS'
                               ,method_opt    => 'FOR COLUMNS CUST_CITY SIZE AUTO' 
                               ,no_invalidate => FALSE ) ; 
END ; 
/

SELECT /*+ FULL(C) */ *
FROM CUSTOMERS C
WHERE CUST_CITY = 'Los Angeles';
@XPLAN
/*
COST = 405
-----------------------------------------------------------------------------------------
| Id  | Operation         | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |      1 |        |    200 |00:00:00.01 |     359 |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS |      1 |    940 |    200 |00:00:00.01 |     359 |
-----------------------------------------------------------------------------------------
*/

SELECT /*+ INDEX(C(CUST_CITY)) */ *
FROM CUSTOMERS C
WHERE CUST_CITY = 'Los Angeles';
@XPLAN

/*
버퍼의 개수는 상대적으로 FULL SCAN에 비해서 낮지만, 클러스터링 팩터가 안좋아서 COST 가 더 높음
- COST = 853
- 따라서 힌트 미사용시 FULL SCAN 을 선택한다
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |      1 |        |    200 |00:00:00.01 |     159 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMERS     |      1 |    940 |    200 |00:00:00.01 |     159 |
|*  2 |   INDEX RANGE SCAN                  | CUSTS_CITY_IX |      1 |    940 |    200 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------------
*/

2) 힌트 미사용시 full table scan 을 선택

3) 데이터 딕셔너리에서 클러스터링 팩터를 확인해보자

-- CLUSTERING FACTOR = 51552.. 너무 안좋다.. 그러니까 TABLE FULL SCAN 타지.. 딱봐도 테이블 랜덤 액세스 많음
SELECT index_name, blevel, leaf_blocks, distinct_keys, clustering_factor 
  FROM user_indexes 
 WHERE index_name = 'CUSTS_CITY_IX' ;

2. 클러스터링 팩터 개선

클러스터링 팩터를 더 좋게 생성하기 위해서는 인덱스에 맞춰 정렬한 후 데이터를 생성하면된다. customers 테이블을 기반으로 cust2 테이블을 생성하여 좋은 클러스터링 팩터를 만들어보자

1) 테스트용 테이블을 인덱스 컬럼에 맞춰 정렬해서 생성: 인위적으로 좋은 cf 를 위해..

CREATE TABLE custs2
AS 
SELECT * FROM CUSTOMERS
ORDER BY CUST_CITY; -- FOR GOOD CLUSTERING FACTOR

CREATE INDEX CUST2_IX01 ON CUSTS2(CUST_CITY);

-- CLUSTERING_FACTOR = 1650 -> 블록개수의 근접 = 좋은 클러스터링 팩터임
SELECT INDEX_NAME, BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR
FROM USER_INDEXES
WHERE INDEX_NAME = 'CUST2_IX01';

-- COST = 405 비슷하네
SELECT /*+ full(c) */ * 
  FROM custs2 c
 WHERE cust_city = 'Los Angeles'; 
 @XPLAN
 /*
 --------------------------------------------------------------------------------------
| Id  | Operation         | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |      1 |        |    200 |00:00:00.01 |     810 |
|*  1 |  TABLE ACCESS FULL| CUSTS2 |      1 |     90 |    200 |00:00:00.01 |     810 |
--------------------------------------------------------------------------------------
 */

-- COST = 4.. ?! -> 클러스터링 팩터가 엄청 좋아짐
SELECT /*+ index(c CUST2_IX01) */ * 
  FROM custs2 c
 WHERE cust_city = 'Los Angeles'; 
  @XPLAN
  /*
------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |      1 |        |    200 |00:00:00.01 |       8 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CUSTS2     |      1 |     90 |    200 |00:00:00.01 |       8 |
|*  2 |   INDEX RANGE SCAN                  | CUST2_IX01 |      1 |     90 |    200 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------------------------  
  */

• CUST_CITY 컬럼에 정렬하여 데이터를 insert 했더니 코스트가 무려 853 -> 4 로 줄어든 모습을 확인할 수 있다

2) 클러스터링 팩터 확인

-- CLUSTERING_FACTOR = 1650 -> 블록개수의 근접 = 좋은 클러스터링 팩터임
SELECT INDEX_NAME, BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR
FROM USER_INDEXES
WHERE INDEX_NAME = 'CUST2_IX01';