Datasheet

Book VII
Chapter 1
Tuning the
Database
583
on EMPLOYEE.DepartmentID usually performs best. The number of rows
in the EMPLOYEE table is sure to be larger than the number of rows in the
DEPARTMENT table, so the index is of more use applied to EMPLOYEE than
it is applied to DEPARTMENT.
A hash index stores pairs of keys and values based on a pseudo-randomizing
function called a hash function.
If the WHERE clause contains a range selection, such as EMPLOYEE.Age
BETWEEN 55 AND 65, a B+ tree index on EMPLOYEE.Age will probably be
the best performer. (A B+ tree is a balanced tree data structure whose leaves
contain a sequence of key/pointer pairs.) If the table is rarely updated, an
ISAM index may be competitive with the B+ tree index.
ISAM indexes are small and can be searched quickly. However, if insertions
or deletions are frequent, a table with ISAM indexing can quickly lose its effi-
ciency advantage.
Using multicolumn indexes
If a WHERE clause imposes conditions on more than one attribute, such as
EMPLOYEE.Age BETWEEN 55 AND 65 AND EMPLOYEE.DeptName =
Shipping, you should consider using a multicolumn index. If the index
includes all the columns that the query retrieves (an index-only query),
the query could be completed without touching the data table at all. This
method could speed the query dramatically and may be sufficient motiva-
tion to include in the index a column that you otherwise wouldn’t include.
Clustering indexes
A clustered index is one that determines the sort order of the table that it’s
indexing, as opposed to an unclustered index, which has no relationship to
the sort order of the table.
Suppose that several queries of the EMPLOYEE table have a WHERE clause
similar to WHERE EMPLOYEE.LastName = ‘Smith’. In such a case, it
would be beneficial to have a clustered index on EMPLOYEE.LastName. All
the employees named Smith would be clustered in the index, and they’d be
retrieved very quickly. Quick retrieval is possible because after you’ve found
the index to the first Smith, you’ve found them all. Access to the desired
records is almost instantaneous.
Any given table can have only one clustered index. All other indexes on that
table must be unclustered. Unclustered indexes can be helpful, but not as
helpful as clustered indexes. For that reason, if you’re going to choose one
index to be the clustered index for a table, choose the one that will be used
by the most important queries in the list of queries in the workload descrip-
tion (refer to “Analyzing the Workload,” earlier in this chapter).
Choosing the Right Indexes
40_9780470929964-bk07ch01.indd 58340_9780470929964-bk07ch01.indd 583 2/24/11 3:45 PM2/24/11 3:45 PM