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 wner = '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