PowerPoint Presentation

Save this PDF as:

WORD PNG TXT JPG

Size: px

Start display at page:

Download "PowerPoint Presentation"

矮胥
2 years ago
Views:

Transcription

1 数据库培训项目研究 Oracle 索引探究 B*tree 索引与位图索引的特点作者 : 赵超 2008 年 12 月 18 日

2 实验环境 Windows-server2003 内存 :2G Oracle ORACLE_SID=orcl

3 索引类型 B*tree 索引 ( 默认方式 ) 位图索引 (bitmap) 反向键索引 (reverse key) 位图连接索引 (bitmap Join) 基于函数的索引 (function-based) 域索引 (domain) B*tree 索引是比较基础的索引, 大多数索引是在其存储结构上的此外, 根据不同的分类方法, 可分为不同的类型 : 根据存储结构可共分为 b*tree 索引位图索引反向索引 ; 按照索引个数可分为单列索引复合索引 ; 按照索引列值唯一性可分为唯一性索引和非唯一性索引

其存储结构上的此外, 根据不同的分类方法, 可分为不同的类型 : 根据存储结构可共分为 b*tree 索引位图索

4 B*tree 索引 B*tree 索引 :B 是 (balance) 是默认的索引类型, 是数值的有序列表他是基于二叉树, 索引的顶层是根块儿, 指向下的分支块儿, 最终指向叶块叶块中包含被索引列的值和行所对应的 rowid 结构 : 少于等于 50 大于等于 > rid 101 rid 102 rid ~~~ 50 rid 51 rid 52 rid ~~~ 25 rid 26 rid 27 rid ~~~ 0 rid 2 rid 3 rid ~~~

的 rowid 结构 : 少于等于 50 大于等于 50 50-75 75-100 >100 0-25 25-50 100 rid 101 rid

5 位图索引 Oracle 7.3 版本开始引入, 企业版和个人版支持, 标准版不支持位图索引是一个索引键条目存储指向多个指针 ; 有很少的索引条目, 每个条目指向多行行值 :A/B A B

6 用户创建用户 test create user test identified by test default tablespace users quota unlimited on users temporary tablespace temp; 授权 :create table,create index, connect,plustrace 角色等等

