2012-10-10 11gR2 concepts page 65 - 96

 

每次看CONCEPTS都能有新的收获，对Oracle的概念进行一个梳理。

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.

简单来讲，聚簇因子低的，在某个范围内的值比较有序，几乎存储于同一个块中，所以消耗的IO较小。

 

The clustering factor is relevant for index scans because it can show:
 -- Whether the database will use an index for large range scans
 -- The degree of table organization in relation to the index key
■ Whether you should consider using an index-organized table, partitioning, or
table cluster if rows must be ordered by the index key

Example 3–2 Clustering Factor -- 用类似此条SQL语句查询聚簇因子的大小
SQL> SELECT INDEX_NAME, CLUSTERING_FACTOR
2 FROM ALL_INDEXES
3 WHERE INDEX_NAME IN ('EMP_NAME_IX','EMP_EMP_ID_PK');
INDEX_NAME CLUSTERING_FACTOR

-------------------- -----------------
EMP_EMP_ID_PK 19
EMP_NAME_IX 2

 

Reverse Key Indexes -- 反键索引
A reverse key index is a type of B-tree index that physically reverses the bytes of each
index key while keeping the column order. For example, if the index key is 20, and if
the two bytes stored for this key in hexadecimal are C1,15 in a standard B-tree index,
then a reverse key index stores the bytes as 15,C1.

Reversing the key solves the problem of contention for leaf blocks in the right side of a
B-tree index. This problem can be especially acute in an Oracle Real Application
Clusters (Oracle RAC) database in which multiple instances repeatedly modify the
same block. For example, in an orders table the primary keys for orders are
sequential. One instance in the cluster adds order 20, while another adds 21, with each
instance writing its key to the same leaf block on the right-hand side of the index.

特别是在RAC中，反键索引可以减少“热块”的产生。

 

Because the data in the index is not sorted by column key when it is stored, the reverse
key arrangement eliminates the ability to run an index range scanning query in some
cases. For example, if a user issues a query for order IDs greater than 20, then the
database cannot start with the block containing this ID and proceed horizontally
through the leaf blocks. -- 这边要注意下，反键索引无法使用索引范围扫描。

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 indexes are primarily designed for data warehousing or environments in
which queries reference many columns in an ad hoc fashion. Situations that may call
for a bitmap index include: --- 以下情况适合创建位图索引

 -- The indexed columns have low cardinality, that is, the number of distinct values is
small compared to the number of table rows. -- 也就是说重复的数据较多。
 -- The indexed table is either read-only or not subject to significant modification by
DML statements. -- 表只读或者更改（DML）较少。

 

Each bit in the bitmap corresponds to a possible rowid. If the bit is set, then the row
with the corresponding rowid contains the key value. A mapping function converts
the bit position to an actual rowid, so the bitmap index provides the same functionality
as a B-tree index although it uses a different internal representation.

If the indexed column in a single row is updated, then the database locks the index
key entry (for example, M or F) and not the individual bit mapped to the updated row.

-- 这里要注意，位图索引在表更新时会锁住所有相关的行，而不只是被更新的那行。

 

Because a key points to many rows, DML on indexed data typically locks all of these
rows. For this reason, bitmap indexes are not appropriate for many OLTP applications. -- 位图索引不适合OLTP的环境。

 

Bitmap Join Indexes -- 位图连接索引
A bitmap join index is a bitmap index for the join of two or more tables. For each
value in a table column, the index stores the rowid of the corresponding row in the
indexed table. In contrast, a standard bitmap index is created on a single table.

 

Bitmap Storage Structure -- 位图存储的结构
Oracle Database uses a B-tree index structure to store bitmaps for each indexed key.
For example, if jobs.job_title is the key column of a bitmap index, then the index
data is stored in one B-tree. The individual bitmaps are stored in the leaf blocks.
Assume that the jobs.job_title column has unique values Shipping Clerk,
Stock Clerk, and several others. A bitmap index entry for this index has the
following components:
 -- The job title as the index key
 -- A low rowid and high rowid for a range of rowids
 -- A bitmap for specific rowids in the range
Conceptually, an index leaf block in this index could contain entries as follows:
Shipping Clerk,AAAPzRAAFAAAABSABQ,AAAPzRAAFAAAABSABZ,0010000100
Shipping Clerk,AAAPzRAAFAAAABSABa,AAAPzRAAFAAAABSABh,010010
Stock Clerk,AAAPzRAAFAAAABSAAa,AAAPzRAAFAAAABSAAc,1001001100
Stock Clerk,AAAPzRAAFAAAABSAAd,AAAPzRAAFAAAABSAAt,0101001001
Stock Clerk,AAAPzRAAFAAAABSAAu,AAAPzRAAFAAAABSABz,100001

 

