ORACLE BALANCE of CLUSTERING FACTOR
from
1.
CLUSTERING FACTOR 개요
A.
INDEX 를 FULL SCAN 한다고 했을 때
INDEX의 특징 (값이 SORT되어있다.) 값에대해 순차적으로 접근하게 된다. 이 때 실제값을 가지고 오기
위해 블록에 접근을 하게 된다. 이때마다 카운트가 하나씩 올라간다. 때문에
가장 이상적인 클러스터링 팩터값은 블록의 수만큼 이며 이와 반대로 비이상적인 경우는 ROW의 수만큼의
값을 가지는 경우이다.
2.
CLUSTERING FACTOR 의 균형관계
A.
한 테이블에 대해서 인덱스가 두개 있다고 가정하자. 두개의 인덱스가 한
컬럼에 대해 만들어지는 경우는 보통 없고 각기 다른 컬럼을 가지고 만들어 질 것이다. 추가적으로 A 컬럼의 정렬상태와 B 컬럼의 정렬상태는 관련성이 없다고 치자. A컬럼에 대해 정렬되어있는 테이블이 된다고 하면 B 컬럼의 정렬상태는
상대적으로 안좋아 질 것이다.
3.
CLUSTERING FACTOR 의 균형관계 예제
SQL> conn hr/hr
Connected.
SQL> create table cf_samp
2 as select first_name,last_name from
employees;
*hr user의 employees 를
사용하였다.
Table created.
SQL> create index cf_samp_first_name
on cf_samp(first_name);
Index created.
SQL> create index cf_samp_last_name
on cf_samp(last_name);
Index created.
*두개의 컬럼에 대해 각기 인덱스를 생성하였다.
SQL> analyze table cf_samp compute
statistics;
Table analyzed.
SQL> select index_name,
clustering_factor
2 from dba_indexes
3 where owner = 'HR'
4 and table_name = 'CF_SAMP';
INDEX_NAME CLUSTERING_FACTOR
------------------------------
-----------------
CF_SAMP_FIRST_NAME 1
CF_SAMP_LAST_NAME 1
*데이터가 적어 한블록에 다 수용되어있는 상태이다.
SQL> save CF
Created file CF.sql
SQL> desc cf_samp
Name
Null? Type
-----------------------------------------
-------- ----------------------------
FIRST_NAME
VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
SQL> alter table cf_samp modify
(first_name varchar2(50), last_name varchar2(50));
Table altered.
*데이터를 더 넣기위해 우선 컬럼사이즈를 변경하였다.
SQL> begin
2 for i in 1..10 loop
3 insert into cf_samp select last_name||i, first_name||i
from cf_samp;
4 end loop;
5 end;
6 /
PL/SQL procedure successfully
completed.
*데이터를 추가적으로 입력하였다.
SQL> commit;
Commit complete.
SQL> select count(*) from cf_samp;
COUNT(*)
----------
110592
SQL> analyze table cf_samp compute
statistics;
Table analyzed.
SQL> @CF
INDEX_NAME CLUSTERING_FACTOR
------------------------------
-----------------
CF_SAMP_FIRST_NAME 103943
CF_SAMP_LAST_NAME 103937
*거의 row 수만큼
블록엑세스가 일어나는 걸 확인하였다. 이를 클러스터링 팩터가 나쁘다라 한다.
SQL> col SEGMENT_NAME for a30
SQL> select segment_name,sum(blocks)
2 from user_segments
3 where segment_name = 'CF_SAMP'
4 group by segment_name;
SEGMENT_NAME SUM(BLOCKS)
------------------------------
-----------
CF_SAMP 960
* used block 수를 조회한 결과이다.
SQL> create table cf_bak as select *
from cf_samp;
Table created.
*데이터를 백업할 테이블을 만들었다.
SQL> commit;
Commit complete.
SQL> cl scr
SQL> truncate table cf_samp;
Table truncated.
*cf_samp 에 정렬된 값을 넣기 위해 내용을 비웠다.
SQL> desc cf_samp
Name
Null? Type
-----------------------------------------
-------- ----------------------------
FIRST_NAME
VARCHAR2(50)
LAST_NAME NOT NULL
VARCHAR2(50)
SQL> insert into cf_samp
2 select first_name, last_name
from cf_bak
3 order by 1 asc;
110592 rows created.
*first_name 에 대해 값을 오름차순으로 정렬해 입력하였다.
SQL> commit;
Commit complete.
SQL> analyze table cf_samp compute
statistics;
Table analyzed.
SQL> @CF
INDEX_NAME CLUSTERING_FACTOR
------------------------------
-----------------
CF_SAMP_FIRST_NAME 953
CF_SAMP_LAST_NAME 21087
*cf_samp_first_name 의 clustering
factor 가 크게 개선된 걸 볼 수 있다.
SQL> select segment_name,sum(blocks)
2 from dba_extents
3 where segment_name = 'CF_SAMP'
4 group by segment_name;
SEGMENT_NAME SUM(BLOCKS)
------------------------------
-----------
CF_SAMP 960
SQL> save seg
Created file seg.sql
SQL> alter index cf_samp_first_name
rebuild;
Index altered.
SQL> alter index cf_samp_last_name
rebuild;
Index altered.
*혹시나 하는 마음에
index rebuild 를 수행하고 재확인하였다.
SQL> analyze index
cf_samp_first_name compute statistics;
Index analyzed.
SQL> analyze index cf_samp_last_name
compute statistics;
Index analyzed.
*통계치도 재수집하였다.
SQL> @CF
INDEX_NAME CLUSTERING_FACTOR
------------------------------
-----------------
CF_SAMP_FIRST_NAME 953
CF_SAMP_LAST_NAME 21087
*달라진 값이 없다.
*위의 과정을
last_name 에 대해 다시 진행해보았다.
SQL> truncate table cf_bak;
Table truncated.
SQL> insert into cf_bak
2 select * from cf_samp;
110592 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from cf_samp;
COUNT(*)
----------
110592
SQL> select count(*) from cf_bak;
COUNT(*)
----------
110592
SQL> truncate table cf_samp;
Table truncated.
SQL> desc cf_samp
Name
Null? Type
-----------------------------------------
-------- ----------------------------
FIRST_NAME
VARCHAR2(50)
LAST_NAME NOT NULL
VARCHAR2(50)
SQL> desc cf_bak
Name
Null? Type
-----------------------------------------
-------- ----------------------------
FIRST_NAME VARCHAR2(50)
LAST_NAME NOT NULL
VARCHAR2(50)
SQL> insert into cf_samp
2 select first_name,last_name
from cf_bak
3 order by last_name asc;
110592 rows created.
SQL> commit;
Commit complete.
SQL> analyze table cf_samp compute
statistics;
Table analyzed.
SQL> @CF
INDEX_NAME CLUSTERING_FACTOR
------------------------------
-----------------
CF_SAMP_FIRST_NAME 21090
CF_SAMP_LAST_NAME 965
*아까의 경우와 거꾸로
last_name 의 clustering factor 가 향상되었다.
4.
Clustering Factor 에 대한 결론
A.
결과적으로 통상적으로 한 테이블에 대해 두개이상의 인덱스가 있다고 할 때 두 개 이상의 인덱스가 좋은 클러스터링
팩터를 가지기란 어렵다. 통상적으로란 의미는 두 값이 별다른 관계를 가지고 있지 않다라는 이야기를 의미한다.