Full Table Scan과 인덱스 접근

Full Table Scan의 블록 접근과 선택 조건, 10만 건 테이블의 인덱스 접근 비용 및 버퍼 비교

Full Table ScanIndex Range ScanHigh Water MarkDB_FILE_MULTIBLOCK_READ_COUNTFULL 힌트선택도

#핵심 요약

Full Table Scan은 테이블의 모든 행을 읽은 뒤 조건에 맞지 않는 행을 걸러 냄
High Water Mark 아래 포맷된 블록을 순서대로 한 번씩 읽음

이웃한 블록을 읽으므로 multiblock read로 큰 I/O 호출을 쓸 수 있음
적은 수의 큰 호출이 많은 작은 호출보다 싸임

인덱스가 있어도 테이블 블록 대부분을 읽어야 한다면 옵티마이저는 Full Table Scan을 고름

소량 조회는 Index Range Scan이 유리함
실습에서 소량 조회를 인덱스로 바꾸자 버퍼가 708에서 4로, 비용이 124에서 1로 줄었음

실행계획에 Full Table Scan이 보인다고 무조건 튜닝 대상은 아님
조회 비율이 크거나 배치처럼 대량 처리일 때는 Full Table Scan이 나음

#개념과 동작 원리

옵티마이저는 다른 접근 경로를 쓸 수 없거나, 쓸 수 있는 경로의 비용이 더 높을 때 Full Table Scan을 고름
공식 문서의 대표 이유:

이유	공식 문서 설명
인덱스가 없음	인덱스가 없으면 Full Table Scan을 씀
조건이 인덱스 컬럼에 함수를 적용	함수 기반 인덱스가 아니면 인덱스는 컬럼 값을 담을 뿐임 문자 컬럼을 `WHERE char_col=1`로 조회하면 암시적 `TO_NUMBER`가 적용되어 인덱스를 못 씀
`SELECT COUNT(*)`인데 인덱스 컬럼에 NULL이 있음	인덱스는 NULL 항목을 담지 않으므로 행 수를 셀 수 없음
조건이 B-tree 인덱스의 선두 컬럼을 쓰지 않음	인덱스를 고르지 않을 수 있음 다만 Index Skip Scan을 고를 수 있음
선택도가 나쁨	테이블 블록 대부분이 필요하면 인덱스가 있어도 Full Table Scan
통계가 오래됨	작던 테이블이 커졌는데 통계가 반영하지 못하면 인덱스가 더 효율적임을 모름
테이블이 작음	High Water Mark 아래 블록이 `DB_FILE_MULTIBLOCK_READ_COUNT`보다 적으면 Index Range Scan보다 쌀 수 있음
테이블 병렬도가 높음	높은 병렬도는 옵티마이저를 Full Table Scan 쪽으로 기울게 함(`ALL_TABLES.DEGREE`로 확인)
`FULL` 힌트	`FULL(table alias)`는 Full Table Scan을 지시함

인덱스 사용을 막는 조건은 인덱스 사용을 방해하는 조건에서 자세히 다룸

퀴즈 정리: Index Range Scan은 ROWID로 레코드에 접근하며 랜덤 액세스와 Single Block I/O로 소량 데이터를 조회할 때 유리함
조회 건수가 많아지면 인덱스 경로가 같은 테이블 블록을 여러 번 반복해 방문하는 비효율이 생기지만, Full Table Scan은 한 번만 순서대로 읽고 지나감

#필수 명령어 또는 SQL

#실습 환경

SQL*Plus (실습 계정)데이터 또는 파일을 삭제함, 되돌릴 수 없음

DROP TABLE scan_test PURGE;

SQL*Plus (실습 계정)객체 또는 설정을 변경함

CREATE TABLE scan_test AS
SELECT ROWNUM                                AS id,
       MOD(ROWNUM, 100)                      AS dept_id,   -- 0~99
       MOD(ROWNUM, 10)                       AS grade,     -- 0~9
       DBMS_RANDOM.STRING('A', 30)           AS emp_name,
       ROUND(DBMS_RANDOM.VALUE(1000, 9000))  AS salary
  FROM dual
CONNECT BY LEVEL <= 100000;

CREATE INDEX scan_test_idx ON scan_test (dept_id);

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'SCAN_TEST');

SQL*Plus (실습 계정)조회만 함

SELECT dept_id, COUNT(*) AS cnt,
       ROUND(COUNT(*) / 1000, 1) AS pct
  FROM scan_test
 WHERE dept_id IN (0, 1, 2)
 GROUP BY dept_id
 ORDER BY dept_id;

결과 해석: 10만 건을 MOD(ROWNUM, 100)으로 나눴으므로 dept_id 값 하나에 1000건(전체의 1%)씩임
pct 컬럼은 1000건 기준으로 1.0 근처가 나와야 함(식에 따른 기대값, 실행 결과 아님)

#비교 결과

경우	인덱스 스캔	Full Table Scan	실습 결론
`dept_id` 한 값(약 1000건) 조회	cost 705	cost 124	1000건이나 되는 대량 조회라 Full Table Scan이 나음
소량 조회(조건 그림)	cost 1, 버퍼 4	cost 124, 버퍼 708	소량 조회는 Index Range Scan이 나음

실습 소제목은 첫 번째 경우를 "소량 데이터 조회 (full table scan 유리한 경우)"라고 적었지만 설명은 "대량의 데이터"라고 해 표현이 맞지 않음
두 번째 경우의 SQL 조건은 확인 불가

#사용 조건과 주의사항

Full Table Scan이 유리한 경우: 조회 대상이 테이블에서 아주 큰 비율을 차지할 때, 배치처럼 한 번에 대량 데이터를 처리할 때

공식 문서는 Full Table Scan 중 블록 캐시 방식이 테이블 크기에 따라 달라진다고 적음
버퍼 캐시를 거치지 않는 읽기는 논리적 I/O와 물리적 I/O의 설명을 참고함