在表上建立索引,索引对于表,就像目录对于书一样,有了索引可以直接定位到表中的数据位置,大大的加快查找速度。索引可以减少磁盘IO,在逻辑上和物理上都独立于表的数据,索引可以存放在任何磁盘上面,此外Oracle会自动维护索引。
1.唯一索引、组合索引、反向键索引、函数索引、位图索引
索引可以分为B树(B-Tree)索引和位图(Bitmap)索引,B树索引又分为唯一索引、组合索引、反向键索引、基于函数的索引。学过数据结构的都知道B树,所有叶子节点都在同一个深度。每个节点块可以包含关键字: 比如某个节点块包含了关键字有 100,200 500,那么他的叶子节点就有四个,数据范围分别是 ...~100、100~200, 200~500 ,500~....。
创建索引最简单的语法: CREATE INDEX [索引名] ON [表名](表字段名)。 表名 user_indexes 、all_indexes可以看索引信息。表名user_ind_columns、all_ind_columns 查看索引的关联信息。
索引碎片,指的是当索引关联的数据如果被删除,而索引信息还存在,那么这个数据的索引就是无用的索引称为索引碎片,无用的信息占用内存这样会影响到Oracle性能。减少碎片的方法之一就是重建索引。
分析索引语句: ANALYZE INDEX [索引名] VALIDATE STRUCTURE; 然后查询 INDEX_STATS查看pct_used 的索引碎片的多少。
首先先创建一个1300000个数据的表。来看看索引的效果。
--drop table salary_tbl;
create table salary_tbl(
employer_nm varchar(20),
department varchar(20) not null,
salary number not null,
leader_nm varchar(20)
);
truncate table salary_tbl;
begin
for i in 1..1300000
loop
insert into salary_tbl values('雇佣者'||i,'部门'||Mod(i,50),100+sqrt(i),'雇佣者'||Mod(i,20));
if Mod(i,1000)=0 then
commit;
end if;
end loop;
end;
/
commit;
新建索引,并查询信息:
create index idx1 on salary_tbl(DEPARTMENT);
ANALYZE INDEX IDX1 VALIDATE STRUCTURE; --分析索引
select s.name,s.pct_used,s.blocks from INDEX_STATS s ;
此时是没有索引碎片的,因为指定的PCT_USED 最大值就是90.
删除数据后再分析索引,查看索引信息:
此时的PCT_USED 使用率下降了,这就产生了索引碎片。想使使用率上升,其中一个方法就是重建索引:
ALTER INDEX IDX1 REBUILD; -- ALTER INDEX [索引名] REBUILD
ANALYZE INDEX IDX1 VALIDATE STRUCTURE;
select s.name,s.pct_used,s.blocks from INDEX_STATS s ;
重建索引后,使用率又上去了。
唯一索引:确保索引关联的列上没有重复值就叫做唯一索引,create unique index [索引名] on [表名](列名); unique表示独一无二,不能重复。索引关联的字段值不能重复,否则会报错,但是关联字段允许有多个空值。
组合索引: 索引可以关联多个字段,关联多个字段的索引叫组合索引。create index [索引名] on [表名](列名1,列名2...),这样可以加快查询的语句是 select * from [table] where 列名1 = .. and 列名2... 这样的语句。
反向键索引:Oracle为了平均分散数据到各索引叶子节点上,避免某些叶子节点数据量太大而有些叶子节点过小问题,提供了反向键索引。键值逆序排序,和一般索引相比如果键值连续在一起的,这表示都放在同一个数据块中,当要存取两个相邻的键值时,将会造成同时去抢同一个数据块。反向键值的话,两个相邻的键值不会放到同一个数据块中,就不会发生互抢了,但是由于键值的关系已经没有了,叶子快没有双向连接的功能。所以,SQL语法条件是 = 时 ,才能产生最大的作用。
create index [索引名] on [表名](列名) REVERES;
例如关联的字段值是 1001 1002, 3211, 3212 ,3213 ,那么反向键值就是 1001 2001 1123 2123 3123,用该值来建立索引。
函数索引:关联的键值是通过函数计算的值,在where 函数(字段的时候)= ... 可以加快查询速度。创建时需要有QUERY REWRITE权限,并且不能在 LOB类型的列上创建
create index [索引名] on [表名](函数名(参数)) ;
位图索引 (Bitmap索引) : Bitmap索引不属于B-Tree索引,两者有很大的区别。B-Tree索引键值后面跟着ROWID,而BitMap索引,键值后面跟着串位(String of bit),串位也就是'0'和‘1’组成的字符串。位图索引能够有效地节省索引空间,它适合创建在低基数列上,所谓低基数列指的就是取值非常少的字段。比如 性别=(‘男‘,’女’); 年级=(‘1',‘2’,‘3’) 像这样的。
create bitmap index [索引名] on [表名](列名) ;
B-Tree索引 | Bitmap索引 |
适合在索引键值比较多的时候,如学号、身份证等 | 适用于索引键值比较少的字段,如性别,血型,年级等 |
可以经常执行修改操作 | 执行修改操作的成本较高 |
对 ‘or’ 表达式很没有效率 | 适用于 or 表达式的SQL语法 |
适用于线上交易系统(OTP),经常变化的系统 | 适用于数据仓库,资料量大,但不经常变化的系统 |
重建索引完整语法: ALTER INDEX index_name REBULID [Online] [NOLOGING] [COMPUTE STATISTICS]
Online:在线重建索引,在重建索引的过程中,还允许其他用户进行增删改操作,如果要建立的索引数量过大,那么建立索引的时间会较长,在一般情况下,这段时间禁止对索引进行增删改,可能会对业务造成影响。设成Online就可以避免这个。
NOLOGGING:表示在重建过程中产生最少的重做条目 Redo Entry.
COMPUTE STATISTICS: 重建过程中就生成了优化器所需的统计信息,不需要重建之后再手动的来analyze 或者 dbms_stats.
2.索引的分区:
索引的分区有三种类型:局部分区索引、全局分区索引、全局非分区索引,分区索引可以在user_indexes 、user_ind_partitions表里查找。
局部分区索引:在分区表上创建的索引,索引的分区范围和表一致。create index [索引名] on [表名](字段) local ;表怎么分区, 局部分区索引就怎样分区。
全局分区索引:create index [索引名] on [表名](字段) global Partition by .......:手动给索引进行自定义分区。表的分区状况和索引分区没有关系,索引的分配在哪个区上是自定义的。
全局非分区索引:在分区表上创建的全局普通索引,索引不进行分区。create index [索引名] on [表名](字段) global;