Oracle索引详解

索引

类似于书的目录，提高查询效率。

创建索引语法：

 CREATE [UNIQUE] [BITMAP] INDEX 索引名称 ON 表名(字段,[字段,..,..]);
 名词解释：
     UNIQUE 唯一索引
     BITMAP 位图索引
     默认不写 UNIQUE 和 BITMAP 为普通索引
     表名后面写多个字段为复合索引
     在字段加函数为函数索引
     
 注意：当给表中字段添加主键约束或唯一约束时会自动生成一个索引

示例1：创建主键约束和唯一约束

1）创建INDEX_TB表

 CREATE TABLE INDEX_TB(
        ID NUMBER,
        NAME VARCHAR2(30),
        SEX CHAR(2),
        AGE NUMBER(3)
 );

2）给INDEX_TB表中添加数据

 BEGIN
   FOR V IN 1..10000 LOOP
     IF MOD(V,2) = 1 THEN
       INSERT INTO INDEX_TB VALUES(V,CHR(FLOOR(DBMS_RANDOM.value(45000,60000)))||CHR(FLOOR(DBMS_RANDOM.value(45000,60000))),'男',FLOOR(DBMS_RANDOM.value(18,100)));
     ELSE
       INSERT INTO INDEX_TB VALUES(V,CHR(FLOOR(DBMS_RANDOM.value(45000,60000)))||CHR(FLOOR(DBMS_RANDOM.value(45000,60000))),'女',FLOOR(DBMS_RANDOM.value(18,100)));      
     END IF;
   END LOOP;
 END;

3）给INDEX_TB表中的ID列添加主键

 alter TABLE index_tb add constraint pk_id primary key(ID);

当我给表中添加主键约束时，会发现表中已经有索引了。

4）删除INDEX_TB表中的主键约束，为其添加唯一约束

 alter table index_tb drop constraint PK_ID; -- 删除主键约束
 alter table index_tb add constraint unique_id unique(ID); -- 添加唯一约束

这个时候我们再次查看表就发现，表中已经默认生成索引了

示例2：创建普通索引

 CREATE INDEX PT_IND ON INDEX_TB(NAME);
 什么叫普通索引?
     任何列都可以创建        

示例3：创建唯一索引

 CREATE UNIQUE INDEX u_ind ON INDEX_TB(AGE);

如上图所示：

当我们需要给表中某个列添加唯一索引时，那么该列的值中不能出现重复值，否则添加失败；同理，当我们给某个列添加上唯一索引时，那么该列也会被添加上唯一约束，以后该列就不能在添加重复数据了。

示例4：创建位图索引

 CREATE BITMAP INDEX m_ind ON INDEX_TB(sex);

什么是位图索引？

位图索引适合建立在基数小的列，例如：性别、地区、婚姻状况等列。

示例5：创建复合索引

CREATE UNIQUE INDEX fh_ind ON INDEX_TB(id,name,sex);

注意：

在使用复合索引时一定要保证最左原则，也就是说当我在查询时，我where条件中没有id列，而是其他俩个列则索引失效。

验证：

EXPLAIN PLAN FOR
SELECT * FROM INDEX_TB WHERE SEX = '男';
SELECT * FROM TABLE(DBMS_XPLAN.display);

如上图所示，该语句并没有走我创建的复合索引（fh_ind），如果我将where条件换成id则不一样，测试如下显示：

EXPLAIN PLAN FOR
SELECT * FROM INDEX_TB WHERE ID = 888;
SELECT * FROM TABLE(DBMS_XPLAN.display);

示例6：函数索引

已知在示例2中我已经创建了普通索引，但是，不管什么索引，当我们在索引列上使用函数时，索引就会失效。测试如下：

EXPLAIN PLAN FOR
SELECT * FROM INDEX_TB WHERE ASCII(NAME) = 48615;
SELECT * FROM TABLE(DBMS_XPLAN.display);

如上图所示，我在示例2中为name列创建的普通索引pt_ind没有被使用。其原因是因为我在索引列上使用了函数。但是在实际工作中，我们有时候必须要使用函数，该怎么办呢？我们可以为其创建一个函数索引。如下：

CREATE INDEX HS_ind ON INDEX_TB(ASCII(NAME)); -- 为name列添加函数索引
-- 再次执行上一条代码
EXPLAIN PLAN FOR
SELECT * FROM INDEX_TB WHERE ASCII(NAME) = 48615;
SELECT * FROM TABLE(DBMS_XPLAN.display);

如上图所示，当我再次查看执行计划时，发现该语句在执行是使用了索引。

说白了，函数索引就是基于对表中列计算后的结果创建索引。