Good Practices for SQL

o Keywords should be entered in upper case

o All keywords should be placed on a new line

o Do a proper indentation.

o Constants, program variables etc. should be on the right hand side of a WHERE or HAVING clause.

o Columns requiring to be outer joined should appear on the right hand side of a WHERE or HAVING clause.

o Table aliases should be used if queries uses same table more than once in FROM clause. Aliases speeds up the parsing of query.

o Its Good to put Joins First and condition at the end in WHERE clause.

o The Order of tables in FORM clause is also important. Put table returning the least number of rows in the last of FROM list.

o Indexes will not work effectively in following cases

o If we modify the indexed column in WHERE clause. Like if we manipulate as trunc(date), to_char(num_id), etc.

o If there is no ‘WHERE’ Clause or Indexed column is not used in WHERE clause.

o The search is for ‘NULL’ or ‘NOT NULL’

o Use EXISTS operator. If we use EXISTS operator once the match was found the oracle will stop and do not perform Full Table Scan.

o Don’t create unnecessary joins like joining with SYS_PARAM tables.

o Avoid using DISTINCT, UNION, MINUS, INTERSECT, ORDER BY and GROUP BY keywords. Because DISTINCT requires one sort, other set operator required more than one sort.

o Don’t use !=, Unless it’s absolutely required. The use of this operator disables the use of indexes.

o Oracle cursor should be declared at the start of the program।

o Avoid using * in INSERT statement because if a table is modified then inserts could fail.