UNION ALL View Performance

Structure queries to evaluate the DISTINCT operator before the ORDER BY, where the sort order is ASC.

Certain optimizations, such as pushing a DISTINCT operator into a UNION ALL view, are not applied when the ORDER BY is DESC because the optimization that evaluates DISTINCT below a UNION does not apply to DESC order. For example, the following query would impact performance:

SELECT DISTINCT state FROM testVU ORDER BY state DESC;

To work around this performance issue, queries should have the DISTINCT operator evaluated before the ORDER BY, where the sort order is ASC and the optimization can be applied:

SELECT c.state FROM (SELECT DISTINCT state 
  FROM testVUA) c
ORDER BY c.state DESC;