TIPS for writing good optimized SQL Query

Here are some of the TIPS for writing good optimized
SQL Query (apart from analyzing through EXPLAIN PLAN):

## If you specify 2 tables in the FROM clause of a
SELECT statement, the parser will process the tables
from right to left, so the table name you specify last
will be processed first. In this case you have to
choose one table as driving table. Always choose the
table with less number of records as the driving
table.

## If three tables are being joined, select the
intersection tables as the driving table. The
intersection table is the table that has many tables
dependent on it.

## Never compare NULL to anything else. All
expressions return NULL if one of the operands is
NULL. This is applicable for all operators except
Concatenation operator (||).

## Use DECODE when you want to scan same rows
repetitively or join the same table repetitively.

## Always use table alias and prefix all column
names with the aliases when you are using more than
one table.

## Use NOT EXISTS in place of NOT IN.

## Join Tables in place of using EXISTS through
Sub-Query.

## Use EXISTS in place of DISTINCT.

## Never use NOT on an indexed column. Whenever
Oracle encounters a NOT on an index column, it will
perform full-table scan.

## Never use a function / calculation on an
indexed column. If there is any function is used on
an index column, optimizer will not use index. Use
some other alternative.

## There are three good reasons why it is better
to name the columns in a query rather than to use
"select * from ...".

· Network Traffic is reduced

· The Code is easier to understand

· It could save the need for changes in
future.

## It is also better to use bind variables in
queries. That way the query becomes generic and
therefore re-usable.

## It is a good practice to use a standard syntax
for wiring SQL queries. Incase, the same query has
different standard then Oracle will re-parse the
statement again.
## Combine Multiples Scans with CASE Statements.