[Oracle Tunning] 인덱스 활용과 튜닝

Clustering Factor

- 데이터가 모여 있는 정도를 수치로 보여주는 것
- 데이터 분포도에 따라서 데이터 블록에 접근하는 횟수가 달라지기 때문에 성능 측면에서 중요한 요소이다.

- 클러스터링 펙터 출력 쿼리

SELECT 
	T.TABLE_NAME, 
	I.INDEX_NAME, 
	T.BLOCKS, 
	T.NUM_ROWS, 
	I.CLUSTERING_FACTOR
FROM USER_TABLES T, USER_INDEXES I
WHERE I.TABLE_NAME = T.TABLE_NAME
	AND T.TABLE_NAME IN ('ORD_ITEM', 'ORD_ITEM_RANDOM');

- 클러스터링 팩터는 테이블 자체로는 좋다 나쁘다고 할 수 없다. 특정 인덱스를 이용할 때, 해당 인덱스으 클러스터링 팩터가 좋은지 나쁜지를 말할 수 있다.
- 클러스터링 팩터의 값이 블록 수에 가까우면 좋은 것이고, 로우 수에 가까우면 나쁜 것이다.

---

기존 인덱스에 컬럼 추가하기

아래와 같은 테이블과 인덱스 구성이 있다고 가정하자.
    - 테이블
        - 상품 ID(PK)
        - 상품명
        - 상품구분코드
        - 판매여부
    - 인덱스
        - 상품구분코드
        - 상품명
 

위와 같은 인덱스를 사용하여

SELECT * FROM 상품 WHERE 상품구분코드 = '100101' AND 판매여부 = 'Y';

 

쿼리를 날리면

1. 상품 구분 코드 인덱스를 스캔하고 상품 구분 코드가 100101인 데이터를 모두 테이블에서 랜덤 엑세스 한 뒤, 판매여부가 조건에 맞는 데이터를 찾는다.

2. 조건으로 붙이는 상품구분코드 + 판매여부 순서로 된 인덱스를 추가하면 가장 좋겠지만 이런 식으로 인덱스를 추가하면 추후에 다른 조건으로 select를 수행할 때마다 비슷한 식으로 인덱스를 추가해야 한다. 

3. 이런 상황에서는 기존 인덱스의 맨 뒤에 판매여부 칼럼을 추가하여 사용해야 한다.

4. 인덱스의 용량은 증가하겠지만, 테이블로의 래덤 엑세스는 최종 결과 집합에 해당하는 데이터만큼만 엑세스 하기 때문에 성능면에서 좋은 효과를 볼 수 있다.

다른 경우로 ORD_X01 => ORD_DT + ORD_HMS가 있고

SELECT 
	COUNT(UPPER_CASE)
FROM ORD
WHERE ORD_DT BETWEEN '20120101' AND '20120228'
AND SHOP_NO = 'SH0001';

위 쿼리를 날렸을 때, SHOP_NO가 인덱스에 없는 칼럼이기 때문에 테이블의 모든 데이터에 엑세스하여

SHOP_NO = 'SH0001' 조건을 처리하여 6213건을 최종 집합으로 만든다.

-------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |         |        |       | 32551 (100)|          |
|   1 |  SORT AGGREGATE                      |         |      1 |    27 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ORD     |   6213 |   163K| 32551   (1)| 00:00:02 |
|*  3 |    INDEX RANGE SCAN                  | ORD_X01 |   1251K|       |  4692   (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------

 

즉, 125만 건 이상의 INDEX RANGE SCAN이 불필요한 것이다.

이럴 때도 기존 인덱스에 SHOP_NO 컬럼을 추가하면 SHOP_NO만 가지는 새 인덱스를 추가하는 것 보다 효율적이다. (새 인덱스를 스캔하는 시간을 줄이기 때문에)

DROP INDEX ORD_X01;
CREATE INDEX ORD_X01 ON ORD(ORD_DT, ORD_HMS, ORD_NO);

 

이제 동일한 쿼리를 날려보면

-------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |         |        |       |  1080 (100)|          |
|   1 |  SORT AGGREGATE                      |         |      1 |    27 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ORD     |   6213 |   163K|  1080   (1)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | ORD_X01 |   6213 |       |   915   (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------

 

랜덤 액세스 횟수가 비약적으로 감소하는 것을 확인할 수 있다.

---

PK 인덱스 확장하기

PK 인덱스를 변경할 때 주의할 사항은 기존 PK 컬럼들을 선두에 놓아야 한다는 것이다.

PK 인덱스의 변경 처리는 아래와 같다.

CREATE TABLE ITEM (
	ITEM_ID NUMBER(10),
	ITEM_NM VARCHAR2(100),
	ITEM_TYPE_CD VARCHAR2(6),
	SALE_YN VARCHAr2(1),
	...
);

CREATE INDEX ITEM_PK ON ITEM(ITEM_ID, ITEM_NM);
ALTER TABLE ITEM ADD CONSTRAINT ITEM_PK PRIMARY KEY (ITEM_ID) USING INDEX ITEM_PK;

---

인덱스 스캔 범위 계산

SELECT * FROM 상품 WHERE 상품명 = '불고기버거' AND 상품구분코드 BETWEEN '100100' AND '100101';

위 쿼리가 있을 때,

INDEX01(상품구분코드, 상품명)과 INDEX02(상품명, 상품구분코드)를 사용한 스캔의 범위는 분명한 차이가 난다.

1번은 상품구분코드의 범위를 다 읽으면서 불고기 버거를 필터링하는 방식이고,
2번은 = 조건이 먼저 있어서 상품명을 인덱스 시작지점으로 찾은 후에 상품코드를 찾게 된다.

 

CREATE INDEX ORD_X02 ON ORD(ORD_DT, SHOP_NO);
CREATE INDEX ORD_X03 ON ORD(SHOP_NO, ORD_DT);

SELECT /*+ INDEX(ORD_X02) */
    * 
FROM ORD
WHERE ORD_DT BETWEEN '20120101' AND '20120131'
AND SHOP_NO = 'SH0001';

------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |        |       |   280 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ORD     |    305 | 47275 |   280   (1)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN                   | ORD_X02 |    305 |       |     6  (17)| 00:00:01 |
------------------------------------------------------------------------------------------------

SELECT
    * 
FROM ORD
WHERE ORD_DT BETWEEN '20120101' AND '20120131'
AND SHOP_NO = 'SH0001';

------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |        |       |   278 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ORD     |    305 | 47275 |   278   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | ORD_X03 |    305 |       |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------