# 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

- Quick DISTINCT scans
  Derby can use a hash table instead of a sorter to eliminate duplicates when performing a DISTINCT in the following cases.

Parent topic: Non-cost-based sort avoidance (tuple filtering)
Related concepts
GROUP BY