Contents Index Types of semantic transformations Subquery unnesting

ASA SQL User's Guide
  Query Optimization and Execution
    Semantic query transformations
      Types of semantic transformations

Unnecessary DISTINCT elimination

Sometimes a DISTINCT condition is unnecessary. For example, the properties of one or more column in your result may contain a UNIQUE condition, either explicitly, or implicitly because it is in fact a primary key.

Examples 

The distinct keyword in the following command is unnecessary because the product table contains the primary key p.id, which is part of the result set.

SELECT DISTINCT p.id, p.quantity
FROM product p

p<seq>

The database server actually executes the semantically equivalent query:

SELECT p.id, p.quantity
FROM product p

Similarly, the result of the following query contains the primary keys of both tables so each row in the result must be distinct.

SELECT DISTINCT *
FROM sales_order o JOIN customer c
   ON o.cust_id = c.id
WHERE c.state = 'NY'

c<seq> JNL o<ix_sales_cust>


Contents Index Types of semantic transformations Subquery unnesting