ORACLE索引总结

最新推荐文章于 2024-08-28 12:22:16 发布

huanghui22

最新推荐文章于 2024-08-28 12:22:16 发布

阅读量7.3k

点赞数

分类专栏： ORACLE技术文章标签： oracle statistics null insert 优化 table

本文链接：https://blog.youkuaiyun.com/huanghui22/article/details/1347559

版权

ORACLE技术专栏收录该内容

52 篇文章

订阅专栏

一． 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(colum1 asc,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) 。