대용량 데이터 처리의 기본, 인덱스와 스캔 방식(Unique/Range/Full/Fast Full/Skip Scan)

 

오라클의 자료구조 중 인덱스에 대해 알아보고, 인덱스에 액세스하기 위한 방식 다섯 가지에 대해 소개한다.

그리고 인덱스 액세스가 문제 없이 잘 일어나도록 하려면 어떻게 SQL을 작성해야 좋을지 세 가지 팁을 준비했다.

---

1. 인덱스란?

 

인덱스란 해당 데이터에 접근하기 위한 방식 중 하나이다. 

다양한 인덱스 구조가 있지만, 이번 포스팅에서는 가장 범용적으로 사용되는 B-Tree 인덱스를 다룬다.

 

1.1 인덱스의 구조

B-Tree 인덱스

 

 

B-Tree 인덱스 구조에서는 브랜치와 리프로 블록을 구성해서 정렬 정보를 저장하고 있다. 책에 비유하자면 브랜치 블록에는 대목차 정보가 담겨있고, 리프 블록에는 소목차와 페이지 정보가 담겨있다.

 

(1) Branch Blocks (브랜치 블록)

브랜치 블록은 인덱스의 상위 수준의 블록으로 검색 경로 정보를 담고 있다. 브랜치 블록은 하위 리프 블록들의 데이터 범위(range)와 다음 블록(다음 브랜치 블록 또는 리프 블록)을 가리키는 포인터를 가지고 있다.
브랜치 블록에는 단순히 리프 블록까지의 경로를 제공하는 역할을 한다.

(2) Leaf Blocks (리프 블록)

리프 블록은 실제 데이터와 연결되는 정보(Rowid)를 관리한다. 리프 블록은 인덱스 키와 해당 키의 Rowid(데이터가 저장된 테이블의 행을 식별하는 정보)를 저장하고 있다.

리프 블록은 더블 링크드 리스트(double linked list) 형태로 연결되어 있어 왼쪽➡️오른쪽 리프 블록으로 찾아가거나 반대로 오른쪽➡️왼쪽 리프 블록으로 찾아갈 수 있다.

 

1.2 왜 인덱스를 사용해야 할까?

인덱스를 사용하는 가장 큰 이유는 인덱스는 데이터의 정렬을 유지하고 있기 때문에 (1) 필요한 데이터를 빠르게 찾거나 (2) 소트 연산을 생략할 수 있기 때문이다.

 

참고로, 데이터 구조별 액세스 경로는 다음과 같이 정리할 수 있다.

일반적인 테이블(Heap-Organized Tables)의 데이터에 접근하기 위해서 오라클은 세 가지 접근 방식을 지원하고 있다.

• Full Table Scan (테이블 전체 스캔)
• Table Access by Rowid (Rowid를 사용한 테이블 액세스)
• Sample Table Scan (테이블 샘플 스캔)

 

(1) Full Table Scan (테이블 전체 스캔)

Aggregation 함수(sum, average, count 등)를 사용하기 위해서 등 테이블 전체 데이터가 필요한 상황이라면 당연히 Full Table Scan 방식으로 데이터에 접근해야 한다.

 

하지만 단건의 데이터만 필요하다든지 Where 조건에 해당하는 일부 데이터만 추출하고 싶은 경우라면 Full Table Scan 방식으로 전체 테이블을 읽어들이는 것은 고비용에 비효율적일 수 있다. (Disk O/I뿐 아니라 메모리를 낭비하게 된다.)

 

(2) Table Access by Rowid (Rowid를 사용한 테이블 액세스)

테이블에서 특정 데이터만 읽어오고 싶을 때 활용할 수 있는 방식이 Rowid를 먼저 알아내고, Rowid에 해당하는 데이터만 읽어오는 방식이다.

내가 조회하고자 하는 데이터 로우의 Rowid는 어떻게 알 수 있을까? 원하는 데이터의 Rowid를 알고 있는 경우는 극히 드물다.

