oracle概念_索引

Index

使用索引可以大大提高数据库的性能。

Primary and unique keys automatically have indexes.

复合索引的列顺序问题。组合列中在前面的列在where子句中查询才会走索引。


B-Tree Indexes 典型的B-Tree索引。

Full Index Scan：可以消除排序，因为index本身已经排序。


Suppose that an application runs the following query:

SELECT department_id, last_name, salary
FROM   employees
WHERE  salary > 5000
ORDER BY department_id, last_name;
Also assume that department_id, last_name, and salary are a composite key in an index. Oracle Database performs a full scan of the index, reading it in sorted order (ordered by department ID and last name) and filtering on the salary attribute. In this way, the database scans a set of data smaller than the employees table, which contains more columns than are included in the query, and avoids sorting the data.



Fast Full Index Scan：只扫描Index table，因为Index table已经包含了所有要返回的数据。
A fast full index scan is a full index scan in which the database reads the index blocks in no particular order. The database accesses the data in the index itself, without accessing the table.


Index Range Scan
Index Unique Scan

Index Skip Scan：customers table的cust_gender列不是M就是F,该表有一个复合索引(cust_gender, cust_email).
那么以下这个查询
SELECT * FROM sh.customers WHERE cust_email = '[e-mail]';
数据库自己优化为：
SELECT * FROM sh.customers WHERE cust_gender = 'F'
  AND cust_email = '[e-mail]'
UNION ALL
SELECT * FROM sh.customers WHERE cust_gender = 'M'
  AND cust_email = '[e-mail]';

Reverse Key Indexes:反转Index key的存储。对于没有反转的index，连续值的index会插入在同一或者相近的block里，造成竞争。而反转的index，连续的index被分散存储。但是同时，Reverse Key Indexes丢失了range scan的能力。


Index Clustering Factor：衡量索引的聚集性，该值越低，则一个范围内的索引更倾向于指向同一个数据块(data block)。

索引的顺序：索引可以定义存储时的顺序，升序或者降序。

Key Compression：索引的Key的存储可以重新排列以节省空间。原有的key可以被分解为prefix entry和suffix entry。
如
online,0,AAAPvCAAFAAAAFaAAa
online,0,AAAPvCAAFAAAAFaAAg
online,2,AAAPvCAAFAAAAFaAAm
可以排成
online,0
AAAPvCAAFAAAAFaAAa
AAAPvCAAFAAAAFaAAg
online,2
AAAPvCAAFAAAAFaAAm

Bitmap Indexes：对于查询多且取值范围小的列适用。
Bitmap Join Indexes。
Bitmap Storage Structure：一样用B-Tree.
其他书上看到位图索引对于一个值是单行存储的。有可能导致性能问题。
因此改动一个值，会锁行，导致该行所有其他值无法修改。

Function-Based Indexes:基于函数的索引。
Application Domain Indexes：基于应用域的索引，用户可以高度定制。

Index Storage：索引可以任意存储，索引和其索引的表不在同一个tablespace中给表备份带来了方便。

Heap-Organized Table
rows are inserted where they fit。

Index-Organized Tables
叶子节点直接存储row。以PK作为索引。

两种表的对比

Heap-Organized Table
The rowid uniquely identifies a row. Primary key constraint may optionally be defined.
Physical rowid in ROWID pseudocolumn allows building secondary indexes.
Individual rows may be accessed directly by rowid.
Sequential full table scan returns all rows in some order.
Can be stored in a table cluster with other tables.
Can contain a column of the LONG data type and columns of LOB data types.
Can contain virtual columns (only relational heap tables are supported).

Index-Organized Table
Primary key uniquely identifies a row. Primary key constraint must be defined.
Logical rowid in ROWID pseudocolumn allows building secondary indexes.
Access to individual rows may be achieved indirectly by primary key.
A full index scan or fast full index scan returns all rows in some order.
Cannot be stored in a table cluster.
Can contain LOB columns but not LONG columns.
Cannot contain virtual columns.

Index-Organized Tables with Row Overflow Area
Index-Organized Tables中,为了解决行过大问题，可以把一行分为2部分存储。The index entry储存PK和一些可选列，放在index segment里面，The overflow part包含其他的列，放在storage area segment里面。

Secondary Indexes on Index-Organized Tables
logical rowids以PK为基础，所以2级索引可以直接构建在logical rowid之上。

Bitmap Indexes on Index-Organized Tables