SQL Coding Guidelines

Finally, we'll discuss some good and poor ways to code SQL statements. The following are some guidelines for SQL statement coding that will help both the rule-based and cost-based optimizers:

• Do use the IN operator instead of NOT. Try to avoid using the NOT command by using >=, <=, and so on.

• Do use array processing whenever possible (Export, and Pro*C applications).

• Do use hints to ensure the desired execution plan results.

• Don't use HAVING without a WHERE clause.

• Don't use calculations in the WHERE clause on indexed columns. Unless the intended behavior is to disable index use, any function on indexed columns will ignore the index.

• Don't use an index if more than 20% of the rows will be returned by the query.

• Don't use subqueries if other solutions exist (PL/SQL loop, for example).

• Don't write applications that use SQL execution plan defaults. Oracle Corporation makes no guarantees that default behavior will be maintained in future releases, or even between different hardware platforms.