Databases Reference
In-Depth Information
that are computed based on the contents of other columns. For example, you could
have a view based on employee records and time cards. The database stores the
employee's rate of pay and hours worked, but you create a view that multiplies
these values and returns instead the amount the employee should be paid.
During database design and data modeling, you often end up breaking data
up into smaller, more specific entities and creating smaller tables. This is done
for various reasons, but it may be that many of these tables provide little useful
information when standing alone. Instead, to get any really useful information it
may be necessary to combine the contents of two or more tables. You can cre-
ate a view based on these tables, extracting the columns that you need and pre-
senting them in the order that best suites the users' requirements.
Indexes
Indexes are a key part of database organization and optimization. A database
index
has a similar function to an index in a topic: it organizes and sorts the
data and provides a pointer to the specific physical location of the data on the
storage media.
An index can be based on one or more table columns. The database engine
organizes the data based on both the columns selected and the order in which
they are selected. You can define multiple indexes on a table, each with a dif-
ferent set of key columns, thereby organizing the data in different ways. The
query processor selects the optimum index or indexes to use for data retrieval.
Why different indexes? Here's an example. Your database includes a Cus-
tomer table. Most reports run against the table sort data by last name and then
first name. It makes sense to have an index that uses the columns containing
the last name and first name values to make it easier to retrieve the data in that
order. However, you also run reports by geographic region, sorted by postal code
and address. To support this, you might create a separate index based on the
columns containing address information.
Most database management systems automatically create an index for a table's
primary key, sometimes called the
primary index.
In that case, additional
indexes are called
secondary indexes.
Indexes can also be referred to as clus-
tered and nonclustered. The primary index is usually a clustered index. With a
clustered index,
the data is physically sorted in the index order. This is used
to organize the data in the order in which it is most often needed. Secondary
indexes are usually all nonclustered indexes.
Nonclustered indexes
organize the
data for retrieval, but through the index only, and have no effect on the table.
The most common type of index used in modern databases is the
balanced
tree
or
B-Tree
(also called a
binary tree
) index, based on nodes with pointer
values directing you, in the end, to the desired data. An example of a primary
index created as a B-Tree index, along with index node contents, is shown in
Figure 3-9, illustrating how pointers are used to search for a specific record.
