인덱스 관련 실행 계획은 SQL 최적화의 기본이다

권순용의 DB 이야기

인덱스 관련 실행 계획은 SQL 최적화의 기본이다

 인덱스의 실행 계획은 다른 어떤 실행 계획보다 종류가 많다. 그래서인지 많은 사람들이 인덱스의 실행 계획에 대해 잘 이해하지 못하는 경우가 많다. 인덱스의 실행 계획은 인덱스를 이용했는지 안 했는지만을 의미한다고 생각하는 경우가 많은 것 같다. 이와 같은 생각이 우리로 하여금 SQL 최적화를 더욱 어렵게 만드는 원인일 것이다. 이제부터라도 인덱스에 대한 모든 실행 계획을 이해해야 할 것이다.

 SQL의 최적화에 있어서 인덱스의 실행 계획에 대한 이해는 매우 중요하다. 이는 대부분의 실행 계획에는 인덱스 관련 실행 계획이 생성되기 때문이다. 이와 같기 때문에 인덱스 관련 실행 계획을 이해하지 못한다면 SQL 최적화는 절대 수행할 수 없게 된다. 또한 인덱스 관련 실행 계획의 종류가 많기 때문에 인덱스 실행 계획에 대한 정확한 이해는 반드시 필요하다. 이번 호에서는 인덱스를 이용하는 실행 계획 중 Index Full Scan에 대한 실행 계획을 분석하는 방법에 대해 같이 한번 확인해 보도록 하자.

 인덱스도 Full Scan이 가능하다

 많은 사람들은 Full Scan하면 테이블에 대한 Full Scan만 생각하게 된다. 테이블에 대한 Full Scan이 보편적이기는 하지만 인덱스 또한 Full Scan이 발생할 수 있다는 것을 잊지 않길 바란다. 그렇다면 인덱스에 대한 Full Scan은 한 가지만 존재하는가? 인덱스에 대한 Full Scan에는 여러 가지 종류가 존재한다. 우선 인덱스에서 발생할 수 있는 모든 실행 계획을 확인해 보자. 

- Index Range Scan - Index Unique Scan - Index Full Scan - Index Fast Full Scan - Index Skip Scan - Index Min/Max Scan - Index Join

 인덱스를 엑세스하는 방법에는 위와 같은 형태가 존재한다. 이와 같이 우리가 생각지 못한 여러 방법으로 인덱스를 엑세스할 수 있다. 위의 인덱스 관련 실행 계획 중 인덱스 Full Scan에 대한 실행 계획은 어떤 실행 계획일까? 위의 인덱스 실행 계획 중 인덱스 Full Scan에 대한 실행 계획은 아래와 같이 두 가지이다.

 위의 두 가지 실행 계획 중 하나의 실행 계획이 생성된다면 이는 인덱스를 Full Scan한 실행 계획이 된다. 인덱스 Full Scan은 일반적으로 성능 저하를 발생시키므로 주의해야 될 실행 계획임을 명심하길 바란다.

 인덱스도 Full Scan의 수행을 이해하자

 인덱스 Full Scan은 단어 그대로 인덱스를 처음부터 끝까지 엑세스하는 실행 계획이다. 그렇기 때문에 일반적으로 성능 저하가 발생할 수 있다. 다음 예제를 확인해 보자.  

SQL> SELECT COL1, COL2 FROM TAB1 WHERE COL3 = 'AAA' ORDER BY COL4

 위의 SQL에서 TAB1 테이블에는 COL4+COL3 인덱스가 있다고 가정하자. 그렇다면 해당 SQL은 과연 COL4+COL3 인덱스를 이용할 수 있는가? 기본적으로는 대부분의 사람들은 해당 인덱스를 이용할 수 없다고 이야기할 것이다. 하지만 해당 SQL은 COL4+COL3 인덱스를 이용할 수 있다. 해당 인덱스를 이용한다면 어떻게 실행이 되겠는가? COL4 컬럼은 WHERE 절의 조건이 아니므로 처리 범위를 감소시킬 수 없게 된다. 하지만 해당 인덱스는 COL4 컬럼이 인덱스의 앞에 존재하므로 인덱스를 이용하여 처리 범위를 감소시킬 수는 없게 된다. 그렇기 때문에 해당 SQL은 COL4+COL3 인덱스를 처음부터 끝까지 엑세스하는 인덱스 Full Scan의 실행 계획이 생성되게 된다. 

SELECT STATEMENT SORT(ORDER BY) TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' INDEX (FULL SCAN) OF 'COL4_COL3_IDX'

