Designing a Cost Model - Automated Physical Database Design and Tuning - page 78

Suppose then that we can create a version of an index that contains only
metadata information but not actual data (we call it a hypothetical index).
From the point of view of the optimizer, such a hypothetical index is ex-
actly like any ordinary materialized index. Optimization can proceed in the
usual manner considering both regular and hypothetical indexes simultane-
ously. The final execution plan can even include operators that reference hy-
pothetical indexes. Of course, such execution plans are not actually executable
because hypothetical indexes do not really exist. However, the final execution
plan and cost of a query that uses hypothetical indexes are indistinguishable
from the alternative obtained by materializing all hypothetical indexes. We
can therefore effectively simulate the presence of an index by creating the
corresponding metadata in the system catalogs. Since such metadata are very
small and independent of the index size, this process is very ecient and
allows performing what-if optimizations with almost no overhead compared
with regular optimizations.

5.1.1 What-If Optimization Interface
Figure 5.1 shows a schematic interface that extends a DBMS to enable what-if
optimization. The key aspects of this what-if interface are:

1. A command to create a hypothetical index in the DBMS, which popu-
lates the system catalogs with the index metadata. This command could
be as simple as a regular index creation statement with a specific flag
that marks the index as hypothetical, such as:

CREATE HYPOTHETICAL INDEX empNameIndex ON Emp(name)
2. A mechanism to create any supporting information about the hypothet-
ical index. This step depends on the specific DBMS implementation. Re-
call that a hypothetical index must be indistinguishable from a regular
index during optimization. Therefore, any information that is obtained
while creating a regular index and later exploited during optimization
must be recreated in this step. A common example is statistical infor-
mation on index columns. We discuss this topic in more detail in the
next section.

Create hypothetical index

Create supporting information

Physical Design
To o l

Database System

Define configuration

Optimize what-if query
Obtain what-if results

FIGURE 5.1 What-if interface for optimizing under hypothetical

configurations.