3.1.2 Index Clustering Factor

클러스터링 팩터 (Clustering Factor)는 '군집성 계수'쯤으로 번역할 수 있는 용어이다.

 

특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미한다.

CF가 좋은 컬럼에 생성한 인덱스는 검색 효율이 매우 좋다.
예를 들어 [거주지역='제주'] 에 해당하는 고객 데이터가 물리적으로 접근해 있으면 데이터를 찾는 속도가 빠르다

 

위 그림은 클러스터링 팩터가 가장 좋은 상태를 도식화 한 것이다.
'인덱스 레코드 정렬 순서와 테이블 레코드 정렬 순서가 100% 일치하는 것을 확인할 수 있다.'

 

이것은 가장 안좋을 때이다. 레코드 정렬 순서와 테이블 레코드 정렬 순서가 전혀 일치하지 않는다.

 

 

테스트 (오라클 VS 티베로)
내림차순으로 정렬한 T1 테이블과 랜덤데이터로 저장한 T3 테이블의 CF를 확인한다.

SQL> CREATE TABLE T1 (C1 NUMBER, C2 VARCHAR(20)); SQL> CREATE TABLE T3 (C1 NUMBER, C2 VARCHAR(20)); SQL> INSERT INTO T1 SELECT LEVEL, 'test' FROM DUAL CONNECT BY LEVEL <= 10000;   SQL> INSERT INTO T3 SELECT TRUNC(DBMS_RANDOM.VALUE(1,10000)), 'test' FROM DUAL CONNECT BY LEVEL <= 10000; SQL> CREATE INDEX IDX_T1 ON T1(C1); SQL> CREATE INDEX IDX_T3 ON T3(C1); 티베로 SQL> SELECT TABLE_NAME, INDEX_NAME, CLUSTERING_FACTOR      FROM   DBA_INDEXES      WHERE  OWNER='TIBERO'; TABLE_NAME                     INDEX_NAME                     CLUSTERING_FACTOR ------------------------------ ------------------------------ ----------------- T3                             IDX_T3                                      .899 T1                             IDX_T1                                      .002 ---------------------------------- 플랜 확인 ----------------------------------- SQL> SELECT * FROM T1 WHERE C1 > 500 AND C1 < 2000; Execution Plan -----------------------------------------------------------------------------------------------------      1  TABLE ACCESS (ROWID): T1 (Cost:13, %%CPU:0, Rows:2773)    2    INDEX (RANGE SCAN): IDX_T1 (Cost:7, %%CPU:0, Rows:2773)   SQL> SELECT * FROM T3 WHERE C1 > 500 AND C1 < 2000;   Execution Plan -----------------------------------------------------------------------------------------------------      1  TABLE ACCESS (ROWID): T3 (Cost:2816, %%CPU:0, Rows:3123)    2    INDEX (RANGE SCAN): IDX_T3 (Cost:7, %%CPU:0, Rows:3123) 오라클 TABLE_NAME                     INDEX_NAME           CLUSTERING_FACTOR                                                                                                                                                                                                       ------------------------------ -------------------- -----------------                                                                                                                        T1                             IDX_T1                                20                                                                                                                         T3                             IDX_T3                            9447 오라클은 클러스터링 팩터가 테이블 블록 수에 가까울 수록 좋고, 테이블 레코드수에 가까울 수록 나쁘다.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

COST 값 자체가 달라지는 것을 확인할 수 있다.

 

Q: 테이블 정렬 순서는 달라도 인덱스에서 정렬되서 저장되는데 CF에 대한 확인이 필요한가?

 

A: 상단 PLAN을 보면 TABLE ACCESS (ROWID)를 통해서 테이블 블록에 접근하게 된다.

즉, 인덱스 C1 컬럼을 통해 만들어진 인덱스를 통해 ACCESS 조건을 잡고
실제 C2컬럼의 값을 확인하기 위해 접근하는 과정이다.

 

연속된 ROWID면 같은 블록을 읽으면 되지만, 흩어져 있으면 블록을 여러 개 I/O로 읽어야 한다.

 

 

Q: CF가 좋은 컬럼에 생성한 인덱스는 검색 효율이 좋다고 했는데
이는 테이블 액세스량에 비해 블록 I/O가 적게 발생하는 것을 의미한다.
인덱스 레코드마다 테이블 레코드를 건건이 블록 단위로 I/O 한다면, CF가 달라도 블록 I/O 발생량이 차이가 없어야 하는거 아닌가?

A: 테이블 액세스 시 오라클은 [래치 획득+ 해시 체인 스캔] 과정을 거쳐 찾아간 테이블 블록에 대한 포인터를 바로 해제하지 않고 일단 유지한다. 버퍼 Pinning 이라고 한다.

 

이 상태에서 다음 인덱스 레코드를 읽었는데 '직전과 같은 ' 테이블 블록을 가리키면 위의 논리 I/O과정을 생략할 수 있다.

 

 

 

 

티베로는

 

 

 

 

 

 

굵은 실선이 실제 Block I/O가 발생하는 경우고

가는 점선은 블록을 찾아가는 과정(논리 I/O) 없이 포인터로 바로 액세스 하는 경우이다.

 

 

 

 

 

 

 

 

실제 예시

 

 

'서비스번호' 단일 컬럼으로 구성된 인덱스 (로밍렌탈_N2)를 사용하여 조회한다.

 

사용여부 'Y'인 것을 필터링 하고 SELECT LIST 컬럼을 확인하기 위해 (TABLE ACCESS BY INDEX ROWID 로밍렌탈) 의 블록 I/O가  265,957(266968-1011)개 발생했다.

 

이를 통해서 인덱스 클러스터링 팩터가 매우 안좋은 상태임을 알 수 있다.

 

데이터량이 워낙 많다보니 서비스번호 조건을 만족하는 데이터가 뿔뿔이 흩어져 있는 것이라고 보면 된다.

 

* 블록 I/O는 우측의 CR값을 확인하면 된다

  위의 예제에서는 사용여부 컬럼을 추가함으로서 사용여부 Y를 체크하는 과정에서 대부분 거를 수 있다.