Glossary - Relational Database Index Design and the Optimizers - page 297

Three-star index The ideal index for a given SELECT statement.
Touch The cost of the DBMS reading one index or table row. If the DBMS scans a
slice of an index or table (the rows being read are physically next to each other),
reading the first row infers a random touch. Reading the next rows takes one
sequential touch per row. The cost of an index touch is essentially the same as the
cost of a table touch.
Tuning potential The upper limit for the achievable reduction in the local response
time as a result of planned index improvements.
Victim A transaction that is affected by a culprit because it has to wait for a resource.
GENERAL
Access path The method chosen by the optimizer to build a result table for an SQL
statement; for a single-table SELECT, using a chosen index in a certain way or a full
table scan; for a join, in addition to this, a table access order and a join method.
Asynchronous read Performed in advance while a previous set of pages is being
processed; there may be a considerable overlap between the processing and the I/O
time; ideally the asynchronous I/O will complete before the pages are actually
required for processing. This activity is called prefetch.
Bitmap index Used instead of a B-tree index; each value of an index column has a
bit vector. Appropriate for columns with a low cardinality provided that inserts,
updates, and deletes are rare. Fast with certain kinds of queries with complex
WHERE clauses, typically in data warehouse. Not supported by all products.
Block See page .
Boolean term predicate A row can be rejected when a predicate is evaluated false,
otherwise it is non-Boolean. Non-BT predicates may make a WHERE clause too
difficult for the optimizer—the access path is not optimal. If a WHERE clause
contains no OR operators, all predicates are BT.
B-tree The most common type of index; columns are copied from (normally) a single
table. The lowest level (leaf pages) contains a pointer to each table row. The leaf
page level has its own index tree whose top level is called the root page.
Buffer pool An area of computer memory into which index and table pages are read
from disk; the pool may be subdivided into subpools, which are allocated to individual
indexes and tables. The buffer pool managers attempt to ensure that frequently used
data remains in the pool to avoid the necessity of additional reads from disk.
Clustered index In SQL server, an index that contains the table rows; in DB2, any
index whose index rows are stored in the same, or almost the same, order as the
table rows.
Clustering index Causes the DBMS to insert a new table row into (or close to) a
home page defined by the clustering index key. A table may have only one clustering
index. Not all products support a clustering index, but a reorganization or reload can
be used to place the table rows in the required sequence.
Covering index In SQL Server, an index that contains all the columns referred to in
a SELECT statement so that table access can be avoided; this is the opposite of the