이때 주로 활용되는 것이 인덱스 스캔이다. 인덱스를 통해서 조건에 해당하는 데이터를 빠르게 식별한 뒤 인덱스에 기록된 Rowid를 참조해 테이블에 접근하는 방식이다.

달리 말하면, 인덱스를 통해서 테이블에 접근하지 않는다면, Table Full Scan이 일어날 수 밖에 없다. 따라서 Table Full Scan을 피하기 위해서는 인덱스의 활용이 필수적이다.

 

(3) Sample Table Scan (테이블 샘플 스캔)

테이블에서 랜덤하게 데이터를 조회하는 방식으로, `SAMPLE` clause를 기술하는 경우 이 방식으로 액세스 된다.

 

(예시)

SELECT * FROM hr.employees SAMPLE BLOCK (1);

 

결론적으로는, 테이블에서 특정 조건에 해당하는 일부 데이터를 읽고자 하는 경우, 세 가지 접근 방식 중 Table Access by Rowid가 (대체로) 가장 유리하기 때문에 이 방식으로 데이터에 접근하기 위해서 인덱스를 사용한다. 

 

---

2. 인덱스 스캔 방식

앞서 인덱스를 어떤 경우에 왜 사용하는지에 대해서 다루어 보았다. 인덱스는 특정 조건의 일부 데이터 추출하고자 하는 경우에, 빠르게 데이터를 찾기 위해 사용한다.

이번 파트에서는 인덱스가 어떻게 사용되는 지에 대해서 이야기해 본다.

 

오라클은 각각 특정한 검색 요구사항과 데이터 구조에 최적화한 인덱스 스캔 방식을 제공하고 있다. 오라클에서 제공하는 인덱스 스캔 방식 중 다섯 가지를 소개한다. 

 

2.1 Index Unique Scan

인덱스 유니크 스캔은 인덱스에서 단일 행(정확히 하나의 Rowid)을 검색하기 위해서 사용된다.

`/*+ INDEX(table index_name) */` 힌트로 옵티마이저에게 스캔 방식을 요구할 수 있다.

 

고유 제약 조건을 사용하는 경우 가장 효율적인 방식이다. 이 방식이 사용되려면 조건 컬럼을 쿼리에서 가공하지 않아야하고, '=' 조건이 인덱스의 선두에 있어야 한다.

 

SELECT * 
FROM table 
WHERE primary_key = 123;

 

위 예시 쿼리에서 primary_key 컬럼이 인덱스 선두컬럼이어야 인덱스를 유니크 스캔할 수 있다.

또한 WHERE 조건을 기술할 때 `TO_NUMBER(primary_key) = 123` 과 같이 조건컬럼을 함수로 가공해서는 안 된다.

 

2.2 Index Range Scan

인덱스 레인지 스캔은 인덱스에서 특정 범위에 해당하는 데이터를 검색할 때 사용되는 스캔 방식이다.

범위 조건 (`BETWEEN`, `<`, `>`, `>=`, `<=`)이 포함된 쿼리일 때 주로 사용된다.

 

(1) Index Range Scan (Ascending)

기본적으로 인덱스는 오름차순(Ascendig)으로 정렬되어 있기 때문에 일반적인 경우에는 인덱스 스캔도 오름차순 스캔을 한다.

`/*+ INDEX(table index_name) */` 힌트로 옵티마이저에게 스캔 방식을 요구할 수 있다.

유니크 스캔과 마찬가지로 (1) 조건 컬럼이 인덱스 선두에 있어야 하며 (2) 조건 컬럼이 가공되지 않아야 인덱스 레인지 스캔이 정상적으로 일어날 수 있다.

SELECT * 
FROM table 
WHERE column BETWEEN 10 AND 20;

 

인덱스 레인지 스캔을 사용하는 경우 성능 최적화를 위해서 스캔 범위와 테이블 액세스를 최소화할 수 있도록 인덱스 구성을 조정한다.

 

(2) Index Range Scan Descending

