ORACLE学习笔记：索引

索引简介：

　　索引与表一样，也属于段（segment）的一种。里面存放了用户的数据，跟表一样需要占用磁盘空间。索引是一种允许直接访问数据表中某一数据行的树型结构，为了提高查询效率而引入，是一个独立于表的对象，可以存放在与表不同的表空间中。索引记录中存有索引关键字和指向表中数据的指针（地址）。对索引进行的I/O操作比对表进行操作要少很多。索引一旦 被建立就将被Oracle系统自动维护，查询语句中不用指定使用哪个索引.

  　　从物理上说，索引通常可以分为：分区和非分区索引、常规B树索引、位图（bitmap）索引、翻转（reverse）索引、基于函数的索引、局部索引、全局索引等。其中，B树索引属于最常见的索引。

　　索引是表与视图关联的一种树状结构，通过该结构可以快速访问表中的数据。索引需要占用额外的存储空间来保存。索引包函表或者视图中的一列或者多列生成的建机器对应的记录的物理记录号。(ROWID) ，物理所引记录号是表中数据库行的唯一标识，然虽然不能直接指示出行的物理位置，但是可以用来定位行。

 由于索引占用的存储空间远小于表占用的空间，在系统中通过索引进行数据检索时，可以将索引调入内存，通过索引对记录进行定位，可大大减少磁盘I/O操作的次数，提高检索的效率。

　　创建索引的好处：创建索引后，可以保证每条记录的唯一性，可以提高检索数据的速度；多表查询是，可以加快表之间的链接，有效减少分组和排序的时间等。

　　创建索引的坏处：创建和维护索引需要占用额外的时间和空间；对表中的数据进行DML操作时，也需要动态的维护索引。

 

建立索引的目的是为了提高查询效率的，但建立的索引过多，会影响插入、删除数据的速度，因为我们修改的表数据，索引也要跟着修改

 

创建索引：

　　创建索引的方法有两种，一是在SQL*PLUS中使用CREATE INDEX 语句来进行创建，二是在OEM中通过交互界面来进行创建

 在使用CREATE INDEX 语句创建索引时，如果在自己的方案中创建，则需要有CREATE INDEX 的权限；如果在其他用户的方案中创建索引，则需要有CREATE ANY INDEX 系统权限。

1.使用SQL*PLUS 创建索引

　　CREATE [UNIQYE] INDEX [方案名] 索引名

　　ON [方案名] 表名 （<列名>  [ASC  | DESC  ]    [   <列名> [ ASC | DESC ]...... ]）

参数说明：UNIQUE|BITMAP :UNIQUE 表示创建唯一索引。要求创建索引的表达式或字段必须唯一，不能重复 。 BITMAP表示创建位图索引，省略这两个关键字时，默认创建的索引是可以重复的B树索引。

　　　　[方案名] 表名：该子句指出创建索引的表。省略方案名则在当前方案的指定表上创建索引

　　　　[

　　　　创建索引注意事项：

　　　　1.如果一个列已经包含了索引，则无法在该列上再创建索引。

　　　　2.在创建索引和修改表的主键时，将自动基于主键列创建唯一索引。

　　　　3.索引应该建立在WHERE子句频繁引用的列上。

　　　　4.数据量比较小的表一般不创建索引；

　　　　5.将表和索引部署在相同的表空间，则可以简化表空间的管理，将表和表空间部署到不通的表空间，可以提高系统查询的性能。

　　　　6.在多表的链接的情况下，在连接的列上建立索引

　　　　]

2.什么时候用索引

答：1）表数据比较多的时候（如果表数据太少的话，则不用建立索引）

    2）表字段经常使用查询，不经常频繁的更新，删除。

3）表字段重复的数据没那么多的时候

4）作为where 后查询条件的字段的时候

5）在经常需要搜索的列上，可以加快查询的速度

6）在作为主键的列上，这些列主要是一些外键，可以加快链接的速度

7）在经常需要排序的列上创建索引，因为列已经排序，这样查询可以利用索引的排序，加快排序查询时间。

 

创建索引：

　　　　CRATE INDEX BKindex ON BOOK (BOOK);

唯一索引

　　　　CRATE UNIQUE INDEX BKindex ON BOOK (BOOK  DESC);

 组合索引

　　即同时包含两个或两个以上列的索引

　　1、 何时创建：当两个或多个列经常一起出现在where条件中时，则在这些列上同时创建组合索引

　　2、 组合索引中列的顺序是任意的，也无需相邻。但是建议将最频繁访问的列放在列表的最前面

　　　　create index indx_t on t(object_type,object_name);

 位图索引

1、 何时创建：

列中有非常多的重复的值时候。例如某列保存了 “性别”信息。

Where 条件中包含了很多OR操作符。

较少的update操作，因为要相应的跟新所有的bitmap

2、 结构：位图索引使用位图作为键值，对于表中的每一数据行位图包含了TRUE(1)、FALSE(0)、或NULL值。

3、 优点：位图以一种压缩格式存放，因此占用的磁盘空间比标准索引要小得多

4、 语法：CREATE BITMAP INDEX index ON table (column[, column]...);

5、 掩饰：

create table bitmaptable as select * from indextable where owner in('SYS','PUBLIC');

 基于函数的索引

   1、 何时创建：在WHERE条件语句中包含函数或者表达式时

2、 函数包括：算数表达式、PL/SQL函数、程序包函数、SQL函数、用户自定义函数。

3、 语法：CREATE INDEX index ON table (FUNCTION(column));

 

 

6) 反向索引

反向索引作为B-tree索引的一个分支，主要是在创建索引时，针对索引列的索引键值进行字节反转，进而实现分散存放到不同叶子节点块的目的。

格式： CREATE INDEX 索引名 ON 表名 (列名)；

 CREATE INDEX idx_of_imsi ON uim_auth_file(imsi) ;

 

4.索引的好处

答：

    1）建立索引可以加快表查询的速度

2)可以加速表跟表之间的链接

3）减少读写(I/O)操作

4）利用索引的唯一性来控制记录的唯一性

5）降低查询中分组和排序的时间

 

 

那些原因可能导致不走索引

 

答：

 

    1.）在包含空值（null）的table列上建立索引

 

2.）where 子句中使用不等条件

 

<>   !=   NOT COL >= ?  NOT COL <= ?

 

例如：select sex from student where age <> '18';

 

1. ) like '%_' 百分号在前

 

 例如： select sex from student  where age like '%2';

 

4) not in,not exist

 

例如：select sex from student where age not in ('18');

 

5) 比较不匹配类型的数据类型，比如age为数字，但是加了引号

 

 例如：select sex from student where age > '18';

 

  age 为数字时

 

1）where 子句上没有使用索引中的列

 

2）Where 子句中使用 is null 或者 is not null   使用 判断空或者非空的条件会导致索引失效

 

例如：SELECT * FROM student WHERE COMM IS NULL;

 

 

 

3）Where 子句中使用函数 如果没有使用基于函数的索引，那么where字句中存在索引的列使用函数时，会使优化器忽略这些索引

 

例如：SELECT * FROM student WHERE TRUNC(BIRTHDAY) = '2019-04-01'

 

 

 

4）对索引进行位运算导致索引失效

 

例如：SELECT * FROM student WHERE age - 1 = 23

 

 

 

5）表中的数据量的多少也会影响索引的使用

 

查询的数量是大表的大部分，应该是30%以上，如果查询的数量超过大表数量的30% 就不走索引了

 

 

 

删除索引

　　　　DROP INDEX BOOK.BKindex