Index

테이블의 데이터를 좀 더 빠르게 검색하기 위해 사용하는 데이터베이스 객체

Oracle Server가 Optimization method에 따라 어떤 인덱스를 사용할지, 인덱스를 사용하지 않을 지 결정한다

특징

• 소량 검색(전체 데이터의 5 ~ 10%)에 유리하다
  → 소량 검색일 때 오라클 서버가 인덱스를 사용한다
• PK, UK에 대해서는 인덱스가 자동으로 생성된다
• DML 시 인덱스가 재정렬된다
• 인덱스는 논리적, 물리적으로 테이블과 독립적이다

인덱스를 유지하려면 그만큼 저장공간이 필요하고 테이블 수정 시에 인덱스도 변경되어야 하므로 필요한 경우에만 인덱스를 생성하여 사용하는 것이 좋다

인덱스를 생성하면 좋은 경우

• WHERE 절이나 조인 조건에서 컬럼을 자주 이용할 때
  → hash join을 제외하면 조인 시 정렬을 해야 하기 때문
• 컬럼이 넓은 범위의 값을 가질 경우
• 많은 NULL 값을 갖는 경우
  → 인덱스는 NULL을 저장하지 않기 때문
• 테이블의 데이터가 많고 조회되는 행의 수가 전체의 10 ~ 15% 정도인 경우

인덱스를 생성할 필요가 없는 경우

• 테이블이 작을 때
• 컬럼이 조회 조건으로 사용되는 경우가 별로 없는 경우
• 대부분의 조회가 10 ~ 15% 이상의 데이터를 조회하는 경우
• 테이블이 자주 변경되는 경우 (UPDATE, INSERT, DELETE)

인덱스 구조

Oracle 에서는 기본적으로 B*Tree 구조로 인덱스를 생성한다

B Tree : B(Balanced)는 트리 높이가 균형 잡혀있다는 뜻으로 높이 차가 최대 1인 트리를 말한다. 때문에 B Tree 는 어떤 값에 대해서도 같은 시간에 결과를 얻을 수 있다(O(logN)). 또한, B Tree는 BST와 달리 여러 자식 노드를 가질 수 있다

• 인덱스는 인덱스 컬럼의 값을 key로 그에 해당하는 row의 ROWID를 value로 저장한다
• 인덱스 컬럼이 여러 개인 경우, key에 그 값들을 tuple로 저장한다
• Oracle DB에서 fan-out 값(한 인덱스 노드가 가질 수 있는 자식 노드 수)는 보통 약 200 ~ 400의 값을 가진다
  • 인덱스 블록 크기와 키 크기, ROWID 크기에 따라 자동으로 결정
  • 1000만건 데이터가 깊이 3 정도를 가진다고 보면 된다
• 리프 노드 하나는 디스크 블록(8KB) 단위로 구성되어 있다
  • 리프 노드 내부에는 key-value 쌍이 정렬된 배열 구조로 저장되어 있다

B-Tree vs. B+Tree vs. B*Tree

항목	B-Tree	B+Tree	B*Tree
데이터 저장 위치	내부 노드와 리프 노드 모두에 저장	🔸 데이터는 리프 노드에만 저장	🔸 B+Tree 기반, 데이터는 리프 노드에만 저장
검색 종료 위치	중간 노드에서도 검색 종료 가능	🔸 항상 리프 노드까지 내려감	🔸 B+Tree와 동일, 리프 노드에서만 검색 종료
리프 노드 연결	❌ 없음	✅ 좌우 리프 노드 연결	✅ 연결 + 밀도 높은 구조(범위 탐색 최적화)
노드 분할 조건	50% 이상 찼을 때 분할	50% 이상 찼을 때 분할	🔸 형제 노드와 공간 공유 후 70% 이상일 때 분할 시도
탐색 성능	빠름 (단일 값 검색)	✅ 범위 + 단일 모두 효율적	✅ 가장 효율적, 디스크 I/O 감소
사용 예시	이론 설명, 알고리즘 교재 등	일부 DBMS, 파일 시스템	✅ 대부분의 DBMS (Oracle, MySQL 등)

• B-Tree가 중간 노드에서 검색 종료가 가능하여 B+Tree 보다 검색이 빠른 경우가 있을 수도 있지만, 실제로 동일한 자식 노드 수를 가진다고 했을 때 B-Tree의 깊이가 더 깊고 B+Tree가 얕으므로 비슷한 탐색 시간을 갖는다
• B-Tree와 B+Tree의 핵심적인 차이는 데이터를 리프 노드에만 저장하느냐 와 리프 노드 간 연결이 되어있느냐 이다
• B+Tree와 B*Tree의 핵심적인 차이는 리프 노드 분할에 있다
  • B+Tree는 리프 노드에 key-value 쌍이 50% 이상 차 있으면 새로운 리프 노드를 생성해서 넘치는 데이터를 현재 넣으려는 리프 노드에서 절반 정도 분할하여 넣는다
    → 50% 이상일 때 분할하므로 공간 낭비가 생기고, 새로운 리프 노드에 분할하므로 상위 노드의 구조를 변경해야 하는 오버헤드가 생긴다
  • B*Tree는 형제 노드(오른쪽 노드)에 공간이 있다면 그 공간을 사용해서 새로운 데이터를 저장한다
    → 위의 단점을 어느 정도 해결

인덱스가 사용되지 않는 경우

인덱스 컬럼이 비교되기 전에 변형된 경우

WHERE UPPER(name) = 'JOHN'

예시와 같이 함수를 사용해서 컬럼값을 변형한 경우 인덱스를 사용하지 않는다

부정으로 조건을 기술한 경우

WHERE title <> '사원'

인덱스 컬럼이 NULL로 비교할 경우

WHERE phone IS NULL

LIKE '%패턴' 인 경우

WHERE name LIKE '%son'

데이터 타입이 일치하지 않는 경우

WHERE emp_id = '100'

emp_id 가 NUMBER 타입일 때 암묵적 형변환으로 비교는 가능하지만, 인덱스를 사용하지 않는다

IN, OR

OR 는 거의 인덱스 사용을 하지 않는다고 보면 된다
IN 은 보통 인덱스 사용이 가능하지만 특정 경우에는 사용하지 않는다

값이 너무 많은 경우

WHERE dept_id IN (10, 20, 30, ...);

예시와 같이 리스트에 데이터가 너무 많거나 서브쿼리로 작성했을 경우, 쿼리 결과가 매우 많을 경우 인덱스를 사용하지 않는다

복합 인덱스의 후행 컬럼이고 선행 컬럼 조건 없음

인덱스 컬럼이 emp_id, name, dept_id 라고 할 때

WHERE dept_id IN (10, 20, 30);

예시 에서 후행 컬럼인 dept_id 에 IN을 사용했으므로 인덱스 적용이 되지 않는다. 앞에 emp_id 와 name 에 대한 조건이 있으면 인덱스 사용이 가능하다

그 외

위에서 기술한 조건들이 IN의 리스트 안에도 적용될 경우 인덱스를 사용하지 않는다

• 리스트 내에 NULL이 있는 경우
• 리스트 내에서 함수 사용이나 암묵적 형변환에 의해 데이터가 변형된 경우