[Oracle] 오라클 인덱스 설계

인덱스 설계 전략

- 조건절에 항상 사용되거나, 자주 등장하는 컬럼들을 선정

- '=' 조건으로 자주 조회되는 컬럼들을 앞쪽에 위치

-- 총 고객수 100만 명, 상품은 10만 개, 거래일자의 검색범위는 유동적인 테이블 

[검색조건 1] 
WHERE 고객번호 = '0000001'
     AND 거래일자 BETWEEN '20150101' AND '20171001';

[검색조건 2]
WHERE 상품번호 = '1'
     AND 거래일자 BETWEEN '20150101' AND '20171001';

[검색조건 3]
WHERE 고객번호 = '0000001'
     AND 상품번호 = '1'
     AND 거래일자 BETWEEN '20150101' AND '20171001';

[검색조건 4]
WHERE 거래일자 BETWEEN '20150101' AND '20171001';

1) 일단 '=' 조건으로 자주 등장하는 컬럼은 고객번호와 상품번호인데 고객번호는 100만 건, 상품번호는 10만 건이니 우선순위로 볼 때 고객번호가 선두에 놓여야 효율적일 확률이 높음

2) 인덱스 개수는 불가피한 경우를 제외하고는 많이 만들지 않는게 좋으므로 2개 ~ 3개로 한정

3) 내 마음속의 후보들

• 후보1 IX1[고객번호+거래일자+상품번호] / IX2[상품번호+거래일자] / IX3[거래일자]
• 후보2 IX1[고객번호+거래일자+상품번호] / IX2[거래일자] => 사실 상 후보1에서 상품번호+거래일자만 제외한 것임

4) Let's 실습!!

CREATE TABLE MINA.DEAL AS

SELECT LPAD(TRIM(TO_CHAR(ROWNUM)),7,'0') CUST_NO,

      DECODE(MOD(ROWNUM,10),0,ROWNUM/10,TRUNC(ROWNUM/10)+1) GOODS_CODE,

      'GOODS'||ROWNUM GOODS_NAME,

      SYSDATE-ROUND(DBMS_RANDOM.VALUE(10, 1000),0) DEAL_DATE

  FROM DUAL 

  CONNECT BY LEVEL <= 1000000 ;

 

※ SELECT 절은 모든 컬럼을 가져오도록 했으며 실제로 그럴 일은 없겠지만 고객 한 명당 한 건의 거래 내역만 존재한다고 가정함

 

[검색조건 1]

- 후보1과 후보2 모두 IX1[CUST_NO, DEAL_DATE, GOODS_CODE] 이용

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |      1 |      1 |00:00:00.01 |       4 |
|*  1 |  FILTER                      |      |      1 |      1 |00:00:00.01 |       4 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEAL |      1 |      1 |00:00:00.01 |       4 |
|*  3 |    INDEX RANGE SCAN          | IX1  |      1 |      1 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------

 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(TO_DATE('20150101')<=TO_DATE('20171001'))
   3 - access("CUST_NO"='0000001' AND "DEAL_DATE">='20150101' AND
              "DEAL_DATE"<='20171001')

▶ INDEX RANGE SCAN, 비효율 거의 없음

[검색조건 2]

- 후보1

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | A-Rows |   A-Time   | Buffers | 
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |      1 |     10 |00:00:00.01 |       4 |
|*  1 |  FILTER                      |      |      1 |     10 |00:00:00.01 |       4 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEAL |      1 |     10 |00:00:00.01 |       4 |
|*  3 |    INDEX RANGE SCAN          | IX2  |      1 |     10 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(TO_DATE('20150101')<=TO_DATE('20171001'))
   3 - access("GOODS_CODE"=1 AND "DEAL_DATE">='20150101' AND "DEAL_DATE"<='20171001')
   
▶ IX2[GOODS_CODE, DEAL_DATE]를 이용하여 INDEX RANGE SCAN, 비효율 거의 없음

- 후보2

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |      1 |     10 |00:00:00.05 |    4314 |
|*  1 |  FILTER                      |      |      1 |     10 |00:00:00.05 |    4314 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEAL |      1 |     10 |00:00:00.05 |    4314 |
|*  3 |    INDEX FULL SCAN           | IX1  |      1 |     10 |00:00:00.05 |    4313 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(TO_DATE('20150101')<=TO_DATE('20171001'))
   3 - access("DEAL_DATE">='20150101' AND "GOODS_CODE"=1 AND
              "DEAL_DATE"<='20171001')
       filter(("GOODS_CODE"=1 AND "DEAL_DATE">='20150101' AND
              "DEAL_DATE"<='20171001'))
    
