一、为什么引入索引:提高查询的效率,加快查询速度。
在计算机的所有操作当中,I/O操作应该是最慢的,使用索引减少了I/O操作就等于加快了查询的速度。
二、建立、查看、删除索引
1、建立索引的两种方式:
A、Oracle系统自动建立:当用户在一个表上建立逐渐(Primary Key)或惟一(UNIQUE)约束时,Oracle系统会自动创建唯一索引(UNIQUE INDEX)。
主键约束:ALTER TABLE tableName ADD CONSTRAINT constraintName PRIMARY KEY(col1,.....);
唯一约束:ALTER TABLE tableName ADD CONSTRAINT constraintName UNIQUE(col1,.....);
B、手工建立:用户在一个表中的一列或多列上用Create Index语句来创建非唯一索引(NONUNIQUE INDEX)。
普通索引:CREATE INDEX indexName ON tableName (列名|表达式[,列名|表达式]...);
Oracle 公司推荐的索引命名方式:表名_列名_对象的类型,比如ORDER_ORDERNO_IDX 。
示例:CREATE INDEX order_orderno_idx ON order (order_no);
CREATE INDEX order_orderno_idx ON order (UPPER(order_no)); --函数索引
CREATE INDEX order_adddte_idx ON order (add_dte-7); --函数索引
注:SELECT * FROM order WHERE add_dte>sysdate-1不走如上索引,因为左侧不是add_dte-7;
若用add_dte建索引,add_dte-7>sysdate-10不走该索引,当add_dte>sysdate-3才会使用。
2、建立索引的几点指导原则(当下列条件之一成立时;反过来说就是什么时候不应该建立索引):
1)表很大而且大多数查询的返回数据量很少(Oracle推荐为小于总函数的百分之二到百分之四),因为如果返回数据量很大的话就不如顺序地扫描整个表了;
2)此列的取值范围很广,一般为随机分布;
3)一列或多列经常出现在WHERE子句或连接条件中;
4)表上的DML操作较少;
5)此列中包含了大量的空值(NULL);
6)此列不经常作为SELECT语句中的某个表达式的一部分;
3、查看索引:
1)查看索引概况:从视图USER(或ALL或DBA)_INDEXES中查询
常见列:INDEX_NAME:索引名称
INDEX_TYPE:索引类型,包含LOB,NORMAL,FUNCTION-BASED NORMAL。
函数索引在视图中列名格式为SYS_NC00000$
TABLE_NAME:建立索引的表的名称
UNIQUENES:唯一索引或者非唯一索引,值为UNIQUE或NONUNIQUE
STATUS:VALID,INVALID
2)查看索引列:从视图USER(或ALL或DBA)_IND_COLUMNS中查询
常见列:INDEX_NAME:索引名称
TABLE_NAME:建立索引的表的名称
COLUMN_NAME:使用索引的列名
COLUMN_POSITION:索引列顺序,单值索引为1,复合索引为1,2,3,....
3)使用执行计划查看索引是否在一个语句中使用:
如果没有找到plan_table,需要执行$ORACLE_HOME\rdbms\admin\utlxplan下的这个脚本。
使用命令:SQL>EXPLAIN PLAN FOR sql语句 ;
最后查询SELECT * FROM plan_table;即可看到执行计划。
一般使用的列:SELECT id,operation,options,object_name,position FROM plan_table;
4)查看索引使用情况:
ALTER INDEX cust_name_idx MONITORING USAGE;(9i以后版本)
这个命令开销很小,之后可以使用V$OBJECT_USAGE视图和USAGE字段(值为YES和NO)来判断索引是否被访问过。得到批量监控语句的方法如下:
SQL>set pages 999
SQL>set heading off
SQL>spool run_mon.sql
SQL>select 'alter index ' || index_name || ' monitoring usage;' from dba_indexes where owner = 'XXX';
SQL>spool off
SQL>@run_mon
对于Oracle9i之前的版本,监控索引使用的唯一方法是执行他们的程序库缓中的所有SQL,然后手工记下所有被使用的索引。
4、删除索引:DROP INDEX 索引名;
该DDL将从数据字典中删除索引的定义,并释放这个索引所占用的磁盘空间。
所需权限:索引的所有者或具有DROP ANY INDEX的系统权限。
其它用处:DBA向数据库导入大量数据时,可以先删除索引,导完后再重建索引。
三、索引的存储结构(待续...)
四、使用索引的注意事项
避免在一个表上创建过多的索引(Over Indexes),尤其对DML操作频繁的表,过多索引会大大降低DML操作的速度。要控制索引的数量,在查询速度与DML操作速度之间做出折中。