A DISTINCT (and the corresponding sort) can be eliminated from a query
if a uniqueness condition exists that ensures that no duplicate values will
be returned. If no duplicate values are returned, the DISTINCT node is superfluous,
and transforms the
statement internally into one without the DISTINCT keyword.
The requirements are:
- 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
For example: 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