Declarative Query Processing in Relational Database Systems - Automated Physical Database Design and Tuning - page 17

Filter
[TMP>10]

Stream Aggregate
[TMP count(*)]

Sort
[D.City]

Index Join
[E.DId=M.EId]

Index Join
[E.Did=D.DId]

Index Seek
[Emp (M) .E1]

Index Join
(RID)

Index Seek
[Dept (D) .D1]

Index Seek
[Emp (E) .E2]

RID Lookup
[Emp (E)]

FIGURE 1.5 Index-based execution plan for a SQL query.

to be maintained for every update to their corresponding tables, and they use
additional disk space to redundantly store database tuples. Finally, indexes
that are very useful to a given query might degrade performance for other
queries (e.g., updates).
For all these reasons, there has always been a great amount of interest in
deciding, for a given query workload, what would be a reasonably good set
of indexes to materialize in the database to maximize query processing per-
formance. Database developers and administrators spend significant amounts
of time not only writing SQL queries but also choosing indexes that enable
ecient executions for such queries. Recall that SQL is a declarative language,
and therefore developers should not worry about how queries are executed.
What happens in practice, though, is that developers guess how queries would