索引
在oracle中，索引是除表之外另一个重要的模式对象，索引主要用于提高表查询速度。索引与表一样，有独立的数据段存储，并且可以通过设置储存的参数，控制索引段的盘区分配方式。索引可以由用户显示创建，也可以有oracle自动创建。
Ø
Oracle数据库的体系结构
1.
物理存储结构
2.
逻辑存储结构
3.
内存结构
4.
实力进程结构
一、
了解索引
常用的索引类型有B树索引、反向键索引、位图索引、基于函数的索引、簇索引、全局和局部索引等。其中，簇索引专门用于簇的索引
Oracle支持多种类型的索引，可以按列的多少、索引值是否唯一和索引数据的组织形式对索引进行分类，以满足各种表和查询条件的要求。
1.
单列索引和复合索引
一个索引可以由一个或多个列组成。
基于单个列所创建的索引称为单列索引，基于两列或多列所创建的索引称为多列索引。
2.
B树索引
B树索引是Oracle数据库中最常用的一种索引。当使用CREATE INDEX语句创建索引时，默认创建的索引就是B树索引。
B树索引是按B树结构或使用B树算法组织并存储索引数据的。B树索引就是一棵二叉树，它由根、分支节点和叶子节点三部分构成。其中，根包含指向分支节点的信息，分支节点包含指向下级分支节点和指向叶子节点的信息，叶子节点包含索引列和指向表中每个匹配行的ROWID值。叶子节点是一个双向链表，因此可以对其进行任何方面的范围扫描。
B树索引中所有叶子节点都具有相同的深度，所以不管查询条件如何，查询速度基本相同。另外，B树索引能够适应各种查询条件，包括精确查询、模糊查询和比较查询。
B树索引的分类如下所示：
1)
Unique：唯一索引，其索引值不能重复，但允许为NULL。在创建索引时指定UNIQUE关键字可以创建唯一索引。当建立“主键约束条件”时Oracle会自动在相应列上建立唯一索引，主键列不允许为NULL。
2)
Non-Unique：非唯一索引，其索引值可以重复，允许为NULL。默认情况下，Oracle创建的索引是非唯一索引。
3)
Reverse Key：反向关键字索引。通过在创建索引时指定“REVERSE”关键字，可以创建反向关键字索引，被索引的每个数据列中的数据都是反向存储的，但仍然保持原来数据列的次序。
对建立了B树索引的表进行查询时，只需要读取4次数据：
1)
第一次读取根节点
2)
第二次读取分支节点
3)
第三次读取叶子节点
4)
最后一次用于从表中获取相关的数据
3.
位图索引
在B树索引中，保存的是经排序过的索引列及其对应的ROWID值。但是对于一些基数很小的列来说，这样做并不能显著提高查询的速度。所谓基数，是指某个列可能拥有的不重复值的个数。比如性别列的基数为2（只有男和女）。
因此，对于象性别、婚姻状况、政治面貌等只具有几个固定值的字段而言，如果要建立索引，应该建立位图索引，而不是默认的B树索引。
当创建位图索引时，Oracle会扫描整张表，并为索引列的每个取值建立一个位图。在这个位图中，对表中每一行使用一位（bit，取值为0或1）来表示该行是否包含该位图的索引列的取值，如果为1，则表示该位对应的ROWID所在的记录包含该位图索引列值。最后通过位图索引中的映射函数完成位到行的ROWID的转换。
4.
反向键索引
在oracle中，系统会自动为表的主键列建立索引，这个默认的索引是普通的B树索引。对于主键值是按顺序添加的，默认的B树索引并不理想，反向键是一种特殊的类型的B树索引，在索引基于含有序数的列时非常有用。
反向键索引的工作原理：在存储结构方面，它与常规的B树索引相同，
然而，如果用户使用序列编号在表中输入新纪录，则反向键索引首先反向每个列键值字节，然后在反向后的新数据上进行索引。
5.
函数索引
前面的索引都是直接对表中的列创建索引，除此之外，Oracle还可以对包含有列的函数或表达式创建索引，这就是函数索引。
当需要经常访问一些函数或表达式时，可以将其存储在索引中，当下次访问时，由于该值已经计算出来了，因此，可以大大提高那些在WHERE子句中包含该函数或表达式的查询操作的速度。
函数索引既可以使用B树索引，也可以使用位图索引，可以根据函数或表达式的结果的基数大小来进行选择，当函数或表达式的结果不确定时采用B树索引，当函数或表达式的结果是固定的几个值时采用位图索引。
下面通过一个例子看看函数索引的用法。在SALES表中，TOPIC列的值如果采用首字母大写的方式存储。
|
ID |
TOPIC |
ISLOOK |
ROWID |
|
T0001 |
Book |
Y |
AAAHagAABAAAMZKAAA |
|
T0203 |
Pen |
Y |
AAAHagAABAAAMZKAAB |
|
T1437 |
Tee |
N |
AAAHagAABAAAMZKAAC |
|
T1682 |
Cup |
Y |
AAAHagAABAAAMZKAAD |
|
T2735 |
Hat |
N |
AAAHagAABAAAMZKAAE |
|
T3412 |
Apple |
N |
AAAHagAABAAAMZKAAF |
|
T4724 |
Wine |
Y |
AAAHagAABAAAMZKAAG |
现在使用下列代码查询：
SELECT * FROM SALES WHERE TOPIC=’TEE’;
将没有结果。现在忽略大小写，将代码修改如下：
SELECT * FROM SALES WHERE UPPER(TOPIC)=’TEE’;
这样可以查到相应的结果，但是，由于不是直接查询TOPIC列，所以，即使在TOPIC列上创建了索引也无法使用。
这时，就可以使用函数索引，创建函数索引的代码如下：
CREATE INDEX
funidx_upper_topic ON SALES(UPPER(TOPIC));
由于函数索引存储了预先计算过的值，因此，查询时不需要对每条记录都再计算一次WHERE条件，从而可以提高查询的速度。
在函数索引中可以使用各种算术运算符、PL/SQL函数和内置SQL函数，如LEN、TRIM、SUBSTR等。这些函数的共同特点是为每行返回独立的结果，因此，象集函数（如SUM、MAX、MIN、AVG等）不能使用。
6.
全局索引和局部索引
通常在对表进行分区时，也会将对应的索引进行分区，但是分区的表也可以有未分区的索引，而未分区的表可以有分区的索引。对索引分区的目的与对表进行分区相同，都是为了更加易于管理和维护。
在oracle中，一共可以为分区表建立3种类型的索引：局部分区索引、全局分区索引、全局非分区索引。
1)
局部分区索引
局部分区索引是为分区表中的各个分区单独地建立分区，各个索引分区之间是相互独立的。
局部分区索引相对比较简单，也比较容易管理
2)
全局分区索引
全局分区管理是对整个分区建立的索引，然后再由oracle对索引进行分区。全局分区索引的各个分区不是相互独立的，索引分区与分区表之间不是简单的一对一的关系
3)
全局非分区索引
全局非分区就是对整个分区表建立索引，但是未对索引进行分区。局部分区索引的管理大部分由oracle自动完成，而全局分区索引的部分管理操作比较特殊，需要DBA进行更多的干预
二、
管理索引的原则
使用索引的目的是为了提高系统的效率，但同时它也会增加系统的负担，进行影响系统的性能，因为系统必须在进行DML操作后维护索引数据。
在新的SQL标准中并不推荐使用索引，而是建议在创建表的时候用主键替代。因此，为了防止使用索引后反而降低系统的性能，应该遵循一些基本的原则。
使用索引应该遵循以下一些基本的原则:
1.
小表不需要建立索引。
2.
对于大表而言，如果经常查询的记录数目少于表中总记录数目的15%时，可以创建索引。这个比例并不绝对，它与全表扫描速度成反比。
3.
对于大部分列值不重复的列可建立索引。
4.
对于基数大的列，适合建立B树索引，而对于基数小的列适合建立位图索引。
5.
对于列中有许多空值，但经常查询所有的非空值记录的列，应该建立索引。
6.
LONG和LONG
RAW列不能创建索引。
备注：
字符类
LONG:可变长的字符串数据，最长2G，LONG具有VARCHAR2列的特性，可以存储长文本一个表中最多一个LONG列
二进制类
LONG RAW:可变长二进制数据，最长2G
LONG使用说明：
1)
LONG 数据类型中存储的是可变长字符串，最大长度限制是2GB。
2)
对于超出一定长度的文本，基本只能用LONG类型来存储，数据字典中很多对象的定义就是用LONG来存储的。
3)
LONG类型主要用于不需要作字符串搜索的长串数据，如果要进行字符搜索就要用varchar2类型。
4)
很多工具，包括SQL*Plus，处理LONG 数据类型都是很困难的。
5)
LONG 数据类型的使用中，要受限于磁盘的大小。
能够操作 LONG 的 SQL 语句：
1)
Select语句
2)
Update语句中的SET语句
3)
Insert语句中的VALUES语句
限制：
1)
一个表中只能包含一个 LONG 类型的列。
2)
不能索引LONG类型列。
3)
不能将含有LONG类型列的表作聚簇。
4)
不能在SQL*Plus中将LONG类型列的数值插入到另一个表格中,如insert into ...select。
5)
不能在SQL*Plus中通过查询其他表的方式来创建LONG类型列,如create
table as select。
6)
不能对LONG类型列加约束条件（NULL、NOT NULL、DEFAULT除外），如：关键字列(PRIMARY KEY)不能是 LONG 数据类型。
7)
LONG类型列不能用在Select的以下子句中：where、group
by、order by，以及带有distinct的select语句中。
8)
LONG类型列不能用于分布查询。
9)
PL/SQL过程块的变量不能定义为LONG类型。
10) LONG类型列不能被SQL函数所改变，如：substr、instr。
SQL*Plus 中操作LONG类型列：
1)
set long n// n代表n位字符（n为大于零的整数）
2)
col 列名 format An
//An表示将此列的数据显示宽度限制为不超过n位。
7.
经常进行连接查询的列上应该创建索引。
8.
在使用CREATE INDEX语句创建查询时，将最常查询的列放在其他列前面。
9.
维护索引需要开销，特别是对表进行插入和删除操作时，因此要限制表中索引的数量。对于主要用于读的表，则索引多就有好处，但是，一个表如果经常被更改，则索引应少点。
10. 在表中插入数据后创建索引。如果在装载数据之前创建了索引，那么当插入每行时，Oracle都必须更改每个索引。
三、
创建索引
创建索引使用CREATE INDEX语句。
在用户自己的方案中创建索引，需要CREATE INDEX系统权限，在其他用户的方案中创建索引则需要CREATE ANY INDEX系统权限。另外，索引需要存储空间，因此，还必须在保存索引的表空间中有配额，或者具有UNLIMITED TABLESPACE系统权限。
CREATE INDEX语句的语法如下：
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在创建索引时对表中记录进行排序。如果表中数据已经是按该索引顺序排列的，则可以使用该选项。
备注：
可以在一个表上创建多个索引，但这些索引的列的组合必须不同。如下列的索引是合法的。
CREATE INDEX idx1 ON SALES(ID,TOPIC)
CREATE INDEX idx2 ON SALES(TOPIC,ID)
其中，idx1和idx2索引都使用了ID和TOPIC列，但由于顺序不同，因此是合法的。
1.
创建B树索引
B树索引是Oracle默认的索引类型，当在WHERE子句中经常要引用某些列时，应该在这些列上创建索引。例如，经常需要在SALES表的TOPIC列上按标题查询，就可以在TOPIC列上建立B树索引。
如果用户要在自己的模式中创建索引，则必须有create Index系统权限，如果要在其他用户模式下创建索引，则必须有create
any index
默认情况下，当用户为表定义一个主键时，系统将自动为该列创建一个B树索引，因此用户不能再为主键创建B树索引
//在“商品信息”表的“供应商编号”列创建一个名为MERCH_PURVEY_INDEX的索引
Create index merch_purvey_index on 商品信息（供应商编号）tablespace users;
On关键字后指定索引基于的表和列名
Tablespace指定存储索引的表空间storage子句为索引指定存储参数
//为“商品信息”表的“商品名称”列创建唯一的索引：
Create unique index merch_uniqne_index on 商品信息（商品名称）;
//复合索引
Create unique index merch_uniqne_index on 商品信息（供应商编号，产地）;
Create unique index merch_uniqne_index on 商品信息（产地，供应商编号）;
备注：顺序不同索引不同
//对索引列进行压缩
Create unique
index merch_uniqne_index on 商品信息（供应商编号，产地）compress 2;