SQL statements should be formated consistently to aid readability.
Each expression in the SELECT clause, each table in the FROM clause, each condition in the WHERE clause and GROUP BY, ORDER BY, etc should be placed on a new line.
Each keyword should be placed on a new line and lined up with the the previous keyword
Each line starting with a non keyword should be lined up with the second word on the previous line.
Sub-queries should be idented to line up with the second word on the previous line.
Within each WHERE clause align each line around the condition
eg., around the '='
Constants and variables should be on the right hand side of a WHERE clause.
eg., WHERE employee_number = 123
Outer join conditions should appear on the right hand side of a WHERE clause.
eg., WHERE e.employee_number = d.employee_number (+);
Where multiple tables are used, always refer to a column by either using an alias or using the fully qualified name.
eg., e.employee_number or abc_employee.employee_number
Use meaningful aliases for tables by taking the first letter of each part of the table name following the project prefix, not just a, b, c.
eg., for the table abc_employee_address use ea.
Optionally, keywords can be entered in uppercase. However, consistency must apply throughout.
eg.,
SELECT e.last_name,
e.first_name,
ea.address_line1,
ea.address_line2,
eh.start_date
FROM abc_employee_history eh,
abc_employee_address ea,
abc_employee e
WHERE e.employee_number = 123
AND ea.employee_number = e.employee_number
AND eh.employee_number = ea.employee_number
AND EXISTS
(SELECT 1
FROM abc_current_employees
WHERE employee_number = e.employee_number)
ORDER BY e.last_name, e.first_name;
Do not modify indexed columns with functions such as RTRIM, TO_CHAR, UPPER, TRUNC as this will disable the index. Instead perform the modification on the constant side of the condition.
When writing sub-queries make use of the EXISTS operator where possible as Oracle knows that once a match has been found it can stop and avoid a full table scan.
When writing queries that will be analysed by the Rule rather than Cost based optimiser, order the FROM clause such that the driving (or most restrictive) table is last.
UNION statements should be written as a UNION ALL where possible to avoid unneccessary sorting to extract duplicates.
All SQL statements must have an EXPLAIN PLAN performed on them against the production database before deployment.
eg.,
explain plan set statement_id = 'js' for
SELECT e.last_name,
e.first_name,
ea.address_line1,
ea.address_line2,
eh.start_date
FROM abc_employee_history eh,
abc_employee_address ea,
abc_employee e
WHERE e.employee_number = 123
AND ea.employee_number = e.employee_number
AND eh.employee_number = ea.employee_number
AND EXISTS
(SELECT 1
FROM abc_current_employees
WHERE employee_number = e.employee_number)
ORDER BY e.last_name, e.first_name;
SELECT LPAD(' ', 2 * level) ||
operation || ' ' ||
options || ' ' ||
object_name explain_plan
FROM plan_table
WHERE statement_id = 'js'
CONNECT BY prior id = parent_id
AND statement_id = 'js'
START WITH id = 1;
DELETE
FROM plan_table
WHERE statement_id = 'js';
To prevent Oracle passwords appearing on the output of a ps listing the following techniques can be used.
When in intractive mode simply allow the Oracle tool being utilised to prompt for the password, ie., use 'sqlplus' NOT 'sqlplus userid/password' on the command line.
When in non-interactive mode use a here document embedded within a shell script, ie.,
sqlplus << !EOF
userid/password
...
sql commands here
...
!EOF
