Optimizing Performance for Joins

To gain the fastest processing of joins, create a Low_Fast (LF) or High_Group (HG) index on all columns that may be referenced in:

• WHERE clauses of join queries

• HAVING clause conditions of 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