By creating an index on one or more columns of a table, you gain the ability in some cases to retrieve a small set of randomly distributed rows from the table. Indexes are one of many means of reducing disk I/O.
创建索引之后，你能够在查询表时某些情况下在随机分布的行中能够检索更小的集合
Indexes are one of many means of reducing disk I/O.
索引也是能够降低磁盘I/O的几种方法之一
If a heap-organized table has no indexes, then the database must perform a full table scan to find a value.
如果在堆栈表中没有索引，则数据库必须完成全表扫描才能发现一个值

In general, consider creating an index on a column in any of the following situations:
1.The indexed columns are queried frequently and return a small percentage of the total number of rows in the table.
2.A referential integrity constraint exists on the indexed column or columns. The index is a means to avoid a full table lock that would otherwise be required if you update the parent table primary key, merge into the parent table, or delete from the parent table.

3.A unique key constraint will be placed on the table and you want to manually specify the index and all index options.

在以下情况下考虑创建一个索引：
1.索引列经常被查询，并且根据索引列返回的行占总共索引行的比较小的半分比
2.如果外键列没有建索引，则更新、删除和合并父表的主键列就会在子表中加上一个全表锁(所以应该在所有外键列都创建一个索引)
3.如果你想设置一个唯一键索引

Index Characteristics

Indexes are schema objects that are logically and physically independent of the data in the objects with which they are associated. Thus, an index can be dropped or created without physically affecting the table for the index.

索引与表中的数据是物理及逻辑上独立的，所以在删除及创建索引并不影响表中的数据
Given a data value that has been indexed, the index points directly to the location of the rows containing that value.
查询一个已经被所以的值，这个索引直接指定包含该值的行的位置
However, the presence of many indexes on a table degrades DML performance because the database must also update the indexes.
索引并不需要用户进行维护，数据库本身自己进行维护。索引的存在会减低表的DML性能，因为在DML时数据库必须对索引进行更新

Indexes have the following properties:

Usability

Indexes are usable (default) or unusable. An unusable index is not maintained by DML operations and is ignored by the optimizer. An unusable index can improve the performance of bulk loads. Instead of dropping an index and later re-creating it, you can make the index unusable and then rebuild it. Unusable indexes and index partitions do not consume space. When you make a usable index unusable, the database drops its index segment.
Visibility

Indexes are visible (default) or invisible. An invisible index is maintained by DML operations and is not used by default by the optimizer. Making an index invisible is an alternative to making it unusable or dropping it. Invisible indexes are especially useful for testing the removal of an index before dropping it or using indexes temporarily without affecting the overall application.
索引具备以下属性：
可用性：
索引分为可用和不可用。一个不可用的索引在DML操作的时候数据库是不进行自动维护的，并且会被查询优化器忽略。在数据库进行大量的load的时候，把索引设置为不可用可以增加load的性能。不可用的索引不占用存储空间，当把可用索引设置为不可用的时候，数据库删除索引段。
可见性：
索引可以分为可见和不可见。一个不可见索引在进行DML操作的时候可以被数据库进行维护，但是会呗查询优化器忽略。不可见索引一般应用于测试删除索引对应用有多大的影响时使用。
Primary and unique keys automatically have indexes, but you might want to create an index on a foreign key.
主键和唯一键会自动创建一个索引，但是你也应该在外键上创建一个索引

Composite indexes can speed retrieval of data for SELECT statements in which the WHERE clause references all or the leading portion of the columns in the composite index. Therefore, the order of the columns used in the definition is important. In general, the most commonly accessed columns go first.

复合索引能够增强select中where后的查询条件是复合索引所有列或者前导列的查询速度。因此在复合索引中列定义的顺序就很重要，一般来讲，越频繁查询的列应该越在前面。

CREATE INDEX employees_ix
   ON employees (last_name, job_id, salary);

Queries that access all three columns, only the last_name column, or only the last_name and job_id columns use this index. In this example, queries that do not access the last_name column do not use the index.

复合索引中当使用所有列，仅使用last_name列或者仅使用last_name和job_id列会使用索引，如果不使用前导列则不使用复合索引
在某些情况下如果前导列是低基数列，则可能会发生index skip scan

