Starting 11g, you can create statistics for two or more related columns or an expression on a column that are part of a join condition are corrected.
Extended statistics on multi-column statistics
Extended statistics on multi-column statistics
DECLARE
exst_name VARCHAR2(30);
BEGIN
exst_name := DBMS_STATS.CREATE_EXTENDED_STATS('HR','emp',
'(last_name,department_id)');
END;
/
exst_name VARCHAR2(30);
BEGIN
exst_name := DBMS_STATS.CREATE_EXTENDED_STATS('HR','emp',
'(last_name,department_id)');
END;
/
SELECT SYS.DBMS_STATS.SHOW_EXTENDED_STATS_NAME('hr','emp',
'(last_name,department_id)') col_group_name
FROM DUAL
/
'(last_name,department_id)') col_group_name
FROM DUAL
/
COL_GROUP_NAME
--------------------------------------------------------------------------------
SYS_STUAV0Z346SKNRSGSE2XA1MM6L
--------------------------------------------------------------------------------
SYS_STUAV0Z346SKNRSGSE2XA1MM6L
SQL> select dbms_stats.create_extended_stats('hr','emp','(last_name,department_id)') from dual;
DBMS_STATS.CREATE_EXTENDED_STATS('HR','EMP','(LAST_NAME,DEPARTMENT_ID)')
--------------------------------------------------------------------------------
SYS_STUAV0Z346SKNRSGSE2XA1MM6L
DBMS_STATS.CREATE_EXTENDED_STATS('HR','EMP','(LAST_NAME,DEPARTMENT_ID)')
--------------------------------------------------------------------------------
SYS_STUAV0Z346SKNRSGSE2XA1MM6L
Extended statistics on Expression:
SQL> execute dbms_stats.gather_table_stats('HR','EMP', method_opt =>'for all columns size skewonly for columns(lower(last_name)) size skewonly');
To drop extended stats:
SQL> exec dbms_stats.drop_extended_stats('hr','emp','(last_name,department_id)');
Gathering statistics on Column Groups:
Method_opt enables you to gather statistics on column group:
FOR ALL COLUMNS SIZE AUTO: optimizer gathers statistics on all existing column groups
FOR COLUMNS: The column group is automatically created as part of statistics gathering
exec dbms_stats.gather_table_stats('HR','EMP',METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY FOR COLUMNS (LAST_NAME, DEPARTMENT_ID) SIZE SKEWONLY');
To monitor column groups about multi column or expression statstistics
SQL> select * from user_stat_extensions;
TABLE_NAME EXTENSION_NAME EXTENSION CREATO DRO
---------- ------------------------------ ------------ ------ ---
EMP SYS_STUAV0Z346SKNRSGSE2XA1MM6L ("LAST_NAME" USER YES
,"DEPARTMENT
_ID")
EMP SYS_STU$OGQI#ACVJROJQ#33ZBENKN (LOWER("LAST USER YES
_NAME")
SQL> select * from user_stat_extensions;
TABLE_NAME EXTENSION_NAME EXTENSION CREATO DRO
---------- ------------------------------ ------------ ------ ---
EMP SYS_STUAV0Z346SKNRSGSE2XA1MM6L ("LAST_NAME" USER YES
,"DEPARTMENT
_ID")
EMP SYS_STU$OGQI#ACVJROJQ#33ZBENKN (LOWER("LAST USER YES
_NAME")
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 e.TABLE_NAME=t.TABLE_NAME
AND t.TABLE_NAME='EMP';
