Redundant SELECT Clauses

Here's an example of a redundant clause. Suppose that Table1 has a compound PRIMARY KEY consisting of (column1 , column2) . This SQL statement:

 SELECT DISTINCT column1, column2    FROM Table1 

then has a redundancywhen all columns in a PRIMARY KEY or UNIQUE constraint are in a query's select list, and there are either no joins at all or the only joins are PRIMARY KEY to FOREIGN KEY, then the DISTINCT operator is redundant. You can improve performance by removing DISTINCT:

 SELECT column1, column2    FROM Table1 GAIN: 5/8 

Once again, though, there are some caveats. It's dangerous to throw out the DISTINCT if:

• The constraint is deferrablebecause the constraint could have been violated earlier in the transaction or, if your isolation level is READ UNCOMMITTED, another program could have violated the constraint.

• There is a possibility that the constraint could be disabled or dropped at some future time. (Remember that there is no way to make a trigger for ALTER TABLE.)

• There is a possibility that the query will select duplicate rows containing NULLs.

Optimizations like these require some extra trouble, because your program must check whether a constraint has been dropped and adapt accordingly . Unfortunately, there is no trigger for the ALTER TABLE . . . DROP CONSTRAINT statement, so you'll have to check the INFORMATION_SCHEMA for the information each time.

As optimizers improve, this technique will lose utility. So far, we are seeing some DBMSs that claim to do some constraint- related optimizations during SELECT. However, we've yet to see any DBMS that does the whole job, and we doubt that we will see any for a long while.

The Bottom Line: Redundant SELECTs

If a constraint exists with logic that duplicates a query's search condition, get rid of the redundant clause (unless NULLs are a possibility).