Indexes can be unique or nonunique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column or column. For example, no two employees can have the same employee ID. Thus, in a unique index, one rowid exists for each data value. The data in the leaf blocks is sorted only by key.

Nonunique indexes permit duplicates values in the indexed column or columns. For example, the first_name column of the employees table may contain multiple Mike values. For a nonunique index, the rowid is included in the key in sorted order, so nonunique indexes are sorted by the index key and rowid (ascending).

索引分为唯一索引和非唯一索引。唯一索引不存在重复的key，在叶节点的数据是仅按照key进行排序。非唯一索引允许重复值，非唯一索引是按照rowid和key进行排序(升序)
Oracle Database does not index table rows in which all key columns are null, except for bitmap indexes or when the cluster key column value is null.
oracle不会对所有列都是null值的行进行索引

Types of Indexes

索引分为以下几类：
1.B-tree索引
B-tree索引是最普通的索引，它又分为以下几种子类型：
索引组织表
反向索引
降序索引
B-tree集群索引
2.bitmap索引和bitmap join索引
3.函数索引
4.应用区域索引

B-Tree Indexes

A B-tree index is an ordered list of values divided into ranges.
B-tree索引是普通的索引，B-tree索引是被区分范围的有序的值。
By associating a key with a row or range of rows, B-trees provide excellent retrieval performance for a wide range of queries, including exact match and range searches.
索引key关联一个行或者一个范围的行，B-tree提供极好的查询性能为一个范围的查询，包括精准匹配和范围搜索。

A B-tree index has two types of blocks: branch blocks for searching and leaf blocks that store values. The upper-level branch blocks of a B-tree index contain index data that points to lower-level index blocks.
一个B-tree索引包含俩个类型的块：支块和叶节点块。上级支块包含的数据指定到下级索引块
A B-tree index is balanced because all leaf blocks automatically stay at the same depth.
一个B-tree索引是平衡的，因为所有的叶节点块自动保持相同的深度
The height of the index is the number of blocks required to go from the root block to a leaf block.
索引中的height是从根块到叶块的层数
The branch level is the height minus 1.
这个branch level是高度-1
Branch blocks store the minimum key prefix needed to make a branching decision between two keys
支节点存储最小的key前缀用于决定在两个key之间做判断
This technique enables the database to fit as much data as possible on each branch block. The branch blocks contain a pointer to the child block containing the key. The number of keys and pointers is limited by the block size.
这个技术允许数据库能够在支节点存储尽可能多的适合的数据，这个支节点块包含一个指针到子块上，key的数目和指针由块大小进行限制。
The leaf blocks contain every indexed data value and a corresponding rowid used to locate the actual row. Each entry is sorted by (key, rowid). Within a leaf block, a key and rowid is linked to its left and right sibling entries. The leaf blocks themselves are also doubly linked. In the leftmost leaf block (0-10) is linked to the second leaf block (11-19).
叶节点块包含每个索引数据值和对应的rowid用于定位真实的行位置。每个条目是有(key，rowid)进行排序。在叶块里，一个key和rowid与它的左叶块和右叶块进行关联

Index Scans

In an index scan, the database retrieves a row by traversing the index, using the indexed column values specified by the statement. If the database scans the index for a value, then it will find this value in n I/Os where n is the height of the B-tree index. This is the basic principle behind Oracle Database indexes.
索引扫描，数据库在查询语句中使用索引列通过索引检索一个行，如果数据库通过索引扫描来查找一个值，它找这个值在n个I/O之内(n为索引的height)。这是oracle数据库基本的原理。

If a SQL statement accesses only indexed columns, then the database reads values directly from the index rather than from the table. If the statement accesses columns in addition to the indexed columns, then the database uses rowids to find the rows in the table. Typically, the database retrieves table data by alternately reading an index block and then a table block.

如果SQL语句仅仅访问索引列，之后数据库直接从索引段中读取这个值而不是表中读取。如果语句访问的列不是索引列，则数据库用rowid去定位在表中的行。通常的，数据库检索表数据轮流的读取索引块和表块
Full Index Scan