위와 같이 실행 계획이 생성되면 해당 SQL은 COL4+COL3 인덱스를 Full Scan하게 되므로 인덱스의 처음부터 끝까지 엑세스하게 된다. 이와 같이 수행되는 경우 일반적으로 성능 저하가 발생하게 될 것이다. 이는 처리 범위를 감소시키지 못하고 인덱스를 처음부터 끝까지 엑세스하기 때문이다. 그렇다면 인덱스 Full Scan은 단점만을 가지는 것일까? 세상사가 다 그렇듯이 단점이 있으면 장점이 있고 장점이 있으면 단점이 존재할 것이다. 인덱스 Full Scan도 마찬가지이다.

 인덱스도 Full Scan의 장점과 단점을 분석하자

 인덱스 Full Scan은 앞서 언급한 것과 같이 인덱스를 처음부터 끝까지 엑세스해야 한다는 단점이 존재한다. 앞의 예제에서 COL3 조건을 만족하는 데이터는 100건이며 TAB1 테이블의 전체 데이터는 100,000건이라면 100,000건의 데이터를 인덱스에서 모두 엑세스하여 99,900건은 버리고 100건만을 취하여 결과로 추출하게 되므로 비효율이 발생하게 될 것이다. 이와 같다면 인덱스 Full Scan이 성능 저하를 발생시키는 것은 자명한 일이다.  

- 처리 범위 증가로 인한 성능 저하 발생 가능

이처럼 인덱스 Full Scan은 성능 저하를 발생시킬 수 있는 구조임에는 틀림없다. 하지만 TAB1 테이블이 100,000건이며 조건을 만족하는 데이터가 90,000건이라고 가정하자. 또한, 인덱스는 COL4+COL3+COL2+COL1으로 구성되어 있다면 해당 인덱스를 이용하는 순간 인덱스 Full Scan이 발생하는 것은 동일하다. 하지만 인덱스를 Full Scan하므로 인덱스에서 추출되는 데이터의 순서로 데이터가 추출될 것이다. 인덱스는 현재 COL4 컬럼으로 정렬되어 있으므로 해당 인덱스를 이용하는 순간 자동으로 COL4 컬럼으로 정렬된 데이터가 결과로 추출된다. 이와 같다면 해당 SQL은 정렬을 발생시키지 않게 된다. 또한 인덱스에 필요한 모든 컬럼이 존재하므로 테이블을 별도로 엑세스할 필요가 없으며 엑세스한 데이터의 대부분이 결과로 추출되므로 인덱스를 처음부터 끝까지 엑세스한 데이터에 대한 낭비가 거의 없게 된다. 이와 같다면 인덱스 Full Scan이 정렬도 없애며 테이블보다 크기가 작은 인덱스를 엑세스하게 되어 성능을 향상시킬 수 있다. 이와 같이 실행된다면 실행 계획은 다음과 같이 생성될 것이다. 

SELECT STATEMENT TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' INDEX (FULL SCAN) OF 'COL4_COL3_COL2_COL1_IDX'

 인덱스 Full Scan은 해당 테이블의 데이터를 대부분 추출하는 경우 다음과 같은 장점을 가지게 된다.  

- 테이블보다 작은 인덱스를 전부 엑세스하므로 I/O 양을 감소시킴 - 인덱스의 첫 번째 컬럼으로 정렬된 데이터를 자동으로 추출

위와 같이 인덱스 Full Scan은 인덱스 자동 정렬과 테이블을 엑세스하는 현상만 없다면 더 작은 크기의 인덱스를 엑세스하므로 디스크 I/O에서 장점을 가지게 된다. 물론 해당 테이블의 데이터 중 대부분의 데이터를 정렬하여 추출하는 경우에 해당할 것이다.  

이처럼 인덱스 Full Scan은 단점이 크지만 잘 사용한다면 자동 정렬 등의 장점을 효과적으로 이용할 수 있을 것이다. 이러한 점에 유의하여 인덱스 Full Scan 실행 계획이 생성된다면 많은 분석을 통해 최적화를 해야 하며 인덱스 Full Scan의 장점을 이용하여 SQL을 최적화할 수도 있다. 다음 호에서는 인덱스 Full Scan 중 하나인 Index Fast Full Scan에 대해 함께 확인해 보자.

 필자소개
권순용 kwontra@hanmail.net｜Data Consulting 업무를 수행하는 ㈜엑시엄의 대표이사이며 DBA로 시작하여 SQL 튜닝, 데이터베이스 아키텍처 및 모델링 업무를 주로 수행했다. 데이터베이스 교육에도 많은 관심을 가지고 있으며 저서로는 『Perfect! 오라클 실전 튜닝, 『초보자를 위한 오라클 10g』 및 『INSIDE SQL』이 있다. 또한, 데이터 액세스 최적화에 대한 특허를 출원했다.

 출처 : 한국 마이크로 소프트웨어 [2009년 5월호]
제공 : DB포탈사이트 DBguide.net