📔설명

인덱스를 이용할 때 어떤 과정을 거치고, 어떻게 활용하는게 좋을지 알아보자

---

🍔인덱스를 이용한 테이블 액세스 최적화

• 읽고자 하는 블록이 메모리(DB버퍼 캐시)에 없으면, 메모리의 어디에 위치시킬지 빈 공간(프리 버퍼)를 찾아 해당 공간에 디스크에서 찾은 블록을 적재

• 클러스터링 팩터(데이터가 모여 있는 정도)가 좋은 경우 버퍼 피닝 효과 발생

• 클러스터링 팩터 값이 블록 수에 가까우면 좋은 것

• 클러스터링 팩터 값이 로우 수에 가까우면 나쁜 것

• 인덱스를 통해 다음 행의 블록 번호가 바뀔 때 1씩 증가 -- 클러스터링 팩터

---

🥞인덱스 튜닝

비효율 판단

• 비교적 적은 인덱스 블록을 읽어 데이터를 추출하고 테이블 방문 후에 결과 건수가 크게 변하지 않았다면 비효율 X
• 인덱스에서 읽은 건수(A-Rows)가 테이블 방문 후 크게 줄었다면 비효율 O
• 많은 양의 인덱스 블록을 읽어, 적은 양의 데이터 추출한 경우 비효율 O

기존 인덱스에 컬럼 추가

ex) 상품구분코드+상품명 인덱스가 존재할 때

select *
from 상품
where 상품구분코드='100101' 
and 판매여부='Y';

기존 인덱스 사용 시, 테이블 액세스를 최종 결과 집합에 해당되는 만큼 액세스

-> (변경) 상품구분코드+상품명+판매여부 인덱스

테이블 랜덤 액세스를 한 번만 함

-- 주문일자+주문시간 인덱스

select /*+ gather_plan_statistics */ count(upper_case)
from ord
where ord_dt between '20120101' and '20120228'
and shop_no='SH0001';

인덱스에 매장번호가 없기 때문에, 테이블에 방문하여 필터로 처리하였다.
125만건의 테이블을 방문하여, 6289건이 최종 결과 집합이 된다.

위 경우 약 124만건이 의미 없는 테이블 방문이므로, 인덱스에 shop_no 컬럼을 추가하는 것을 고려

• 인덱스 컬럼 명 추가를 위해선 인덱스 삭제 후 재생성

PK 인덱스 확장

• 기존 PK 컬럼들을 선두에 놓아야 함

-- PK 인덱스 변경

alter table 테이블명 add constraint PK명 PRIMARY KEY(PK컬럼들) USING INDEX 인덱스명;

• 조인 시 PK 인덱스에 컬럼을 포함시키면, 조인 컬럼 외에 select 컬럼을 위해 테이블에 방문하는 상황을 막을 수 있음

--ITEM_PK 인덱스가 item_id만 존재

select oi.ord_no, oi.item_id, i.item_nm, oi.ord_item_qty
from ord_item oi, item i
where ord_dt between '20120101' and '20120110'
and i.item_id=oi.item_id;

-- -->테이블 액세스 존재



--ITEM_PK 인덱스가 item_id+item_nm으로 구성

select oi.ord_no, oi.item_id, i.item_nm, oi.ord_item_qty
from ord_item oi, item i
where ord_dt between '20120101' and '20120110'
and i.item_id=oi.item_id;

--select의 i.item_nm을 위해 테이블에 방문하는 상황 발생X (테이블 액세스 발생X)

인덱스에서만 처리

• SQL문에서 사용하는 모든 컬럼을 인덱스에 포함

select ord_dt, item_id, sum(ord_item_qty)
from ord_item
where ord_dt between '20120101' and '20120131'
group by ord_dt, item_id;

-- 인덱스 ord_dt+ord_hms일 땐 테이블 액세스 존재
-- 인덱스 ord_dt+ord_hms+item_id+ord_item_qty 일 경우 테이블 액세스 X

---

🥫인덱스 스캔 범위 계산

• 인덱스를 너무 많이 스캔하고, 스캔한 결과에서 일부만 결과 집합이 된다면 인덱스 범위 스캔이 잘못되었는지 체크 해야 함

• 등치(=)조건과 범위 조건이 섞인 상태에서 첫 번째 범위까지는 원하는 인덱스 스캔을 하지만, 범위 조건 뒤 부터는 원하지 않는 구간까지 스캔

where A=:a
and B=:b
and C between :c1 and :c2
and D=:d

-- A, B, C 컬럼은 원하는 구간을 찾아가지만 D는 A,B,C 만족하는 모든 인덱스를 스캔하고
-- 마지막에 결과 집합에 해당되는지만 체크하는 필터 값으로 사용됨

• 인덱스는 주로 등치 조건이 먼저 나오도록 구성해주는 것이 이득

---

🥞인덱스 설계 기준 및 전략

• 등치(=) 조건 으로 사용되고 자주 사용되는 컬럼을 앞
• 등치(=) 조건 으로 사용되고 값의 수가 적은 컬럼을 앞
• 자주 사용되는 컬럼 선정
• 테이블 랜덤 액세스 줄이기
• 정렬 대신