在11g中，Oracle在统计信息方面进行了进一步的增强。
这篇介绍多列统计信息。
11.2中Oracle增加了多列统计的功能。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database11gEnterprise Edition Release11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> create table t_multi_col
2 as select *
3 from dba_extents
4 where file_id < 4;
表已创建。
SQL> select count(*) from t_multi_col;
COUNT(*)
----------
12038
SQL> select count(distinct file_id) file_id, count(distinct relative_fno) r_file_id
2 from t_multi_col;
FILE_ID R_FILE_ID
---------- ----------
3 3
SQL> create index ind_multi_col_file
2 on t_multi_col(file_id, relative_fno);
索引已创建。
SQL> exec dbms_stats.gather_table_stats(user, 't_multi_col')
PL/SQL过程已成功完成。
SQL> explain plan for
2 select *
3 from t_multi_col
4 where file_id = 2;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 1795650781
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8391 | 565K| 27 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_MULTI_COL | 8391 | 565K| 27 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FILE_ID"=2)
已选择13行。
SQL> explain plan for
2 select *
3 from t_multi_col
4 where file_id = 2
5 and relative_fno = 2;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 1189586738
--------------------------------------------------------------------------------------------
| Id | Operation | Name |Rows|Bytes| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |2797| 188K| 19 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_MULTI_COL |2797| 188K| 19 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_MULTI_COL_FILE |2797| | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FILE_ID"=2 AND "RELATIVE_FNO"=2)
已选择14行。
SQL> select distinct file_id, relative_fno
2 from t_multi_col;
FILE_ID RELATIVE_FNO
---------- ------------
1 1
2 2
3 3
Oracle判断是否使用索引，是根据选择度来判断，对于上面的SQL，由于列FILE_ID的选择度是1/3，因此查询的时候Oracle认为使用索引的代价大于全表扫描。
如果指定了两个列的情况，那么Oracle认为同时指定两个列的选择度等于每个单列选择度相乘，对于上面的例子就是1/3×1/3＝1/9。
如果查询的选择度是1/9，那么Oracle认为索引的效率比全表扫描更高一些。
但是对于当前的例子，FILE_ID和RELATIVE_FNO是强关联性，如果FILE_ID确定，那么RELATIVE_FNO也基本确定，从上面最后的查询也可以看到，事实上同时指定了FILE_ID和RELATIVE_FNO，查询的选择度仍然是1/3。
而对于这种强相关性，跟业务逻辑密切相关，因此Oracle没有办法自动处理。
在11.2中，Oracle增加了COLUMN GROUP的概念，将这种强关联的列组合在一起进行统计信息的收集：
SQL> exec dbms_stats.gather_table_stats(user, 't_multi_col', method_opt => 'for columns (file_id, relative_fno) size skewonly')
PL/SQL过程已成功完成。
SQL> explain plan for
2 select *
3 from t_multi_col
4 where file_id = 2
5 and relative_fno = 2;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 1795650781
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8391 | 598K| 27 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_MULTI_COL | 8391 | 598K| 27 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RELATIVE_FNO"=2 AND "FILE_ID"=2)
已选择13行。
这是一个最简单的例子，直接在收集统计信息的时候指定了对列组收集信息。因此Oracle对于这个查询选择了全表扫描，而没有去选择索引扫描。
oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html
