INDEX의 기본 개념_INDEX RANGE SCAN VS. TABLE ACCESS FULL

INDEX RANGE SCAN VS. TABLE ACCESS FULL

 

랜덤 액세스(RANDOM ACCESS)

 

IO 작업 한 번에 하나의 블록을 가져오는 접근 방법을 뜻한다. 인덱스의 리프 블록에서 ROWID를 이용해 테이블에 접근할 때 랜덤 액세스가 발생한다. 실행계획에는 'TABLE ACCESS BY INDEX ROWID'로 표시 된다. 찾으려는 데이터가 많지 않으면, 랜덤 액세스가 나쁜 방법은 아니다. 그러나 찾으려는 데이터가 많 으면 랜덤 액세스는 오히려 비효율적이다.

 

예제 ) INDEX RANGE SCAN을 사용하는 SQL

SELECT /*+ GATHER_PLAN_STATISTICS */
       T1.CUS_ID, COUNT(*) ORD_CNT
  FROM T_ORD_BIG T1
 WHERE T1.ORD_YMD = '20170316'
GROUP BY T1.CUS_ID
ORDER BY T1.CUS_ID;

SQL의 WHERE 조건절을 보면 ORD_YMD가 '20170316'인 주문 데이터를 조회하고 있다. T_ORD_BIG 테 이블에는 총 3천만 건 정도의 데이터가 있다. 그 중에 ORD_YMD가 '20170316'인 데이터는 5만 건이다. 3천만 건에서 5만 건 정도를 찾는 경우라면 'INDEX RANGE SCAN'이 효율적이라고 판단할 수 있다.(정확하게는 데이터 건수가 아닌 블록 수로 판단해야한다.)

 

이번에는 T_ORD_BIG 테이블에서 3개월간의 주문 약 7,650,000건에 달하는 대량 데이터를 조회해보자

 

예제 ) 3개월간의 주문을 조회 - ORD_YMD컬럼 인덱스를 사용

SELECT /*+ GATHER_PLAN_STATISTICS INDEX(T1 X_T_ORD_BIG_1) */
       T1.ORD_ST, SUM(T1.ORD_AMT)
  FROM T_ORD_BIG T1
 WHERE T1.ORD_YMD BETWEEN '20170401' AND '20170630'
GROUP BY T1.ORD_ST;

실행계획에서 'TABLE ACCESS BY INDEX ROWID'가 7,650K 번 실행되었다. 'TABLE ACCESS BY INDEX ROWID'는 바로 전 단계(INDEX RANGE SCAN)의 A-Rows만큼 실행된다. 매우 많은 랜덤 액세스가 발생했다.

 

같은 SQL을 'FULL' 힌트를 사용해 실행해보면 '테이블 전체 읽기' 방식으로 SQL이 처리된다.

 

예제 ) 3개월간의 주문을 조회 - FULL(T1) 힌트 사용

SELECT /*+ GATHER_PLAN_STATISTICS FULL(T1) */
       T1.ORD_ST, SUM(T1.ORD_AMT)
  FROM T_ORD_BIG T1
 WHERE T1.ORD_YMD BETWEEN '20170401' AND '20170630'
GROUP BY T1.ORD_ST;

같은 SQL을 'FULL' 힌트를 사용하자 인덱스로 처리할때보다 속도가 빨라졌다.

총 실행시간이 5.24초로 단축되었고, Buffers 수치도 258K로 좋아졌다. 찾고자 하는 데이터가 '특정 수 준' 이상으로 많으면 인덱스를 이용한 '랜덤 액세스'보다 'FULL SCAN'방식이 훨씬 효율적이다. 정리하자면 다음과 같다.

 

- 적은 양의 데이터를 읽는다면 'INDEX RANGE SCAN'이 유리하다.

- 많은 양의 데이터를 읽어야 한다면 'FULL SCAN'이 유리할 수 있다.

- 'FULL SCAN'은 데이터가 쌓일수록 성능이 점차 나빠진다. 테이블 관리 전략이 필요하다.