11g Extended statistics

To take advantages from statistics on group of columns, you can use extended statistics.


SQL> select count(*) from tab;

  COUNT(*)
----------
   1003241

SQL> select column_name, num_distinct, num_nulls, histogram from user_tab_col_statistics
  2  where table_name='TAB';

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS HISTOGRAM
------------------------------ ------------ ---------- ---------------
X                                         5          0 NONE
Y                                         5          0 NONE

SQL> select dbms_stats.create_extended_stats(null,'tab','(x,y)') from dual;

DBMS_STATS.CREATE_EXTENDED_STATS(NULL,'TAB','(X,Y)')
--------------------------------------------------------------------------------
SYS_STUYPW88OE302TFVBNC6$MMQXE

SQL> select column_name, num_distinct, num_nulls, histogram from user_tab_col_statistics
  2  where table_name='TAB';

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS HISTOGRAM
------------------------------ ------------ ---------- ---------------
X                                         5          0 NONE
Y                                         5          0 NONE


SQL> select * from user_stat_extensions;

TABLE_NAME                     EXTENSION_NAME
------------------------------ ------------------------------
EXTENSION
--------------------------------------------------------------------------------
CREATO DRO
------ ---
TAB                            SYS_STUYPW88OE302TFVBNC6$MMQXE
("X","Y")
USER   YES


SQL>  exec dbms_stats.gather_table_stats(null,'tab');

PL/SQL procedure successfully completed.

SQL>  select column_name, num_distinct, num_nulls, histogram from user_tab_col_statistics
  2  where table_name='TAB';

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS HISTOGRAM
------------------------------ ------------ ---------- ---------------
SYS_STUYPW88OE302TFVBNC6$MMQXE            5          0 NONE
X                                         5          0 NONE
Y                                         5          0 FREQUENCY