Database Reference
In-Depth Information
Distinct Placement
The purpose of
distinct placement
is to eliminate duplicates as soon as possible. This is a cost-based query
transformation that's available as of version 11.2 only.
The
DISTINCT
operator eliminates duplicate rows from a result set. When it's specified in a query along with one
or several joins, conceptually the database engine should process the
DISTINCT
operator
after
the joins have already
been resolved. To achieve best performance, though, some cases call for eliminating duplicates before processing
joins. Eliminating duplicates earlier keeps intermediate result sets as small as possible, and less processing is required
for joins.
In the following example, based on the
distinct_placement.sql
script, there is a parent-child relationship
between the two tables. Furthermore, you can presume that the child table (
t2
) contains many more rows than the
parent table (
t1
):
SELECT DISTINCT t1.n, t2.n
FROM t1, t2
WHERE t1.id = t2.t1_id
When the number of distinct values of
t2.n
is much lower than the number of rows stored in the child table,
distinct placement produces the following query. Notice the additional
DISTINCT
operator that, applied to the data of
the child table, eliminates the duplicates before joining the parent table:
SELECT DISTINCT t1.n, vw_dtp.n
FROM t1, (SELECT DISTINCT t2.t1_id, t2.n
FROM t2) vw_dtp
WHERE t1.id = vw_dtp.t1_id
Distinct Elimination
The purpose of
distinct elimination
, which is available as of version 10.2.0.4, is to remove
DISTINCT
operators
that aren't required to guarantee that the result set doesn't contain duplicates. This is a heuristic-based query
transformation that can be applied when a
SELECT
clause references, without modifying them, all columns of a
primary key, all columns of a unique key that's not nullable, or the rowid.
The following example is based on the
distinct_elimination.sql
script. Notice that the primary key of the
table is defined on the column named
id
:
SELECT DISTINCT id, n
FROM t
The presence of the
id
column, which is the primary key of the table, in the
SELECT
clause guarantees the
uniqueness of the rows. Hence, distinct elimination produces the following query:
SELECT id, n
FROM t
Group-by Placement
The purpose of
group-by placement
is basically the same as that of
distinct placement
. The only obvious difference is
the types of queries to which they're applied. Whereas the former is used for queries containing a
GROUP BY
clause, the
latter is used for queries containing a
DISTINCT
operator. Group-by placement is a cost-based query transformation
