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.
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