▶ 마땅한 인덱스가 없어 IX1[CUST_NO, DEAL_DATE, GOODS_CODE]를 이용, GOODS_CODE가 선두 컬럼이 아닌 관계로 INDEX FULL SCAN을 했으며 이로 인해 메모리에서 읽은 block 수가 적진 않음 (10 Rows를 읽기 위해 4314개의 block 스캔)

[검색조건 3]

- 후보1과 후보2 모두 IX1[CUST_NO, DEAL_DATE, GOODS_CODE] 이용

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |      1 |      1 |00:00:00.01 |       4 |
|*  1 |  FILTER                      |      |      1 |      1 |00:00:00.01 |       4 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEAL |      1 |      1 |00:00:00.01 |       4 |
|*  3 |    INDEX RANGE SCAN          | IX1  |      1 |      1 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(TO_DATE('20150101')<=TO_DATE('20171001'))
   3 - access("CUST_NO"='0000001' AND "DEAL_DATE">='20150101' AND
              "GOODS_CODE"=1 AND "DEAL_DATE"<='20171001')
       filter("GOODS_CODE"=1)

▶ GOODS_CODE가 '=' 조건임에도 선행 컬럼인 DEAL_DATE가 범위 조건인 관계로 필터 조건으로 빠짐, 그럼에도 불구하고 선두 컬럼인 CUST_NO의 변별력이 막강하여 비효율 거의 없음

 

[검색조건 4]

- 후보1과 후보2 모두 [DEAL_DATE]  인덱스 이용
 
(이 쿼리는 XPlan으로 보려니 자꾸 COLLECTION ITERATOR PICKLER FETCH 가 나옴
FETCH 수가 너무 많아서 그런가 하고 검색 범위를 줄여도 마찬가지.. (아시는 분 댓글 좀 ㅠㅠ)
그래서 Sql-Developer 실행 계획으로 확인)

▶ INDEX RAGNE SCAN 이용, 비효율은 거의 없음

☞ [검색조건 2]와 같은 쿼리가 자주 수행된다면 후보1 인덱스가 더 적합하다고 볼 수 있음

* 그밖의 좋은 인덱스 후보

IX1[고객번호+거래일자] / IX2[거래일자+상품번호+고객번호]

▶ 자주 입력되는 거래일자 범위가 아주 넓지만 않다면 성능이 그다지 나쁘지 않음

인덱스 설계 시 시스템 전체적인 관점에서 고려해야 할 요소

- 쿼리 수행 빈도

- 업무상 중요도

- 클러스터링 팩터

- 데이터량

- DML 부하(= 기존 인덱스 개수, 초당 DML 발생량, 자주 갱신되는 컬럼 포함 여부 등)

- 저장 공간

- 인덱스 관리 비용

결합 인덱스 컬럼 순서 결정 시, 선택도 이슈

- '=' 조건으로 항상 사용되는 컬럼들을 앞쪽에 위치시켰다면, 그 중 선택도가 낮은 것을 앞쪽에 두려는 노력은 의미가 없는 것이거나 오히려 손해일 수 있음

- 선택도가 높은 컬럼이 선두에 있을 경우 선두 컬럼이 조건절에서 빠졌을 때 INDEX SKIP SCAN을 이용하거나 IN-List로 튜닝 가능 (단, 선두 컬럼의 Distinct Value 개수가 충분히 적어야 함)

- 선택도가 낮은 컬럼이 선두에 있을 경우 인덱스 중간에 범위검색 조건이 와도 비효율이 크지 않음 (ex: 고객번호 + 거래일자 + 상품번호)  

소트 오퍼레이션을 생략하기 위한 컬럼 추가

CREATE INDEX T_IDX ON T(A, B, C, D);

▼

SELECT * FROM T WHERE A = 1  ORDER BY A, B, C;

SELECT * FROM T WHERE A = 1 AND B = 1 ORDER BY C, D;

SELECT * FROM T WHERE A = 1 AND C = 1 ORDER BY B, D;

SELECT * FROM T WHERE A = 1 AND B = 1 ORDER BY A, B, C, D;

=> 모두 ORDER BY 소트 오퍼레이션이 생략됨

* 인덱스를 이용한 소트 오퍼레이션 대체가 불가능한 경우

SELECT * FROM T WHERE A = 1 ORDER BY C;

SELECT * FROM T

WHERE A = 1

    AND B BETWEEN 1 AND 2

ORDER BY C, D;

SELECT * FROM T

WEHRE A = 1

    AND B BETWEEN 1 AND 2

ORDER BY A, C, B;