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.
