[Oracle] INDEX FAST FULL SCAN

당당·2023년 12월 27일

Oracle

목록 보기

35/51

📔설명

INDEX FAST FULL SCAN을 알아보고, table full scan과의 차이를 알아보자

🍔INDEX FAST FULL SCAN

select /*+ gather_plan_statistics */ job, count(*)
from emp
group by job;

직업별 인원수를 보는 sql문을 작성했다.

emp테이블에서 직업에 인덱스가 없다면, full table scan을 하게 된다.

select /*+ gather_plan_statistics index_ffs(emp emp_job) */ job, count(*)
from emp
group by job;

만약 emp_job 인덱스를 건다고 해보자.

create index emp_job on emp(job);

select job,rowid
from emp
where job>' ';

그러면 직업은 asc순으로 정렬이 되는데 인덱스에서 직업과 직업별 인원을 확인할 수 있다. 그러므로 인덱스만 읽으면 된다.
이렇게, 인덱스만 읽을때 빠르게 스캔할 수 있는 방법이 index fast full scan이다.

하지만 이러기 위해선, job 컬럼에 not null 제약이 걸려있어야 한다.
왜냐하면 index fast full scan은 여러개의 블럭을 한꺼번에 읽기(multiblock i/o) 때문에 만약 비어있는 페이지 (null)가 있다면, 제대로 데이터를 카운트 할 수 없기 때문이다.

그러므로 ffs힌트를 쓸 때는 not null을 보장해줘야 한다.

where 절에 is not null 사용
modify를 통해 not null 제약

select /*+ gather_plan_statistics index_ffs(emp emp_job) */ job, count(*)
from emp
group by job;

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

not null 제약이 없기 때문에 table full scan을 했다.

alter table emp
modify job constraint emp_job_nn not null;

select /*+ gather_plan_statistics index_ffs(emp emp_job) */ job, count(*)
from emp
group by job;

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

🥞 vs INDEX FULL SCAN

비교	index full scan	index fast full scan
I/O 방식	single block i/o	multi block i/o
정렬	정렬 보장	정렬 안됨
속도	느림	빠름
병렬 읽기	지원 X	지원 O

create index emp_deptno on emp(deptno);

select /*+ gather_plan_statistics index_ffs(emp emp_deptno) */ deptno, count(deptno)
from emp
group by deptno;

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

결과에 정렬도 되지 않고, TABLE FULL SCAN을 했다.

alter table emp modify deptno constraint emp_deptno_nn not null;

select /*+ gather_plan_statistics index_ffs(emp emp_deptno) */ deptno, count(deptno)
from emp
group by deptno;

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

결과가 정렬이 되지 않았지만, ffs로 작동했다.

select /*+ gather_plan_statistics index_fs(emp emp_deptno) */ deptno, count(deptno)
from emp
group by deptno;

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));