인덱스 레인지 스캔 디센딩은 인덱스를 내림차순으로 탐색하는 스캔 방식이다.

`/*+ INDEX_DESC(table index_name) */` 힌트로 옵티마이저에게 인덱스 레인지 스캔 디센딩 방식을 요구할 수 있다.

 

`ORDER BY column DESC` 조건이 포함된 쿼리를 하거나 `MAX()` 함수를 사용할 때 이용된다.

SELECT * 
FROM table 
WHERE column < 50 
ORDER BY column DESC;

 

마찬가지로 잘 활용하면 소트 연산을 생략할 수 있어 유용하다.

 

2.3 Index Full Scan

인덱스 풀 스캔은 인덱스를 전체적으로 탐색해야 하는 경우 사용되는 스캔 방식이다.

`/*+ INDEX(table index_name) */` 힌트로 옵티마이저에게 이 스캔 방식을 요구할 수 있다.

 

`WHERE`절 없이 인덱스에서 모든 데이터를 읽어야 할 때 주로 사용된다.

인덱스가 테이블보다 작고, 인덱스에 `SELECT` 컬럼을 다 가지고 있을 때 테이블 풀 스캔보다 성능이 좋다.

또한 인덱스는 정렬이 되어 있는 상태이므로, 소트 연산을 생략할 수 있는 이점이 있다.

SELECT col1, col2
FROM table
ORDER BY col1, col2;

 

위 예시 쿼리에서는 col1, col2 컬럼이 인덱스에 모두 있고, 쿼리와 같은 정렬을 하고 있는 인덱스 일 때 인덱스 풀 스캔을 통해 필요 정보를 모두 읽어올 수 있고 소트 연산 또한 생략될 수 있다.

인덱스에 co1 컬럼만 존재하고 col2 컬럼이 없다면, 인덱스를 풀스캔 하더라도 인덱스에서 얻은 Rowid별로 테이블에 방문해서 col2의 정보를 얻어와야 하므로 이런 경우에는 테이블 풀스캔이 더 유리하다.

 

2.4 Index Fast Full Scan

인덱스 패스트 풀 스캔은 전체 인덱스를 병렬로 읽어서 전체 데이터를 스캔하는 방식이다.

`/*+ INDEX_FFS(table index_name) */` 힌트로 인덱스 패스트 풀 스캔을 옵티마이저에게 요구할 수 있다.

순차적으로 읽어야 하는 경우 하나의 블록씩 읽어야 하지만, 병렬로 읽기 때문에 더 빠른 읽기 성능이 보장된다. 대용량의 데이터를 처리해야 할 때 많은 이점이 있다.

단, 병렬로 인덱스 블록들을 읽기 때문에 조회 결과의 정렬 순서가 보장되지 않는다.

 

2.5 Index Skip Scan

인덱스 스킵 스캔은 인덱스 레인지 스캔을 여러번 반복 수행하는 방식으로 이해하면 된다.

주로 복합 인덱스의 첫 번째 컬럼이 조건에 포함되지 않았거나 IN-List 탐색을 위해 사용된다.

`/*+ INDEX_SS(table index_name) */` 힌트로 인덱스 스킵 스캔을 옵티마이저에게 요구할 수 있다.

 

아래 예시 쿼리에서  복합 인덱스 (col1, col2)가 있다고 가정하자.

col1이 조건에 없고 col2만 검색 조건에 있는 경우, 스킵 스캔이 일어날 수 있다.

SELECT * 
FROM table 
WHERE col2 = 'value';

 

또는, col1 컬럼을 In-list 조건으로 조회하는 경우, IN-list 요소 수 만큼 반복적으로 스킵스캔이 일어날 수 있다.

SELECT * 
FROM table 
WHERE col1 IN ('value1', 'value2');

 

지금까지 오라클의 대표적인 인덱스 접근 방식 다섯가지에 대해 알아보았다.

 

 

---

 

3. 인덱스를 태우는 SQL과 못 태우는 SQL

