. There are many situations where "SELECT DISTINCT" and "SELECT ALL" can be proven to yield the same results, but one of them costs a lot more than the other. It would be useful to be able to let the user do a "SELECT DISTINCT", but let the optimizer pick a cheaper "SELECT ALL" strategy, if it's going to give the right answer.

Very often, the proof that "SELECT ALL" and "SELECT DISTINCT" are equivalent for a given query depends on the constraints placed on the data. It doesn't matter whether the constraints are enforced by the DBMS or the transactions that write the data. As long as we can be guaranteed that the constraint holds, the optimizer can do a better job knowing the constraints than not
knowing them.

Declaring Unenforced Constraints

found at:  For Tech One!