計算索引基數所採用的num_rows

--------------表級別的統計資訊值(number of rows)等於索引的(number of rows)

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

plan for
  2   select * from wxh_tbd where object_id=:1;

Explained.

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倍
24997996/2 from dual;

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

1 row selected.

  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.

plan for
  2    select * from wxh_tbd where object_id=:1;

Explained.

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
12498998/2 from dual;

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

1 row selected.

plan for
  2    select * from wxh_tbd where object_id=:1;

Explained.

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.