3.1 테이블 액세스 최소화

3.1.1 테이블 랜덤 액세스

인덱스 ROWID는 물리적 주소? 논리적 주소?

인덱스 스캔 후 반드시 테이블을 액세스하는데 실행계획에서 'TABLE ACCESS BY INDEX ROWID' 표시 부분이 여기에 해당된다.

SQL> from 고객 where 지역 = '서울';

Excution PLan
--------------------------------------------------------------------------------------------
0     SELECT STATEMENT Optimizer=ALL_ROWS
1  0    TABLE ACCESS BY INDEX ROWID OF '고객' (TABLE)
2  1      INDEX RANGE OF '고객_지역_IDX' (INDEX)

인덱스 ROWID는 메모리 상에서의 위치 정보인 포인터가 아니다. 데이터파일 번호, 오브젝트 번호, 블록 번호 같은 물리적 요소로 구성돼 있지만 물리적 주소라기보다 논리적 주소에 가깝다. 인덱스 ROWID는 논리적 주소다. 디스크 상에서 테이블 레코드를 찾아가기 위한 위치 정보를 담는다.

캐시에 적재할 때와 읽을 때 같은 해시 함수를 사용하므로 버퍼 헤더는 항상 같은 해시 체인에 연결된다. 반면, 실제 데이터가 담긴 버퍼 블록은 매번 다른 위치에 캐싱되는데, 그 메모리 주소값을 버퍼 헤더가 가지고 있다. 정리하자면, 해싱 알고리즘으로 버퍼 헤더를 찾고, 거기서 얻은 포인터로 버퍼 블록을 찾아간다.
ROWID는 디스크 상에서 테이블 레코드를 찾아가기 위한 논리적인 주소 정보다. ROWID가 가리키는 테이블 블록을 버퍼캐시에서 먼저 찾아보고, 못 찾을 떄만 디스크에서 블록을 읽는다. 물론 버퍼캐시에 적재한 후에 읽는다. 설령 모든 데이터가 캐싱돼 있더라도 테이블 레코드를 찾기 위해 매번 DBA 해싱과 래치 흭득 과정을 반복해야한다. 동시 액세스가 심할 때는 캐시버퍼 체인 래치와 버퍼 Lock에 대한 경합까지 발생한다. 이처럼 인덱스 ROWID를 이용한 테이블 액세스는 생각보다 고비용 구조다
(1장3절 복습 -> https://gyujingyujin.tistory.com/13?category=1262044)

메인메모리 DB와 인덱스 ROWID를 이용한 테이블 액세스 비교

메인 메모리 DB(MMDB)는 말 그대로 데이터를 모두 메모리에 로드해 놓고 메모리를 통해서만 I/O를 수행하는 DB다. 그런데 잘 튜닝된 OLTP 성 데이터베이스 시스템이라면 버퍼캐시 히트율이 99% 이상인데 메인 메모리 DB만큼 빠르지 않다. 이유는 오라클은 테이블 블록이 수시로 버퍼캐시에서 밀려 났다가 다시 캐싱되며, 그때마다 다른 공간에 캐싱되기 떄문에 인덱스에서 포인터로 직접 연결할 수 없는 구조다. 메모리 주소정보(포인터)가 아닌 디스크 주소 정보(DBA, Data BLock Address)를 이용해 해시 알고리즘으로 버퍼 블록을 찾아간다.

인덱스 ROWID는 우편주소, 메인 메모리DB가 사용하는 포인터는 전화번호

전화통신은 물리적으로 연결된 통신망을 이용하므로 전화번호를 누르면 곧바로 상대방과 통화할 수 있지만, 우편통신은 봉투에 적힌 대로 우체부 아저씨가 일일이 찾아다니는 구조이므로 전화와는 비교할 수 없이 느리다.

---

3.1.2 인덱스 클러스터링 팩터

클러스터링 팩터(Clustrering Factor, 이하 'CF')는 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미한다. CF가 좋은 컬럼에 생성한 인덱스는 검색 효율이 매우 좋다. 데이터가 근접해 있으면 흩어져 있을 때보다 데이터를 찾는 속도가 빠르기 때문이다.
CF가 좋은 컬럼은 인덱스 검색 시, 테이블 액세스량에 비해 블록 I/O가 적게 발생함을 의미한다. 인덱스 ROWID로 테이블을 액세스할 때, 오라클은 래치 획득과 해시 체인 스캔 과정을 거쳐 어렵게 찾아간 테이블 블록에 대한 포인터(메모리 주소값)를 바로 해제하지 않고 일단 유지한다. 이를 '버퍼 Pinning' 이라고 부른다.
이 상태에서 다음 인덱스 레코드를 읽었는데, 마침 '직전과 같은' 테이블 블록을 가리키면 래치 획득과 해시 체인 스캔 과정을 생략하고 바로 테이블 블록을 읽을 수 있다. 논리적인 블록 I/O 과정을 생략할 수 있는 것이다.

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

---

3.1.3 인덱스 손익 분기점

Index Rane Scan에 의한 테이블 액세스가 Table Full Scan보다 느려지는 지점을 '인덱스 손익분기점' 이라고 부른다.

• Table Full Scan은 시퀀셜 액세스인 반면, 인덱스 ROWID를 이용한 테이블 액세스는 랜덤 액세스 방식이다.
• Table Full Scan은 Multiblock I/O인 반면, 인덱스 ROWID를 이용한 테이블 액세스는 Single BLock I/O 방식이다.
• 손익분기점은 보통 5~20%의 낮은 수준에서 결정된다. 하지만 테이블 스캔이 항상 나쁜 것은 아니며, 바꿔 말해 인덱스 스캔이 항상 좋은 것도 아니다.

온라인 프로그램 튜닝 vs 배치 프로그램 튜닝

• 온라인 프로그램
  1. 소량 데이터를 읽고 갱신하므로 인덱스를 효과적으로 활용하는 것이 중요.
  2. 대부분 NL조인 방식 사용.
• 배치
  1. 대량 데이터를 읽고 갱신.
  2. 목표는 일부가 아닌 전체를 빠르게 처리.
  3. 대량 데이터를 빠르게 처리하려면 인덱스와 NL조인보다 Full Scan과 해시 조인이 유리하다.
     테이블 파티셔닝을 하는 이유는 결국 Full Scan을 빠르게 처리하기 위해서다.

---

3.4.1 인덱스 컬럼 추가

인덱스 컬럼 추가는 테이블 액세스 최소화를 위해 가장 일반적으로 사용하는 튜닝 기법이다.
EMP 테이블에 현재 PK 이외에 [DELPTNO + JOB] 순으로 구성한 EMP_X01 인덱스 하나만 있는 상태에서 아래 쿼리를 수행하면

select /*+ index(emp emp_x01) */
from    emp
where    deptno = 30
and        sal >= 2000

처럼 6건을 검색하게 된다. 인덱스를 [DEPTNO + SAL] 순으로 변경하는건 실 운영 환경에선 쉽지 않고, 새로 만들면 DML 부하에 따른 트랜잭션 성능 저하가 생길 수 있다. 이럴 때, 기존 인덱스에 SAL 컬럼을 추가하는 것만으로 큰 효과를 얻을 수 있다.(아래 그림)

---

3.1.5 인덱스만 읽고 처리

인덱스만 읽어서 처리하는 쿼리를 'Covered 쿼리' 라고 하며, 그 쿼리에 사용한 인덱스를 'Covered 인덱스' 라고 한다.
(page150 참고)

---

3.1.6 인덱스 구조 테이블

랜덤 액세스가 아예 발생하지 않도록 테이블을 인덱스 구조로 생성하는 방법을, 오라클에선 'IOT(Index-Organized Table' 라고 부르고 MS-SQL Server 는 '클러스터형(Clustered) 인덱스'라고 부른다.
리프블록에 테이블을 찾아가기 위한 ROWID를 갖는 일반 인덱스와 달리 IOT는 테이블 블록에 있어야 할 데이터를 인덱스 리프 블록에 모두 저장하고 있다.

테이블을 인덱스 구조로 만드는 구문

 create table index_org_t ( a number, b varchar(10), constraint index_org_t_pk primary key (a) )
 organization index;

IOT는 인덱스 구조 테이블이므로 정렬 상태를 유지하며 데이터를 입력한다. 또한, 인위적으로 클러스터링 팩터를 좋게 만드는 방법 중 하나다. 같은 값을 가진 레코드들이 100% 정렬된 상태로 모여 있으므로 핸덤 액세스가 아닌 시퀀셜 방식으로 데이터를 액세스한다.

---

3.1.7 클러스터 테이블

클러스터 테이블에는 인덱스 클러스터 와 해시 클러스터 2 가지가 있다.

인덱스 클러스터 테이블

인덱스 클러스터 테이블은 그림처럼 클러스터 키 (여기서는 deptno) 값이 같은 레코드를 한 블록에 모아서 저장하는 구조다. 한블록에 모두 담을 수 없을 때는 새로운 블록을 할당해서 클러스터 체인으로 연결한다.

여러 테이블 레코드를 같은 블록에 저장할 수도 있는데, 이를 '다중 테이블 클러스터'라고 부른다. SQL 서버나 Sybase에서 말하는 '클러스터형 인덱스'랑은 다르다. 클러스터형 인덱스는 IOT에 가깝고, 오라클 클러스터는 키 값이 같은 데이터를 같은 공간에 저장해 둘 뿐, IOT나 SQL Server의 클러스터형 인덱스처럼 정렬하지는 않는다.

인덱스 클러스터 테이블 구성 순서

1. 클러스터 생성
2. create cluster c_dept# ( deptno number(2) ) index;
3. 클러스터 인덱스 정의
4. create index c_dept#_idx on cluster c_dept#;
5. 클러스터 테이블 생성클러스터 인덱스토 일반 B-Tree 인덱스 구조를 사용하지만, 테이블 레코드를 일일이 가리키지 않고 해당 키 값을 저장하는 첫 번째 데이터 블록을 가리킨다는 점이 다르다. 즉, 일반 테이블에 생성한 인덱스 레코드 는 테이블 레코드와 1:1 대응 관계를 갖지만, 클러스터 인덱스는 위 그림에서 보듯 테이블 레코드와 1:M 관계를 갖는다. 따라서 클러스터 인덱스의 키값은 항상 Unique하다.
   클러스터에 도달해서는 시퀀셜 방식으로 스캔하기 때문에 넓은 범위를 읽더라도 비효율이 없다는게 핵심 원리다.
6. create table dept (
   deptno number(6) not null
   ,dname varchar2(14) not null
   ,loc varchar2(13) )
   cluster c_dept# (deptno );

해시 클러스터 테이블

인덱스 클러스터 테이블과 다른 점은 인덱스를 사용하지 않고 해시 알고리즘을 사용해 클러스터를 찾아간다는 점만 다르다.
(page157 참고)