一、索引的概念
索引类似于查字典，通过拼音、笔画，只要几步就能查到所要的字。数据库里，通过索引，只要少量的读，就能找到所需的记录。
使用索引需要找到一个平衡点，索引太多会影响DML语句的速度，因为要写索引表。索引太少又不能提高查询速度。
二、B*树索引
1、B*树索引：我们所说的“传统”的索引。create index 创建的索引默认是B*索引。
B*数索引类似于一颗二叉树，但并不是二叉树。B*树中的“B”不代表二叉（binary），而代表平衡（balanced）。
B*树的特点之一是：所有叶子块都应该在树的同一层上。
这一层也称为索引的高度（height），这说明所有从索引的根块到叶子块的遍历都会访问同样数目的块。也就是说，对于形如“SELECT INDEXED_COL FROM T WHERE INDEXED_COL = :X”的索引，要到达叶子块来获取第一行，不论使用的:X值是什么，都会执行同样数目的I/O。
换句话说，索引是高度平衡的（height balanced）。大多数B*树索引的高度都是2或者3，即使索引中有数百万行记录也是如此。这说明，一般来讲，在索引中找到一个键只需要执行2或3次I/O。
2、反向键索引
反向键索引为了缓解B*树索引中，数据集中在某些叶子块集中的问题，右侧索引问题。
通常，使用数据时（常见于批量插入操作）都比较集中在一个连续的数据范围内，那么在使用正常的索引时就很容易发生索引叶子块过热的现象，严重时将会导致系统性能下降。
使用反向索引可以分散索引键值在索引叶子块中的分布，减少热点块，提高系统性能。
语句：
create table t (x int);
create index idx_t on t(x) reverse;
加上“reverse”关键字，就创建了反向键索引。
3、降序索引
降序索引用以扩展B*树索引的功能，它允许在索引中以降序（从大到小的顺序）存储一列。
create index idx_t on t(x desc);
加上“desc”关键字，就创建了降序索引。
三、位图索引
位图索引用二进制的0、1来构建索引，在进行or操作时非常快。创建位图索引时，oracle会对整个表进行扫描，并为索引列的每个取值建立一个位图。（取值相同的列位图也相同）
当表有大量数据并且其key columns的基数（关键字不同的值）较少时，查询时常使用or等组合的where子句，对索引关键字修改较少，此时宜用位图索引。
当位图索引中key发生改变时（update），将会引起bitmap的改变，这就会对相同bitmap的所有行加锁。因为位图索引，改一条记录，会锁住所有相同值的记录，所以位图索引特别不适用于OLTP系统，如果系统中的数据会由多个并发会话频繁地更新，这种系统也不适用位图索引。
insert和普通表一样，不会锁相同值的记录，会加一个事务锁，级别比较低。
语句：
create bitmap index idx_t on t(x);
如果行中，某列不同项的个数除以行数，是一个很小的数，可能用位图索引。
读频繁的系统适合位图索引，写频繁的系统不适合位图索引。
四、函数索引
如果在某个列上频繁的使用函数，可以使用基于函数的索引。相当于对函数计算结果进行索引，查询时oracle计算出获得条件中的函数结果后，不用对每行都计算函数，加快查询速度
oracle索引，索引的建立、修改、删除
索引
索引是关系数据库中用于存放每一条记录的一种对象，主要目的是加快数据的读取速度和完整性检查。建立索引是一项技术性要求高的工作。一般在数据库设计阶段的与数据库结构一道考虑。应用系统的性能直接与索引的合理直接有关。下面给出建立索引的方法和要点。
§3.5.1 建立索引
1. CREATE INDEX命令语法:
CREATE INDEX
CREATE [unique] INDEX [user.]index
ON [user.]table (column [ASC | DESC] [,column
[ASC | DESC] ] ... )
[CLUSTER [scheam.]cluster]
[INITRANS n]
[MAXTRANS n]
[PCTFREE n]
[STORAGE storage]
[TABLESPACE tablespace]
[NO SORT]
Advanced
其中：
schema ORACLE模式，缺省即为当前帐户
index 索引名
table 创建索引的基表名
column 基表中的列名，一个索引最多有16列，long列、long raw
列不能建索引列
DESC、ASC 缺省为ASC即升序排序
CLUSTER 指定一个聚簇（Hash cluster不能建索引）
INITRANS、MAXTRANS 指定初始和最大事务入口数
Tablespace 表空间名
STORAGE 存储参数，同create table 中的storage.
PCTFREE 索引数据块空闲空间的百分比(不能指定pctused)
NOSORT 不（能）排序（存储时就已按升序，所以指出不再排序）
2.建立索引的目的：
建立索引的目的是：
l 提高对表的查询速度；
l 对表有关列的取值进行检查。
但是，对表进行insert,update,delete处理时，由于要表的存放位置记录到索引项中而会降低一些速度。
注意：一个基表不能建太多的索引；
空值不能被索引
只有唯一索引才真正提高速度,一般的索引只能提高30%左右。
Create index ename_in on emp (ename,sal);
例1：商场的商品库表结构如下，我们为该表的商品代码建立一唯一索引，使得在前台POS收款时提高查询速度。
Create table good(good_id number(8) not null,/* 商品条码 */
Good_desc varchar2(40), /* 商品描述 */
Unit_cost number(10,2) /* 单价 */
Good_unit varchar2(6), /* 单位 */
Unit_pric number(10,2) /* 零售价 */
)；
注：提高查询速度的方法还有在表上建立主键，主键与唯一索引的差别
在于唯一索引可以空，主键为非空，比如：
Create table good(good_id number(8) primary key,
Good_desc Varchar2(40),
Unit_cost number(10,2),
Good_unit char(6),
Unit_pric number(10,2)
);
§3.5.2 修改索引
对于较早的Oracle版本，修改索引的主要任务是修改已存在索引的存储参数适应增长的需要或者重新建立索引。而Oracle8I及以后的版本，可以对无用的空间进行合并。这些的工作主要是由管理员来完成。
简要语法结构如下,更详细的语法图见电子文档《Oracle8i Reference 》 中的 Alter index.
ALTER [UNIQUE] INDEX [user.]index
[INITRANS n]
[MAXTRANS n]
REBUILD
[STORAGE n]
其中：
REBUILD 是 根据原来的索引结构重新建立索引，实际是删除原来的索引后再重新建立。
提示：DBA经常用 REBUILD 来重建索引可以减少硬盘碎片和提高应用系统的性能。
例：
alter index pk_detno rebuild storage(initial 1m next 512k);
ALTER INDEX emp_ix REBUILD REVERSE;
Oracle8i 的新功能可以对索引的无用空间进行合并，它由下面命令完成：
ALTER INDEX . . . COALESCE;
例如：
ALTER INDEX ename_idx COALESCE;
§3.5.3 删除索引
当不需要时可以将索引删除以释放出硬盘空间。命令如下：
DROP INDEX [schema.]indexname
例如：
sql> drop index pk_dept;
注：当表结构被删除时，有其相关的所有索引也随之被删除。
§3.6 新索引类型
Oracle8i为了性能优化而提供新的创建新类型的索引。这些新索引在下面介绍：
§3.6.1 基于函数的索引
基于函数的索引就是存储预先计算好的函数或表达式值的索引。这些表达式可以是算术运算表达式、SQL或PL/SQL函数、C调用等。值得注意的是，一般用户要创建函数索引，必须具有GLOBAL QUERY REWRITE和CREATE ANY INDEX权限。否则不能创建函数索引，看下面例子：
例1：为EMP表的ename 列建立大写转换函数的索引idx ：
CREATE INDEX idx ON emp ( UPPER(ename));
这样就可以在查询语句来使用：
SELECT * FROM EMP WHERE UPPER(ename) LIKE ‘JOH%’;
例2：为emp 的工资和奖金之和建立索引：
1) 查看emp 的表结构：
SQL> desc emp
Name Null? Type
----------------------------------------- -------- ------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
2)没有授权就创建函数索引的提示：
SQL> create index sal_comm on emp ( (sal+comm)*12, sal,comm)
2 tablespace users storage(initial 64k next 64k pctincrease 0);
create index sal_comm on emp ( (sal+comm)*12, sal,comm)
*
ERROR at line 1:
ORA-01031: insufficient privileges
3) 连接到DBA帐户并授权：
SQL> connect sys/sys@ora816
Connected.
SQL> grant GLOBAL QUERY REWRITE to scott;
Grant succeeded.
SQL> grant CREATE ANY INDEX to scott;
Grant succeeded.
4）在连接到scott帐户，创建基于函数的索引：
SQL> connect scott/tiger@ora816
Connected.
SQL> create index sal_comm on emp ( (sal+comm)*12, sal,comm)
2 tablespace users storage(initial 64k next 64k pctincrease 0);
Index created.
1)在查询中使用函数索引：
SQL> select ename,sal,comm from emp where (sal+comm)*12 >5000;
ENAME SAL COMM
---------------------- ---------------- ----------------
ALLEN 1600 300
WARD 1250 500
MARTIN 1250 1400
TURNER 1500 0
赵元杰 1234.5 54321
§3.6.2 反向键索引
反向键索引通过反向键保持索引的所有叶子键上的插入分布。有时，可用反向键索引来避免不平衡的索引。对于反向键索引可以进行下面操作：
l 通过在ALTER INDEX命令后加REBUILD NOREVERSE或REBUILD REVERSE子句来使索引边为反向键索引或普通索引；
l 采用范围扫描的查询不能使用反向键索引；
l 位图索引不能反向；
l 索引编排表不能反向。
例1：创建一个反向键索引：
CREATE INDEX i ON t (a,b,c) REVERSE;
例2：使一个索引变为反向键索引：
ALTER INDEX i REBUILD NOREVERSE;
§3.6.3 索引组织表
与普通的索引不一样，索引组织表（Index_Organized Table）是根据表来存储数据，即将索引和表存储在一起。这样的索引结构表（Index_organized table—IOT）的特点是：对表数据的改变，如插入一新行、删除某行都引起索引的更新。
索引组织表就象带一个或多个列所有的普通表一样，但索引组织表在B-树索引结构的叶节点上存储行数据。通过在索引结构中存储数据，索引组织表减少了总的存储量，此外，索引组织表也改善访问性能。
由于表中的行与B_树索引存放在一起，每个行都没有ROWID，而是用主键来标识。但是Oracle会“猜”这些行的位置并为每个行分配逻辑的ROWID。此外，你可以为这样的表建立第二个索引。
创建索引结构表也是用CREATE TABLE 命令加ORGANIZATION INDEX关键字来实现。但是，这样的表在创建完后，你还必须为该表建立一个主键。
例子：
CREATE TABLE IOT_EXPAMPLE
(
Pk_col1 number(4),
Pk_col2 varchar2(10),
Non_pk_col1 varchar2(40),
Non_pk_col2 date,
CONSTRAINT pk_iot PRIMARY KEY
( pk_col1, pk_col2)
)
ORGANIZATION INDEX
TABLESPACE INDEX
STORAGE( INITIAL 1M NEXT 512K PCTINCREASE 0 );
索引组织表有些限制：
l 不能使用唯一约束；
l 必须具有一个主键；
l 不能建立簇；
l 不能包含LONG类型列；
l 不支持分布和复制。
提示：如果建立了索引组织表，则会在DBA_TABLES中的IOT_TYPE和IOT_NAME列上记录有索引组织表的信息。
例1．修改索引结构表 docindex 的索引段的INITRANS参数：
ALTER TABLE docindex INITRANS 4;
例2．下面语句加一个的溢出数据段到索引组织表 docindex中：
ALTER TABLE docindex ADD OVERFLOW;
例3．下面语句为索引组织表 docindex的溢出数据段修改INITRANS参数：
ALTER TABLE docindex OVERFLOW INITRANS 4;
============================================================================================================
适当的使用索引可以提高数据检索速度，可以给经常需要进行查询的字段创建索引
oracle的索引分为5种:唯一索引，组合索引，反向键索引，位图索引，基于函数的索引
创建索引的标准语法:
CREATE INDEX 索引名 ON 表名 (列名)
TABLESPACE 表空间名;
创建唯一索引:
CREATE unique INDEX 索引名 ON 表名 (列名)
TABLESPACE 表空间名;
创建组合索引:
CREATE INDEX 索引名 ON 表名 (列名1,列名2)
TABLESPACE 表空间名;
创建反向键索引:
CREATE INDEX 索引名 ON 表名 (列名) reverse
TABLESPACE 表空间名;
查看文章
oracle 查看索引类别以及查看索引字段被引用的字段方法2008年01月04日 星期五 13:20查看索引个数和类别
select * from user_indexes where table='表名' ;
查看索引被索引的字段
SQL>select * from user_ind_columns where index_name=upper('&index_name');
PS:
查看某表的约束条件
SQL>select constraint_name, constraint_type,search_condition, r_constraint_name
from user_constraints where table_name = upper('&table_name');
SQL>select c.constraint_name,c.constraint_type,cc.column_name
from user_constraints c,user_cons_columns cc
where c.owner = upper('&table_owner') and c.table_name = upper('&table_name')
and c.owner = cc.owner and c.constraint_name = cc.constraint_name
order by cc.position;
查看视图的名称
SQL>select view_name from user_views;