인덱스를 의도한 바대로 이용하기 위해서는 SQL을 이에 맞게 작성해주어야 한다. 즉, 인덱스를 사용해서 데이터에 접근할 수 있도록 쿼리를 작성해주어야 한다.

 

인덱스를 잘 활용할 수 있게 하는 SQL 기법에는 여러가지가 있고, 인덱스 설계와 `WHERE` 조건 등에 따라서 최적의 SQL은 달라지지만 가장 중요한 점만 추려보았다.

 

3.1  SQL Tip💫

간단하게 기억하고 당장 적용할 수 있는 SQL 작성 팁을 소개한다.

첫번째, 인덱스 컬럼 가공을 피하자

인덱스 컬럼을 가공하게 되면 인덱스의 정상적 사용이 원천적으로 불가능하다. (인덱스를 사용하더라도 인덱스 풀스캔이 일어나거나 인덱스의 정렬을 그대로 사용하지 못하는 등 비효율을 일으킨다.)

`WHERE`절을 기술할 때 좌변(인덱스 컬럼)을 가공하지 않고 우변 조건을 바꾸어서 해결가능 하지 않은지 체크하자.

옵티마이저에 의해 일어나는 묵시적 형변환도 조심해야 한다.

 

두번째, 선두에 '%'를 붙혀서 LIKE 문을 작성하는 습관을 버리자

습관적으로 양쪽에 '%'를 모두 붙혀서 작성된 쿼리를 보는 경우가 많다.

이 경우 인덱스 레인지 스캔이 어려워지고 풀 스캔을 일으키게 된다.

선두의 '%' 조건이 꼭 필요한 것인지 검토해보고, 범위 검색으로 대체할 수 있는지 고민해야 한다.

 

세번째, 범위 검색과 등치조건 (`=`, `IN`)을 활용하자

인덱스 유니크 스캔과 레인지 스캔이 일어날 수 있으려면 조건이 범위이거나 등치여야 한다.

 

3.2 SQL 개선 예시

구체적으로 인덱스 활용에 걸림돌이 되는 나쁜 SQL과 개선된 SQL을 소개한다.

• `SUBSTR(컬럼, 1, 3) = :var` ➡️ `컬럼 LIKE :var||‘%’` 

컬럼 가공의 대표적 사례다.

`컬럼 LIKE :var||‘%’` 의 형식으로 대체해서 사용하자.

 

• `NVL(컬럼, 0) > 0` ➡️ `컬럼 IS NOT NULL`

`NVL` 함수나 `COALESCE` 함수도 컬럼 가공의 대표적 사례다.

`컬럼 IS NOT NULL` 로 대체하자.

 

• `OR`, `IN` ➡️ `UNION ALL`

옵티마이저에 따라서 `UNION ALL`로 쿼리를 변환해주기도 한다.

하지만 때로 `OR`이나 `IN` 조건 때문에 인덱스를 사용하지 않는 실행계획을 수립하는 때도 있다.

실행계획이 만족스럽지 않다면 `UNION ALL`을 사용해서 쿼리를 수정해주거나 힌트를 명시하자.

 

• `입사일자 = 20221114`  ➡️ `입사일자 = TO_CHAR(20221114)`

이 쿼리는 문제가 될 수도 안 될 수도 있다.

입사일자 컬럼이 VACHAR 타입이라면, 묵시적 형변환(`TO_NUMBER(입사일자) = 20221114`)이 일어나면서 인덱스를 사용하지 못하게 될 수도 있다.

컬럼 데이터타입을 확인하고 묵시적 형변환이나 컬럼 가공이 일어나지 않도록 적절히 쿼리를 작성해주어야 한다.

 

 

지금까지 오라클에서 인덱스란 무엇이며, 인덱스 스캔 방식 다섯가지, 인덱스 활용을 위해서 SQL 작성 시 주의 사항을 알아보았다.

 

 

---

참고 자료

1. Oracle, SQL Tunning Guide(2021)

2. 「친절한 SQL 튜닝」 , 조시형(2018)