
48 Automated Physical Database Design and Tuning
SELECT n name, SUM(l extendedprice*(1-l discount)) AS revenue
FROM customers, orders, lineitem, supplier, nation, region
WHERE c custkey = o custkey
AND o orderkey = l orderkey
AND l suppkey = s suppkey
AND c nationkey = s nationkey
AND s nationkey = n nationkey
AND n regionkey = r regionkey
AND r name = ’AMERICA’
AND o orderdate BETWEEN ’1997-01-01’ AND dateadd(YY, 1, ’1997-01-01’)
GROUP BY n name
ORDER BY revenue DESC
FIGURE 3.1 A synthetic query of moderate complexity.
such narrow indexes to seek the relevant tuples, followed by RID lookups to
the primary index and a final filter for the remaining predicates, ...