COLUMN_STATS 힌트

지난 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)|
---------------------------------------------------------------