Oracle学习笔记day08——索引

一、概述

索引是一个单独的、物理的数据结构，在这个数据结构中包括表中一列或若干列的值以及相应的指向表中物理标识这些值的数据页的逻辑指针的集合。
索引是建立在表上的可选对象，设计索引的目的是为了提高查询的速度。但同时索引也会增加系统的负担，进行影响系统的性能。
索引一旦建立后，当在表上进行DML操作时，Oracle会自动维护索引，并决定何时使用索引。
索引的使用对用户是透明的，用户不需要在执行SQL语句时指定使用哪个索引及如何使用索引，也就是说，无论表上是否创建有索引，SQL语句的用法不变。用户在进行操作时，不需要考虑索引的存在，索引只与系统性能相关。

1. 索引的原理

   当在一个没有创建索引的表中查询符合某个条件的记录时，DBMS会顺序地逐条读取每个记录与查询条件进行匹配，这种方式称为全表扫描。全表扫描方式需要遍历整个表，效率很低。

   

2. 索引的类型

   Oracle支持多种类型的索引，可以按列的多少、索引值是否唯一和索引数据的组织形式对索引进行分类，以满足各种表和查询条件的要求。
   ①.单列索引和复合索引[索引列多少]
   ②.B树索引[结构]
   ③.位图索引[结构]
   ④.函数索引[语法]

3. 创建索引

   CREATE [UNIQUE] | [BITMAP] INDEX index_name
   ON table_name([column1 [ASC|DESC],column2
   [ASC|DESC],…] | [express])
   [TABLESPACE tablespace_name]
   [PCTFREE n1]
   [STORAGE (INITIAL n2)]
   [NOLOGGING]
   [NOLINE]
   [NOSORT];
   
   /*
   UNIQUE：表示唯一索引，默认情况下，不使用该选项。
   BITMAP：表示创建位图索引，默认情况下，不使用该选项。
   PCTFREE：指定索引在数据块中的空闲空间。对于经常插入数据的表，应该为表中索引指定一个较大的空闲空间。
   NOLOGGING：表示在创建索引的过程中不产生任何重做日志信息。默认情况下，不使用该选项。
   ONLINE：表示在创建或重建索引时，允许对表进行DML操作。默认情况下，不使用该选项。
   NOSORT：默认情况下，不使用该选项。则Oracle在创建索引时对表中记录进行排序。如果表中数据已经是按该索引顺序排列的，则可以使用该选项。
   */

二、单列索引和复合索引

概述:

一个索引可以由一个或多个列组成。基于单个列所创建的索引称为单列索引，基于两列或多列所创建的索引称为多列索引。

三、B树索引

概述:
B树索引是Oracle数据库中最常用的一种索引。当使用CREATE INDEX语句创建索引时，默认创建的索引就是B树索引。B树索引就是一棵二叉树，它由根、分支节点和叶子节点三部分构成。叶子节点包含索引列和指向表中每个匹配行的ROWID值。叶子节点是一个双向链表，因此可以对其进行任何方面的范围扫描。
B树索引中所有叶子节点都具有相同的深度，所以不管查询条件如何，查询速度基本相同。另外，B树索引能够适应各种查询条件，包括精确查询、模糊查询和比较查询。

例子:

--创建B树索引，属于单列索引
create index idx_emp_job on emp(job);
--创建B树索引，属于复合索引
create index idx_emp_nameorsal on emp(ename,sal);
--创建唯一的B树索引，属于单列索引
create unique index idx_emp_ename on emp(ename);
--创建反向索引
create index index_two on emp(mgr) reverse;
--如果表已存在大量的数据，需要规划索引段
create index idx_emp_nameorsal on emp(ename,sal)
  pctfree 30 tablespace users;
--删除索引
drop index idx_emp_job;
drop index idx_emp_nameorsal;
drop index idx_emp_ename;

四、位图索引

在B树索引中，保存的是经排序过的索引列及其对应的ROWID值。但是对于一些基数很小的列来说，这样做并不能显著提高查询的速度。所谓基数，是指某个列可能拥有的不重复值的个数。比如性别列的基数为2（只有男和女）。
因此，对于象性别、婚姻状况、政治面貌等只具有几个固定值的字段而言，如果要建立索引，应该建立位图索引，而不是默认的B树索引。

例子:

--创建位图索引,单列索引
create bitmap index idx_bm_job on emp(job)
--创建位图索引,复合索引
create bitmap index idx_bm_jobordeptno on emp(job,deptno);
--删除位图索引
drop index idx_bm_job;
drop index idx_bm_jobordeptno;

五、函数索引

函数索引既可以使用B树索引，也可以使用位图索引，可以根据函数或表达式的结果的基数大小来进行选择，当函数或表达式的结果不确定时采用B树索引，当函数或表达式的结果是固定的几个值时采用位图索引。

例子:

--创建B树类型的函数索引
create index idx_fun_emp_hiredate on emp(to_char(hiredate,'yyyy-mm-dd'));
--创建位图类型的函数索引
Create bitmap index idx_fun_emp_job on emp(upper(job));

七、管理索引的原则

使用索引的目的是为了提高系统的效率，但同时它也会增加系统的负担，进行影响系统的性能，因为系统必须在进行DML操作后维护索引数据。
在新的SQL标准中并不推荐使用索引，而是建议在创建表的时候用主键替代。因此，为了防止使用索引后反而降低系统的性能，应该遵循一些基本的原则:
1.小表不需要建立索引。
2.对于大表而言，如果经常查询的记录数目少于表中总记录数目的15%时，可以创建索引。这个比例并不绝对，它与全表扫描速度成反比。
3.对于大部分列值不重复的列可建立索引。
4.对于基数大的列，适合建立B树索引，而对于基数小的列适合建立位图索引。