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.