Oracle SQL - Index Range Scan

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

Index Range Scan

* 발생조건
- Non-Unique Index를 Access 하는 경우
- Unique Index를 구성하고 있는 칼럼 중 일부 칼럼에만 값이 공급된 경우
- Unique Index에 Range 조건(like, betwwen, >, <, >=, <=)으로 값이 공급되는 경우

* 특징
- 해당조건을 만족하는 범위 + 1 (1 plus, 아닌 값) 읽음
- Range 조건이 들어온 경우 Index 구성 순서상 이후에 이후에 있는 칼럼에 공급된 조건들은 작업범위를 줄이는데 작용하지 못함.
- 수행속도가 Range에 의해서 좌우

* 10만건 이하의 건수를 access 하거나, 10만 건 이상이라도 부분 범위 처리가 가능할 때 적용하고,
인덱스만 scan하고 table access가 없을 때 적용한다.
* OLTP 시스템에 적용한다.

* Hint는 다음과 같이 사용한다.
/*+ index(테이블명 또는 테이블 별칭 인덱스명) */
/*+ index_dexc(테이블명 또는 테이블 별칭 인데스명) */

Index Range Scan 예제

DEPTNO 컬럼을 Non-unique index로 먼저 지정하였다.
Index Ragne Scan을 예제를 통해 살펴보자.
발생조건은 범위를 나타낼 수 있는 조건을 사용하면 발생한다고 되어있는데
실제로 발생하는지 한번 보자.

이번에도 테이블을 이렇게 만들었다.
DEPTNO가 2000부터 8000까지 해당하는 데이터를 조회해보겠다.

SELECT /*+ INDEX_RESC(EMP PK_EMP) */ T1.EMPNO ,T1.ENAME ,T1.MGR ,T1.DEPTNO FROM EMP T1 WHERE DEPTNO BETWEEN 2000 AND 8000 ;

그냥 평소대로 해당 조건에 따라 잘 조회하는 것을 볼 수 있다.
여기까지는 누구나 할 수 있고, 항상 쿼리를 조회하기 전에 실행계획을 확인하는 습관을
가지도록 하자.
실행계획을 확인해보자.

2 -&gt; 1 -&gt; 0

INDEX RANGE SCAN으로 실행하겠다는 것을 볼 수 있다.
사실은 실행계획부터 먼저 확인하고 위 쿼리를 실행해야 한다.

Index Range Scan Descending 예제

Index Range Scan Descending은 DEPTNO 인덱스를 내림차순으로 읽어 데이터를 조회한다.

SELECT /*+ INDEX_RESC(EMP PK_EMP) */ T1.EMPNO ,T1.ENAME ,T1.MGR ,T1.DEPTNO FROM EMP T1 WHERE DEPTNO BETWEEN 2000 AND 8000 ORDER BY DEPTNO DESC;

실행계획을 살펴보자.

/*실행계획 보기*/ EXPLAIN PLAN FOR SELECT /*+ INDEX_RESC(EMP PK_EMP) */ T1.EMPNO ,T1.ENAME ,T1.MGR ,T1.DEPTNO FROM EMP T1 WHERE DEPTNO BETWEEN 2000 AND 8000 ORDER BY DEPTNO DESC ; /* 실행계획 출력 */ SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Index Range Scan Descending이 적용되었음을 알 수 있다.

마무리

이번에 Index Range Scan에 대해 알아보았다.
그냥 단순히 SQL 쿼리에 대해 알고만 있었더라면 어떠한 원리로 데이터를 찾아서 조회하는지 잘 몰랐을 것이다.
튜닝 공부를 입문하면서 단순했던 쿼리에 대해 어떻게 동작하는지 알아가는 재미가 있는 것 같다.