CLUSTERING FACTOR

■ CLUSTERING FACTOR
1) 정의: 특정 컬럼을 기준으로 한 인덱스의 클러스터링 정도를 나타내는 지표. 서로 다른 행들의 데이터가 같은 블록에 모여 있는 정도를 나타난다.

2) 좋은 CLUSTERING_FACTO

- 해당 컬럼에 인덱스를 사용할 때 I/O를 최소화할 수 있다. Range Scan 등의 작업에서 성능 향상이 기대됨. (해당 컬럼에 대한 인덱스를 사용할 때 성능이 향상되는 효과를 기대할 수 있다.)

- Buffer Pinning이 수행되기 때문에 Latch를 점유하는 시간이 감소할 수 있다. 따라서 블록에 대한 접근이 더 효율적으로 이루어질 수 있다.
3) 예시 상황

- 파티션 분리: 데이터가 특정 범위 또는 순서에 따라 파티션으로 나뉘어 있거나, ORDER BY에서 사용되는 정렬에 적합한 컬럼에 인덱스를 적용한 경우 CLUSTERING_FACTOR가 좋을 수 있다.

□ CLUSTERING FACTOR 계산
1) 변수 선언: count 변수 선언
2) leaf 블록 스캔: Leaf 블록을 처음부터 끝까지 스캔하면서 각 행의 RowID로부터 해당 블록의 번호를 찾는다.
3) 블록 주소 비교: 찾은 블록 주소가 바로 직전의 블록 주소와 다를 때마다 `count` 변수를 1 증가시킨다.
4) CLUSTERING_FACTOR 계산: Leaf 블록의 끝까지 위 작업을 수행하면 `count` 변수에 있는 값을 CLUSTERING_FACTOR로 간주하고 해당 인덱스의 통계 정보에 저장한다.

#) CLUSTERING_FACTOR 좋은 경우 
- buffer pinning 돌아가는 경우

      leaf
------------------ 
|   id  |  rowid | 		   
|   1   |    A   | 
|   2   |    A   |
|   3   |    A   |
|   4   |    A   |
|   5   |    A   |
|   6   |    A   | 		

    A
---------
|   1   |
|   2   |
|   3   |
|   4   |
|   5   |

    B
---------
|   6   |

#2) CLUSTERING_FACTOR 좋지 않은 경우 
- buffer pinning 돌아가지 않을 때

       leaf
------------------ 
|   id  |  rowid |
------------------
|   1   |    A   |
|   2   |    B   |
|   3   |    A   |
|   4   |    B   |
|   5   |    A   |
|   6   |    B   |
------------------

    A
---------
|   1   |
|   3   |
|   5   |

    B
---------
|   2   |
|   4   |
|   6   |

 

---

[실습 해보기]

<hr sess>
#) 기본적으로 redo 생성이 된다. nologging 을 붙이면 redo 가 생성되지 않는다.

create table hr.c_table
nologging
as
select * from all_objects
order by object_id;

#) index 생성 

create index hr.c_obj_idx on c_table(object_id);
create index c_obj_name_idx on c_table(object_name);

 

#) 생성한 c_table의 기본적인 통계정보 확인 

=> 보이지 않음

select num_rows, blocks, avg_row_len
from user_tables
where table_name = 'C_TABLE';

  NUM_ROWS     BLOCKS AVG_ROW_LEN
---------- ---------- -----------

#) hr 스키마의 c_table 테이블에 대한 통계를 수집

execute dbms_stats.gather_table_stats('hr','c_table')

 

#) 생성한 c_table의 기본적인 통계정보 다시 확인

select num_rows, blocks, avg_row_len
from user_tables
where table_name = 'C_TABLE';

  NUM_ROWS     BLOCKS AVG_ROW_LEN
---------- ---------- -----------
     68178       1009          98

#) 인덱스 정보 확인

select index_name, blevel, leaf_blocks, clustering_factor
from user_indexes
where table_name = 'C_TABLE';

INDEX_NAME                       ★BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ----------- -----------------
C_OBJ_IDX                 	            1         152               985
C_OBJ_NAME_IDX                          2         352             61813

#) 설명

- index_name: 인덱스의 이름
- blevel: 인덱스의 레벨 (B-tree 인덱스의 높이)
- leaf_blocks: 리프 블록의 수 (인덱스의 끝 부분에 위치한 블록)
- clustering_factor: 클러스터링 팩터 (인덱스의 클러스터링 정도를 나타내는 값)

 

#) 힌트 사용: /*+ gather_plan_statistics index(c c_obj_idx)*/

select /*+ gather_plan_statistics index(c c_obj_idx)*/ count(*)
from c_table c
where object_id >= 0
and object_name >= ' ';

  COUNT(*)
----------
     68178

#) 실행계획 확인

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  2tc3v10cbk00j, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(c c_obj_idx)*/ count(*) from
c_table c where object_id >= 0 and object_name >= ' '

Plan hash value: 1213134236

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |      1 |        |      1 |00:00:00.01 |    1138 |
|   1 |  SORT AGGREGATE              |           |      1 |      1 |      1 |00:00:00.01 |    1138 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| C_TABLE   |      1 |  68178 |  68178 |00:00:00.02 |    1138 |
|*  3 |    INDEX RANGE SCAN          | C_OBJ_IDX |      1 |  68178 |  68178 |00:00:00.01 |     153 |
----------------------------------------------------------------------------------------------------
-- 실제 row 수보다 buffers 수가 적은 이유? buffer pinning 발생 / latch 점유율이 적다.

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_NAME">=' ')
   3 - access("OBJECT_ID">=0)

 

#) 힌트 사용: /*+ gather_plan_statistics index(c c_obj_name_idx)*/ 

select /*+ gather_plan_statistics index(c c_obj_name_idx)*/ count(*)
from c_table c
where object_id >= 0
and object_name >= ' ';

  COUNT(*)
----------
     68178

#) 실행계획 확인

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  78hphzt12rd5j, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(c c_obj_name_idx)*/ count(*)
from c_table c where object_id >= 0 and object_name >= ' '

Plan hash value: 586128974

---------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |      1 |        |      1 |00:00:00.03 |   62167 |
|   1 |  SORT AGGREGATE              |                |      1 |      1 |      1 |00:00:00.03 |   62167 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| C_TABLE        |      1 |  68178 |  68178 |00:00:00.04 |   62167 |
|*  3 |    INDEX RANGE SCAN          | C_OBJ_NAME_IDX |      1 |  68178 |  68178 |00:00:00.01 |     354 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_ID">=0)
   3 - access("OBJECT_NAME">=' ')

#) 옵티마이저에서 선택하는 방법

select index_name, blevel, leaf_blocks, clustering_factor
from user_indexes
where table_name = 'C_TABLE';

INDEX_NAME                         BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ----------- -----------------
C_OBJ_IDX                               1         152               985 ★ 작은 것을 선택
C_OBJ_NAME_IDX                          2         352             61813