Oracle SQL - Index Unique Scan

* 회사에서 오라클 SQL를 주로 쓰길래 성능을 향상하기 위해 항상 공부를 하고 있고, 교육받는 내용을 복습하는 용도로 사용하고자 블로그를 작성한다.

 

Index Unique Scan

* 발생조건

 - Unique 인덱스 및 PK 인덱스의 모든컬럼에 대하여 Where 절에 = 조건으로 상수 및 변수가 들어올 때 발생된다.

 

* 특징

 - 해당조건을 만족하는 하나의 row만 Scan 한다.

 - 빠른 성능을 보이는 것이 특징이다.

 

* OLTP 시스템과 건 단위로 처리해야 하는 경우에 많이 적용한다.

 

* Hint는 다음과 같이 사용한다.

/*+ index(테이블명 또는 테이블 별칭 인덱스명) */

 

 

Index Unique Scan 예제

다음과 같이 EMP 테이블을 만들어 준다.

/* EMP 테이블 만들기 */
CREATE TABLE EMP(
    EMPNO NUMBER(4) NOT NULL, --사원번호
    ENAME VARCHAR(12),        --사원이름
    MGR   VARCHAR(12)         --직책
);

 

기본키를 설정해준다. 기본키는 EMPNO로 설정한다.

/* 기본키 부여하기 */
ALTER TABLE EMP ADD CONSTRAINT PKEMP PRIMARY KEY(EMPNO);

/* 테이블 컬럼 확인하기 */
DESC EMP;

기본키를 설정했으면 잘 만들어 줬는지 테이블 정보를 조회해본다.

잘 만들어졌다. 이제 데이터를 INSERT 해보자. 

테이블을 만들고 INSERT 하는 방법이 궁금하다면 다음 게시물을 참고해보자.

[SQL] CREATE TABLE, INSERT, SELECT

/* 데이터 넣기 */
INSERT INTO EMP VALUES(2372, 'KIMS', 'MANAGER');
INSERT INTO EMP VALUES(2373, 'MANG', 'INTON');
INSERT INTO EMP VALUES(2374, 'PARK', 'CEO');

데이터를 넣었으니 이제 UNIQUE 인덱스를 생성하고, 데이터가 잘 들어갔는지 전체 데이터를 조회해보자.

/*인덱스 생성 */
CREATE UNIQUE INDEX PK_EMPNO ON EMP(EMPNO);

/*전체 데이터 조회*/
SELECT * FROM EMP;

이제 힌트를 사용해서 특정 데이터를 조회해보자. (EMPNO가 = 2372 인 ROW 출력)

/* 힌트써서 특정 데이터 조회 */
SELECT /*+ INDEX(EMP PK_EMP) */
       EMPNO
       ,ENAME
    FROM EMP
    WHERE EMPNO = 2372;

 

조건에 맞는 데이터가 나왔으니 이제 실행계획을 보자.

 필자가 쓰는 ORACLE sql developer는 다음과 같이 실행계획을 볼 수 있다.

 

/*실행계획 보기*/
EXPLAIN PLAN FOR
    SELECT /*+ INDEX(EMP PK_EMP) */
       EMPNO
       ,ENAME
    FROM EMP
    WHERE EMPNO = 2372;

/* 실행계획 출력 */
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

구조는 다음과 같다.

EXPLAIN PLAN FOR [쿼리문];

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

실행 순서는 2 -> 1 -> 0 이다.

UNIQUE INDEX를 생성했기 때문에 데이터를 조회할 때 INDEX UNIQUE SCAN으로 옵티마이저가 실행한다는 것을 볼 수 있다. 

 

 

 

마무리

 

필자는 INDEX UNIQUE SCAN을 실습하기 위해 간단하게 테이블을 만들고 적은 데이터를 삽입하였다.

물론 인덱스를 지정하지 않으면 실행 계획에 TABLE ACCESS FULL, 즉 전체 데이터를 조회한다.

예제와 같이 데이터가 적을 때는 인덱스 스캔 방식보다 TABLE ACCESS FULL 방식이 더 효과적이다.

하지만 데이터가 많은 경우에는 INDEX UNIQUE SCAN 방식을 사용하여 조회 속도 성능을 향상할 수 있다.

필자는 Oracle Data Access Pattern에 대해 공부하여

상황에 맞는 스캔 방식을 사용해 성능을 향상해보도록 노력하겠다.