Tuple filtering is applied for a DISTINCT when the following criteria are
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
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
WHERE c1 = 5
SELECT DISTINCT c2, c4
WHERE c1 = 5 and c3 = 7
-- the columns don't have to be in the
-- same order as the index
SELECT DISTINCT c2, c1