zoukankan      html  css  js  c++  java
  • 深入理解Oracle索引(17):Cost 值相同 CBO 对索引的选择

         规则如下:

                         

         测试如下:

    hr@ORCL> drop table t purge;
    
    Table dropped.
      
    hr@ORCL> create table t as select * from dba_objects;
    
    Table created.
    
    hr@ORCL> alter table t add (object_id_1 number);
    
    Table altered.
    
    hr@ORCL> update t set object_id_1=object_id;
    
    50363 rows updated.
    
    hr@ORCL> commit;
    
    Commit complete.
    
    hr@ORCL> create index idx_t_a on t(object_id);
    
    Index created.
    
    hr@ORCL> create index idx_t_b on t(object_id_1);
    
    Index created.
    
    /* 统计信息相同、意味着同类型执行计划的Cost值会相同*/
    hr@ORCL> exec dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'T',estimate_percent=>100,cascade=>TRUE,no_invalidate=>false);
    
    PL/SQL procedure successfully completed.
    
    /* 叶子块数量相同、CBO按字母顺序在前走索引*/
    hr@ORCL> select index_name,leaf_blocks from user_indexes where table_name='T' and index_name in ('IDX_T_A','IDX_T_B');
    
    INDEX_NAME                     LEAF_BLOCKS
    ------------------------------ -----------
    IDX_T_A                                111
    IDX_T_B                                111
    
    hr@ORCL> set autot trace exp
    hr@ORCL> select * from t where object_id=1000 and object_id_1=1000;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1194865126
    
    ---------------------------------------------------------------------------------------
    | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |         |     1 |    98 |     2   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS BY INDEX ROWID| T       |     1 |    98 |     2   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | IDX_T_A |     1 |       |     1   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("OBJECT_ID_1"=1000)
       2 - access("OBJECT_ID"=1000)
    
    /* 把idx_t_b叶子块数量从111改为110*/
    hr@ORCL> set autot off
    hr@ORCL> exec dbms_stats.set_index_stats(ownname=>'HR',indname=>'IDX_T_B',numlblks=>110);
    
    PL/SQL procedure successfully completed.
    
    hr@ORCL> select index_name,leaf_blocks from user_indexes where table_name='T' and index_name in ('IDX_T_A','IDX_T_B');
    
    INDEX_NAME                     LEAF_BLOCKS
    ------------------------------ -----------
    IDX_T_A                                111
    IDX_T_B                                110
    
    /* Cost 值相同、CBO  选择叶子块数量较少的索引*/
    hr@ORCL> set autot trace exp
    hr@ORCL> select * from t where object_id=1000 and object_id_1=1000;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3073359464
    
    ---------------------------------------------------------------------------------------
    | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |         |     1 |    98 |     2   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS BY INDEX ROWID| T       |     1 |    98 |     2   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | IDX_T_B |     1 |       |     1   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("OBJECT_ID"=1000)
       2 - access("OBJECT_ID_1"=1000)
    


                        By David Lin 
                        20113-06-05 
                        Good Luck

  • 相关阅读:
    【C语言程序设计】C语言回文数怎么求?
    今天又要加班了,谁叫我是程序员!
    【编程入门】C语言字符串的加密和解密算法!
    某程序员吐槽:媳妇要给孩子报少儿编程班,将来继续做程序员!自己拿命换钱,难道后代也要继续拿命换钱?
    Linux 误删文件恢复命令及方法!
    后端程序员的成长之路:从菜鸟到架构!
    C语言基础教程 之 如何定义变量!
    程序员如何知晓自己被绿?在网上,面对黑客男朋友的你将毫无秘密可言!
    【编程书库】入门+进阶C语言,这几本就够了!
    【C语言笔记】ASCII码可见字符与不可见字符!
  • 原文地址:https://www.cnblogs.com/dyllove98/p/3119935.html
Copyright ? 2011-2022 开发猿


http://www.vxiaotou.com