Oracle (7) - 인덱스를 타는지 확인하는 방법 (EXPLAIN PLAN)

SQL을 작성하여 어떤 데이터를 만들기 위해 많은 JOIN 및 서브쿼리를 하게 되는 경우가 있습니다. 해당 과정을 반복하다 보면 쿼리 실행시 성능 문제가 발생할 수 있습니다. 특히 인덱스를 타지 못하고 FULL SCAN 하는 경우 성능이 크게 떨어집니다. 이러한 문제를 해결하기 위해 EXPLAIN PLAN을 이용하여 쿼리의 실행 계획을 확인하고, 인덱스를 잘 활용하도록 최적화하는 방법을 알아보겠습니다.

 

EXPLAIN PLAN

-- 실행 계획을 생성
EXPLAIN PLAN FOR
SELECT *
  FROM TEST
 WHERE MAJOR = '국문학과';

-- 생성된 실행 계획 확인
SELECT *
  FROM TABLE(DBMS_XPLAN.DISPLAY);

우선 EXPLAIN PLAN은 오라클에서 제공하는 기능으로, SQL 쿼리의 실행 계획을 미리 확인할 수 있게 해줍니다. 이를 통해 쿼리가 어떤 방식으로 데이터를 읽고 처리할 수 있는지 예측할 수 있습니다.

 

실행하려고 하는 실행 계획을 확인하는 방법은 다음과 같습니다.

 

EXPLAIN PLAN 실행 결과 해석

DBMS_XPLAN.DISPLAY는 실행 계획을 화면에 출력해 줍니다. 하단 이미지와 같이 다음과 같은 형태로 나타납니다.

EXPLAIN PLAN으로 실행시킨 이미지

위 실행 계획에서 중요한 부분은 Operation, Name을 보면 됩니다

 

먼저 Operation은 다음과 같이 나올 수 있습니다.

• TABLE ACCESS FULL: 테이블 전체를 스캔합니다. 전체 데이터를 조회하므로 성능이 저하될 수 있습니다.
• TABLE ACCESS BY INDEX ROWID: 인덱스를 사용 후, ROWID를 찾고 해당 행을 읽습니다.
• INDEX RANGE SCAN: 인덱스를 범위 조회로 스캔합니다.
• INDEX UNIQUE SCAN: 인덱스를 고유한 값으로 스캔합니다. Primary Key, Unique Key 조건에서 사용됩니다.

Name은 사용하는 테이블을 의미합니다.

쿼리를 튜닝할 때는 FULL SCAN(TABLE ACCESS FULL) 을 피하는 방향으로 쿼리를 작성해야 합니다. 

 

인덱스가 사용되지 않는 이유

인덱스를 사용하지 않는 대표적인 사유는 다음과 같습니다.

1. WHERE 절에서 인덱스 컬럼에서 사용하지 않는 경우
2. 인덱스가 걸린 컬럼에 함수적용
3. 데이터 양이 적을 경우 : Oracle의 CBO(Cost-Based Optimizer)가 데이터 양이 적어 FULL SCAN이 더 효율적이라고 판단한 경우 사용하지 않습니다.

 

인덱스를 사용하도록 최적화하기

위에서 적은 사유를 예시로 해결하는 방법을 적어보려고 합니다.

INDEX 생성

해당 명령어를 이용하여 인덱스를 생성할 수 있습니다.

CREATE INDEX TEST_IDX ON TEST(MAJOR);

WHERE 절 최적화

인덱스를 활용하기 위해 WHERE 절을 인덱스 컬럼과 일치하게 작성합니다.

# Before
SELECT *
  FROM TEST
 WHERE SUBSTR(MAJOR,3,4) = '국어국문학과';

# AFter
SELECT *
  FROM TEST
 WHERE MAJOR = '국문학과';

함수 기반 인덱스 활용

만약 함수적용이 필요하다면, 함수 기반 인덱스를 생성할 수 있습니다.

CREATE INDEX TEST_IDX_FUNC ON TEST(SUBSTR(MAJOR,3,6));

힌트(Hint) 사용

인덱스를 강제로 사용하고 싶다면 Oracle의 힌트를 사용할 수 있습니다.

 SELECT /*+ INDEX(t idx_major) */ 
 		*
   FROM TEST t
  WHERE MAJOR = '국문학과';

 

마지막으로

많은 JOIN이나 서브쿼리가 있는 경우가 아니라면 최대한 인덱스를 이용하는게 효율적입니다. 인덱스를 최대한 사용하는 것이 프로그램의 속도 측면으로 좋습니다. EXPLAIN PLAN은 가는 길을 보여주는 방법이기 때문에 해당 명령어를 이용하여 인덱스를 프로그램을 튜닝하면 효율적인 속도를 낼 수 있다고 생각합니다.

 

도움이 된 게시글

https://wakestand.tistory.com/570

오라클 인덱스 타는지 확인방법

https://www.nextree.co.kr/oracle-explain-plan-sayong/

Oracle Explain Plan 사용