Overview of Indexes【每日一译】--20121209

Indexes are optional structures associated with tables and clusters. You can create
indexes on one or more columns of a table to speed SQL statement execution on that
table. Just as the index in this manual helps you locate information faster than if there
were no index, an Oracle index provides a faster access path to table data. Indexes are

the primary means of reducing disk I/O when properly used.

索引是可选结构它与表和聚簇表相关。你可以创建一个或者多个表的索引去加速SQL语句的执行

在那个表上。作为索引通过这种方法帮你更快的定位信息比起没有索引的情况，ORACLE索引提供

一个快速访问表数据的访问路径。索引是主要减少磁盘I/O主要方法当使用索引适当的时候。

You can create many indexes for a table as long as the combination of columns differs
for each index. You can create more than one index using the same columns if you
specify distinctly different combinations of the columns. For example, the following

statements specify valid combinations:

你可以创建多个索引对于一个表只要多个索引的组合不同于每一个索引。你可以创建多个索引使用相同的列

如果你指定唯一不同的列组合。比如，以下的语句指定可用的组合。

CREATE INDEX employees_idx1 ON employees (last_name, job_id);
CREATE INDEX employees_idx2 ON employees (job_id, last_name);
Oracle provides several indexing schemes, which provide complementary
performance functionality:
■ B-tree indexes
■ B-tree cluster indexes
■ Hash cluster indexes
■ Reverse key indexes
■ Bitmap indexes
■ Bitmap join indexes
Oracle also provides support for function-based indexes and domain indexes specific
to an application or cartridge.
The absence or presence of an index does not require a change in the wording of any

SQL statement. An index is merely a fast access path to the data. It affects only the

speed of execution. Given a data value that has been indexed, the index points directly
to the location of the rows containing that value.

ORACLE也提供支持基于函数索引和域索引指定到一个应用或黑盒
Indexes are logically and physically independent of the data in the associated table.
You can create or drop an index at any time without affecting the base tables or other
indexes. If you drop an index, all applications continue to work. However, access of
previously indexed data can be slower. Indexes, as independent structures, require
storage space.

索引是逻辑和物理依赖于相关表的数据。你可以创建或者DROP一个索引在任何时间不影响

基表或者其它索引。如果你删除一个索引，所有应用继续工作。但是之前的被索引化数据访问

会变慢。索引作为独立结构，要求存储空间。

Oracle automatically maintains and uses indexes after they are created. Oracle
automatically reflects changes to data, such as adding new rows, updating rows, or
deleting rows, in all relevant indexes with no additional action by users.
Retrieval performance of indexed data remains almost constant, even as new rows are
inserted. However, the presence of many indexes on a table decreases the performance
of updates, deletes, and inserts, because Oracle must also update the indexes
associated with the table.
The optimizer can use an existing index to build another index. This results in a much
faster index build.

ORACLE自动维护和使用索引在它们创建后。O自动应用数据的修改，比如增加新行，更新行

或者删除行，在与索引所有的相关不需要额外动作对于用户。

检索索引性能数据保留大多数常量，甚至新行插入。但是，非常多索引存在一个表上将降低更新

删除和插入性能，因为O必须也要更新与表相关的索引。

优化器能使用一个已存在的索引去创建另一个索引，这导致一个更快的索引创建。