지난 TABLE_STATS 힌트 글에 이어 COLUMN_STATS 힌트의 동작을 살펴보겠습니다.
COLUMN_STATS 힌트의 구문은 아래와 같습니다.
/*+ COLUMN_STATS(tablespec columnspec method adjustment) */
method에 아래의 항목을 사용할 수 있습니다.
{ SET | SCALE | SAMPLE | DEFAULT }
adjustment는 아래의 형식으로 사용하며 다수의 항목을 함께 사용할 수 있습니다.
{ DISTINCT | NULLS | LENGTH | MIN | MAX } = 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;
EXEC DBMS_STATS.GATHER_TABLE_STATS ('TUNA', 'T1');
아래는 c1 칼럼의 통계정보입니다. DISTINCT, NULLS, LENGTH, MIN, MAX 항목은 각각 num_distinct, num_nulls, avg_col_len, low_value, high_value 값을 나타냅니다.
-- 2
SELECT num_distinct -- DISTINCT
, num_nulls -- NULLS
, avg_col_len -- LENGTH
, UTL_RAW.CAST_TO_NUMBER (low_value) AS low_value -- MIN
, UTL_RAW.CAST_TO_NUMBER (high_value) AS high_value -- MAX
FROM user_tab_col_statistics
WHERE table_name = 'T1'
AND column_name = 'C1';
NUM_DISTINCT NUM_NULLS AVG_COL_LEN LOW_VALUE HIGH_VALUE
------------ --------- ----------- --------- ----------
10000 0 4 1 10000
1개의 행이 선택되었습니다.
아래는 c1 칼럼으로 t1 테이블을 조회하는 실행계획입니다.
-- 3
SELECT * FROM t1 WHERE c1 > 5000;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5001 | 532K| 44 (0)|
|* 1 | TABLE ACCESS FULL| T1 | 5001 | 532K| 44 (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 num_distinct -- DISTINCT
, num_nulls -- NULLS
, avg_col_len -- LENGTH
, UTL_RAW.CAST_TO_NUMBER (low_value ) AS low_value -- MIN
, UTL_RAW.CAST_TO_NUMBER (high_value) AS high_value -- MAX
FROM user_tab_col_statistics
WHERE table_name = 'T1'
AND column_name = 'C1';
NUM_DISTINCT NUM_NULLS AVG_COL_LEN LOW_VALUE HIGH_VALUE
------------ --------- ----------- --------- ----------
20000 0 5 1 20000 -- before: 10000, 0, 4, 1, 10000
1개의 행이 선택되었습니다.
통계정보 변경으로 인해 실행계획의 Rows, Bytes, Cost 값이 아래와 같이 변경됩니다.
-- 6
SELECT * FROM t1 WHERE c1 > 5000;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15001 | 3061K| 110 (1)|
|* 1 | TABLE ACCESS FULL| T1 | 15001 | 3061K| 110 (1)| -- before: 5001, 532K, 44 (0)
---------------------------------------------------------------
변경 전 통계정보 값을 지정하고 4개의 method로 실행계획을 생성하면 SET, SCALE, DEFAULT 방식이 정상적으로 동작하며 명칭과 달리 세 방식의 동작에 차이가 없는 것을 확인할 수 있습니다.
-- 7-1: working
SELECT /*+ COLUMN_STATS(T1 C1 SET DISTINCT=10000 NULLS=0 LENGTH=4 MIN=1 MAX=10000) */
*
FROM t1
WHERE c1 > 5000;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10001 | 2041K| 110 (1)|
|* 1 | TABLE ACCESS FULL| T1 | 10001 | 2041K| 110 (1)|
---------------------------------------------------------------
-- 7-2: working
SELECT /*+ COLUMN_STATS(T1 C1 SCALE DISTINCT=10000 NULLS=0 LENGTH=4 MIN=1 MAX=10000) */
*
FROM t1
WHERE c1 > 5000;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10001 | 2041K| 110 (1)|
|* 1 | TABLE ACCESS FULL| T1 | 10001 | 2041K| 110 (1)|
---------------------------------------------------------------
-- 7-3: not working
SELECT /*+ COLUMN_STATS(T1 C1 SAMPLE DISTINCT=10000 NULLS=0 LENGTH=4 MIN=1 MAX=10000) */
*
FROM t1
WHERE c1 > 5000;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15001 | 3061K| 110 (1)|
|* 1 | TABLE ACCESS FULL| T1 | 15001 | 3061K| 110 (1)|
---------------------------------------------------------------
-- 7-4: working
SELECT /*+ COLUMN_STATS(T1 C1 DEFAULT DISTINCT=10000 NULLS=0 LENGTH=4 MIN=1 MAX=10000) */
*
FROM t1
WHERE c1 > 5000;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10001 | 2041K| 110 (1)|
|* 1 | TABLE ACCESS FULL| T1 | 10001 | 2041K| 110 (1)|
---------------------------------------------------------------
COLUMN_STATS 힌트는 TABLE_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) */
*
FROM t1
WHERE c1 > 5000;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5001 | 532K| 44 (0)|
|* 1 | TABLE ACCESS FULL| T1 | 5001 | 532K| 44 (0)|
---------------------------------------------------------------
adjustment 항목의 개별 동작을 살펴보면 DISTINCT는 카디널리티(Rows)를 변경하는 것을 알 수 있습니다.
-- 9-1
SELECT /*+ COLUMN_STATS(T1 C1 SET DISTINCT=10000) */ * FROM t1 WHERE c1 = 5000;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 418 | 110 (1)|
|* 1 | TABLE ACCESS FULL| T1 | 2 | 418 | 110 (1)|
---------------------------------------------------------------
-- 9-2
SELECT /*+ COLUMN_STATS(T1 C1 SET DISTINCT=20000) */ * FROM t1 WHERE c1 = 5000;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 209 | 110 (1)|
|* 1 | TABLE ACCESS FULL| T1 | 1 | 209 | 110 (1)|
---------------------------------------------------------------
NULLS도 카디널리티를 변경합니다.
-- 10-1
SELECT /*+ COLUMN_STATS(T1 C1 SET NULLS=0) */ * FROM t1 WHERE c1 > 5000;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15001 | 3061K| 110 (1)|
|* 1 | TABLE ACCESS FULL| T1 | 15001 | 3061K| 110 (1)|
---------------------------------------------------------------
-- 10-2
SELECT /*+ COLUMN_STATS(T1 C1 SET NULLS=1000) */ * FROM t1 WHERE c1 > 5000;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14251 | 2908K| 110 (1)|
|* 1 | TABLE ACCESS FULL| T1 | 14251 | 2908K| 110 (1)|
---------------------------------------------------------------
LENGTH는 카디널리티를 변경하지 않고 Bytes만 변경합니다.
-- 11-1
SELECT /*+ COLUMN_STATS(T1 C1 SET LENGTH=4) */ c1 FROM t1 WHERE c1 > 5000;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15001 | 60004 | 110 (1)|
|* 1 | TABLE ACCESS FULL| T1 | 15001 | 60004 | 110 (1)|
---------------------------------------------------------------
-- 11-2
SELECT /*+ COLUMN_STATS(T1 C1 SET LENGTH=5) */ c1 FROM t1 WHERE c1 > 5000;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15001 | 75005 | 110 (1)|
|* 1 | TABLE ACCESS FULL| T1 | 15001 | 75005 | 110 (1)|
---------------------------------------------------------------
MIN은 조건에 따라 카디널리티를 변경합니다.
-- 12-1
SELECT /*+ COLUMN_STATS(T1 C1 SET MIN=1) */ * FROM t1 WHERE c1 > 5000;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15001 | 3061K| 110 (1)|
|* 1 | TABLE ACCESS FULL| T1 | 15001 | 3061K| 110 (1)|
---------------------------------------------------------------
-- 12-2
SELECT /*+ COLUMN_STATS(T1 C1 SET MIN=1000) */ * FROM t1 WHERE c1 > 5000;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15789 | 3222K| 110 (1)|
|* 1 | TABLE ACCESS FULL| T1 | 15789 | 3222K| 110 (1)|
---------------------------------------------------------------
MAX은 조건에 따라 카디널리티를 변경합니다. low_value와 high_value를 벗어난 값에 대한 예상 카디널리티 글에서 MIN, MAX와 관련된 내용을 확인할 수 있습니다.
-- 13-1
SELECT /*+ COLUMN_STATS(T1 C1 SET MAX=10000) */ * FROM t1 WHERE c1 > 5000;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10001 | 2041K| 110 (1)|
|* 1 | TABLE ACCESS FULL| T1 | 10001 | 2041K| 110 (1)|
---------------------------------------------------------------
-- 13-2
SELECT /*+ COLUMN_STATS(T1 C1 SET MAX=20000) */ * FROM t1 WHERE c1 > 5000;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15001 | 3061K| 110 (1)|
|* 1 | TABLE ACCESS FULL| T1 | 15001 | 3061K| 110 (1)|
---------------------------------------------------------------