[ORACLE] 인덱스 접근

ORACLE은 두 방식으로 index를 통해 접근한다.

 

1. INDEX UNIQUE SCAN
조회하고자 하는 테이블에 인덱스가 존재하는 경우 optimizer는 인덱스를 이용한다.
예) 테이블에 A 컬럼에 A_PK라는 unique index와 B컬럼에 non-unique index인 B_1이라는 두개의 인덱스가 존재한다.

-- INDEX UNIQUE SCAN 작업만으로 수행
SELECT *
FROM  
WHERE A = 'ABCD';

내부적으로 두 개의 step으로 나뉘어 query를 수행한다. 첫번째는 먼저 A_PK 인덱스를 통한 INDEX UNIQUE SCAN 작업이 수행되어 A_PK에서 A이 'ABCD'인 데이터의 물리적 위치인 RowID를 찾는다. 그 다음에 RowID를 이용한 TABLE ACCESS BY ROWID 작업이 수행되어 EMP 테이블에서 해당 row의 나머지 컬럼을 찾아 결과값을 되돌려 준다.

 

만약 query를 통해 요청 되는 값이 index 내의 컬럼 이라면, 첫번째 작업인 INDEX UNIQUE SCAN만으로도 결과값을 되돌려 줄 수 있어 더 높은 성능을 기대할 수 있다. 

 

2. INDEX RANGE SCAN
Index 컬럼에 대한 범위 또는 non-unique index의 컬럼을 이용한 query를 수행하면 INDEX RANGE SCAN을 통해 데이터를 조회하고 값을 되돌려 받게 된다.

SELECT A
FROM 
WHERE A LIKE 'AB%';

위에서 where절 내에 A에 대한 범위로 조회를 하므로 unique-index인 A_PK를 통해 조회되더라도 INDEX RANGE SCAN 작업을 통해 데이터가 조회된다. INDEX RANGE SCAN을 통해 수행되는 작업은 인덱스로부터 여러 개의 데이터를 조회하기 때문에, INDEX UNIQUE SCAN에 의한 것보다 비효율적이다. 조회하는 컬럼이 A_PK를 구성하는 A이므로 INDEX RANGE SCAN 만으로 query가 수행되고 값을 되돌려 받게 된다.

SELECT A
FROM 
WHERE B = 'ABCE';

위와 같은 SQL은 조회하는 컬럼이 인덱스의 구성 컬럼이 아니므로 내부적으로 두 단계에 걸쳐 실행된다. 첫번째는 non-unique index인 B_1을 INDEX RANGE SCAN하여 RowID를 얻게 되고, 이를 통해 테이블에 대한 TABLE ACCESS BY ROWID를 통해 수행되어 A 컬럼 값을 되돌려 준다. 여기서 non-unique index인 B_1의 B값이 unique할지라도 INDEX UNIQUE SCAN을 통해 수행되지는 않고 INDEX RANGE SCAN을 통해 n+1건에 대한 access가 발생한다.
그러나 아래의 경우와 같이 인덱스 컬럼에 대해 range로 조회를 하더라도 맨 앞 글자가 '%'와 같은 와일드카드일 경우에는 non-unique index인 B_1이 존재하더라도 인덱스를 통한 access가 일어나지 않고 FULL TABLE SCAN으로 조회된다.

 

 

인덱스 사용 제한하기

둘 이상의 인덱스가 동일한 순위로 참조되어 query가 비효율적인 경로로 실행될 때, 어느 하나의 인덱스를 강제로 사용하지 못하게 함으로써 SQL문의 실행 성능을 높일 수 있다. Character 타입의 컬럼에는 ||''을 붙이고, Number 타입의 컬럼에는 +0을 하여 좌변을 가공하게 되면 ORACLE은 해당 인덱스를 사용하지 않는 실행 계획을 세우게 된다. 하지만 현재는 A 컬럼에 대한 인덱스가 다른 인덱스보다 분포도가 좋아 더 나은 결과를 보일지 몰라도 향후 데이터의 양이나 분포도가 변할 경우 dynamic하게 경로를 생성하는 CBO의 장점을 활용하지 못하게 된다. 그러므로 필요한 경우에만 잠시 사용하는 것이 좋다.

 

현재 A컬럼에 대해 nonunique index가 생성되어 있고, B컬럼에는 인덱스가 없다. Optimizer는 당연히 A에 대한 인덱스를 사용한 실행계획을 수립한다. 그러나 향후에 B에 대한 인덱스가 생성되면 어떻게 될 것인가?

일반적인 경우 앞에서 설명한 바와 같이 두개의 인덱스를 모두 활용한 실행계획을 생성하여 sort/merge를 통해 query를 수행한다. 이 때 한 인덱스는 분포도가 좋아 10건 미만의 결과값을 찾고, 다른 인덱스는 분포도가 떨어져 수천 건을 결과값으로 돌려 받았다면, 이를 sort/merge 하는데 걸리는 overhead는 분명히 성능 저하의 원인이 된다. 이와 같이 어느 한쪽이 항상 우수한 분포도를 보이며, 이를 확신할 수 있을 경우 아래와 같이 전략적으로 인덱스의 사용을 제한함으로써 향후 발생 가능한 overhead를 미연에 방지할 수 있다.

 

SELECT 
FROM 
WHERE A||'' = 'A'
AND B = 'X';

 

ORACLE에 의한 INDEX 사용 제한
하나의 테이블에 두개 이상의 사용 가능한 인덱스가 있고, 하나의 인덱스만 Unique이고 나머지는 non-unique 일 때 ORACLE은 unique index만을 사용한 실행계획을 생성하고 나머지 인덱스들은 완전히 무시한다.