The same job title appears in multiple entries because the rowid range differs.
Assume that a session updates the job ID of one employee from Shipping Clerk to
Stock Clerk. In this case, the session requires exclusive access to the index key entry
for the old value (Shipping Clerk) and the new value (Stock Clerk). Oracle
Database locks the rows pointed to by these two entries—but not the rows pointed to
by Accountant or any other key—until the UPDATE commits.
The data for a bitmap index is stored in one segment. Oracle Database stores each
bitmap in one or more pieces. Each piece occupies part of a single data block.

 

Function-Based Indexes -- 函数索引
You can create indexes on functions and expressions that involve one or more columns
in the table being indexed. A function-based index computes the value of a function
or expression involving one or more columns and stores it in the index. A
function-based index can be either a B-tree or a bitmap index.

A function-based index is also useful for indexing only specific rows in a table. For
example, the cust_valid column in the sh.customers table has either I or A as a
value. To index only the A rows, you could write a function that returns a null value
for any rows other than the A rows. You could create the index as follows: -- 只对表中的“A”值创建索引
CREATE INDEX cust_valid_idx
ON customers ( CASE cust_valid WHEN 'A' THEN 'A' END );

 

Optimization with Function-Based Indexes
The optimizer can use an index range scan on a function-based index for queries with
expressions in WHERE clause. The range scan access path is especially beneficial
when the predicate (WHERE clause) has low selectivity. In Example 3–6 the optimizer
can use an index range scan if an index is built on the expression
12*salary*commission_pct.
A virtual column is useful for speeding access to data derived from expressions. For
example, you could define virtual column annual_sal as
12*salary*commission_pct and create a function-based index on annual_sal.
The optimizer performs expression matching by parsing the expression in a SQL
statement and then comparing the expression trees of the statement and the
function-based index. This comparison is case-insensitive and ignores blank spaces.

Application Domain Indexes
An application domain index is a customized index specific to an application. Oracle
Database provides extensible indexing to do the following:
 -- Accommodate indexes on customized, complex data types such as documents,
spatial data, images, and video clips (see "Unstructured Data" on page 19-11)
 -- Make use of specialized indexing techniques

Partitioned Indexes -- 分区索引
A partitioned index is an index that, like a partitioned table, has been decomposed
into smaller and more manageable pieces. Global indexes are partitioned
independently of the table on which they are created, whereas local indexes are
automatically linked to the partitioning method for a table. Like partitioned tables,
partitioned indexes improve manageability, availability, performance, and scalability.
The following graphic shows index partitioning options.

 

 

 

Local Partitioned Indexes -- 本地分区索引
Like other indexes, you can create a bitmap index on partitioned tables. The only
restriction is that bitmap indexes must be local to the partitioned table—they cannot be
global indexes. Global bitmap indexes are supported only on nonpartitioned tables. -- 位图索引无法创建成全局分区索引。

 

Local Prefixed and Nonprefixed Indexes 本地前缀和本地非前缀索引
Local partitioned indexes are divided into the
following subcategories:
 -- Local prefixed indexes
In this case, the partition keys are on the leading edge of the index definition.

 --  Local nonprefixed indexes
In this case, the partition keys are not on the leading edge of the indexed column
list and need not be in the list at all.

 

Global Partitioned Indexes -- 全局分区索引
A global partitioned index is a B-tree index that is partitioned independently of the
underlying table on which it is created. A single index partition can point to any or all
table partitions, whereas in a locally partitioned index, a one-to-one parity exists
between index partitions and table partitions.

In general, global indexes are useful for OLTP applications, where rapid access, data
integrity, and availability are important. In an OLTP system, a table may be partitioned
by one key, for example, the employees.department_id column, but an
application may need to access the data with many different keys, for example, by
employee_id or job_id. Global indexes can be useful in this scenario.

全局分区索引一般什么时候用呢，比如你以某个字段创建分区，但是查询语句包含了其他字段，可以在这几个字段上创建全局索引。

 

Overview of Views
Views enable you to tailor the presentation of data to different types of users. Views
are often used to: -- 视图的好处
 -- Provide an additional level of table security by restricting access to a
predetermined set of rows or columns of a table
For example, Figure 4–6 shows how the staff view does not show the salary or
commission_pct columns of the base table employees.
 -- Hide data complexity
For example, a single view can be defined with a join, which is a collection of
related columns or rows in multiple tables. However, the view hides the fact that
this information actually originates from several tables. A query might also
perform extensive calculations with table information. Thus, users can query a
view without knowing how to perform a join or calculations.
 -- Present the data in a different perspective from that of the base table
For example, the columns of a view can be renamed without affecting the tables on
which the view is based.
 -- Isolate applications from changes in definitions of base tables
For example, if the defining query of a view references three columns of a four
column table, and a fifth column is added to the table, then the definition of the
view is not affected, and all applications using the view are not affected.