11G new Features--multicolumn Statistics (Column groups)

The default Oracle collects statistics for each column in the table, but ignores the association between columns. In most cases, the optimizer assumes that the columns in the complex query are independent of each other. When you specify more than one column condition for a table after the WHERE clause, the optimizer will typically multiply the selectivity of multiple columns (selectivity) to get the selectivity of the where statement, causing the optimizer to make an error judgment!
Oracle 11g introduces the concept of multi-column statistics, and if the above conditions are well-correlated, you can do long-column statistics gathering to make the optimizer make the right decision.

In Oracle 10g, the optimizer considers the association between columns only on special occasions:
-the Optimizer used the number of distinct keys in an index to estimate selectivity provided all columns of a conjunctive predicate match all columns of a concatenated index key. In addition, the predicates must is equalities used in equijoins.
-If you set dynamic_sampling to level 4, the optimizer used DYNAMIC sampling to estimate the selectivity of predicates in volving multiple columns from a table. Because the sampling size is quite small, the results was dubious in most cases.

Create Column Groups:

DECLARE  cg_name varchar2 (+); BEGIN  cg_name:= dbms_stats.create_extended_stats (null,'customers' ) ' (cust_state_province,country_id) ' ); END; /

View Column Groups:

Sql>SelectExtension_name, extension fromDba_stat_extensionswhereTable_name='CUSTOMERS'; Extension_name EXTENSION------------------------------ --------------------------------------------------------------------------------Sys_stu#s#wf25z#qahihe#moffmm_ ("cust_state_province","country_id") or SQL>SelectSys.dbms_stats.show_extended_stats_name ('SH','Customers','(cust_state_province,country_id)') Col_group_name fromdual; Col_group_name--------------------------------------------------sys_stu#s#wf25z#qahihe#moffmm_

Delete:

sql> exec dbms_stats.drop_extended_stats ('sh','customers' ,'(cust_state_province, country_id)');

Collect statistics for column groups:

sql> exec dbms_stats.gather_table_stats ('sh','customers' , method_opt ='for allcolumns size skewonly for columns (cust_state_province,country_id) size Skewonly');

Monitor column Groups:

--querying multi-column statistics SQL> Select extension_name, extension fromUser_stat_extensionswhereTable_name='CUSTOMERS'; Extension_name EXTENSION------------------------------ --------------------------------------------------------------------------------Sys_stu#s#wf25z#qahihe#moffmm_ ("cust_state_province","country_id") SQL>--view distinct numbers and histogram usage SQL>SelectE.extension Col_group, T.num_distinct, T.histogram fromUser_stat_extensions E, User_tab_col_statistics twhereE.extension_name = t.column_name and E.table_name = t.table_name and T.table_name ='CUSTOMERS'; Col_group num_distinct Histogram-------------------------------------------------------------------------------- ------------ ---------------("cust_state_province","country_id")145Frequencysql>

Experiment:
1) When you do not use multi-column statistics, the real result is 3341, and the execution plan is 1132.

Sql> exec Dbms_stats.drop_extended_stats ('SH','Customers','(cust_state_province,country_id)'); PL/SQL procedure successfully completed. SQL>SelectCOUNT (*) fromSh.customerswhereCust_state_province ='CA'and country_id=52790; COUNT (*)----------3341Execution Plan----------------------------------------------------------Plan Hash Value:296924608--------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |--------------------------------------------------------------------------------|0|           SELECT STATEMENT | |1| -|405(1)|xx:xx: to||1|           SORT AGGREGATE | |1| -|          | || *2| TABLE ACCESS full| CUSTOMERS |1132|18112|405(1)|xx:xx: to|--------------------------------------------------------------------------------predicate information (identified by Operation ID):---------------------------------------------------2-Filter ("cust_state_province"='CA'and"country_id"=52790) Statistics----------------------------------------------------------121Recursive calls0db block gets1685consistent gets0Physical reads0Redo Size527Bytes sent via sql*Net to client524Bytes received via Sql*net fromClient2Sql*net roundtrips to/ fromClient thesorts (memory)0sorts (disk)1Rows processed

2) When using multi-column statistics, the real result is 3341, and the execution plan is 3437.

Sql> EXEC dbms_stats. Gather_table_stats ('SH','CUSTOMERS', method_opt ='For all COLUMNS size skewonly for COLUMNS (cust_state_province,country_id) size skewonly'); PL/SQL procedure successfully completed. SQL>SelectCOUNT (*) fromSh.customerswhereCust_state_province ='CA'and country_id=52790; COUNT (*)----------3341Execution Plan----------------------------------------------------------Plan Hash Value:296924608--------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |--------------------------------------------------------------------------------|0|           SELECT STATEMENT | |1| -|405(1)|xx:xx: to||1|           SORT AGGREGATE | |1| -|          | || *2| TABLE ACCESS full| CUSTOMERS |3437|54992|405(1)|xx:xx: to|--------------------------------------------------------------------------------predicate information (identified by Operation ID):---------------------------------------------------2-Filter ("cust_state_province"='CA'and"country_id"=52790) Statistics----------------------------------------------------------8Recursive calls0db block gets1460consistent gets0Physical reads0Redo Size527Bytes sent via sql*Net to client524Bytes received via Sql*net fromClient2Sql*net roundtrips to/ fromClient0sorts (memory)0sorts (disk)1Rows processed

3) that is, the use of multi-column statistics can make the optimizer to get more accurate judgment!

11G new Features--multicolumn Statistics (Column groups)