7 实验表建表 : Create table order2 (a1 number(10), a2 varchar2(40), a3 varchar2(20), a4 number(10),a5 number(10), a6 varchar2(20),a7 varchar2(20)); 插入值 : begin for i in Loop insert into order2 values(i,dbms_random.string( a',30), dbms_random.string( b',30), dbms_random.value(1,100), dbms_random.value(1000,100000), dbms_random.string( c',30), chr(round(dbms_random.value(97,98)))); if mod(i, 10000) = 0 then Commit; end if; end loop; end; / 表说明 :a1 字段中的值为唯一性值, 各不相同 ;a2 a3 a6 中的值为极少数相同 ;a4 100 个不同的值,100/ = , 比例非常小 ;a 个值, 99000/ = ;a7 只有两个值 2/ = ; 其中这个比例可以叫做基数, 越小, 选择做位图索引越有可能

8 语句执行跟踪使用 autotrace 自动为用户指定 execution plan Sys 建立 PLAN_TABLE 表建立 plustrace 角色 Set autot on 或者 set autotrace traceonly 每一次执行查询插入等操作均执行 shutdown immediate 清空缓存

sql, 建立 plustrace 角色 : @$ORACLE_HOME/sqlplus/admin/plustrace.

9 a1 未建立索引 Select * from order2 where a1=1; Select * from order2 where a1=20000; Select * from order2 where a1= ; Select * from order2 where a2=yuxvfnftll;

10 Select * from order2 where a1>=1 and a1<= ; Select * from order2 where a1>=1 and a1<=

11 a1 建立 b*tree 索引 Create index ix_order2_a1 on order2(a1) nologging; Nologging 可以减少归档空间

12 Select * from order2 where a1=1; Select * from order2 where a1=20000; Select * from order2 where a1= ; Select * from order2 where a2=yuxvfnftll;

13 Select * from order2 where a2= yuxvfnftll and a1= ;

14 Select * from order2 where a1>=1 and a1<= ; Select * from order2 where a1>=1 and a1<= ; Select * from order2 where a1>=1 and a1<= ; Select * from order2 where a1>=1 and a1<= ; Select * from order2 where a1>=1 and a1<= ;

where a1>=1 and a1<=2000000; Select * from order2 where a1>=1

15 Select * from order2 where a4=1; a4( 列中 100 个值重复 ) 作为条件

16 a4 建立 B*tree 索引 : Create index ix_order2_a4 on order2(a4) nologging;

17 Select * from order2 where a4=1;

18 Select owner,segment_name,round(bytes/1024/1024,2) M from dba_segments where owner= TEST ;

19 Drop index ix_order2_a4; create bitmap index ix_order2_a4_b on order2(a4) nologging; 如果有多个重复值,Bitmap 索引比 b*tree 节省物理空间

20 Select * from order2 where a4=1;

21 ( 属性表 ) 10 7 a1 Ix_order2_a1 a4 Ix_order2_a4 Ix_order2_a4_b 数值 / 行数 1 * 1/10 5 * * 类型 * B*tree * B*tree Bitmap 索引大小 * 192M * 160M 22M

22 (cost(%cpu) 表 ) 字段条件 time 索引利用 a1 ix_order 2_a1 1 行 : * %10: %30: 25~30s * 16807(3) 50~60s * 16849(4) 1 行 : %10: %15: %20: %25: %30: 0.40~ ~22s 25~30s 38~42s 48~53s 60s 以上 ( 利 ) ( 利 ) ( 利 ) ( 利 ) ( 未 ) ( 未 ) 4(0) 2898(1) 8562(1) 14226(1) 16839(4) 16846(4) a4 1 行 9~10s * 16216(4) ix_order 2_a4 ix_order 2_a4_b 1 行 1 行 9~10s ( 未 ) 16216(4) 8~9s ( 利 ) 9812(1) Cost (%cpu) 7~8s 16119(3)

23 a7( 只有 a b 两个值 ) 未建立索引 Select * from order2 where a7= a ; Select * from order2 where a7= b ;

24 建立 b*tree 索引 : Create index ix_order2_a7 on order2(a7); Select owner,segment_name,round(bytes/1024/1024,2) M from dba_segments where owner= TEST ; Select * from order2 where a7= a ; Select * from order2 where a7= b ;

25 建立 bitmap 索引 Drop index ix_order2_a7; Create bitmap index ix_order2_a7_b on order2(a7) nologging; Select owner,segment_name,round(bytes/1024/1024,2) M from dba_segments where owner= TEST ; Select * from order2 where a7= a ; Select * from order2 where a7= b ;

26 ix_order2_a7_b,ix_order2_a4_b 比较 10 7 a4 Ix_order2_a4 Ix_order2_a4_ b a7 Ix_order2_a7 Ix_order2_ a7_b 数值 / 行数 1/10 5 * * 2/10 7 * * 类型 * B*tree Bitmap B*tree Bitmap 以该列为条件查询 (s) 9~10s 9~10s( 未利用索引 ) 8~9s( 利用了索引 ) 80-90s s( 未利 s 用索引 ) ( 利用了索引 ) 索引大小 * 160M 22M * 144M 4M 当使用位图索引时, 如果表中的行数比较少, 可以迅速得到结果, 如果数值比较大, 看似时间上没有什么变化, 但是结果输出也占用一定的时间, 对于查询的性能还是提高的, 占用较少的 cpu 资源

27 Update 测试以上索引均正常情况下 : Update 1 行 : 00:0:13.93(table_full) Update 10 4 行 : 无法执行,session 锁住

28 建立主键, 同时在该列上也有了唯一索引 drop index ix_order2_a1; 删除系统为了保证 b*tree 索引的唯一性而加上的 rowid 索引 Alter table order2 add constraint cst_a1 primary key (a1); Select * from order2 where a1=1;

29 总结一 B*tree 索引可以很好的自动平衡创建了主键, 同时创建了索引 ; 索引会对频繁的 DML 操作造成影响, 而不合适的位图索引造成的结果是严重的, 可能造成表锁会话锁在实际应用中, 应结合表数据特点检索行数主外键多表结合检索等因素, 选取合适的列建立相应类别的检索

30 总结二索引的好处 : 1 创建唯一性索引, 保证数据库表中每一行数据的唯一性 2 大大加快数据的检索速度 3 加速表和表之间的连接, 特别是在实现数据的参考完整性方面特别有意义索引的缺点 : 1 索引降低了数据库插入修改删除等维护任务的速度 2 创建索引和维护索引要耗费时间, 这种时间随着数据量的增加而增加 3 每一个索引要占一定的物理空间

31 结束!

32 (cost(%cpu) 表 ) 10 7 a1 Ix_order2_a1 a4 Ix_order2_a4 Ix_order2_a4_b 数值 / 行数 1 * 1/10 5 * * 类型 * B*tree * B*tree Bitmap 以该列为条件查询 (s) 1 行 :7~8s %10: 25~30s %30: 50~60s 1 行 :0.40~0.50 ( 利 ) %10:18~22s ( 利 ) %15:25~30s ( 利 ) %20:38~42s ( 利 ) %25:48~53s ( 未 ) 9~10s 9~10( 未 ) Cost (%cpu): ~9( 利用了索引 ) Cost (%cpu):9812 %30:60s 以上 ( 未 ) 索引大小 * 192M * 160M 22M

Oracle数据库应用技术4 [兼容模式]

Oracle数据库应用技术4 [兼容模式] Oracle 数据库应用技术河南中医学院信息技术学院王哲第四章管理表空间主讲内容 : 表空间及管理第 2 页主要内容一. 表空间基础知识二. 管理表空间三. 其他表空间第 3 页一. 表空间基础知识在创建数据库时,Oracle 会自动地创建多