SQL Tuning [Electronic resources] نسخه متنی

6.7 Exercise (See Section A.2
for the solution to the exercise.)

Here's an exercise that
puts it all together with more filters and exceptions in a single
query than you are likely to see in your life. Figure 6-33 is a more complex and difficult query diagram
than I've ever encountered in a real-world tuning
problem.

Figure 6-33. A horribly, unrealistically complex problem

If you can handle this, you can easily handle any query diagram you
will ever find in real life, so give it a shot! (If you
don't get it right the first time, come back and try
again after more practice.) Find the best join order. Find the best
join method for each join, assuming table A1 has
30,000,000 rows and assuming a full table scan is preferable for any
table on which you will read at least 5% of the rows. Find the set of
primary-key indexes needed, the set of foreign-key indexes needed,
and any other indexes needed. Find any modifications to the SQL
needed to pick up hidden filters soonest. Make the usual assumptions
about referential integrity.