Optimizers Are Not Perfect - Relational Database Index Design and the Optimizers - page 254

In summary, why does the cost based optimizer make such poor decisions?
First of all, I must point out that poor decision-making is the exception rather
the rule. The examples in this section indicate that columns are looked at
individually rather than as a group. If they were looked at as a group, the cost
based optimizer would have realized in the first example that each row looked
at was unique without the DBA having to rebuild the index as unique. The
second example illustrates that if several of the columns have a low number of
distinct values, and the SQL is requesting most of those values, the cost based
optimizer will often bypass the index. This happens despite the fact that
collectively, the columns are very specific and will return very few rows.

Cautionary Tale of Partial Index Keys

The following cautionary tale, albeit somewhat simplified and containing a few
hypothetical numbers, is based on an experience encountered by a Scandina-
vian company.
This company had bought a monitoring tool to identify exceptionally slow
SQL. In the very first report produced, the DBAs found that one frequently
executed SELECT was quite slow and consumed a large amount of CPU time.
The SELECT was a simple one, and the problem was obvious—there was no
adequate index. The SELECT, SQLA shown in SQL 14.6, was using the primary
key index, which was not even semifat for the call. Furthermore, there was only
one matching column (C1), and the index slice defined by that column was quite
thick with the worst input. A new index with three matching columns, shown in
Figure 14.4, was created and, not surprisingly, the performance of the slow and
expensive SELECT improved dramatically. This was the good news.
Now for the bad news. Soon after the new index was created, a sales manager,
due to leave for a meeting in Germany early the following morning, was trying
to create a report with an SQL reporting tool. Normally, this report was produced
quite quickly, but now it took a very long time. The sales manager, fortunately,
did not miss his flight, but he complained mightily to the IT department. The

MC = 1 MC = 3

P

C1,C2,C3,
C4,C5,C6,
C7,C8,C9

C1,C3,C10

New

SALES

Figure 14.4 New index
makes SQLA much faster.

2,000,000 rows