DISTINCT

Tuple filtering is applied for a DISTINCT when the following criteria are met:

The SELECT list is composed entirely of simple column references and constants.
All simple column references come from the same table and the optimizer has chosen the table in question to be the outermost table in the query block.
The optimizer has chosen an index as the access path for the table in question.
The simple column references in the SELECT list, plus any simple column references from the table that have equality predicates on them, are a prefix of the index that the optimizer selected as the access path for the table.

Note: The set of column references must be an in-order prefix of the index.

Here is the most common case in which tuple filtering will be applied:

SELECT DISTINCT c1 FROM t1

Equality predicates allow tuple filtering on the following:

SELECT DISTINCT c2
FROM t1
WHERE c1 = 5

SELECT DISTINCT c2, c4
FROM t1
WHERE c1 = 5 and c3 = 7
-- the columns don't have to be in the
-- same order as the index
SELECT DISTINCT c2, c1
FROM t1