oracle 索引回表,oracle 索引简单总结

建立索引时先进行排序，逻辑上分为Root(根块)，Branch(茎块)，leaf(叶子块)。leaf中存在索引列的值、长度、和所在rowid，茎块存了指向具体茎块的指针，root块同理。这是逻辑上的划分实际上根据表中数据量的多少可能会存在多层，但是索引整体上层次较低，例如一张500G的表数据量达到几百亿条，这时候它的索引只有6层。

在查询结果返回多的情况下使用索引会浪费更多的代价，另外如果一张表有五个字段，我们在三个字段上建上btree索引。那样效率只会更低。可考虑使用位图索引

索引的三大特点：

1.索引数的高度一般较低

2.索引由索引列存储的值及rowid组成

索引SELECT * FROM T WHERE ID = 1会导致索引回表的产生，若不需要看全部数据可用SELECT ID FROM T WHERE ID= 1或可建多列的复核索引，但是复核索引最好不要超过3列的复核。在更新操作不频繁的情况下可考虑使用索引组织表

3.索引本身是有序的

减少ORDER BY、DISTINCT排序所浪费的COST

聚合因子：

建立索引的列的顺序与索引自动排序的列的顺序的不对应度被称为聚合因子，聚合因子越大索引回表读越浪费时间(索引回表读不可避免的情况下)

oracle 执行计划分类：

1、TABLE ACCESS FULL   全表扫描

2、INDEX FAST FULL SCAN   索引快速扫描   (不考虑排序COUNT(ID), SUM(ID), AVG(ID)，列必须非空或IS NOT NULL)

3、INDEX FULL SCAN   索引全扫   (SELECT ID FROM T WHERE ID = 120)

4、INDEX FULL SCAN(MIN/MAX)  最大值最小值索引全扫  (SELECT MAX(ID) FROM T)

5、TABLE ACCESS BY INDEX ROWID  索引回表读   (SELECT * FROM T WHERE ID = 1)

6、INDEX RANGE SCAN   索引范围扫描   (SELECT * FROM T WHERE ID 

7、BITMAP INDEX FAST FULL SCAN  位图索引快速扫描  (不考虑排序COUNT(ID), SUM(ID), AVG(ID)，位图快速索引扫描速度非常快将近是普通索引的百倍.列不必非空)

btree索引优化简介

T表  字段：OBJECT_ID, OBJECT_NAME

单列索引：

CREATE INDEX IDX1_OBJECT_ID ON T(OBJECT_ID);

COUNT(*)、SUM、AVG优化：

改为COUNT(OBJECT_ID)查询，但是必须保证OBJECT_ID列非空。可用如下方法告知oracle可走IDX1_OBJECT_ID索引：

1、SELECT COUNT(OBJECT_ID) FROM T WHERE OBJECT_ID IS NOT NULL;

2、设置字段非空

MAX/MIN优化：

根据第三个特性有序排列，所以MAX/MIN的查询代价会非常小。

SELECT MAX(OBJECT_ID) FROM T; 不需加上IS NOT NULL; 使用执行计划：INDEX FULL SCAN(MIN/MAX);

ORDER BY、DISTINCT排序优化：

SELECT * FROM T WHERE OBJECT_ID 

未建立索引的情况下会进行排序产生TEMPSPC;

建立索引的情况下不需要产生排序 会使用IDX1_OBJECT_ID索引

位图索引

创建语法：

CREATE BITMAP INDEX IDX_BITM_T_STATUS ON T(STATUS);

适用在更新非常少的表，建立在重复度较高的列(性别)

存储结构：

位图索引存储的是比特位值

函数索引：

CREATE TABLE T AS SELECT * FROM DBA_OBJECT;

CREATE INDEX IDX_OBJECT_ID ON T(OBJECT_ID);

CREATE INDEX IDX_OBJECT_NAME ON T(OBJECT_NAME);

CREATE INDEX IDX_CREATED ON T(CREATED);

SELECT * FROM T WHERE UPPER(OBJECT_NAME) = 'T';

普通的BTREE索引，如果在对列做运算的条件下是无法使用索引查询的，会使用TABLE ACCESS FULL;

创建语句：

CREATE INDEX IDX_UPPER_OBJ_NAME ON(UPPER(OBJECT_NAME));

SELECT INDEX_NAME, INDEX_TYPE FROM USER_INDEXES WHERE TABLE_NAME = 'T';

函数索引的TYPE是：FUNCTION-BASED NORMAL;

函数索引的cost比全表扫描要小，但是比普通的索引要大的多。

SELECT * FROM T WHERE OBJECT_ID - 10<30;

这时候如果在object_id列建立普通索引时无法使用的。oracle会默认使用全表扫描的方式进行查询。可有以下两个思路进行优化：

1、SELECT * FROM T WHERE OBEJCT_ID 

2、在OBJECT_ID - 10上建立函数索引

写sql时要注意规范，很多语句是等价的。

SELECT * FROM T WHERE SUBSTR(OBJECT_NAME,1，4) = 'CLUS'   =    SELECT * FROM T WHERE OBJECT_NAME LIKE 'CLUS%';

SELECT * FROM T WHERE TRUNC(CREATED) >= TO_DATE('2012-10-02', 'YYYY-MM-DD') AND TRUNC(CREATED) <= TO_DATE('2012-10-03','YYYY-MM-DD')

=

SELECT * FROM T WHERE CREATED >= TO_DATE('2012-10-02', 'YYYY-MM-DD') AND CREATED 

;