[Oracle] 결합 인덱스

결합 인덱스

두 개 이상의 컬럼을 합쳐서 인덱스를 만드는 것.

 

SQL 문장에서 WHERE절의 조건 컬럼이 2개 이상 AND 로 연결되어 함께 사용되는 경우에 결합 인덱스를 사용

                                                               2개 이상의 조건이 OR로 조회되는 경우는 결합 인덱스를 사용하면 안된다

첫번째 조건에서 최대한 많은 데이터를 걸러, 두번째 검사를 쉽게 만들어 주어야 한다.

컬럼의 순서대로 1차 필터링, 2차 필터링 하는 개념

 

결합 인덱스의 경우 첫번째 컬림이 사용되지 않는다면 그 결합 인덱스는 사용되지 않는다.

ex ) 만약 id라는 컬럼은 조건절에 잘 사용되지 않는다면 결합 인덱스의 첫 컬럼으로 사용하지 않던가,

별도의 인덱스를 생성하여 사용하는 것이 바람직

 

 

※ 결합 인덱스 생성 시에 주의할 점은 '얼마나 자주 사용되는가'

  WHERE 절에서 AND 조건으로 자주 결합되어 사용되면서

  각각의 분포도 보다는 두 개 이상의 컬럼이 결합되었을 때 분포도가 양호해지는 컬럼들의 조합

 

---

결합 인덱스를 구성하는 컬럼의 순서

SELECT 카드번호, 사용액
  FROM 거래내역
 WHERE 거래일자 = '200803'
   AND 사용_구분 = '정상'

WHERE 조건에 존재하는 거래 일자, 사용_구분 컬럼을 활용하여 인덱스를 만들 수 있을 것

위 컬럼들을 이용해 인덱스를 생성하면 항상 처리 범위가 감소할까? 그건 아니다.

→ WHERE 절에 사용하는 연산자의 종류를 이해해야 한다.

 

• 점 조건
  • =, IN 연산자를 이용한 조건
  • 해당 연산자는 하나의 점만을 의미한다.
• 선분 조건
  • LIKE, BETWEEN, <, > 등과 같이 점 조건을 제외한 연산자를 사용한 조건
  • 해당 조건을 만족하는 모든 실수를 의미한다.

1. 점 조건 + 점 조건 : 두 조건에 의해 처리 범위 감소
2. 점 조건 + 선분 조건 : 두 조건에 의해 처리 범위 감소
3. 선분 조건 + 선분 조건 : 앞의 선분 조건에 의해 처리 범위 감소
4. 선분 조건 + 점 조건 : 앞의 선분 조건에 의해서만 처리 범위 감소

→ 뒤의 컬럼이 점 조건이더라도 처리할 범위는 줄어들지 않는다.

     따라서 앞선 칼럼들의 '=' 비교가 중요하다.

 

예시)

SELECT 카드번호, 사용액
  FROM 거래내역
 WHERE 거래일자 > '200803' 
   AND 사용_구분 = '정상'

거래일자 : 선분 조건

사용_구분 : 점 조건

→ 최적의 인덱스를 이용하고자 한다면 (사용_구분 + 거래 일자) 인덱스를 생성해야한다.

 

→ 인덱스에서 가장 중요한 요소는 처리 범위를 최소화 시키는 것

그 중심에는 결합 칼럼 인덱스가 있다. 생성 시에 점 조건과 선분 조건의 순서에 의해 처리 범위가 변하니 주의

 

---

선행 컬럼 우선 순위

선행 컬럼은 다음 조건에 해당하는 것이 유리하다

1. WHERE 절에서 자주 검색되는 컬럼
2. 분포도가 좋은 컬럼 ( = 로 조회할 경우. 분포도보다 연산자를 더 고려해야 한다.)
3. 데이터 양이 적은 컬럼
4. 동등조건으로 검색되는 컬럼 (BETWEEN, > AND < , LIKE 연산자로 검색되지 않는 컬럼)

 

※ 결합 인덱스를 생성할때는 가장 적은 데이터를 가진 컬럼을 선행 컬럼으로 결정하는 것이 유리한 이유

→ 첫번째 컬럼 (1차 필터링) 에서 많이 걸러줘야 하는데, 값이 적은 것부터 걸러야 많이 거를 수 있다.

 

 

ex) 28명의 남자들 중에서 pen을 든 사람을찾는 것보다,

pen을 든 사람들 중에서 남자를 찾는 것이 더 빠르다.

→ 따라서 (having-item, gender)의 순서로 composite Index를 거는게 유리하다.

즉 빈도수가 적은 인덱스를 먼저 거는게 더 좋다.