SQL Statements for Improved Performance

Make Your SQL Statements Readable

Even though readability doesn't affect the actual performance of SQL statements, good programming practice calls for readable code. Readability is especially important if you have multiple conditions in the WHERE clause. Anyone reading the clause should be able to determine whether the tables are being joined properly and should be able to understand the order of the conditions.

Try to read this statement:

SQL> SELECT EMPLOYEE_TBL.EMPLOYEE_ID, EMPLOYEE_TBL.NAME,EMPLOYEE_PAY_TBL.SALARY,EMPLOYEE_PAY_TBL.HIRE_DATE
  2  FROM EMPLOYEE_TBL, EMPLOYEE_PAY_TBL
  3  WHERE EMPLOYEE_TBL.EMPLOYEE_ID = EMPLOYEE_PAY_TBL.EMPLOYEE_ID AND
  4  EMPLOYEE_PAY_TBL.SALARY > 30000 OR (EMPLOYEE_PAY_TBL.SALARY BETWEEN 25000
  5  AND 30000 AND EMPLOYEE_PAY_TBL.HIRE_DATE < SYSDATE - 365);

Here's the same query reformatted to enhance readability:

SQL> SELECT E.EMPLOYEE_ID, E.NAME, P.SALARY, P.HIRE_DATE
  2  FROM EMPLOYEE_TBL E,
  3       EMPLOYEE_PAY_TBL P
  4  WHERE E.EMPLOYEE_ID = P.EMPLOYEE_ID
  5    AND P.SALARY > 30000
  6     OR (P.SALARY BETWEEN 25000 AND 30000
  7    AND P.HIRE_DATE < SYSDATE - 365);

--------------------------------------------------------------------------------
NOTE: Notice the use of table aliases in the preceding query. EMPLOYEE_TBL in line 2 has been assigned the alias E, and EMPLOYEE_PAY_TBL in line 3 has been assigned the alias P. You can see that in lines 4, 5, 6, and 7, the E and P stand for the full table names. Aliases require much less typing than spelling out the full table name, and even more important, queries that use aliases are more organized and easier to read than queries that are cluttered with unnecessarily long full table names.
--------------------------------------------------------------------------------

The two queries are identical, but the second one is obviously much easier to read. It is very structured; that is, the logical components of the query have been separated by carriage returns and consistent spacing. You can quickly see what is being selected (the SELECT clause), what tables are being accessed (the FROM clause), and what conditions need to be met (the WHERE clause).