1. 인덱스 컬럼 추가
SELECT /*+ index(emp emp_x01) */ *
FROM emp
WHERE depno = 30
AND sal >= 2000
- emp 테이블에 현재 PK 이외에 [depno + job] 순으로 구성된 emp_x01 인덱스 하나만 있는 상태다.
테이블 액세스가 발생하게 된다.
인덱스 구성을 [depno + sal] 순으로 바꿔주면 좋겠지만 실 운영 환경에서는 인덱스 구성을 함부로 바꾸기가 쉽지 않다.
인덱스를 새로 만들어야겠지만 인덱스를 추가해 나가다 보면 테이블마다 인덱스가 수십 개씩 달려 배보다 배꼽이 더 커지게 된다.
인덱스 관리 비용이 증가함은 물론 DML 부하에 따른 트랜잭션 성능 저하가 생길 수 있음을 예상할 수 있다.
- 이럴 때 기존 인덱스에 sal 컬럼을 추가하는 것만으로 큰 효과를 거둘 수 있다.
인덱스 스캔량은 줄지 않지만 테이블 Random 액세스 횟수를 줄여주기 때문.
2. PK 인덱스에 컬럼 추가
- NL 조인할 때 Inner 쪽(=right side)에서 엑세스될 때는 Random 액세스 부하가 만만치 않다.
특히 Outer 테이블에서 Inner 테이블 쪽으로 조인 액세스가 많은 상황에서 Inner 쪽 필터 조건에 의해 버려지는 코드가 많다면, 비효율은 매우 심각한 것일 수 있다.
- 아래 쿼리는 emp를 기준으로 NL 조인하고, 조인에 성공한 데이터 중 loc = 'NEW YORK'인 레코드만 취하므로 필터 조건에 의해 버려지게 된다.
-
SELECT /*+ ordered use_nl(d) */ *
FROM emp e, dept d
WHERE d.deptno = e.deptno
AND d.loc = 'NEW YORK'
- dept_pk 인덱스에 loc 컬럼을 추가하면 불필요한 Random 액세스를 없앨 수 있지만 PK 인덱스에는 컬럼을 추가할 수 없다.
그러다 보니 [PK컬럼 + 필터조건 컬럼] 형태의 새로운 Non-Unique 인덱스룰 추가하는 경우가 있다.
그럴 때 Non-Unique 인덱스를 이용해 PK 제약을 설정한다면 인덱스 개수를 줄일 수 있다.
-
ALTER TABLE dept DROP PRIMARY KEY;
CREATE INDEX dept_x01 ON dept(deptno, loc);
ALTER TABLE dept ADD
CONSTRAINT dept_pk PRIMARY KEY (deptno) USING INDEX dept_x01;
- 위와 같이 PK 제약과 인덱스를 다시 만들고 SQL을 수행하면, 조인에 성공하고서 필터 조건 체크까지 완료된 3건만 dept 테이블을 엑세스한다.
PK 제약을 위해 사용되는 인덱스는 PK 제약 순서와 서로 일치 하지 않아도 상관없다.
PK 제약 컬럼들이 선두에 있기만 하면 된다.
3. 컬럼 추가에 따른 클러스터링 팩터 변화
- 변별력이 좋지 않은 컬럼 뒤에 변별력이 좋은 다른 컬럼을 추가할 때는 클러스터링 팩터 변황에 주의를 기울여야 한다.
4. 인덱스만 읽고 처리
- 테이블 Random 액세스가 많더라도 필터 조건에 의해 버려지는 레코드가 거의 없다면 비효율은 없다.
이 때는 아예 테이블 엑세스가 발생하지 않도록 모든 필요한 컬럼을 인덱스에 포함시키는 방법을 고려해 볼 수 있다.
MS-SQL Server에서 사용하는 용어긴 하지만 그런 인덱스를 'Covered 인덱스'라 부른다.
5. 버퍼 Pinning 효과 활용
SELECT /*+ ordered use_nl(b) rowid(b) */ b.*
FROM (SELECT /*+ index(emp emp_pk) no merge */ rowid rid
FROM emp
ORDER BY rowid) a, emp b
WHERE b.rowid = a.rid
- 위 쿼리는 emp_pk 인덱스 전체를 스캔해 얻은 레코드를 rowid 순으로 정렬한 다음(-> 이 중간집합의 CF는 가장 완벽하게 좋은 상태가 됨)
오라클의 경우, 한번 입력된 테이블 레코드는 절대 rowid가 바뀌지 않는다.
- 위 처럼 User ROWID에 의한 테이블 엑세스시에도 버퍼 Pinning 효과가 나타난다면 어떨까?
Random 액세스 비효율은 한 건을 읽기 위해 블록을 통째로 읽기 때문에 발생하는 것 인데,
위 같은 쿼리에 버퍼 Pinning 효과가 나타나면 한 번 액세스로 블록 안에 있는 모든 레코드를 다 읽어 들이는 셈이 된다.
CF가 가장 좋을 때 인덱스 손익분기점이 90% 이상에서 결정되는 것도 그 때문이다.
- 따라서 인덱스를 통해 아무리 많은 테이블 레코드를 액세스하더라도 Random 액세스에 의한(I/O 측면에서의) 비효울은 거의 존재하지 않게 된다.
6. 수동으로 클러스터링 팩터 높이기
- 인위적으로 CF를 높일 목적으로 테이블을 Reorg 한다.
실시간 정렬이 필요한 경우, IOT(클러스터드 인덱스를 사용한 테이블)
실시가 정렬이 필요하지 않은 경우, 일반 인덱스를 이용하고 특정 주기마다 Reorg.
댓글