Databases Reference
In-Depth Information
lection of interrelated records of different types, possibly including a collection of inter-
related files. Query and update transactions to a database are made efficient by the
implementation of certain search methods as part of the database management system.
1.3.1 Indexes
An
index
is a data organization set up to speed up the retrieval (query) of data from
tables. In database management systems, indexes can be specified by database applica-
tion programmers using the following SQL commands:
CREATE UNIQUE INDEX supplierNum ON supplier(snum);
/*unique index on a key*/
A unique index is a data structure (table) whose entries (records) consist of attribute
value, pointer pairs such that each pointer contains the block address of an actual data-
base record that has the associated attribute value as an index key value. This is known
as an ordered index because the attribute (key) values in the index are ordered as ASCII
values. If all the key values are letters, then the ordering is strictly alphabetical. Ordered
indexes are typically stored as B+trees so that the search for the matching key value is
fast. Once the key value and corresponding data block pointer are found, there is one
more step to access the block containing the record you want, and a quick search in
memory of that block to find the record.
Sometimes data is better accessed by an attribute other than a key, an attribute that
typically has the same value appear in many records. In a unique index based on a key,
the key has a unique value in every record. For a nonunique attribute, an index must
have multiple attribute, pointer pairs for the same attribute value, and each pointer has
the block address of a record that has one of those attribute values. In the B+tree index,
the leaf nodes contain these attribute, pointer pairs that must be searched to find the
records that match the attribute value. The SQL command for this kind of index, also
called a secondary index, is:
CREATE INDEX shippingDate ON shipment (shipdate);
/*secondary index on non-key*/
In a variation of the secondary or nonunique index, it is possible to set up a collec-
tion of attribute values that you want to use to query a table. Each entry in the index
consists of a set of attribute values and a block pointer to the record that contains exact
matches for all those attribute values in the set. An example of an SQL command to set
up this kind of index is:
CREATE INDEX shipPart ON shipment (pnum, shipdate);
/*secondary concatenated index*/
