快速熟悉Oracle索引

一、索引

1.1 什么是索引？

• 一种用于提升查询效率的数据库对象；
• 通过快速定位数据的方法，减少磁盘的输入输出操作；
• 索引信息与表独立存放；
• Oracle数据库自动使用和维护索引。

1.2 索引分类

• 唯一性索引
• 非唯一性索引

1.3创建索引的两种方式

• 自动创建---在定义主键或唯一键约束时系统会自动在相应的字段上创建唯一性索引.
• 手动创建----用户可以在其他列上创建非唯一的索引，以加速查询. 

 

select * from user_indexes /** 查询现有的索引*/ 

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

drop index ' 索引名'  删除索引

二、如何创建索引：

2.1、创建索引的语法

CREATE UNIUQE | BITMAP INDEX <schema>.<index_name>      ON <schema>.<table_name>           (<column_name> | <expression> ASC | DESC,            <column_name> | <expression> ASC | DESC,...)     TABLESPACE <tablespace_name>     STORAGE <storage_settings>     LOGGING | NOLOGGING    COMPUTE STATISTICS     NOCOMPRESS | COMPRESS<nn>     NOSORT | REVERSE     PARTITION | GLOBAL PARTITION<partition_setting>

相关字段的说明

1）UNIQUE | BITMAP：指定UNIQUE为唯一值索引，BITMAP为位图索引，省略为B-Tree索引。

2）<column_name> | <expression> ASC | DESC：可以对多列进行联合索引，当为expression时即“基于函数的索引”

3）TABLESPACE：指定存放索引的表空间(索引和原表不在一个表空间时效率更高)

4）STORAGE：可进一步设置表空间的存储参数

5）LOGGING | NOLOGGING：是否对索引产生重做日志(对大表尽量使用NOLOGGING来减少占用空间并提高效率)

6）COMPUTE STATISTICS：创建新索引时收集统计信息

7）NOCOMPRESS | COMPRESS<nn>：是否使用“键压缩”(使用键压缩可以删除一个键列中出现的重复值)

8）NOSORT | REVERSE：NOSORT表示与表中相同的顺序创建索引，REVERSE表示相反顺序存储索引值

9）PARTITION | NOPARTITION：可以在分区表和未分区表上对创建的索引进行分区

 

看上面的内容的确让人有点头晕，下面是对上面创建索引语法的一个简单示例：

1、创建索引的标准语法    CREATE INDEX 索引名 ON 表名 (列名)TABLESPACE 表空间名;例如：    CREATE INDEX idx_of_imsi ON uim_auth_file(imsi) TABLESPACE users;2、创建唯一索引    CREATE unique INDEX 索引名 ON 表名 (列名)TABLESPACE 表空间名;例如：     CREATE UNIQUE INDEX idx_of_imsi ON uim_auth_file(imsi) TABLESPACE users;3、创建组合索引     CREATE INDEX 索引名 ON 表名 (列名1,列名2)TABLESPACE 表空间名;例如：     CREATE INDEX idx_of_imsi ON uim_auth_file(iccid,imsi) TABLESPACE users;4、创建反向键索引     CREATE INDEX 索引名 ON 表名 (列名) reverseTABLESPACE 表空间名;例如：    CREATE INDEX idx_of_imsi ON uim_auth_file(imsi) reverse TABLESPACE users;

 

三、有关索引的细节问题

3.1 索引的存储

     索引和表都是独立存在的。在为索引指定表空间的时候，不要将被索引的表和索引指向同一个表空间，这样可以避免产生的IO冲突。是Oracle能够并行访问存放在不同硬盘中的索引数据和表数据，更好的提高查询速度。

3.2使用索引的原则

• 查询结果是数据行的5%以下时，使用index查询最好
• where条件中经常用到表的多列时，使用复合索引的效果会好于几个单列索引。因为sql 语句所查询的列全部都出现在复合索引中时，此时由于Oracle只需查询索引块即可获得所有数据，当然比使用单个索引列要快的多。
• 索引利于select ，但对经常insert ，delete尤其update的表，会降低效率，因为需要调整索引的位置和值.
• 在使用复合索引时，where字句中的这个字段必须是复合索引的第一个字段. 例如一个索引时按f1、f2、f3的次序建立的，若where 子句是f2=:var2，因为f2不是索引的第一个字段，无法使用该索引。
• 应尽量熟悉各种操作符对Oracle是否使用的影响：以下这些操作会显示的阻止Oracle使用索引：is null、is not null、not in 、!=、like 以及各种函数等.