你的位置:首页 > 数据库

[数据库]Oracle组合索引与回表


回表

简单来说就是数据库根据索引找到了指定的记录所在行后,还需要根据rowid再次到数据块里取数据的操作。

"回表"一般就是指执行计划里显示的"TABLE ACCESS BY INDEX ROWID"。

例如select的字段里有索引不包含的列

根据tom的oracle编程艺术,建表big_table,300W数据。

建索引:

create index idx_big_table_created on big_table(created);

下面语句不会回表,因为只查询了索引列。

explain plan for

select created

from big_table a

where created = to_date('2010/3/30 10:08:30', 'yyyy/mm/dd hh24:mi:ss');

select * from table(dbms_xplan.display)

下面语句要回表,因为还查询了除索引列的其他列,虽然id字段是主键。

explain plan for

select id,created

from big_table a

where created = to_date('2010/3/30 10:08:30', 'yyyy/mm/dd hh24:mi:ss');

select * from table(dbms_xplan.display)

组合索引

当某个索引包含有多个已索引的列时,称这个索引为组合(concatented)索引。

下面语句不会回表:

explain plan for

select object_name, count(1)

from big_table a

where created >= to_date('2010/3/30 10:07:50','yyyy/mm/dd hh24:mi:ss')

and created <= to_date('2010/3/30 10:08:37','yyyy/mm/dd hh24:mi:ss')

group by object_name;

select * from table(dbms_xplan.display)

关于组合索引

1.一个表建立多少个索引比较合适?比如说不大于5个  

    这个没有定论,楼主要综合查询效率和dml效率自己确定,索引可以加快select的查询速度,但也会降低delete,insert和update等dml语句的执行速度。

2.联合索引比单索引的效率高么? 

    如果联合索引中的多个字段都在where谓词中出现了,则联合索引效率比单列索引高,因为通过多个条件可以从索引中过滤得到更少的记录条数,也就减少了需要回表扫描的次数,甚至可以直接在联合索引中得到所查的所有结果,则不再需要回表。

    但是由于多列的联合索引肯定要比单列索引大,也就是说同样的索引需要存储的物理块要多于单列索引,所以,如果查询中只出现了联合索引中的某一列,则其效率不如单列索引。

3.compress 这个压缩 ,能起到什么作用? 

    没研究过

4.上网上查资料时,都说oracle使用联合索引需要前导列,这个和版本有关系吗? 

    前导列跟版本没有关系。

    前导列的概念是这样的,如果建立了f1,f2上的联合索引,则在查询时必须要用到f1,也就是所谓的前导列,该索引才会有效,因为索引是按照前导列排序的,如果where条件谓词中没有前导列,则需要执行索引扫描才能得到想要的结果,这种情况下其效率往往较差。

5.如果不需要前导列的话,reverse 这个反转 又起到什么作用呢? 

    鉴于前面描述的前导列的概念,我们考虑如下表存储table(f1,f2);

   aa  1

   ab  2

   ac  3

   ad  4

   ae  5

   如果我们对表table建立f1上的普通索引,由于按照f1进行排序,所以针对where f1=ad则需要遍历所有的a开始的索引,而如果对f1建立reverse索引,则由于da只有一个,则可以更快的得到需要的结果。