지난 COLUMN_STATS 힌트 글에 이어 INDEX_STATS 힌트의 동작을 살펴보겠습니다.
INDEX_STATS 힌트의 구문은 아래와 같습니다.
/*+ INDEX_STATS(tablespec indexspec method adjustment) */
method에 아래의 항목을 사용할 수 있습니다.
{ SET | SCALE | SAMPLE | DEFAULT }
adjustment는 아래의 형식으로 사용하며 다수의 항목을 함께 사용할 수 있습니다.
{ BLOCKS | KEYS | CLUSTERING_FACTOR | INDEX_ROWS } = value
테스트를 위해 아래과 같이 테이블과 인덱스를 생성하고 통계정보를 수집하겠습니다.
-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1 NUMBER, c2 NUMBER, c3 VARCHAR2(4000));
INSERT INTO t1 SELECT ROWNUM, ROWNUM, LPAD ('X', 100, 'X') FROM XMLTABLE ('1 to 10000');
COMMIT;
CREATE INDEX t1_x1 ON t1 (c1, c2);
EXEC DBMS_STATS.GATHER_TABLE_STATS ('TUNA', 'T1');
아래는 t1_x1 인덱스의 통계정보입니다. BLOCKS, KEYS, CLUSTERING_FACTOR, INDEX_ROWS 항목은 각각 leaf_blocks, distinct_keys, clustering_factor, num_rows 값을 나타냅니다.
-- 2
SELECT leaf_blocks -- BLOCKS
, distinct_keys -- KEYS
, clustering_factor -- CLUSTERING_FACTOR
, num_rows -- INDEX_ROWS
FROM user_ind_statistics
WHERE index_name = 'T1_X1';
LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS
----------- ------------- ----------------- --------
27 10000 159 10000
1개의 행이 선택되었습니다.
아래는 t1_x1 인덱스로 t1 테이블을 조회하는 실행계획입니다.
-- 3
SELECT /*+ INDEX(T1) */ * FROM t1 WHERE c1 > 5000;
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5001 | 532K| 95 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 5001 | 532K| 95 (0)|
|* 2 | INDEX RANGE SCAN | T1_X1 | 5001 | | 15 (0)|
----------------------------------------------------------------------------------
비교를 위해 아래와 같이 데이터를 입력하고 통계정보를 수집하겠습니다.
-- 4
INSERT INTO t1 SELECT ROWNUM + 10000, ROWNUM + 10000, LPAD ('X', 300, 'X') FROM XMLTABLE ('1 to 10000');
COMMIT;
EXEC DBMS_STATS.GATHER_TABLE_STATS ('TUNA', 'T1');
통계정보가 아래와 같이 변경되었습니다.
-- 5
SELECT leaf_blocks -- BLOCKS
, distinct_keys -- KEYS
, clustering_factor -- CLUSTERING_FACTOR
, num_rows -- INDEX_ROWS
FROM user_ind_statistics
WHERE index_name = 'T1_X1';
LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS
----------- ------------- ----------------- --------
53 20000 614 20000 -- before: 27, 10000, 159, 10000
1개의 행이 선택되었습니다.
통계정보 변경으로 인해 실행계획의 Rows, Bytes, Cost 값이 아래와 같이 변경됩니다.
-- 6
SELECT /*+ INDEX(T1) */ * FROM t1 WHERE c1 > 5000;
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15001 | 3061K| 503 (1)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 15001 | 3061K| 503 (1)| -- before: 5001, 532K, 95 (0)
|* 2 | INDEX RANGE SCAN | T1_X1 | 15001 | | 41 (0)| -- before: 5001, , 15 (0)
----------------------------------------------------------------------------------
변경 전 통계정보 값을 지정하고 4개의 method로 실행계획을 생성하면 SET, SCALE 방식만 정상적으로 동작하며 명칭과 달리 두 방식의 동작에 차이가 없는 것을 확인할 수 있습니다.
-- 7-1: working
SELECT /*+ INDEX(T1) INDEX_STATS(T1 T1_X1 SET BLOCKS=27 KEYS=10000 CLUSTERING_FACTOR=159 INDEX_ROWS=10000) */
*
FROM t1
WHERE c1 > 5000;
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15001 | 3061K| 187 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 15001 | 3061K| 187 (0)|
|* 2 | INDEX RANGE SCAN | T1_X1 | 10000 | | 28 (0)|
----------------------------------------------------------------------------------
-- 7-2: working
SELECT /*+ INDEX(T1) INDEX_STATS(T1 T1_X1 SCALE BLOCKS=27 KEYS=10000 CLUSTERING_FACTOR=159 INDEX_ROWS=10000) */
*
FROM t1
WHERE c1 > 5000;
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15001 | 3061K| 187 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 15001 | 3061K| 187 (0)|
|* 2 | INDEX RANGE SCAN | T1_X1 | 10000 | | 28 (0)|
----------------------------------------------------------------------------------
-- 7-3: not working
SELECT /*+ INDEX(T1) INDEX_STATS(T1 T1_X1 SAMPLE BLOCKS=27 KEYS=10000 CLUSTERING_FACTOR=159 INDEX_ROWS=10000) */
*
FROM t1
WHERE c1 > 5000;
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15001 | 3061K| 503 (1)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 15001 | 3061K| 503 (1)|
|* 2 | INDEX RANGE SCAN | T1_X1 | 15001 | | 41 (0)|
----------------------------------------------------------------------------------
-- 7-4: strange behavior
SELECT /*+ INDEX(T1) INDEX_STATS(T1 T1_X1 DEFAULT BLOCKS=27 KEYS=10000 CLUSTERING_FACTOR=159 INDEX_ROWS=10000) */
*
FROM t1
WHERE c1 > 5000;
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15001 | 3061K| 487 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 15001 | 3061K| 487 (0)|
|* 2 | INDEX RANGE SCAN | T1_X1 | 15001 | | 20 (0)|
----------------------------------------------------------------------------------
INDEX_STATS 힌트는 TABLE_STATS, COLUMN_STATS 트와 함께 사용해야 통계정보 변경 전 실행계획을 생성할 수 있습니다.
-- 8
SELECT /*+ TABLE_STATS(T1 SET ROWS=10000 BLOCKS=244 ROW_LENGTH=109)
COLUMN_STATS(T1 C1 SET DISTINCT=10000 NULLS=0 LENGTH=4 MIN=1 MAX=10000)
INDEX(T1) INDEX_STATS(T1 T1_X1 SET BLOCKS=27 KEYS=10000 CLUSTERING_FACTOR=159 INDEX_ROWS=10000) */
*
FROM t1
WHERE c1 > 5000;
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5001 | 532K| 95 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 5001 | 532K| 95 (0)|
|* 2 | INDEX RANGE SCAN | T1_X1 | 5001 | | 15 (0)|
----------------------------------------------------------------------------------
adjustment 항목의 개별 동작을 살펴보면 BLOCKS는 내부 계산식에 의해 인덱스의 카디널리티(Rows)를 변경하는 것으로 보입니다.
-- 9-1
SELECT /*+ INDEX(T1) INDEX_STATS(T1 T1_X1 SET BLOCKS=27) */ * FROM t1 WHERE c1 > 5000;
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15001 | 3061K| 310 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 15001 | 3061K| 310 (0)|
|* 2 | INDEX RANGE SCAN | T1_X1 | 9170 | | 28 (0)|
----------------------------------------------------------------------------------
-- 9-2
SELECT /*+ INDEX(T1) INDEX_STATS(T1 T1_X1 SET BLOCKS=53) */ * FROM t1 WHERE c1 > 5000;
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15001 | 3061K| 508 (1)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 15001 | 3061K| 508 (1)|
|* 2 | INDEX RANGE SCAN | T1_X1 | 15001 | | 46 (0)|
----------------------------------------------------------------------------------
KEYS도 인덱스의 카디널리티를 변경합니다. 참고로 복합 인덱스의 카디널리티 계산은 인덱스 통계의 distinct_keys 값을 사용하며, 단일 인덱스의 카디널리티 계산은 칼럼 통계의 num_distinct 값을 사용합니다.
-- 10-1
SELECT /*+ INDEX(T1) INDEX_STATS(T1 T1_X1 SET KEYS=10000) */ * FROM t1 WHERE c1 = 5000 AND c2 = 5000;
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 418 | 2 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 2 | 418 | 2 (0)|
|* 2 | INDEX RANGE SCAN | T1_X1 | 2 | | 1 (0)|
----------------------------------------------------------------------------------
-- 10-2
SELECT /*+ INDEX(T1) INDEX_STATS(T1 T1_X1 SET KEYS=20000) */ * FROM t1 WHERE c1 = 5000 AND c2 = 5000;
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 209 | 2 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 209 | 2 (0)|
|* 2 | INDEX RANGE SCAN | T1_X1 | 1 | | 1 (0)|
----------------------------------------------------------------------------------
CLUSTERING_FACTOR는 테이블의 Cost를 변경합니다.
-- 11-1
SELECT /*+ INDEX(T1) INDEX_STATS(T1 T1_X1 SET CLUSTERING_FACTOR=614) */ * FROM t1 WHERE c1 > 5000;
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15001 | 3061K| 503 (1)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 15001 | 3061K| 503 (1)|
|* 2 | INDEX RANGE SCAN | T1_X1 | 15001 | | 41 (0)|
----------------------------------------------------------------------------------
-- 11-2
SELECT /*+ INDEX(T1) INDEX_STATS(T1 T1_X1 SET CLUSTERING_FACTOR=159) */ * FROM t1 WHERE c1 > 5000;
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15001 | 3061K| 162 (1)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 15001 | 3061K| 162 (1)|
|* 2 | INDEX RANGE SCAN | T1_X1 | 15001 | | 41 (0)|
----------------------------------------------------------------------------------
INDEX_ROWS는 인덱스의 카디널리티를 변경합니다.
-- 12-1
SELECT /*+ INDEX(T1) INDEX_STATS(T1 T1_X1 SET INDEX_ROWS=10000) */ * FROM t1 WHERE c1 > 5000;
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15001 | 3061K| 338 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 15001 | 3061K| 338 (0)|
|* 2 | INDEX RANGE SCAN | T1_X1 | 10000 | | 31 (0)|
----------------------------------------------------------------------------------
-- 12-2
SELECT /*+ INDEX(T1) INDEX_STATS(T1 T1_X1 SET INDEX_ROWS=20000) */ * FROM t1 WHERE c1 > 5000;
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15001 | 3061K| 508 (1)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 15001 | 3061K| 508 (1)|
|* 2 | INDEX RANGE SCAN | T1_X1 | 15001 | | 46 (0)|
----------------------------------------------------------------------------------