ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 11g统计信息方面增强(四)

11g统计信息方面增强(四)

原创 Linux操作系统 作者:yangtingkun 时间:2010-12-22 23:47:47 0 删除 编辑

11g中,Oracle在统计信息方面进行了进一步的增强。

这篇介绍多列统计信息。

11g统计信息方面增强(一):http://yangtingkun.itpub.net/post/468/507713

11g统计信息方面增强(二):http://yangtingkun.itpub.net/post/468/507859

11g统计信息方面增强(三):http://yangtingkun.itpub.net/post/468/507897

 

 

11.2Oracle增加了多列统计的功能。

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.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_IDRELATIVE_FNO是强关联性,如果FILE_ID确定,那么RELATIVE_FNO也基本确定,从上面最后的查询也可以看到,事实上同时指定了FILE_IDRELATIVE_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对于这个查询选择了全表扫描,而没有去选择索引扫描。

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-682433/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10441483