ORACLE索引总结_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 1711 | 回复: 0   主题: ORACLE索引总结        下一篇 
shary
注册用户
等级:新兵
经验:54
发帖:66
精华:0
注册:2011-8-31
状态:离线
发送短消息息给shary 加好友    发送短消息息给shary 发消息
发表于: IP:您无权察看 2015-6-17 12:29:24 | [全部帖] [楼主帖] 楼主

一.B树索引

0.     B树索引中不存在非唯一的条目。

1)在非唯一索引中,ORACLE会把rowid作为一个额外的列追加到键上,使得键唯一。Exp:create index I on T( x , y ) ,从概念上讲就是Create unique index I on T(x , y , rowid)。ORACLE会首先按索引键值排序,然后再按照rowid升序排序。

2)在唯一索引中,数据只按索引键值排序。

1. 大多数情况下,B树索引的高度都是2或者3,所以一般情况下,在索引中找到一个键只需要2或3次I/O。

2. B树索引所有叶子块都应该在同一层上,并且叶子节点实际上都是双向链表,这样在进行索引区间扫描(index range scan)的时候,只需通过叶子节点的向前或者向后就可以了,无需再对索引结构进行导航。

3. 适当对B树索引存在重复值的列进行压缩, 可以增加缓存命中率,使I/O数减少,因为相关的条目可能都存在在一个块中。(Exp:create index I on T(userid , username) username=’steven’这个值可能就会对应于多个rowid放在同一个索引块中);但是带来的负面作用是使索引结构复杂化,维护索引更耗时,查询索引占用CPU更多的时间。(压缩适合用于多列索引中)



4. B树索引的反向键索引主要用于缓解索引右侧缓冲区忙等待。适合用于类似于sequence产生的PK主键上,因为这些列不太会需要使用区间扫描,也就是不会用到max(PK),min(PK),between and或者where PK < 200等查询

5. 如果在查询中会有order by colum1 asc,column2 desc, 试着在创建索引时create index I on T(colum1asc,column2 desc) , 因为ORACLE INDEX默认是DESC排序,在索引中排序总比在磁盘中排序好得多。

6. 适合B树索引使用的2种情况:

1)访问表中占很小比例的行

2)根本不访问表,所需查询的数据全部在索引中

3)一种特殊的用法,使用索引访问表的全部行,这样的做法是为了使查询最初的响应时间很短,不是针对总吞吐量进行的优化。

7. 索引是按索引键顺序存储,索引会按键的有序顺序进行访问。索引指向的块则随机存储在堆中。

8. 建议:在thin表中使用B树索引查询<2-3%的列,fat表中<20-25%的列。



二.函数索引

1. 函数索引的好处: Exp: function(column1) 如果表中有1000行,即便column1列上建有索引,索引在此时也不被使用,function会执行1000次。



2. 建立函数索引会使插入/更新性能稍有下降,但是查询速度有了极大的提高。另外如果更新的时候没有涉及到建立函数索引的这个列,那就不会产生额外的开销。



3.   部分行建立索引:

1)某一列只有很少的基数,例如只有Y和N

2)通常只会查询where column1 = ‘N’

3)并且值为N的行占很小的比例

4)在此列上建立索引,由于很大的一部分索引空间是浪费的(等于Y的),不会使用到的,所以我们可以在值为’N’的那些行上面建立索引。

5)Create index I on T(decode(column1 , ‘N’, ‘N’))

6)利用的是B树索引特性,如果列值为null,在索引中就没有相应的条目。



4.使用函数索引来保证复杂的约束:(某个条件成立时,X,Y,Z必须唯一)

1)如果项目表,项目有2种状态,ACTIVE和INACTIVE

2)希望ACTIVE的项目必须有唯一的项目名,INACTIVE的项目没有此要求

3)此时项目状态这列上我们不适合建立唯一键

4)但是我们可以建立一个唯一索引

5)Create unique index I on T(decode(STATUS , ‘ACTIVE’ , NAME))

6)利用的还是B树索引特性,如果列值为null,在索引中就没有相应的条目。



三.索引的一些常见问题

1.B树索引特性,如果列值为null,在索引中就没有相应的条目。

1)Create table T (x int , y int)

2)Create unique index I T(x , y)

3)Insert into T values(1 , 1)

4)Insert into T values(null , 1)

5)Insert into T values(1 , null)

6)Insert into T values(null , null)

7)此时索引中只有三行.(null , null)不在索引行中

8)Insert into T values(null , null) --成功插入

9)Insert into T values(1 , null)    --报错,违反index唯一性

10)所以我们可以看到ORACLE中null <> null (null代表N/A)

11)Where x is null   --这个查询无法使用索引,因为(null,null)不在索引中,如果ORACLE使用索引就会得到错误的答案

12)同样能够得出结论,如果在一个允许null的列上面建立索引,x is null也不会使用索引

13)可以使用索引的条件Create table T (x int , y int not null);



2.外键建立索引是需要的



3.索引跳跃式扫描

1)Create index I T(x , y)

2)Select * from T where x=5; --此时优化器可能不会使用索引

3)Select x , y from T where x=5;      --可能使用索引,因为所需要的内容都在索引中

4)索引跳跃式扫描—skip scan

5)如果y只有2个基数,Y和N ,oracle会采用index

6)Select * from T where x=5;   --会经过如下处理

7)Select * from T where x=5 and y=’Y’

8)Union all

9)Select * from T where x=5 and y=’N’;



4.如果索引建立在一个允许null的列上面, select * from T就会使用全表扫描,不使用索引,因为null的行并不在索引行中,所以不会使用index统计数目。



5.select * from T where x=5 等价于select * from T where to_number(x)=’5’,由于存在隐式转换,所以x上的索引不会得到使用



6.where trunc(date) = trunk(sysdate) 可以转换成 date >=trunc(sysdate) and date<trunk(sysdate+1)



7.定期分析表

如果发现Oracle 在有索引的情况下,没有使用索引,这并不是Oracle 的优化器出错。在有些情况下,Oracle 确实会选择全表扫描(Full Table Scan),而非索引扫描(Index Scan)。这些情况通常可能是: 
  1. 表未做statistics, 或者 statistics 陈旧,导致 Oracle 判断失误。 
    最常见的例子,是以下这句sql 语句: 

 select count(*) from T;


  在未作statistics 之前,它使用全表扫描,需要读取6000多个数据块(一个数据块是8k), 做了statistics 之后,使用的是 INDEX (FAST FULL SCAN) ,只需要读取450个数据块。但是,statistics 做得不好,也会导致Oracle 不使用索引。



8.有些情况下单列索引不如复合索引有效率!

甲、有一种情况是显而易见的,那就是,当sql 语句所查询的列,全部都出现在复合索引中时,此时由于 Oracle只需要查询索引块即可获得所有数据,当然比使用多个单列索引要快得多。(此时,这种优化方式被称为Index only access path)。需要注意的是:where 子句中的这个字段,必须是复合索引的第一个字段

乙、还有一种情况就是where coid>=130000 and issuedate >= to_date ('2001-07-20', 'yyyy-mm-dd') 。此时复合索引I_mytabs_test ( coid, issuedate)的效果远远好于两个单列索引:I_mytabs1(coid), I_mytabs2(issuedate)。



--转自北京联动北方科技有限公司




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论