• No GROUP BY list.
• SELECT list contains at least one simple column reference.
• Every simple column reference is from the same table.
• Every table in the FROM list is a base table.
• Primary table

  There is at least one unique index on one table in the FROM list for which all the columns appear in one of the following:

  • equality predicates with expressions that do not include any column references
  • simple column references in the SELECT list
• Secondary table(s)

  All the other tables in the FROM list also have at least one unique index for which all the columns appear in one of the following:

  • equality predicates with expressions that do not include columns from the same table
  • simple column references in the SELECT list

CREATE TABLE tab1 (c1 INT NOT NULL, 
    c2 INT NOT NULL, 
    c3 INT NOT NULL, 
    c4 CHAR(2), 
    PRIMARY KEY (c1, c2, c3))
CREATE TABLE tab2 (c1 INT NOT NULL,  
    c2 INT NOT NULL, 
    PRIMARY KEY (c1, c2))
INSERT INTO tab1 VALUES (1, 2, 3, 'WA'), 
    (1, 2, 5, 'WA'), 
    (1, 2, 4, 'CA'), 
    (1, 3, 5, 'CA'), 
    (2, 3, 1, 'CA')
INSERT INTO tab2 VALUES (1, 2), 
    (1, 3), 
    (2, 2), 
    (2, 3)
-- all the columns in the index on the only table (tab1) appear
-- in the way required for the Primary table (simple column references)
SELECT DISTINCT c1, c2, c3, c4
FROM tab1
-- all the columns in the index on the only table (tab1) appear
-- in the way required for the Primary table (equality predicates) 
SELECT DISTINCT c3, c4
FROM tab1
WHERE c1 = 1
AND c2 = 2
AND c4 = 'WA'
-- all the columns in the index on tab1 appear
-- in the way required for the Primary table,
-- and all the columns in the
-- other tables appear in the way required
-- for a Secondary table
SELECT DISTINCT tab1.c1, tab1.c3, tab1.c4
FROM tab1, tab2
WHERE tab1.c2 = 2
AND tab2.c2 = tab1.c2
AND tab2.c1 = tab1.c1