Oracle Indexes（索引）

一、为什么引入索引：提高查询的效率，加快查询速度。

       在计算机的所有操作当中，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操作速度之间做出折中。