SQL Statements for Improved Performance

articles: 

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

Comments

There aremany different ways to write code - and this is going to be that way at least for now since PL/SQL code is different from other languages.
I have seen several ways of writing such queries:
- keywords aligned to left
- keywords aligned to left but logical operators aligned to right
- several ways of writing brackets
- writing every column/table/etc. one per line or in one line if there are less than x operands
- writing everything in 1 line unless it is longer than pagewidth
- alignment of the expressions can be: unaligned (worst), aligned per group or aligned for all the query

I will present some hints for your suntax. You can (or not) agree to them, but those are my conclusions after experimenting wiht different formattings.
- you can easily get lost in the indentation if you use (commonly used) right alignment
- your eyes can skip brackets like in your example - then you could read the logic of this query incorrectly without knowing that
- commas at the end of line shows that there are other arguments coming in the next line - while you could move them to the next line keeping the indentation and using them as "keywords"