확장 통계

• 데이터 간의 의존관계가 존재하는 컬럼들을 상관관계 컬럼이라고 한다.(예: country = 'Korea' And language = 'Korean' , 국가가 한국이 경우 언어도 대부분 한국어를 쓸 것이다.)
• 사람은 컬럼간의 상관관계를 쉽게 알아차릴 수 있으나, 옵티마이저는 알 수 없다.
• 이러한 문제를 해결하기 위해 11.1버전부터 확장통계를 지원하기 시작됐다.

SQL> SELECT dbms_stats.CREATE_EXTENDED_STATS(ownname   => user,
  2                                          tabname   => 'T',
  3                                          extension => '(upper(pad))') AS ext1,
  4         dbms_stats.CREATE_EXTENDED_STATS(ownname   => user,
  5                                          tabname   => 'T',
  6                                          extension => '(val2,val3)') AS ext2
  7  FROM dual;

EXT1
----------------------------
EXT2
----------------------------
SYS_STU0KSQX64#I01CKJ5FPGFK3W9
SYS_STUPS77EFBJCOTDFMHM8CHP7Q1

SQL> SELECT extension_name, extension
  2  FROM USER_STAT_EXTENSIONS
  3  WHERE table_name = 'T';

EXTENSION_NAME                 EXTENSION
------------------------------ ---------------
SYS_STU0KSQX64#I01CKJ5FPGFK3W9 (UPPER("PAD"))
SYS_STUPS77EFBJCOTDFMHM8CHP7Q1 ("VAL2","VAL3")

SQL> SELECT column_name, data_type, hidden_column, data_default
  2  FROM USER_TAB_COLS
  3  WHERE table_name = 'T'
  4  ORDER BY column_id;

COLUMN_NAME                    DATA_TYPE HIDDEN
------------------------------ --------- ------
DATA_DEFAULT
--------------------------------------------------------------------------------
ID                             NUMBER    NO


VAL1                           NUMBER    NO


VAL2                           NUMBER    NO


VAL3                           NUMBER    NO


PAD                            VARCHAR2  NO


SYS_STU0KSQX64#I01CKJ5FPGFK3W9 VARCHAR2  YES
UPPER("PAD")

SYS_STUPS77EFBJCOTDFMHM8CHP7Q1 NUMBER    YES
SYS_OP_COMBINED_HASH("VAL2","VAL3")

• 확장통계는 해시함수에 기반을 두므로 equal 조건절에서만 동작한다. (between이나 <, > 연산자에서는 확장통계를 이용할 수 없다.)

SQL> BEGIN
  2    dbms_stats.drop_extended_stats(ownname   => 'JIGI',
  3                                   tabname   => 'T',
  4                                   extension => '(upper(pad))');
  5    dbms_stats.drop_extended_stats(ownname   => 'JIGI',
  6                                   tabname   => 'T',
  7                                   extension => '(val2,val3)');
  8  END;
  9  /

• 위 명령어를 사용해 확장통계를 제거할 수 있다.

SQL> BEGIN
  2    dbms_stats.seed_col_usage(sqlset_name => NULL,
  3                              owner_name => NULL,
  4                              time_limit => 30);
  5  END;
  6  /
SQL>

• 어떤 컬럼그룹에 확장통계를 수집할지 결정하는 것은 쉬운일이 아니다.
• seed_col_usage 프로시저를 통해 보고서를 출력하여 좀더 쉽게 결정할 수 있다.(교재와는 다르게 결과가 정상적으로 나오지 않음)

SQL> SELECT dbms_stats.create_extended_stats(ownname => user, tabname => 't')
  2  FROM dual;

DBMS_STATS.CREATE_EXTENDED_STATS(OWNNAME=>USER,TABNAME=>'T')
--------------------------------------------------------------------------------
###############################################################################
SQL>
SQL> PAUSE

SQL>
SQL> SELECT column_name, data_type, hidden_column, data_default
  2  FROM user_tab_cols
  3  WHERE table_name = 'T'
  4  ORDER BY column_id;

COLUMN_NAME                    DATA_TYPE HIDDEN DATA_DEFAULT
------------------------------ --------- ------ -----------------------------------
VAL1                           NUMBER    NO
VAL2                           NUMBER    NO
VAL3                           NUMBER    NO
VAL4                           NUMBER    NO
SYS_STU4K1K3JNH1Z9#_L_V93K3DT4 NUMBER    YES    SYS_OP_COMBINED_HASH("VAL1","VAL2")
SYS_STUPS77EFBJCOTDFMHM8CHP7Q1 NUMBER    YES    SYS_OP_COMBINED_HASH("VAL2","VAL3")
SYS_STUSS3AZ43WO3NKM#66PIG2JYW NUMBER    YES    SYS_OP_COMBINED_HASH("VAL3","VAL4")
SYS_STUS574STTDWYBF6PGQN#XHGGJ NUMBER    YES    SYS_OP_COMBINED_HASH("VAL1","VAL3")

• creat_extended_stats 를 이용하여 자동으로 확장통계를 생성할 수 있다.

SQL> CREATE TABLE persons (
  2    name VARCHAR2(100),
  3    name_upper AS (upper(name))
  4  );
SQL>
SQL> INSERT INTO persons (name) VALUES ('Michelle');
SQL>
SQL> SELECT name
  2  FROM persons
  3  WHERE name_upper = 'MICHELLE';

NAME
----------
Michelle

• 가상컬럼 : 11.1버전부터 도입된 기술로 확장통계의 사용기반이다.