Oracle索引（1）概述与创建索引

 

索引是为了提高数据检索效率而创建的一种独立于表的存储结构，由Oracle系统自动进行维护。

索引的概述 

      索引是一种可选的与表或簇相关的数据库对象，能够为数据的查询提供快捷的存储路径，减少数据查询的磁盘I/O,提高数据的检索效率。

      Oracle建议将索引与表分散存储在不同的表空间中。

      在索引结构中，有索引值（其实相当于目录的标题）及其相应记录的物理地址ROWID（相当于内容的页码）。

      在创建索引后。对表进行DML操作时，Oracle自动使用和维护索引。索引自动更新的步骤为

          （1）在向表插入数据时，系统自动在索引的叶子节点中插入语数据对应的索引条目

          （2）删除数据时，系统自动在索引的叶子节点中逻辑上删除相应的条目，当空间没被回收，知道整个节点被删除。

          （3）更新数据时，系统会删除索引叶子节点中相应的索引条目，然后重新插入新的索引条目。

     2.索引的结构：

        在Oracle中索引的结构有两种，分别是平衡树（B_Tree）结构索引和位图（Bitmap）索引。

         平衡树（B_Tree）结构索引：

         位图（Bitmap）索引：也是按照平衡树结构组织的，但是在叶子节点中每个索引值对应一个位图而不是ROWID.

    3.索引的分类：B-树索引：是默认创建的索引

                         B-树聚簇索引：在聚簇字段上创建的索引

                         散列索引：在散列簇的聚簇字段上创建的索引

                        全局索引和分区索引：在分区表上创建的索引

                        位图索引：按位图结构组织的索引，合适索引值取值范围小，重复率高的索引。

                        函数索引：基于包含索引列的函数或表达式创建的索引（索引值为计算后的值）。

                        域索引：仅限于特定的应用。

   4.索引的管理策略：

            因为索引在提高数据检索的同时，会减低对数据进行DML操作的效率。此外，索引作为一个独立的数据库对象，占用一定的存储空间。

             （1）.导入/装载数据后创建索引。

             （2）.选择合适的索引类型。

                 可以从几方面考虑：

                     列的基数的大小：如果列的基数比较大，数据重复率低，应该采用B-树索引；如果列的基数小，重复率高，采用位图索引。

                     空值（null）：B-树索引忽略空值，而位图索引中可以包含索引值为空值的记录信息。因此如果要根据某列与空值相关的查询，可以在该列上创建位图索引。

                     逻辑运算：逻辑运算能直接在位图索引内完成，所以查询条件中包含and or等使用位图索引

                     比较运算：比较运算使用B-树索引

                     存储空间：B-树索引会占用存储空间，而位图索引则占用较少的存储空间。

             （3）.在适当表和适当列上创建索引

                      对于大表而言，如果经常查询的记录数目少于表中所有记录总数的15%，则可以考虑创建索引。

                     为了提高多表查询的效率，应该在各个参与连接的表上创建索引。

                     数据量很小的小表，不需要创建索引

                     不能再LONG LONG RAW LOB列上创建索引。

             （4）合理设置复合索引中列的顺序：

                     应该将频繁使用的列放在其他列的前面。

             （5）限制索引的的数目

             （6）为索引设置合适的PCTFREE参数

             （7）选择存储索引的表空间

                     在默认情况下，索引和表空间存储在同一个表空间中，有力于数据库的维护操作，保证表与索引同时处于联机或脱机状态，具有较高的可用性。反之则可提高系统存取性能。

创建索引

         1.创建非唯一性索引

           在默认情况下，create index语句创建的是非唯一性的B-树索引。

           在employees表的job_id上创建一个非唯一性索引

SQL> create index emp_job_index on employees(job_id)
  2  storage(initial 20k next 20K pctincrease 75)
  3  tablespace users online;

索引已创建。

　　  2.oracle显示执行计划   （1） EXPLAIN PLAN FOR SELECT * FROM a;

                                           (2)  set autotrace on

                                                  select * from a;                                      

QL>SET TIMING ON 　　　　　　　　　　　　--控制显示执行时间统计数据

SQL>SET AUTOTRACE ON EXPLAIN 　　　 　　--这样设置包含执行计划、脚本数据输出，没有统计信息

SQL>执行需要查看执行计划的SQL语句

SQL>SET AUTOTRACE OFF 　　　　　　　　　 --不生成AUTOTRACE报告，这是缺省模式

SQL> SET AUTOTRACE ON 　　　　　　　　　 --这样设置包含执行计划、统计信息、以及脚本数据输出

SQL>执行需要查看执行计划的SQL语句

SQL>SET AUTOTRACE OFF

SQL> SET AUTOTRACE TRACEONLY 　　　　　--这样设置会有执行计划、统计信息，不会有脚本数据输出

SQL>执行需要查看执行计划的SQL语句

SQL>SET AUTOTRACE TRACEONLY STAT     --这样设置只包含有统计信息

SQL>执行需要查看执行计划的SQL语句

　　3.创建唯一索引：

         在departments表中创建departments_name列上的一个唯一性索引

SQL> create unique index dep_dname_unique on departments(departments_name)
  2  storage(initial 10k next 10k pctincrease 75)
  3  tablespace users;

索引已创建。

　　4.在student表上创建的sex上创建一个位图索引

SQL> create bitmap index student_sex_bitmap on student(sex) tablespace users;

索引已创建。

　　5.创建反序索引

      为employees表的manager_id列创建一个反序索引

SQL> create index emp_magid_reverse on employees(manager_id) reverse tablespace users;

索引已创建。

　　6.创建函数索引：为了提高查询条件中使用函数或者表达式的查询语句的执行速度，可以创建函数索引。

      基于employees表的first_name列创建一个函数索引

SQL> create index emp_fname_indx on employees(upper(first_name)) tablespace user
s;

索引已创建。