Tips for writing more efficient SQL

General rules for writing efficient SQL in Oracle regardless of the optimizer that is chosen. These rules may seem simple but following them in a diligent manner will help:

Rewrite complex subqueries with temporary tables - Oracle created the global temporary table (GTT) and the SQL WITH operator to help divide-and-conquer complex SQL sub-queries (especially those with with WHERE clause subqueries, SELECT clause scalar subqueries and FROM clause in-line views). Tuning SQL with temporary tables (and materializations in the WITH clause) can result in amazing performance improvements.

Use minus instead of EXISTS subqueries - Some say that using the minus operator instead of NOT IN and NOT Exists will result in a faster execution plan.

Use SQL analytic functions - The Oracle analytic functions can do multiple aggregations (e.g. rollup by cube) with a single pass through the tables, making them very fast for reporting SQL.

Re-write NOT EXISTS and NOT EXISTS subqueries as outer joins - In many cases of NOT queries (but ONLY where a column is defined as NULL), you can re-write the uncorrelated subqueries into outer joins with IS NULL tests. Note that this is a non-correlated sub-query, but it could be re-written as an outer join.

select book_key from book where book_key NOT IN (select book_key from sales);

Below we combine the outer join with a NULL test in the WHERE clause without using a sub-query, giving a faster execution plan. select b.book_key from book b, sales s where b.book_key = s.book_key(+) and s.book_key IS NULL;


Index your NULL values - If you have SQL that frequently tests SQL, creating an index on NULL values. To get around the optimization of SQL queries that choose NULL column values (i.e. where emp_name IS NULL), we can create a function-based index using the null value built-in SQL function to index only on the NULL columns.

Leave column names alone - Never do a calculation on an indexed column unless you have a matching function-based index . Better yet, re-design the schema so that common where clause predicates do not need transformation with a BIF: where salary*5 > :myvalue where substr(ssn,7,4) = "4589" where to_char(mydate,mon) = "january"


Avoid the use of NOT IN or HAVING. Instead, a NOT EXISTS subquery may run faster (when appropriate).


Avoid the LIKE predicate = Always replace a "like" with an equality, when appropriate.


Never mix data types - If a WHERE clause column predicate is numeric, do not to use quotes. For char index columns, always use quotes. There are mixed data type predicates:

where cust_nbr = "123" where substr(ssn,7,4) = 4589


Use decode and case - Performing complex aggregations with the “decode” or "case" functions can minimize the number of times a table has to be selected.


Don't fear full-table scans - Not all OLTP queries are optimal when they uses indexes. If your query will return a large percentage of the table rows, a full-table scan may be faster than an index scan. This depends on many factors, including your configuration (values for db_file_multiblock_read_count, db_block_size), query parallelism and the number of table/index blocks in the buffer cache.


Use those aliases - Always use table aliases when referencing columns.