[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 |
------------------------------------------------------------------------------------------------