lindorm表创建倒序索引sql oracle倒序索引

作者：kindle

一、索引的概念：

    数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。

二、索引的特点：

    1.索引可以加快数据库的检索速度
    2.索引降低了数据库插入、修改、删除等维护任务的速度

三、oracle索引的类型：

b*树索引：

    这是Oracle中最常用的索引，它的构造类似于二叉树，能根据键提供一行或一个行集的快速访问，通常只需要很少的读操作就能找到正确的行。B*树索引由两列组成，第一列是ROWID, 它是行的位置（它指向了数据库表中对应的此行的位置）;第二列是正被索引列的值（具体的被建立了索引的列的值，我们先是根据 

这个值通过导航块找到这个叶子节点的该项（值，rowid），然后通过rowid找到具体的这行记录）。

 

             图:典型的B*树索引布局

    这个树底层的块称为叶子节点/叶子块，其中分别包含各个索引键值以及一个rowid(它是指向所索引的行)。叶子节点之上的内部块称为分支块(branch block)/导航块，这些节点用于实现导航。例如，如果想在索引中找到值20，要从树顶开始，找到左分支，我们检查这个块，并发现需要找到范围"20..25"的块，这个块将是叶子块，其中会指示包含数20的行。索引的叶子节点实际上构成了一个双向链表。一旦发现要从叶子节点中的哪里开始，执行值的有序扫描就会很容易，我们就不必再在索引结构中导航，而只需根据叶子节点向前或向后扫描就可以了（比如要做between 20 to 40，我们只需要先导航找到20，然后顺序扫描直到遇到40即可）。

    B*树的特点之一是：所有叶子块都应该在树的同一层上，这一层称之为索引的高度, 它说明所有从索引的根块到叶子块的遍历都会访问同样数目的块。由此可见B*树的B代表的是balanced，所谓的"Height balanced"。大多数B*树索引的高度都是2或3，即使索引中有数百万行记录也是如此，这说明，一般而言，在索引中找到一个键只需要2到3次I/O , 这确实不错。

    B*树是一个极佳的通用索引机制，无论是大表还是小表都很适用，随着底层表大小增长，获取数据的性能仅会稍有恶化。

比如，我们为customers表建立一个常见的B*树索引:　

CREATE INDEX IDX_Cus_City on customers(city)

b*树子索引---复合索引

    复合索引也是一种B*树索引，它由多列组成。当我们拥有使用两列或超过两列的频繁查询时，就使用B*树复合索引，而其所使用的两列或多列在where子句中and逻辑操作符连接。因为复合索引中列的顺序很重要，所以确信以最有效的索引顺序排列他们，前导列应该是基数大的列（不同的取值多）。而且单独的where也会使用此索引，而后序列则必须与前导一起使用。

反向索引：

    Oracle推出它的主要目的就是为了降低在并行服务器（Oracle Parallel Server）环境下索引叶块的争用。当B*Tree索引中列是由递增的序列号产生的话，那么这些索引信息基本上分布在同一个叶块，当用户修改或访问相似的列时，索引块很容易产生争用。反向索引中的索引码将会被分布到各个索引块中，减少了争用。反向索引反转了索引码中每列的字节，使得它们不会分到一个叶子块中，但是反向索引也因此不能应用于范围搜索，因为它的相邻叶子块是无顺序的。

CREATE INDEX index_name on table_name(column_name) REVERSE ;

降序索引：

    降序索引是oracle 8i引入的，用以扩展B*树索引的功能，它允许在索引中以降序(从大到小的顺序)存储一列。对于降序的order by有效。

位图索引：

    当我们需要根据基数比较小的列进行查询时，一般使用位图索引会对查询效率有明显提高，如查询性别是女，已婚，汉族的人。（性别，婚否，民族基数都比较小）

CREATE BITMAP INDEX index_name ON table_name(column_name1,column_name2) TABLESPACE tablespace_name

函数索引：

    B*Tree的衍生产物，应用于查询语句条件列上包含函数的情况，索引中储存了经过函数计算的索引码值。可以在不修改应用程序的基础上能提高查询效率。

五、建立合适的索引：

1、最简单的情形：

   如果你在查询时常用类似以下的语句 + 表的记录比较多 + category_id基数比较大（不重复记录数） 
   SELECT * FROM mytable WHERE category_id=1;
   最直接的应对之道，是为category_id建立一个简单的索引：
　 CREATE INDEX mytable_categoryid ON mytable (category_id);

2、如果你有不止一个选择条件，例如：
　 SELECT * FROM mytable WHERE category_id=1 AND user_id=2;

   你可以建立复合索引。

   CREATE INDEX mytable_categoryid_userid ON mytable (category_id,user_id);

3、如果有个ORDER BY子句，那么我们就为这个ORDER BY字段建立一个索引

   如果是降序的，我们可以建立降序索引，否则建立普通索引。

4、在基数小的字段上要善于使用位图索引。

   如在员工信息表中的性别字段，一般就只有男跟女两个值，所以，其基数为2;婚姻状况字段的话，则其只有已婚、未婚、离婚三种状态，其基数就为3;民族一览内也是只有有限的几个值。

5、需要利用Join语句连接多个表时，把这些表格关联起来。为了提高数据库的查询效率，这些用来关联的字段，最好能够建立索引。这可以显著的提高查询的速度。

六、索引的屏蔽：

在SQL中有很多陷阱会使一些索引无法使用。下面讨论一些常见的问题：
1、 使用不等于操作符（<>、!=）

下面的查询即使在cust_rating列有一个索引，查询语句仍然执行一次全表扫描。

select cust_Id,cust_name
    from   customers
     where  cust_rating <> 'aa';

把上面的语句改成如下的查询语句，这样将会使用索引。

select cust_Id,cust_name
     from   customers
     where  cust_rating < 'aa' or cust_rating > 'aa';

特别注意：通过把不等于操作符改成OR条件，就可以使用索引，以避免全表扫描。
2、 使用IS NULL 或IS NOT NULL

    使用IS NULL 或IS NOT NULL同样会限制索引的使用。因为NULL值并没有被定义。在SQL语句中使用NULL会有很多的麻烦。因此建议开发人员在建表时，把需要索引的列设成NOT NULL。如果被索引的列在某些行中存在NULL值，就不会使用这个索引（除非索引是一个位图索引）。
3、 使用函数

    如果不使用基于函数的索引，那么在SQL语句的WHERE子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。

下面的查询不会使用索引（只要它不是基于函数的索引）

select empno,ename,deptno
     from   emp
     where  trunc(hiredate)='01-MAY-81';

    把上面的语句改成下面的语句，这样就可以通过索引进行查找。

select empno,ename,deptno
     from   emp
     where  hiredate<(to_date('01-MAY-81')+0.9999);

4、 比较不匹配的数据类型

    比较不匹配的数据类型也是比较难于发现的性能问题之一。注意下面查询的例子，account_number是一个VARCHAR2类型，在account_number字段上有索引。下面的语句将执行全表扫描。

select bank_name,address,city,state,zip
     from   banks
     where  account_number = 990354;

    Oracle可以自动把where子句变成to_number(account_number)=990354，这样就限制了索引的使用,改成下面的查询就可以使用索引：

select bank_name,address,city,state,zip
     from   banks
     where  account_number ='990354';