oracle 每日一题-反向键索引

原始出处：
http://www.plsqlchallenge.com/

作者：ChrisSaxon

运行环境：SQLPLUS, SERVEROUTPUT已打开

你的应用在这张表中存储着用户的登录信息：

create table plch_user_logins (
  user_id        int not null,
  login_datetime date not null,
  primary key ( user_id, login_datetime )
);

create index plch_uslo_date_i on 
  plch_user_logins ( login_datetime ) reverse;

这张表有几百万行。数据是以login_datetime进行聚簇的。

哪些查询的执行计划会在plch_uslo_date_i上进行索引范围扫描(index range scan)?

(A) 
select * from plch_user_logins
where  login_datetime = date'2016-01-01'
and    rownum <= 10;

(B) 
select * from plch_user_logins
where  trunc( login_datetime ) = date'2016-01-01'
and    rownum <= 10;

(C) 
select * from plch_user_logins
where  ( login_datetime = date'2016-01-01' or
         login_datetime = date'2016-01-02' )
and    rownum <= 10;

(D) 
select * from plch_user_logins
where  login_datetime >= date'2016-01-01'
and    login_datetime < date'2016-01-01' + interval '1' hour
and    rownum <= 10;

(E)
select * from plch_user_logins
where  login_datetime in (
  select date'2016-01-01'+(rownum-1)/86400 
  from   dual connect by level <= 3600

)

and    rownum <= 10;

反向键：其实就是oracle在存储数据的时候，将数据反向存储，比如说12345 反向之后就变成了54321.

这样做的目的： 当程序需要访问 12345 和12346 和12347等等这种比较连续的数值时，不至于导致访问的数据在同一个块上。

都知道，现在很多公司里再用oracle数据库，并将其搭建在商用unix系统上，但是这种系统也不是最完美的，因为这样搭建起来的系统最多只能支持1024块cpu。这听起来好像很多了，可是要知道，在一些大型的公司里，或许这还不够用呢，1024块cpu满足不了用户的需求，那怎么办呢？

 

       当硬件已经饱和的时候，就想办法在软件上进行扩展。这时，oracle就想到了一个办法进行扩充。比如说在一个公司里面，我需要超过1024块cpu的系统，这时就可以搭建一个环境来扩展。如果公司有一个机房，前面有一台总机，下面有一大堆的电脑，这时，我就可以将一个被数据库协调管理的系统装在总机上，而在每台电脑上装一个数据库管理系统，每个都支持最多1024块cpu，这样就扩展了公司的系统性能。用户的请求最先到达的是前面的总机，然后总机就会将用户的请求协调分配到下面的每台电脑上，这样就可以平衡用户的请求，是操作的速度加快。

 

       可是，这也存在一个问题。我们都知道，一般在公司、学校等等机构里面，成员的编号是有一定意义的，比如有的是用入职年月日在加上员工序号作为员工的编号，如07年11月23日入职的员工编号为0711231000，0711231001, 0711231002···在数据库中，为公司员工创建了一张表，员工编号是员工在公司的唯一性标号，所以可以为这些公司员工在数据库中以员工编号建立索引。索引是排好了序的，也就是说，索引中员工编号0711231000,0711231001,0711231002这样的连续值被索引到连续相近的地方，这样的话，这一索引块存储在某一台电脑上的可能形就非常大，如果有一个用户的请求是以员工编号为条件的话，那么总机将此用户请求分配下去之后，次用户请求还是会在某一台电脑上产生大量的io而影响用户请求的速度。那有什么办法解决这个问题呢？

 

       还真有办法解决，那就是使用反转键索引。使用反转键索引对员工编号进行索引之后，也就是0711231000,0711231001,0711231002这样的连续值变成了0001321107,1001321107,2001321107这样的不连续值，对这些不连续值进行索引之后，原本可能堆积在某一台电脑上的索引，就被分散存储在不同的电脑上了，这样，用户的请求就会被分散到多台电脑上，从而加快了用户请求的速度。

1.反向索引应用场合
1）发现索引叶块成为热点块时使用
通常，使用数据时（常见于批量插入操作）都比较集中在一个连续的数据范围内，那么在使用正常的索引时就很容易发生索引叶子块过热的现象，严重时将会导致系统性能下降。
2）在RAC环境中使用
当RAC环境中几个节点访问数据的特点是集中和密集，索引热点块发生的几率就会很高。如果系统对范围检索要求不是很高的情况下可以考虑使用反向索引技术来提高系统的性能。因此该技术多见于RAC环境，它可以显著的降低索引块的争用。

2.使用反向索引的优点
最大的优点莫过于降低索引叶子块的争用，减少热点块，提高系统性能。

3.使用反向索引的缺点
由于反向索引结构自身的特点，如果系统中经常使用范围扫描进行读取数据的话（例如在where子句中使用“between and”语句或比较运算符“>”“<”等），那么反向索引将不适用，因为此时会出现大量的全表扫描的现象，反而会降低系统的性能。

有时候可以通过改写sql语句来避免使用范围扫描，例如where id between 12345 and 12347，可以改写为where id in(12345,12346,12347)，CBO会把这样的sql查询转换为where id=12345 or id=12346 or id=12347,这对反向索引也是有效的。