칼럼 통계

SQL> SELECT column_name AS "NAME",
  2         num_distinct AS "#DST",
  3         low_value,
  4         high_value,
  5         density AS "DENS",
  6         num_nulls AS "#NULL",
  7         avg_col_len AS "AVGLEN",
  8         histogram,
  9         num_buckets AS "#BKT"
 10  FROM user_tab_col_statistics
 11  WHERE table_name = 'T';

NAME  #DST LOW_VALUE           HIGH_VALUE             DENS #NULL AVGLEN HISTOGRAM        #BKT
---- ----- ------------------- ------------------- ------- ----- ------ --------------- -----
ID    1000 C102                C20B                 .00100     0      4 NONE                1
VAL1    22 C128                C140                 .03884     0      3 HYBRID             15
VAL2     6 C20202              C20207               .00050     0      4 FREQUENCY           6
VAL3     6 C20202              C20207               .00050     0      4 TOP-FREQUENCY       5
PAD   1000 202623436F294373342 7E79514A202D4946493  .00100     0    251 HYBRID            254
           37B426574336E4A5B30 66C744E253F36264C69
           2E4F4B53236932303A2 27557A57737C6D4B225
           1215F462B7667457032 9414C442D2544364130
           694174782F7749393B6 612F5B3447405A4E714
           5735646366D20736939 A403B6237592B3D7B67
           335D712B233B3F      7D4D594E766B57

• num_distinct : 고유한 값의 개수
• low_value : 컬럼에서 가장 낮은 값(문자열컬럼의 경우 첫 32바이트만 사용, utl_raw 패키지나 convert_raw_value를 이용하여 조회가능)
• high_value : 컬럼에서 가장 높은 값(문자열컬럼의 경우 첫 32바이트만 사용, utl_raw 패키지나 convert_raw_value를 이용하여 조회가능)
• density : 0~1사이의 십진수, 1/num_distinct 이다. 0에 가까울수록 필터되고, 1에 가까울수록 select 된다.
• num_nulls : null 값의 개수
• avg_col_len : 평균 칼럼의 크기(바이트)
• histogram : 히스토그램의 존재여부
• num_buckets : 히스토그램에서 버킷의 개수(11.2 이하버전 : 254개, 12.1 이상버전 : 2,048개)

SQL> SELECT utl_raw.cast_to_number(low_value) AS low_value,
  2         utl_raw.cast_to_number(high_value) AS high_value
  3  FROM user_tab_col_statistics
  4  WHERE table_name = 'T'
  5  AND column_name = 'VAL1';

LOW_VALUE HIGH_VALUE
--------- ----------
       39         63


SQL> WITH
  2    FUNCTION convert_raw_value(p_value IN RAW, p_datatype IN VARCHAR2) RETURN VARCHAR2 IS
  3      l_ret VARCHAR2(64);
  4      l_date DATE;
  5      l_number NUMBER;
  6      l_binary_float BINARY_FLOAT;
  7      l_binary_double BINARY_DOUBLE;
  8      l_nvarchar2 NVARCHAR2(64);
  9      l_rowid ROWID;
 10    BEGIN
 11      IF p_datatype = 'VARCHAR2' OR p_datatype = 'CHAR'
 12      THEN
 13        dbms_stats.convert_raw_value(p_value, l_ret);
 14      ELSIF p_datatype = 'DATE'
 15      THEN
 16        dbms_stats.convert_raw_value(p_value, l_date);
 17        l_ret := to_char(l_date, 'YYYY-MM-DD HH24:MI:SS');
 18      ELSIF p_datatype LIKE 'TIMESTAMP%'
 19      THEN
 20        dbms_stats.convert_raw_value(p_value, l_date);
 21        l_ret := to_char(l_date, 'YYYY-MM-DD HH24:MI:SS');
 22      ELSIF p_datatype = 'NUMBER'
 23      THEN
 24        dbms_stats.convert_raw_value(p_value, l_number);
 25        l_ret := to_char(l_number);
 26      ELSIF p_datatype = 'BINARY_FLOAT'
 27      THEN
 28        dbms_stats.convert_raw_value(p_value, l_binary_float);
 29        l_ret := to_char(l_binary_float);
 30      ELSIF p_datatype = 'BINARY_DOUBLE'
 31      THEN
 32        dbms_stats.convert_raw_value(p_value, l_binary_double);
 33        l_ret := to_char(l_binary_double);
 34      ELSIF p_datatype = 'NVARCHAR2'
 35      THEN
 36        dbms_stats.convert_raw_value(p_value, l_nvarchar2);
 37        l_ret := to_char(l_nvarchar2);
 38      ELSIF p_datatype = 'ROWID'
 39      THEN
 40        dbms_stats.convert_raw_value(p_value, l_nvarchar2);
 41        l_ret := to_char(l_nvarchar2);
 42      ELSE
 43        l_ret := 'UNSUPPORTED DATATYPE';
 44      END IF;
 45      RETURN l_ret;
 46    END;
 47  SELECT column_name,
 48         convert_raw_value(low_value, data_type) AS low_value,
 49         convert_raw_value(high_value, data_type) AS high_value
 50  FROM user_tab_columns
 51  WHERE table_name = 'T'
 52  ORDER BY column_id
 53  /

COLUMN_NAME
------------------------------
LOW_VALUE                      HIGH_VALUE
------------------------------ ------------------------------
ID
1                              1000

VAL1
39                             63

VAL2
101                            106

VAL3