ORACLE 分区与索引

《oracle分区与索引》

引言：oracle的分区和索引可以说是它自己的亮点，可能你会说在其他数据库上也有，嗯是的，但oracle的种类 性能 便利性可以说是比较人性化的，下面我们通过实验来阐述它们的特性和功能。

 

1.分别给出一个B-tree索引针对全表扫描性能高和低的例子。

索引定义：oracle数据库中索引就是为了加快数据访问速度的一种目录结构

B-tree索引特点：

（1）二叉树结构

（2）用比较大小方式查找索引块

（3）适合创建在键值重复率低的字段

例如  主键字段：强调表的参照关系，即可以被外键引用

         唯一性约束字段：强调字段键值的唯一性

（4）第一次扫描时，从root根节点进入，后面就不在返回进入了

（5）叶子与叶子之间有指针链，不用返回上一层，可以直接定位到下一个叶子节点

（6）主键字段做搜索时效率与数据量无关，例如 1万条记录  1亿条记录检索效率差不多

（7）索引块是按顺序存放的，数据块是打散存放的

（8）结果集越小性能越好，结果集越大性能越不好

（9）相比位图索引，占用空间较多

实验

LEO1@LEO1> drop table leo1;

Table dropped.

LEO1@LEO1> drop table leo2;

Table dropped.

先清理环境，我们重新创建表和索引，看看在不同执行计划下的性能如何。

LEO1@LEO1> create table leo1 as select * from dba_objects;

Table created.

我们创建leo1表用于全表扫描

LEO1@LEO1> create table leo2 as select * from dba_objects;

Table created.

我们创建leo2表用于走B-tree索引

LEO1@LEO1> create index idx_leo2 on leo2(object_id);

Index created.

LEO1@LEO1> execute dbms_stats.gather_table_stats('LEO1','LEO1',cascade=>true);

PL/SQL procedure successfully completed.

LEO1@LEO1> execute dbms_stats.gather_table_stats('LEO1','LEO2',cascade=>true);

PL/SQL procedure successfully completed.

对leo1和leo2表及表上的索引进行统计分析，以便让oracle了解它们的数据分布情况

LEO1@LEO1> select table_name, num_rows, last_analyzed, object_type from dba_tab_statistics  where owner = 'LEO1';

TABLE_NAME                       NUM_ROWS  LAST_ANAL  OBJECT_TYPE

------------------------------ ---------- --------- -------------------------------- ---------- --------- -------

LEO1                                71961     09-JAN-13    TABLE

LEO2                                71962     09-JAN-13    TABLE

好已经显示出对2张表进行统计分析了，并且还知道了表上的数据有71961行和71962行

LEO1@LEO1> set autotrace traceonly      启动执行计划

LEO1@LEO1> select * from leo1 where object_id=10000;      

Execution Plan

----------------------------------------------------------

Plan hash value: 2716644435

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    97 |   287   (1)| 00:00:04 |

|*  1 |  TABLE ACCESS FULL | LEO1  |     1 |    97 |   287   (1)| 00:00:04 |

--------------------------------------------------------------------------

走全表扫描，代价Cost=287

Predicate Information (identified by operation id):

---------------------------------------------------------------------------

   1 - filter("OBJECT_ID"=10000)           谓词条件

Statistics

---------------------------------------------------------------------------

          1  recursive calls

          0  db block gets

       1031  consistent gets             1031个一致性读

       1026  physical reads

          0  redo size

       1626  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

LEO1@LEO1> select * from leo2 where object_id=10000;     

Execution Plan

----------------------------------------------------------

Plan hash value: 2495991774

----------------------------------------------------------------------------------------

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |          |     1 |    97 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID | LEO2      |     1 |    97 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN         | IDX_LEO2  |     1 |       |     1   (0)| 00:00:01 |

----------------------------------------------------------------------------------------

走B-tree索引，代价Cost=2

Predicate Information (identified by operation id):

----------------------------------------------------------------------------------------

   2 - access("OBJECT_ID"=10000)        谓词条件

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

          4  consistent gets     4个一致性读=先访问root->在找branch->在找leaf+递归IO

          0  physical reads

          0  redo size

       1629  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

我们从上面的执行计划可以看出，走B-tree索引效率要比全表扫描高出很多很多，尤其在键值重复率低的字段非常适合使用B-tree索引（流程：先访问root->在找branch->在找leaf->在找到键值key->访问对应ROWID数据块->提取数据），我们还要知道当结果集越小使用索引访问速度越快，如果结果集较大那么，我们看看性能如何呢？

LEO1@LEO1> select * from leo1 where object_id>=10000;

62253 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2716644435

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      | 62216 |  5893K|   287   (1)| 00:00:04 |

|*  1 |  TABLE ACCESS FULL | LEO1  | 62216 |  5893K|   287   (1)| 00:00:04 |

--------------------------------------------------------------------------

走全表扫描，代价Cost=287

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("OBJECT_ID">=10000)

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

       5118  consistent gets                 5118个一致性读

          0  physical reads

          0  redo size

    3245084  bytes sent via SQL*Net to client

      46174  bytes received via SQL*Net from client

       4152  SQL*Net roundtrips to/from client

 本文转自 leonarding151CTO博客，原文链接：http://blog.51cto.com/leonarding/1105416，如需转载请自行联系原作者