Oracle 索引基础
如果一个表中包含有很多条记录，当对表执行查询时，必须将所有的记录一一取出，以便将每一条记录与查询条件进行比较，然后再返回满足条件的记录。这样进行操作的时间开销和I/O开销都是十分巨大的。这时就可以考虑通过建立索引来减少开销。
简单地说，如果将表看作一本书，索引的作用则类似于书中的目录。在没有目录的情况下，要在书中查指定的内容必须阅读全书，而有了目录之后，只需要通过目录就可以快速到包含所需内容的页。类似地，如果要在表中查询指定的记录，在没有索引的情况下，必须遍历整个表，而有了索引之后，只需要在索引中到符合查询条件的索引字段值，就可以通过保存在索引中的ROWID（相当于页码）快速到表中对应的记录。
insert语句字段顺序因此，合理地使用索引可以大大降低磁盘的I/O次数，从而提高数据访问性能。假设EMP表中的数据占用了10000了数据块，如果EMPNO列上不存在索引，那么当执行查询操作时需要执行全表扫描，这种操作的I/O次数为10000次；如果EMPNO列上存在索引（假设索引层次为2），那么执行查询时将使用索引进行扫描（I/O次数为4次）。
索引与表一样，不仅需要在数据字典中保存索引的定义，还需要在表空间中为它分配实际的存储空间。当创建索引时，Oracle会自动在用户的默认表空间中或指定的表空间中创建一个索引段，为索引数据提供存储空间。与创建表的情况类以，在创建索引时也可以为它设置存储参数。
为某个表创建的索引不必和该表保存在同一个表空间中。如果将索引和对应的表分别存放在位于不同硬盘上的不同的表空间中，反而能够提高查询的速度。因为Oracle能够并行读取不同硬盘中的数据，这样的查询可以避免产生I/O冲突。
在创建索引时，Oracle首先对将要建立索引的字段进行排序，然后将排序后的字段值和对应记录的ROWID存储在索引段中。例如，假设使用下面的语句为EMP表中的ENAME字段创建索引：
SQL> create index emp_ename on emp (ename);
索引已创建。
Oracle首先在EMP表中按照ENAME字段进行排序，默认为升序排序，然后按照排序后的顺序将ENAME字段值和对应的ROWID逐个保存在索引中。建立索引的字段被称为索此字段，例如ENAME字段即为索引字段。
在索引创建之后，如果执行一条在WHERE子句中引用了ENAME字段的查询。例如：
SQL> select ename,hiredate,sal
2 from emp
3 where ename='SMITH';
ENAME HIREDATE SAL
---------- -------------- ---------
SMITH 17-12月-80 960
Oracle将首先对索引中的ENAME字段进行一次快速搜索（因为索引中的ENAME字段已经
排序，所以该搜索是非常快的），到符合条件的ENAME字段值所对应的ROWID，然后再利用ROWID到EMP表中提取相应的记录。这个操作比逐条读取EMP表中未排序的记录要快得多。可以看出，在索引中只保存索引字段的值与相应的ROWID，这种组合称为“索引条目”。
初学者常常会混淆索引与键，特别是对于惟一索引与主键。其实索引与键两个术语在许多地方都可以互换，尤其是Oracle有时也会利用索引，特别是惟一索引实现的一些完整性约束。但是它们在本质上有着相当明显的区别。索引是存储在数据库中的一种实体结构，可以通过SQL语句创建、修改和删除。而键只是一个逻辑上的概念，在Oracle中，键是与完整性约束相对应的。
在Oracle中可以创建多种类型的索引，以适应各种表的特点。按照索引数据的存储方式可以将索引分为：B树索引、反向键索引、位图索引和基于函数的索引等。按照索引列的个数，索引又可以分为单列索引和复合索引。按照索引列的惟一性，索引又可以分为惟一索引和非惟一索引。
注 意 | 具有索引的表与不具有索引的表，在编写SQL查询语句时没有任何区别。索引只是提供一种快速访问指定记录的方法。可以说，表是否具有索引，表的查询速度影响最大，而对其他方法的影响则非常微小。索引一旦创建，将由Oracle自动管理和维护。例如，在向表中插入、更新或删除一条记录时，Oracle会自动在索引中作出相应的修改。 |
单列索引是基于单个列所建立的索引，而复合索引是基于两个列或多个列所建立的索引。需要注意，B树索引列的个数不能超过32列，位图索引的个数不能超过30例。可以在同一个表上建立多个索引，但要求列的组合必须不同，使用以下语句建立的两个索引是合法的：
SQL> create index emp_name_job_index on emp(ename,job);
索引已创建。
SQL> create index emp_job_name_index on emp(job,ename);
索引已创建。
如上所示，尽管索引EMP_NAME_JOB_INDEX和emp_job_name_index用到了相同的列（ENAME和JOB列），但因为顺序不同，所以是合法的。如果顺序完全相同，则该复合索引是不合法的。
惟一索引是索引列的值不能有重复的索引；非惟一索引是索引列值允许重复值的索引。无
论是惟一索引还是非惟一索此，索引列都允许NULL值。需要注意，当定义主键约束时或惟一约束时，Oracle会自动在相应的约束列上建立惟一索引。
建立和规划索引时，必须选择合适的表和列。如果选择的表和列不合适，不仅无法提高查询速度，反而会极大地降低DML操作的速度。建立索引的策略如下：
● 索引应该建立在WHERE子句经常引用的表列上。如果在大表上频繁使用某列或某几列作为条件执行检索操作，并且检索行数低于总行数的15%，那么应该考虑在这些列上建立索引。
● 为了提高多表连接的性能，应该在连接列上建立索引。
● 不要在小表上建立索引。
● 如果经常需要基于某列或某几列执行排序操作，那么通过在这些列上建立索引，可以加快数据排序的速度。
● 限制表的索引个数。索引主要用于加速查询速度，但会降低DML操作的速度。索引越
多，DML操作的速度越慢，尤其会极大地影响INSERT操作和DELETE操作的速度。因引，规划索引时，必须仔细权衡查询和DML的需求。
● 删除不再需要的索引。因为索引会降低DML速度，所以应该删除不合理或不需要的索引。不合理地的索引包括：在小表上建立的索引，因为表很小，使用索引不会加速查询速度；查询语句不会引用的索引。
● 指定索引块空间的使用参数。基于表建立索引时，Oracle会将相应表列数据添加到索引块。当为索引块添加数据时，Oracle会按照PCTFREE参数确定在索引块上预留部分空间，该预留空间是为将来的INSERT操作准备的。如果将来在表上执行大量INSERT操作，那么应该在建立索引时设置较大的PCTFREE。需要注意，建立索引时不能指定PCTUSED关键字。
● 指定索引所在的表空间。建立索引时，即可以将索引和基表放在相同表空间中，也可以放在不同表空间中。将表和索引部署到相同表空间，可以简化表空间的管理；将表和索引部署到不同表空间，可以降低访问表和索引的区别，从而提高访问性能。
● 当在大表上建立索引时，使用NOLOGGING选项可以最小化重做记录。使用NOLOGGING选项可以节省重做日志空间、降低索引建立时间、提高索引并行建立的性能。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论