blevel为1下，索引部分cost的计算

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                             6,249,499        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      6,249,499      29     179      317,710 12-27-2011

Index                          Column                          Col Column
Name                           Name                            Pos Details
------------------------------ ------------------------------ ---- ------------------------
T_I                            OBJECT_ID                         1 NUMBER(22)

**********************************************************
Partition Level
**********************************************************

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     |  3533 |       |    32   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

sys@SMART>begin
  2  dbms_stats.set_index_stats(ownname => user,indname => 't_i',indlevel => 1);
  3  end;
  4  /

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            |         |  3533 |   296K|   210   (0)| 00:00:03 |
|   1 |  TABLE ACCESS BY INDEX ROWID| WXH_TBD |  3533 |   296K|   210   (0)| 00:00:03 |
|*  2 |   INDEX RANGE SCAN          | T_I     |  3533 |       |    30   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

belevel从2变成了1，按理说索引部分COST也降低1才合理。可是从实验结果来看索引部分的cost降低了2.
李维思在他的CBO里说了，在belevel为1的时候，ORACLE忽略了belevel.不过这是有条件的，那就是你做的是等值查询。
非等值查询，还是会考虑blevel
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            |         |   312K|    25M|  3331   (1)| 00:00:40 |
|   1 |  TABLE ACCESS BY INDEX ROWID| WXH_TBD |   312K|    25M|  3331   (1)| 00:00:40 |
|*  2 |   INDEX RANGE SCAN          | T_I     | 56245 |       |   468   (1)| 00:00:06 |
---------------------------------------------------------------------------------------

14 rows selected.

sys@SMART>begin
  2   dbms_stats.set_index_stats(ownname => user,indname => 't_i',indlevel => 1);
  3  end;
  4  /

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            |         |   312K|    25M|  3330   (1)| 00:00:40 |
|   1 |  TABLE ACCESS BY INDEX ROWID| WXH_TBD |   312K|    25M|  3330   (1)| 00:00:40 |
|*  2 |   INDEX RANGE SCAN          | T_I     | 56245 |       |   467   (1)| 00:00:06 |
---------------------------------------------------------------------------------------

{48B03BAA-2160-4A0E-ACA8-CB2DDA0FED85}.jpg

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

转载于:http://blog.itpub.net/22034023/viewspace-714005/