Databases Reference
In-Depth Information
database query compiler without the involvement of the application designer who
authored the query text.
In this final example let's look at a full query execution plan for a single query with
and without multidimensional clustering (MDC). This example looks at a very simple
range query over the salary column within an EMPLOYEE table. The caller may be
looking for the highest or the lowest paid employees, for example, or perhaps looking
for employees paid within a certain range, such as $50,000-$60,000 per year. Two
query execution plans are shown in Figure 11.9.
The first query execution plan is selected when the database is not using MDC, but
does have an index on the SALARY column of the EMPLOYEE table. The query execu-
tion plan is fairly straightforward. This is a list prefetch plan, first obtaining the record
identifiers (RIDs) of the qualifying rows from the index. The RIDs are then sorted (in
data page order) and the sorted list of RIDs is sent to prefetchers, who should get all
those pages into the buffer pool (hopefully) by the time we start processing the FETCH
operations. The total resource consumption is estimated by the query optimizer as
387.527 units, which is a blend of the I/O, CPU, and network consumption the query
execution plan will require. In the second example the database has been modified with
the multidimensional clustering on the SALARY dimension of the EMPLOYEE table.
A generated column was added defined as INT(SALARY/1000) so that salary ranges
would be grouped together in MDC blocks (see Chapter 8, where these terms are
explained).
The most obvious difference in the resulting query execution plan for the same
range query on the database with MDC is that the resulting plan is clearly simpler, with
fewer steps and operators. The query execution plan selects blocks from an internally
generated block index called SQL0110171419248, and the qualifying rows are fetched
and returned, already having been sorted by cells. The optimizer estimates the resource
consumption for the new plan to be 325.172 units, roughly 15% less resource con-
sumption than the first plan. This shows us that the design choice for MDC appears to
be beneficial to this query, quantifiably how much, and also gives us assurance that the
design attribute will be used by the query at runtime.
11.5
Query Execution Plan Indicators for Improved
Physical Database Designs
There are definite indicators within a query execution plan that can signal a problem
with (or perhaps an opportunity for improvement of ) the physical database design.
Here are some examples of situations to watch out for the following:
1.
Table scans.
It is almost never ideal for a database to perform a scan of an entire
table. There are two major exceptions to this rule: brute-force queries that explic-
