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:
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).