oracle索引

6.1 索引含义
什么是索引？

索引相当于目录，是一种数据结构。是对某些特定列中的数据进行排序，生成索引表，该列作为WHERE条件时，扫描索引列，根据ROWID快速定位具体记录，提高查询效率。

创建索引的条件：

首先表要大，如果只有几兆，还不如不建，全表扫描说不定更好；
select 操作占大部分的表上创建索引；
where 子句中出现最频繁的列上创建索引；
索引的数量不宜过多，看表的大小和字段数量合适规划；
索引需要维护，它会降低dml语句的速度，所以大量的dml时，可以先删除再创建。
6.2 使用索引
添加索引的语法：

create index 索引名 on 表名(列名);

删除索引的语法：

drop index 索引名;

查看索引：

select * from user_indexes 查询现有的索引

select * from user_ind_columns 可获知索引建立在那些字段上

修改索引：alter

写法都没必要讲，一个表最多就那么几个索引，直接删了重建更好。

例：快速查到某个表的索引建立在那些字段上



6.3 索引类别
主要索引结构：B树索引、位图索引和HASH索引。

B树索引：

含义：使用树状结构，通过分支和叶子节点来存储索引信息

类型：主键、唯一、外键、普通、组合、函数索引

位图索引：

含义：使用二进制位来表示索引信息，适用于具有少量不同取值的列。

Hash索引：

定义：哈希索引适用于等值查询。

6.3.1 主键索引、唯一索引、外键索引
主键索引：在创建表的时候，添加了主键约束，那么就会自动生成主键索引。

alter table emp add constraint pri_empno primary key(empno);

--通过主键约束的创建，来自动生成这个主键索引

唯一索引：在创建表的时候，会根据唯一约束自动的生成唯一索引，唯一索引确保索引列中的值唯一。

外键索引：外键索引用于定义表的主键；外键索引用于加速外键关联查询。

6.3.2 普通索引
普通索引：这个列经常需要被查询，但是这个列又没有什么特点

create index idx_ename on emp(ename);

6.3.3 组合索引
就是普通索引的升级版。

组合索引：和普通索引相同，有多个列需要同时被查询，但是这些列也没有什么特点，那么就放在一起，创建一个组合索引

create index 索引名 on 表名(a,b,c);

create index idx_sal_job on emp(sal,job);

注：

1.在查询时需要使用第一列的列查询，否则组合索引会失效。

2.组合索引中应将最具选择性的列放在最前面。选择性就是列中的值唯一或几乎唯一。这样能有效的减少查询的结果集大小，从而提高查询性能。

6.3.4 函数索引
函数索引：如果你的列，是需要被使用函数计算之后再用来查询，那么计算过程需要的函数要写在索引里面

create index idx_func_hiredate on emp(to_char(hiredate,'yyyy'));

6.3.5 位图索引
位图索引：列的内容是由大量的重复的内容组成的 bitmap

create bitmap index idx_emp_deptno on emp(deptno);

6.3.6 分区表索引
分区表索引分为：本地索引、全局索引。

定义：分区索引就是简单地把一个索引分成多个片断。通过把一个索引分成多个片断，可以访问更小的片断(也更快)，并且可以把这些片断分别存放在不同的磁盘驱动器上(避免I/O问题)。

应用：本地索引更适合用于频繁查询单个分区的情况，而全局索引更适合用于需要跨多个分区进行查询的场景。

1.本地索引（分为有前缀索引和无前缀索引）

create index idx_hash_ename on emp_hash(ename) local;

2.全局索引

create index idx_hash_job on emp_hash(job) global;

--全局索引测试1，索引分区键和表分区键相同,成功
create index ig_test_id on testindex(id) global
partition by range(id)
( partition p1 values less than (5),
partition p2 values less than (30),
partition p3 values less than (maxvalue)

);
--全局索引测试2，索引的分区键和表分区键不相同,成功
create index ighash_test_id on testindex(name) global
partition by hash(name)
( partition p1,
partition p2,
partition p3

);
--全局索引测试3,：报错GLOBAL 分区索引必须加上前缀。（全局索引是指索引键值必须和分区键值相同，也就是所谓的前缀索引）
create index i_test_id on testindex(name) global
partition by range(id)
( partition p1 values less than (5),
partition p2 values less than (30),
partition p3 values less than (maxvalue)

);

区别：

本地索引必须建在分区表上，全局索引和表没有直接的关联；使用场景不同；

6.4 索引失效情况及解决方案
1. 经常需要被修改的列，索引是一直处于失效状态的

重建索引

2. 数据发生了隐性的转换，这里使用了函数。

select * from emp where to_char(hiredate,'yyyy')=1981;

新建针对该列的函数索引

3. 计算的时候，公式放在了等号的左边

select * from emp where sal+1000=4000 and job='MANAGER'；

修改为：

select * from emp where sal=3000 and job='MANAGER';

4. 查询的时候使用不等于

select * from emp where deptno!=20;

修改为：

select * from emp where deptno<20 or deptno>20;

5. 查询的时候使用了like模糊查询

select * from emp where ename like '%LL%';

6. 空值查询

select * from emp where ename is null;

7. 组合索引没有使用第一列的列查询

8. 查询的时候，使用了非函数索引的函数
————————————————

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

原文链接：https://blog.csdn.net/weixin_63021300/article/details/132267190