参数依次是用户名,表明,列名
SQL> SELECT DBMS_STATS.CREATE_EXTENDED_STATS(USER, 'OPT_ACCT_FDIM', '(BUS_UNIT_SKID, ACCT_SKID)') FROM DUAL;
DBMS_STATS.CREATE_EXTENDED_STATS(USER,'OPT_ACCT_FDIM','(BUS_UNIT_SKID,ACCT_SKID)')
------------------------------------------------------------------------------------------------------------------
SYS_STUJ8OD#X2IPA_B9_CH00B046T
SQL> SELECT DBMS_STATS.CREATE_EXTENDED_STATS(USER, 'OPT_ACTVY_FCT', '(BUS_UNIT_SKID, ACCT_PRMTN_SKID)') FROM DUAL;
DBMS_STATS.CREATE_EXTENDED_STATS(USER,'OPT_ACTVY_FCT','(BUS_UNIT_SKID,ACCT_PRMTN_SKID)')
------------------------------------------------------------------------------------------------------------------
SYS_STU#CVQNKK5CCM0W2XEQWSRXSM
SELECT EXTENSION_NAME, EXTENSION FROM USER_STAT_EXTENSIONS WHERE TABLE_NAME='CUSTOMERS';
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ADWG_OPTIMA_LA11',
3 tabname => 'OPT_ACCT_FDIM',
4 estimate_percent => 20,
5 method_opt => 'for all columns size auto',
6 degree => 6,
7 granularity => 'ALL',
8 cascade=>TRUE
9 );
10 END;
11 /
--
for all columns size auto
自动收集所有已经创建的column group的统计信息,如果需要创建新的column group,可以通过for columns()单独指定,例如:
EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','CUSTOMERS',METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY FOR COLUMNS (CUST_STATE_PROVINCE,COUNTRY_ID) SIZE SKEWONLY');
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ADWG_OPTIMA_LA11',
3 tabname => 'OPT_ACTVY_FCT',
4 estimate_percent => 20,
5 method_opt => 'for all columns size auto',
6 degree => 6,
7 granularity => 'ALL',
8 cascade=>TRUE
9 );
10 END;
11 /
PL/SQL procedure successfully completed.
删除 EXTENDED STATISTICS
exec DBMS_STATS.DROP_EXTENDED_STATS (USER,'OPT_ACTVY_FCT','(BUS_UNIT_SKID, ACCT_PRMTN_SKID)');
创建基于表达式的拓展统计信息:
EXEC DBMS_STATS.GATHER_TABLE_STATS('sh','customers', method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY FOR COLUMNS (LOWER(cust_state_province)) SIZE SKEWONLY');
也可以如下方式创建:
SELECT
DBMS_STATS.CREATE_EXTENDED_STATS(null,'customers','(LOWER(cust_state_province))')
FROM DUAL;
DBMS_STATS.CREATE_EXTENDED_STATS(null,'customers','(LOWER(cust_state_province))')
FROM DUAL;
通过如下SQL查询拓展统计信息:
SELECT e.EXTENSION col_group, t.NUM_DISTINCT, t.HISTOGRAM FROM USER_STAT_EXTENSIONS e, USER_TAB_COL_STATISTICS t WHERE e.EXTENSION_NAME=t.COLUMN_NAME AND t.TABLE_NAME='CUSTOMERS';