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

7.5 Exercise (See Section A.3
for the solution to the exercise.)

Here is an unrealistically complex query
to thoroughly test your understanding of tuning queries with
subqueries. Figure 7-36 is a more complex and
difficult query diagram than you will likely find in a year of
intensive SQL tuning. If you can handle this, you will easily handle
any subquery scenario 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 and review.

Figure 7-36. A complex problem with multiple subqueries

Fill in the missing ratios for the correlated joins. Assume that
t=5 (the rowcount of the whole query, including
the NOT EXISTS subquery), while
q=50 (the rowcount of the query with the
NOT EXISTS condition removed). Find the best join
order, including all the tables in the subqueries and in the outer
query.