3.1 테이블 엑세스 최소화
를 위해 사용하는 인덱스 튜닝 기법
사전지식
1. ROWID란?
테이블 레코드를 빨리 찾아가기 위한 논리적 주소값
=DBA(데이터파일번호+블록번호)+ROW번호
포인터 같은 물리적 주소는 아님 X : 테이블 레코드와 물리적으로 직접 연결되어있지 않음 !
WHY?
데이터블록이 수시로 버퍼캐시에서 밀려났다가 다시 캐싱되므로, 그때마다 다른공간에 캐싱되기 때문에 인덱스에서 포인터로 직접 메모리주소를 연결할 수 없다.
SO,
그래서 데이터블록주소정보(DBA) 이용해 해시알고리즘으로 버퍼블록 찾아감
2. 인덱스 ROWID 이용한 테이블 엑세스는 생각보다 고비용 구조이다
<순서>
1) 인덱스의 리프블록에서 ROWID 읽고 분해해서 DBA 값 알기
2) 이 DBA를 해시함수에 넣고-> 결과값으로 해시체인을 찾고-> 거기서 버퍼헤더를 찾는다
3) 버퍼헤더에 실제 데이터가 담긴 버퍼블록 메모리 주소값이 있다 !
4) 메모리주소값을 따라가면 버퍼캐쉬에 해당 블럭이 있음.
5) 만약 2번에서 찾는 블록주소가 해시체인에 없다면 메모리에도 없다는 뜻이므로 디스크 I/O 발생하여 해당블록을 디스크에서 읽어 메모리에 적재한 후 읽는다 (해시체인에도 등록)
인덱스 클러스터링 팩터
특정 컬럼을 기준으로 '같은 값'을 가진 데이터가 서로 모여서 저장되어있는 정도
비유를 들자면,
분가한 10명의 자녀가 모두 한동네에 모여살면
CF(클러스터링 팩터)가 좋아서
부모님이 자녀들 집 모두 방문하는데 하루면 충분하지만,,,,,,,,,,
각각 다른 동네에 떨어져살면 모두 방문하는데 수일이 걸릴것이다.!
인덱스클러스터링팩터 GOOD vs BAD 비교
-> 인덱스 클러스트링 팩터가 가장 좋은 상태
인덱스 정렬순서 == 레코드 정렬순서가 100% 일치
-> 인덱스 클러스트링 팩터가 안좋은 상태
클러스터링 팩터가 좋으면 블록 I/O가 적게 발생하는 이유
인덱스 레코드마다 테이블 레코드를 건건이 블록단위로 접근시도하는건 클러스터링 팩터와 관계없이 똑같으나, 실제 블록 I/O 갯수는 다르게 나타난다.
WHY?
Buffer Pinning 때문 !
인덱스 ROWID로 테이블 엑세스시, 1. 래치획득 -> 2. 해시체인스캔 -> 3. 테이블블록 찾아간 후
테이블 블록에 대한 포인터(메모리 주소값)을 바로 해제하지 않고, 일단 유지한다. 이것이 Buffer Pinning !
정의: 한번의 call 에서 2번이상 호출된 블록에 대해, 이에 접근하기 위한 메모리 버퍼주소를 버리지 않고 유지하는 것, 즉 fetch 하는 동안 특정 버퍼블록을 pin(이 블록은 메모리에서 치우지마!) 처리 하는것 ------(lock이랑은 다름 '물리적'보호 임)
다음 인덱스 레코드 읽었는데 마침 직전과 같은 테이블 블록을 가리킨다면, 논리적인 블록 I/O과정 생략하고 (래치획득, 해시체인스캔과정) 바로 테이블 블록 읽을 수 있음--V$BH 에서 상태확인 가능 SQL> desc v$BH COLUMN_NAME TYPE CONSTRAINT ---------------------------------------------------------------------------------------- ------------------ -------------------------------------------- FILE# VARCHAR(256) BLOCK# NUMBER BLKTYPE NUMBER STATUS VARCHAR(7) PIN_MODE VARCHAR(3) TEMP VARCHAR(1) DIRTY VARCHAR(1) TS# VARCHAR(256) OBJD VARCHAR(256)
인덱스 손익분기점
Index Range Scan에 의한 테이블 엑세스가 Table Full Scan 보다 느려지는 지점 = 인덱스 손익분기점 이라고 한다!
인덱스 ROWID를 이용한 테이블 엑세스는 생각보다 고비용 구조다.
따라서 읽어야 할 데이터가 일정량을 넘는 순간, 테이블 전체를 스캔하는것보다 오히려 느려진다.
WHY?
- 테이블 풀스캔 : 시퀀셜 엑세스 & 멀티블록 I/O
- 인덱스 ROWID 이용한 테이블 엑세스 : 랜덤 액세스 & 싱글블록 I/O
일반적으로 손익분기점은 5~20% 정도에서 결정되는 경우가 많음
(=찾으려는 데이터의 양이 전체 데이터양의 5~20%를 차지할때)
but, 클러스터링 팩터, 테이블 크기에 따라 손익분기점이 달라지기도 함
- 클러스터링 팩터가 good -> 손익분기점이 90% 까지 올라가기도 하고
- 테이블 크기 1000만건 이상 -> 손익분기점이 5% 이하로 내려가기도 함
-> 결론 : 인덱스 손익분기점에 따라 테이블 풀스캔이 더 나은 선택지가 될수도 있다.
인덱스 컬럼 추가
쿼리를 더 빠르게 처리하기 위해 인덱스 컬럼 추가하는 방식도 생각해볼수 있음
but, 배보다 배꼽이 더 커지는 상황 조심해야할것
- 인덱스 관리비용 증가
- DML 부하에 따른 트랜잭션 성능 저하 생길수 있음
인덱스만 읽고 처리
인덱스 컬럼 추가의 연장선으로, 쿼리에 사용된 컬럼을 모두 인덱스에 추가해서 테이블 엑세스가 아예 발생하지 않게 하는 방법!
효과는 좋으나, 추가해야할 컬럼이 많아 실제 적용하기에는 곤란한 경우가 많음
인덱스구조 테이블, 클러스터 테이블
인덱스 구조 테이블 = IOT (Index Organized Table)
-> 인덱스를 이용한 테이블 액세스가 고비용 구조라고 하니, 랜덤엑세스가 아예발생하지 않도록 테이블을 인덱스 구조로 생성하면 어떨까?
- 정의 : B*Tree 인덱스 안에 데이터까지 함께 저장,
- 데이터 저장위치: 인덱스 리프노드, 별도의 테이블 세그먼트 없이 인덱스 세그먼트만 존재
- 장점: 빠른 기본키 접근, 디스크 i/o 절감, 공간절약, 정렬유지, 클러스터된 상태로 저장
- 단점: 기본키 수정비용크고, rowid 기반 엑세스 불가하여 조인시 일반테이블보다 불리, dml 시 인덱스 재조정 필요하므로 dml 빈도가 높으면 부적합
=> 1. 기본키 중심으로 select 하고, 2. 작고, 자주쓰이는 읽기전용 정적테이블일 경우(ex. 국가코드, 제품분류코드 등) 유리함.create table T (a number, b number, constraint index_org_t_pk primary key (a)) organization index;
클러스터 테이블 : 2종류가 있다.
- 인덱스 클러스터 테이블
-> 클러스터 키값이 같은 레코드를 한 블록에 모아서 저장하는 구조
(정렬하지는 않는다)
-> 랜덤엑세스가 클러스터 인덱스 값 하나당 한번씩만 발생하고, 클러스터에 도달해서는 시퀀셜 방식으로 스캔하여 넓은 범위를 읽더라도 비효율이 없다.- 해시 클러스터 테이블
-> 해시 알고리즘 사용해 같은 해시값끼리 한 블록에 모아서 저장하는 구조
++그림이랑 실행계획 추후 추가하기
