테이블 접근 비용 줄이기

클러스터링 팩터와 인덱스 손익분기점, 조건 컬럼 추가 전후의 테이블 접근 및 버퍼 수 비교

테이블 랜덤 액세스클러스터링 팩터CLUSTERING_FACTOR인덱스 손익분기점인덱스 컬럼 추가커버링 인덱스

#핵심 요약

클러스터링 팩터는 인덱스 정렬 순서와 테이블 저장 순서가 얼마나 비슷한지 나타냄
값이 테이블 블록 수에 가까울수록 좋고, 행 수에 가까울수록 나쁨

클러스터링 팩터가 나쁘면 같은 범위를 읽어도 테이블 블록을 훨씬 많이 방문하고, 옵티마이저는 인덱스 대신 Full Table Scan을 고를 수 있음

조회 건수가 늘면 어느 지점부터 인덱스보다 Full Table Scan이 유리해짐(인덱스 손익분기점)
클러스터링 팩터가 나쁘면 이 지점이 앞당겨짐

인덱스에 조건 컬럼을 더하면 테이블에 가기 전에 인덱스에서 걸러 테이블 랜덤 액세스가 줄어듦
대신 인덱스가 커지고 INSERT, UPDATE, DELETE 부하가 늘어남

#개념과 동작 원리

클러스터링 팩터를 비용 계산에 쓰는 실습 식은 옵티마이저와 비용에 있음

실습 손익분기점 설명: 인덱스는 테이블 블록을 한 번에 하나씩 읽는 랜덤 액세스(Single Block I/O)라 건수가 늘수록 부담이 커지고, Full Table Scan은 한 번에 여러 블록을 읽는 시퀀셜 액세스(Multiblock I/O)라 대량 조회에서 장점이 커짐

#필수 명령어 또는 SQL

#클러스터링 팩터 비교

SQL*Plus (DBA_OBJECTS 조회 가능 계정)객체 또는 설정을 변경함

CREATE TABLE emp5000 PCTFREE 0 AS
SELECT t2.*, LPAD('x', 630) AS x FROM dba_objects t2 ORDER BY object_id;          -- 정렬 저장

CREATE TABLE emp6000 PCTFREE 0 AS
SELECT t2.*, LPAD('x', 630) AS x FROM dba_objects t2 ORDER BY DBMS_RANDOM.VALUE;  -- 무작위 저장

CREATE INDEX emp5000_object_id ON emp5000 (object_id);
CREATE INDEX emp6000_object_id ON emp6000 (object_id);

SQL*Plus (실습 계정)조회만 함

SELECT i.index_name, t.blocks, i.clustering_factor, t.num_rows
  FROM user_indexes i, user_tables t
 WHERE i.table_name = t.table_name
   AND t.table_name IN ('EMP5000', 'EMP6000');

결과 해석: EMP5000_OBJECT_ID의 클러스터링 팩터가 BLOCKS에 가까우면, 인덱스 키 순서로 읽을 때 연속된 엔트리가 같은 블록이나 인접 블록에 몰려 있다는 뜻임
통계가 없으면 값이 비어 있음

SQL*Plus (실습 계정)조회만 함

SELECT object_id, object_name FROM emp5000 WHERE object_id BETWEEN 1 AND 7334;   -- 실습 898
SELECT object_id, object_name FROM emp6000 WHERE object_id BETWEEN 1 AND 7334;   -- 실습 7411 (Full Table Scan)
SELECT /*+ index(e emp6000_object_id) */ object_id, object_name
  FROM emp6000 e WHERE object_id BETWEEN 1 AND 7334;                             -- 실습 7420

결과 해석:

실습 기록상 전체 행은 73340건이며, 조건은 그 약 10%임

#손익분기점

SQL*Plus (실습 계정)객체 또는 설정을 변경함

CREATE TABLE emp_large AS
SELECT ROWNUM AS empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm,
       MOD(ROWNUM, 100) + 10 AS deptno
  FROM emp e, (SELECT LEVEL FROM dual CONNECT BY LEVEL <= 10000);

CREATE INDEX idx_emp_large_deptno ON emp_large (deptno);

SQL*Plus (실습 계정)조회만 함

SELECT * FROM emp_large WHERE deptno = 10;                                  -- 결과: Full Table Scan, cost 238
SELECT /*+ index(e idx_emp_large_deptno) */ * FROM emp_large e WHERE deptno = 10;  -- 결과: cost 850

결과 해석: 14만 건 중 1400건(1%)인데도 Full Table Scan이 더 쌌음
emp 14행을 1만 번 복제해 deptno 값마다 행이 테이블 전체에 흩어져 있기 때문으로 보임(해석, 클러스터링 팩터 값은 확인 불가)
실습에서는 이어서 ename처럼 값이 적게 걸리는 컬럼에 인덱스를 걸어 힌트 없이 인덱스를 타는지 확인하게 했음

#인덱스 컬럼 추가

SQL*Plus (실습 계정)객체 또는 설정을 변경함

CREATE INDEX m_salegb_salemm ON mcustsum (salegb, salemm);

SQL*Plus (실습 계정)조회만 함

SELECT /*+ index(t m_salegb_salemm) */ COUNT(*)
  FROM mcustsum t
 WHERE salegb = 'A' AND saleamt BETWEEN 1000 AND 2000;   -- 실습 4111

SQL*Plus (실습 계정)데이터 또는 파일을 삭제함, 되돌릴 수 없음

DROP INDEX m_salegb_salemm;
CREATE INDEX m_salegb_salemm ON mcustsum (salegb, salemm, saleamt);

결과 해석: 같은 SQL의 버퍼가 4111에서 18로 줄었음
saleamt가 인덱스에 들어가 테이블에 가지 않고 인덱스에서 조건을 확인할 수 있게 되었기 때문임
mcustsum 생성 스크립트는 스캔 범위와 필터 조건과 같음(실습은 salemm 접두어만 2008)

연습 문제에도 같은 원리를 적용함: emp_large(deptno) 인덱스로 deptno = 30 AND sal >= 4500을 찾던 것을 emp_large(deptno, sal)로 다시 만듦
조회 컬럼까지 모두 인덱스에 넣어 테이블 방문을 없애는 예는 순차 접근과 랜덤 접근에 있음