Extended Statistics - Multicolumn Statistics

In Oracle 11g introduces extended statistics (also called multicolumn statistics) wherein you can collect statistics on a set of columns for the queries involving predicates.  This will give the optimizer more correct estimate of the selectivity for the group of columns as a whole. 

Creating Multicolumn statistics:

SQL> select dbms_stats.create_extended_stats(USER,’CUST_STAGE’,'(CUST_ID,ALERT_ID)') from dual;

DBMS_STATS.CREATE_EXTENDED_STATS(USER,’CUST_STAGE’,'(CUST_ID,ALERT_ID)')

-----------------------------------------------------------------------------------------------------------------------------------------------------------

SYS_STUSTUBNP0BHDV2DL3CX16_KX5

1 row selected.

Gathering Statistics:

SQL> exec dbms_stats.gather_table_stats(ownname=> USER, tabname=>‘CUST_STAGE’,estimate_percent=>100, cascade=>true);

PL/SQL procedure successfully completed.

SQL> select table_name, column_name, num_distinct, num_buckets, sample_size, histogram

  2  from user_tab_col_statistics

  3  where table_name = ‘CUST_STAGE’;

TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT  NUM_BUCKETS  SAMPLE_SIZE HISTOGRAM

------------------------------ ------------------------------ ------------ ------------ ------------ ---------------

CUST_STAGE               CUST_ID                           18264            1        25879 NONE

CUST_STAGE               ALERT_ID                             1            1        25879 NONE

CUST_STAGE               ALERT_DTS                                 1            1        25879 NONE

CUST_STAGE               AUDIT_REC_CREATE_DTS                      1            1        25879 NONE

select extension_name, extension, table_name

 from dba_stat_extensions

 where owner = SOWN

and table_name = ‘CUST_STAGE’

EXTENSION_NAME                   EXTENSION                        TABLE_NAME

-------------------------------- -------------------------------- ------------------------------

SYS_STUSTUBNP0BHDV2DL3CX16_KX5   ("CUST_ID","ALERT_ID")  CUST_STAGE

show_extended_stats function returns the name of the column group as a virtual column.

SQL> select sys.dbms_stats.show_extended_stats_name(SOWN,’CUST_STAGE’,'(CUST_ID,ALERT_ID)') col_group_name from dual;

COL_GROUP_NAME

------------------------------------------------------------------------------------------------------------------------------------

SYS_STUSTUBNP0BHDV2DL3CX16_KX5

Deleting Extended Statistics

SQL> exec dbms_stats.drop_extended_stats(SOWN,’CUST_STAGE’,'(CUST_ID,ALERT_ID)');

PL/SQL procedure successfully completed.