ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 计算索引基数所采用的num_rows

计算索引基数所采用的num_rows

原创 Linux操作系统 作者:wei-xh 时间:2011-12-27 09:49:03 0 删除 编辑

--------------表级别的统计信息值(number of rows)等于索引的(number of rows)
sys@SMART>@tabstat
Please enter Name of Table Owner: sys
Please enter Table Name : WXH_TBD

**********************************************************
Table Level
**********************************************************


Table                                  Number                        Empty    Chain Average Global         Sample Date
Name                                  of Rows          Blocks       Blocks    Count Row Len Stats            Size MM-DD-YYYY
------------------------------ -------------- --------------- ------------ -------- ------- ------ -------------- ----------
WXH_TBD                            24,997,996        3,175,28            0        0      86 YES        17,498,597 12-27-2011

Column                             Distinct              Number       Number         Sample Date
Name                                 Values     Density Buckets        Nulls           Size MM-DD-YYYY
------------------------------ ------------ ----------- ------- ------------ -------------- ----------
OWNER                                     4   .25000000       1            0     17,498,597 12-27-2011
OBJECT_NAME                           1,760   .00056818       1            0     17,498,597 12-27-2011
SUBOBJECT_NAME                            0   .00000000       0   24,997,996                12-27-2011
OBJECT_ID                             1,769   .00056529       1            0     17,498,597 12-27-2011
DATA_OBJECT_ID                        1,128   .00088652       1    8,524,379     11,531,532 12-27-2011
OBJECT_TYPE                              11   .09090909       1            0     17,498,597 12-27-2011
CREATED                                  15   .06666667       1            0     17,498,597 12-27-2011
LAST_DDL_TIME                            30   .03333333       1            0     17,498,597 12-27-2011
TIMESTAMP                                18   .05555556       1            0     17,498,597 12-27-2011
STATUS                                    1  1.00000000       1            0     17,498,597 12-27-2011
TEMPORARY                                 2   .50000000       1            0     17,498,597 12-27-2011
GENERATED                                 2   .50000000       1            0     17,498,597 12-27-2011
SECONDARY                                 1  1.00000000       1            0     17,498,597 12-27-2011
NAMESPACE                                 6   .16666667       1            0     17,498,597 12-27-2011
EDITION_NAME                              0   .00000000       0   24,997,996                12-27-2011
DEBT_BALA                                 0   .00000000       0   24,997,996                12-27-2011

Index                                      Leaf       Distinct         Number      AV      Av      Cluster Date
Name                           BLV         Blks           Keys        of Rows     LEA    Data       Factor MM-DD-YYYY
------------------------------ --- ------------ -------------- -------------- ------- ------- ------------ ----------
T_I                              2       51,624          1,769     24,997,996      29     179      317,710 12-27-2011

sys@SMART>explain plan for
  2   select * from wxh_tbd where object_id=:1;

Explained.

sys@SMART>@display

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3726906094

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         | 14131 |  1186K|   213   (1)| 00:00:03 |
|   1 |  TABLE ACCESS BY INDEX ROWID| WXH_TBD | 14131 |  1186K|   213   (1)| 00:00:03 |
|*  2 |   INDEX RANGE SCAN          | T_I     | 14131 |       |    32   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

--------------表级别的统计信息值(number of rows)是索引的(number of rows)的2倍
sys@SMART>select 24997996/2 from dual;

24997996/2
----------
  12498998

1 row selected.

sys@SMART>begin
  2    dbms_stats.set_index_stats(ownname       => 'sys',
  3                               indname       => 't_i',
  4                               numrows       => 12498998,
  5                               no_invalidate => false);
  6  end;
  7  /

PL/SQL procedure successfully completed.

sys@SMART>explain plan for
  2    select * from wxh_tbd where object_id=:1;

Explained.

sys@SMART>@display

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3726906094

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         | 14131 |  1186K|   422   (1)| 00:00:06 |
|   1 |  TABLE ACCESS BY INDEX ROWID| WXH_TBD | 14131 |  1186K|   422   (1)| 00:00:06 |
|*  2 |   INDEX RANGE SCAN          | T_I     | 14131 |       |    61   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

--------------表级别的统计信息值(number of rows)是索引的(number of rows)的1/2
sys@SMART>select 12498998/2 from dual;

12498998/2
----------
   6249499

1 row selected.

sys@SMART>explain plan for
  2    select * from wxh_tbd where object_id=:1;

Explained.

sys@SMART>@display

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3726906094

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |  3533 |   296K|   212   (0)| 00:00:03 |
|   1 |  TABLE ACCESS BY INDEX ROWID| WXH_TBD |  3533 |   296K|   212   (0)| 00:00:03 |
|*  2 |   INDEX RANGE SCAN          | T_I     |  7066 |       |    32   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=TO_NUMBER(:1))
1)表级别的统计信息值(number of rows)等于索引的(number of rows)。
2)表级别的统计信息值(number of rows)是索引的(number of rows)的2倍。采用表的(number of rows)来计算索引的基数和回表的基数。
而且会看到此时计算出来索引的成本比第一种情况大了一倍。也就是说,计算索引成本和回表成本的时候,参照了表级别的(number of rows),乘了相应的倍数。
3)表级别的统计信息值(number of rows)是索引的(number of rows)的1/2。索引部分采用索引的(number of rows)来计算基数。回表部分采用表的(number of rows)来
计算基数。这个情况下,ORACLE不会再纠正索引的cost和回表的cost.

如果你分析表的方式经常不分析索引的统计信息,那么你得小心了,这可能会导致你表上的num rows>>索引的num rows,那么非常可能你索引的COST计算会过大。执行计划从之前的index scan.走了full table
本文测试的版本,从10.2.0.4以后应该都是这样的。

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

请登录后发表评论 登录
全部评论
Oracle ACE组成员,DBGeeK用户组发起人。曾在DTCC、ORACLE技术嘉年华、Gdevops等公开场合做过数据库技术专题分享,2017年应Oracle邀请在世界最大的数据库会议OOW上做技术分享。组织翻译了《拨云见日,解密Oracle ASM内核》一书。

注册时间:2009-07-04

  • 博文量
    422
  • 访问量
    2285664