Oracle索引详解

news/2024/5/19 20:59:01/文章来源:https://blog.csdn.net/Hao1999_/article/details/126636061

索引

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

创建索引语法：

 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表中添加数据

 BEGINFOR V IN 1..10000 LOOPIF MOD(V,2) = 1 THENINSERT 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)));ELSEINSERT 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);