Database Reference
In-Depth Information
2.
An ORDER BY results in a sort even when several slices are read from
an index in which the index rows are in the right order; this is because
the sort of the set of pointers destroys any inherent sequence provided by
the index.
3.
An index only access path is not possible
if only the pointers
to the table
rows are collected from the index slices. This issue, a DBMS-specific
implementation, will be discussed later.
Let us return to the SELECT statement that looks for large males from a
million-row customer table as shown again in SQL 10.2.
If table CUST has
three
single-column indexes (one with column SEX,
one with column WEIGHT, and one with column HEIGHT) and if the optimizer
decides to collect the matching pointers from these three indexes, the DBMS must
touch half a million SEX index rows. In addition, every large male causes a touch
to the table. If the DBMS performs multiple index access in the manner depicted
above, even making the index SEX fat cannot eliminate the table touches.
SQL 10.2
SELECT
LNAME, FNAME, CNO
FROM
CUST
WHERE
SEX = 'M'
AND
(WEIGHT > 90
OR
HEIGHT > 190)
ORDER BY
LNAME, FNAME
Index ANDing with Query Tables
Index ANDing is a valuable facility when SELECT statements with many
dif-
ferent
and
unpredictable
WHERE clauses are generated for a
query table
.
As we saw in Chapter 9,
query tables
are denormalized fact tables. They are
feasible when the number of rows is not very high, perhaps only a few million.
Because of the denormalization, no joins are needed, all the required columns
being in one table. Table CUST in Figure 10.2 is a typical example. It may
have tens of columns, having a row length of several hundred bytes, even after
compression.
Figure 10.2 shows five search columns, eventual WHERE columns, assum-
ing the customer number is not used as a search column. The six thin indexes
together with multiple index access may provide adequate performance for any
compound predicate referring to the five search columns. Collecting and sorting
the pointers does not take an unacceptable period of time if the index slices to
be scanned contain no more than a few hundred thousand rows. Accessing the
Search WWH ::
Custom Search