In a full index scan, the database reads the entire index in order. A full index scan is available if a predicate (WHERE clause) in the SQL statement references a column in the index, and in some circumstances when no predicate is specified. A full scan can eliminate sorting because the data is ordered by index key.
full index scan，数据库按顺序读取整个索引，如果where子句关联的列是在索引中则使用full index scan，在某些环境中没有where参考也会使用full index scan，一个full scan能够消除排序，因为索引本身就是排序的
Fast Full Index Scan

A fast full index scan is a full index scan in which the database accesses the data in the index itself without accessing the table, and the database reads the index blocks in no particular order.

fast full index scan是一种full index scan，在数据库只访问在index自己包含的数据，而不访问表中其他的数据的时候发生。数据库读取索引块并不是按照指定的顺序读取

Fast full index scans are an alternative to a full table scan when both of the following conditions are met:

• The index must contain all columns needed for the query.

• A row containing all nulls must not appear in the query result set. For this result to be guaranteed, at least one column in the index must have either:

  • A NOT NULL constraint

  • A predicate applied to it that prevents nulls from being considered in the query result set

fast full index scan可以代替full table scan必须要符合下面俩个条件：
1.索引必须包含查询的所有列
2.行中包含的所有null值必须不能出现在查询结果中，要么至少一个索引有not null约束，要么使用where条件阻止null值出现在查询结果中

Index Range Scan

An index range scan is an ordered scan of an index that has the following characteristics:

• One or more leading columns of an index are specified in conditions. A condition specifies a combination of one or more expressions and logical (Boolean) operators and returns a value of TRUE, FALSE, or UNKNOWN.

• 0, 1, or more values are possible for an index key.

一个索引范围扫描是一个有序的范围索引，需要符合以下特性：
一个或者多个前导列指定在条件中。这个条件结合一个或者多个表达式或者逻辑操作（布尔型）操作
The selectivity is the percentage of rows in the table that the query selects, with 0 meaning no rows and 1 meaning all rows.
选择性就是返回行数的百分比，0意味着没有返回行，1意味着返回所有行
A predicate becomes more selective as the value approaches 0 and less selective (or more unselective) as the value approaches 1.
接近0就是意味着高选择性，越接近1即意味着越低选择性
The database could use a range scan because the last_name column is specified in the predicate and multiples rowids are possible for each index key. For example, two employees are named Austin, so two rowids are associated with the key Austin.
数据库可以用所有范围检索，因为last_name被指定到谓词条件中，并且多个可能多个rowid对应一个索引键。例如两个员工的名字都叫做Austin，所以两个rowid管理一个索引键名Austin
An index range scan can be bounded on both sides, as in a query for departments with IDs between 10 and 40, or bounded on only one side, as in a query for IDs over 40. To scan the index, the database moves backward or forward through the leaf blocks. For example, a scan for IDs between 10 and 40 locates the first index leaf block that contains the lowest key value that is 10 or greater. The scan then proceeds horizontally through the linked list of leaf nodes until it locates a value greater than 40.
一个索引范围检索能够在两边边界检索，例如查询部门ID在10到40之间的记录，或者仅在一个边界，例如检索大于40的部门ID。进行检索索引的时候，数据库前后移动叶节点。例如在部门ID检索10和40之间的记录，首先定位第一个索引叶节点在10或者高于10的最低值。之后在叶节点链表中水平的通过link检索直到定位一个最低的超过40的值。
Index Unique Scan In contrast to an index range scan, an index unique scan must have either 0 or 1 rowid associated with an index key. The database performs a unique scan when a predicate references all of the columns in a UNIQUE index key using an equality operator. An index unique scan stops processing as soon as it finds the first record because no second record is possible.
与索引范围检索不同，索引唯一检索必须关联一个或者零个的rowid与索引键。这个数据库完成唯一检索当查询条件关联的所有列都具有唯一索引键用等值操作。一个唯一索引检索停止执行只要发现第一个记录，因为没有第二个记录的可能。
Index Skip Scan An index skip scan uses logical subindexes of a composite index. The database "skips" through a single index as if it were searching separate indexes. Skip scanning is beneficial if there are few distinct values in the leading column of a composite index and many distinct values in the nonleading key of the index.
索引跳跃检索用复合索引中的逻辑子索引。数据库跳跃的使用单个索引。跳跃是有益的，在复合索引中前导列有很少的不同值并且非前导列有很多不同的值

