[SQL] index unique scan 이론

index unique scan이란?

인덱스 값을 찾아서 eccess를 하고 해당 rowid의 값을 가져오고 종료한다. 값이 unique함이 보장이 됐기 때문에 range scan처럼 다음 값을 찾을 필요가 없다.

index range scan과 index unique scan 중에 어느 인덱스를 사용하는 게 좋은가?

where 절 조건에 non unique index와 unique index가 같이 있는 경우, 옵티마이저는 unique index를 우선적으로 사용하게 된다. unique index는 값이 중복되지 않으므로 하나의 행만 찾아서 rowid를 바로 찾으러 가기 때문에 보다 효율적이다.

primary key 제약을 걸면 인덱스가 생성된다.

primary key 제약을 걸 때도 unique index가 자동으로 생성된다.

--primary key 제약 생성 쿼리
alter table emp
	add constraint emp_empno_pk primary key(empno);

--인덱스 확인 (pk만 생성했는데도 unique index가 걸려있다)
                            | UNIQUENESS|
-------------------------------------------------------------------------------------------------
C##SCOTT |  EMP_EMPNO_PK    |   UNIQUE  |	VALID   |	NORMAL  |	N   |	NO  |	NO	|   EMPNO

예제1) 사원번호가 7788번인 사원들의 사원번호와 이름과 월급을 출력하시오

--index 생성
create index emp_empno on emp(empno); --unique를 걸지 않으면 range scan이 된다.
drop index emp_empno; --index를 삭제하고
create unique index emp_empno on emp(empno); --unique를 걸어 인덱스를 생성한다.

--조회
select /*+ gather_plan_statistics */ empno, ename
from emp
where empno = 7788;

--실행계획 조회
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

--실행계획 조회 결과
| Id  | Operation                   | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |      1 |        |      1 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP          |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMPNO_PK |      1 |      1 |      1 |00:00:00.01 |       1 |

예제2) 사원 이름에 인덱스를 걸면 옵티마이저는 사원번호의 인덱스를 엑세스 할것인가 사원 이름에 인덱스를 엑세스 할것인가?

--index 생성
create index emp_ename on emp(ename);

--조회
select /*+ gather_plan_statistics */ empno, ename
from emp
where ename = 'SCOTT' and empno = 7788;

--실행계획 조회
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

--실행계획 결과 (unique index를 엑세스하고 있다)
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |      1 |00:00:00.01 |       2 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP       |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMPNO |      1 |      1 |      1 |00:00:00.01 |       1 |