Database Reference
In-Depth Information
Sample Report
Description
Query frequency
Lists all queries in the workload sorted by fre-
quency of occurrence
Current query cost
Lists all queries in the workload sorted by es-
timated cost under the current configuration
Recommended query cost
Lists all queries in the workload sorted by es-
timated cost under the recommended configu-
ration
Current indexes
Lists information about each index in the cur-
rent configuration, the fraction of queries in
the workload that use the index, and the dif-
ferent ways the index is used in the workload
Recommended indexes
Same as
current indexes
but with respect to
the recommended configuration
Query improvement
List of all queries along with the respective
improvement in execution cost under the rec-
ommended workload
Statement/index usage
Lists each query along with the indexes that it
would use in the recommended configuration
Workload information
Lists number of queries in the workload,
and fraction of queries whose cost de-
creases/stayed the same/increased under the
recommended configuration
FIGURE 7.3
Reports that can be returned along with the recommended
configuration.
existing index that needs to be deleted (to avoid running out of space,
drop
statements precede
create
statements).
Interestingly enough, the creation of the deployment script itself can be
challenging if we are concerned about performance. The reason is that the
presence of an index can speed up the creation of a subsequent index. For
example, to create index
R
)
we need to sort a vertical fragment of
R
on
column
c
. If an index with leading column
c
already exists in the database,
we can build
R
(
c
without sorting by just scanning such existing index. If an
index with a leading column other than
c
(but also including
c
) exists, we
can still scan the index faster than the clustered index, resulting in a better
alternative. In this section we formally describe this task and show that it can
be seen as a search problem.
Let
C
0
be the current configuration, and
C
f
be the desired configuration to
deploy. A physical schedule consists of a sequence of
create-index
and
drop-
index
statements that starts in configuration
C
0
and ends in configuration
C
f
.
The
physical design scheduling problem
consists of finding the minimum cost
(
c
)
Search WWH ::
Custom Search