To gain the fastest processing of ad hoc joins, create a Low_Fast or High_Group index on all columns that may be referenced in:
WHERE clauses of ad hoc join queries
HAVING clause conditions of ad hoc join queries outside of aggregate functions
For example:
SELECT n_name, sum(l_extendedprice*(1-l_discount))
AS revenue
FROM customer, 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 = 'ASIA'
AND o_orderdate >= '1994-01-01'
AND o_orderdate < '1995-01-01'
GROUP BY n_name
HAVING n_name LIKE "I%"
AND SUM(l_extendedprice*(1-l_discount)) > 0.50
ORDER BY 2 DESC
All columns referenced in this query except l_extendedprice and l_discount should have an LF or HG index.
