순차 접근과 랜덤 접근

#핵심 요약

순차 접근은 이웃한 블록을 차례로 읽는 방식이고, 랜덤 접근은 ROWID가 가리키는 블록을 한 건씩 찾아가는 방식임

인덱스에서 조건에 맞는 ROWID를 찾는 것은 빠르지만, ROWID로 흩어진 테이블 블록을 읽는 테이블 랜덤 액세스는 결과 건수만큼 반복됨
실습에서는 이 횟수가 쿼리 성능을 결정짓는 가장 중요한 요소라고 정리했음

필요한 컬럼을 모두 인덱스에 담으면 테이블 랜덤 액세스가 일어나지 않음

연속된 블록은 한 번에 여러 블록을 읽는 Multiblock I/O로 빨리 읽을 수 있음

#개념과 동작 원리

ROWID는 데이터의 저장 위치를 나타내는 내부 표현임
행이 들어 있는 데이터 파일과 데이터 블록, 그 블록 안에서의 행 위치를 지정함

ROWID로 행을 찾는 것은 정확한 위치를 지정하므로 행 한 건을 가져오는 가장 빠른 방법임

블록이 이웃해 있으면 한 블록보다 큰 I/O 호출(multiblock read)로 읽을 수 있음
호출 크기는 1블록부터 DB_FILE_MULTIBLOCK_READ_COUNT에 지정한 블록 수까지임

ROWID는 버전에 따라 바뀔 수 있고 행은 이동할 수 있으므로, 위치에 기대어 데이터에 접근하는 방식은 권장하지 않음

설명: 결과 건수가 많아질수록 인덱스 방식이 불리해지는 이유는, ROWID로 물리적으로 흩어진 테이블 블록을 하나씩 읽는 랜덤 액세스가 건수만큼 반복되어 I/O 부담이 급격히 늘기 때문임

#실습: 어느 SQL이 랜덤 액세스가 더 많은가

실습에서는 ename = 'FORD'를 찾는 두 SQL을 비교했음
설명: emp_ename 인덱스로 찾으면 FORD가 1명이라 테이블 랜덤 액세스가 1번이고, emp_deptno 인덱스로 20번 부서를 찾으면 인덱스에서 4건을 읽어 테이블에 4번 찾아가 ename이 FORD인지 확인하므로 랜덤 액세스가 4번임

#주요 용어와 구성 요소

#필수 명령어 또는 SQL

CREATE INDEX emp_deptno ON emp (deptno);

-- 테이블 랜덤 액세스가 일어남 (주석: 4 block)
SELECT *
  FROM emp
 WHERE deptno = 20;

-- 인덱스만 읽음 (주석: 2 block)
SELECT deptno
  FROM emp
 WHERE deptno = 20;

결과 해석: 두 번째 SQL은 필요한 컬럼이 인덱스에 모두 있어 테이블을 방문하지 않음
실행계획에서 TABLE ACCESS BY INDEX ROWID 단계가 있는지로 구분함

#문제: SQL을 바꾸지 않고 테이블 랜덤 액세스 없애기

SELECT ename, sal, deptno
  FROM emp
 WHERE deptno = 20;

CREATE INDEX emp_idx01 ON emp (deptno, sal, ename);

결과 해석:

실습 답: 조건 컬럼 deptno를 앞에 두고 조회 컬럼 sal, ename을 인덱스에 더해 인덱스만으로 결과를 만듦
인덱스에 컬럼을 더하면 DML 비용이 늘어난다는 점은 테이블 접근 비용 줄이기와 함께 봄

#구문과 옵션 설명

TABLE ACCESS BY INDEX ROWID BATCHED는 단순한 한 행씩의 방문과 구분해 읽음
결과 행 수가 그대로 물리 디스크 읽기 횟수가 되는 것은 아니며 캐시 상태와 같은 블록 재사용이 영향을 줌

일반 B-tree에 모든 키 컬럼이 NULL인 행은 저장되지 않음
인덱스만으로 조회하려면 필요한 행이 인덱스에 빠짐없이 들어 있는지도 확인함
기존 deptno = 20 조건은 NULL 행을 대상으로 하지 않음

#사용 조건과 주의사항

Full Scan의 멀티블록 읽기는 데이터 파일의 블록 범위에 대한 접근임
파일에서 이웃한 블록이 저장 장치에서도 반드시 연속된 위치라는 뜻은 아님

ROWID는 장기 보관할 업무 식별자로 사용하지 않음
커버링을 위해 추가한 컬럼은 인덱스 크기와 키 변경 시 유지 비용을 늘릴 수 있음