The database can use a skip scan of this index even though cust_gender is not specified in the WHERE clause.

数据库能够使用索引跳跃检索即使cust_gender没有在where中指定
In a skip scan, the number of logical subindexes is determined by the number of distinct values in the leading column. In , the leading column has two possible values. The database logically splits the index into one subindex with the key F and a second subindex with the key M.
在跳跃索引检索，逻辑子索引的数目决定于前导列的不同值。数据库逻辑的分为一个索引F和第二个索引M

Index Clustering Factor The index clustering factor measures row order in relation to an indexed value such as employee last name. The more order that exists in row storage for this value, the lower the clustering factor.
索引聚簇因子测量索引键值的行顺序。被索引的行越有序，索引聚簇因子越低

The clustering factor is useful as a rough measure of the number of I/Os required to read an entire table by means of an index:

• If the clustering factor is high, then Oracle Database performs a relatively high number of I/Os during a large index range scan. The index entries point to random table blocks, so the database may have to read and reread the same blocks over and over again to retrieve the data pointed to by the index.

• If the clustering factor is low, then Oracle Database performs a relatively low number of I/Os during a large index range scan. The index keys in a range tend to point to the same data block, so the database does not have to read and reread the same blocks over and over.

聚簇因子对于粗糙测量读取整个表所需要的I/O非常有用
如果聚簇因子高，数据库会在大范围的索引范围检索时执行大量的I/O，索引随机的指向表块，数据库会发生多次的读取和重读取相同的块
如果聚簇因子低，数据库会在大范围的索引范围检索时执行少量的I/O，索引在一个范围内都是指向相同的数据块，数据库不需要多次重读一个相同的数据库块
queries the ALL_INDEXES view for the clustering factor for these two indexes.
可以查询ALL_INDEXES视图来查看索引的聚簇因子

Reverse Key Indexes

反向键索引就是防止RAC环境中两个实例同时写一个块，防止block pin

Bitmap Indexes

In a bitmap index, the database stores a bitmap for each index key. In a conventional B-tree index, one index entry points to a single row. In a bitmap index, each index key stores pointers to multiple rows.
在bitmap索引中，数据库存储为每个索引键存储一个位图。普通的B-tree索引，每个索引条目对于一个单独的行，在bitmap索引中，每个索引存储对应着多个行。
Bitmap indexes are primarily designed for data warehousing
bitmap索引主要被设计与数据仓库环境

Index Storage

Oracle Database stores index data in an index segment. Space available for index data in a data block is the data block size minus block overhead, entry overhead, rowid, and one length byte for each value indexed.
oracle在索引段中存储索引数据。对于一个数据块中索引数据的可用空间是数据块的大小减去块头大小，条目空间开销，rowid开销，和为每个索引值的长度字节开销。
The tablespace of an index segment is either the default tablespace of the owner or a tablespace specifically named in the CREATE INDEX statement. For ease of administration you can store an index in a separate tablespace from its table.
索引段的表空间要么是用户的默认表空间，或者单独制定的表空间。为了方便管理，你能够可以存储索引与表分离的表空间中。

Overview of Index-Organized Tables

An index-organized table is a table stored in a variation of a B-tree index structure. In a heap-organized table, rows are inserted where they fit. In an index-organized table, rows are stored in an index defined on the primary key for the table. Each index entry in the B-tree also stores the non-key column values. Thus, the index is the data, and the data is the index. Applications manipulate index-organized tables just like heap-organized tables, using SQL statements.
一个索引组织表是一个表与B-tree索引结构的异种。在堆栈表中，行被插入它们适合的地方。在索引组织表中，行被存储在表中的主键中。在B-tree中每个索引条目有也存储非键值列。因此索引就是数据，数据就是索引。对于应用来说，索引组织表和堆栈表都是一样的。