Mastering Oracle SQL

Chapter 14. SQL Best Practices

Writing efficient SQL statements requires experience. You can write a SQL query in many different ways, each giving the same result, but one may be a hundred times slower than another. In this chapter, we discuss some tips and techniques that will help you write efficient SQL statements.

Know When to Use Specific Constructs

Depending on the circumstances, certain SQL constructs are preferable to others. For example, use of the EXISTS predicate is often preferable to IN. The same is not true for NOT EXISTS versus NOT IN. The next sections discuss the usage of such constructs.

EXISTS Is Preferable to DISTINCT

The DISTINCT keyword used in a SELECT clause eliminates duplicate rows in the result set. To eliminate those duplicates, Oracle performs a sort, and that sort requires time and disk space. Therefore, avoid using DISTINCT if you can tolerate having duplicate rows returned by a query. If you can’t tolerate the duplicate rows, or your application can’t handle them, use EXISTS in place of DISTINCT.

For example, assume you are trying to find the names of customers who have orders. Your query has to be based on two tables: CUSTOMER and CUST_ORDER. Using DISTINCT, your query would be written as follows:

SELECT DISTINCT C.CUST_NBR, C.NAME
FROM CUSTOMER C, CUST_ORDER O
WHERE C.CUST_NBR = O.CUST_NBR;

The corresponding execution plan for this query is as follows. Note the SORT operation, which is a result of DISTINCT being used.

Query Plan ----------------------------------------- ...