Tips for writing more efficient SQL
Space doesn’t permit me to discuss every detail of Oracle tuning, but I can share some general rules for writing efficient SQL in Oracle regardless of the optimizer that is chosen. These rules may seem simplistic but following them in a diligent manner will generally relieve more than half of the SQL tuning problems that are experienced:
· 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 for NULL, consider 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 (a.k.a. FBI). Better yet, re-design the schema so that common where clause predicates do not need transformation with a BIF:
where salary*5 > :myvaluewhere substr(ssn,7,4) = "1234"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) = 1234
· 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.
Remove unnecessary large-table full-table scans—Unnecessary full-table scans cause a huge amount of unnecessary I/O and can drag-down an entire database. The tuning expert first evaluates the SQL based on the number of rows returned by the query. The most common tuning remedy for unnecessary full-table scans is adding indexes. Standard b-tree indexes can be added to tables, and bitmapped and function-based indexes can also eliminate full-table scans. In some cases, an unnecessary full-table scan can be forced to use an index by adding an index hint to the SQL statement.
Cache small-table full-table scans—In cases where a full-table scan is the fastest access method, the administrator should ensure that a dedicated data buffer is available for the rows. In Oracle8 and beyond, a small table can be cached by forcing it into the KEEP pool.
Verify optimal index usage—Oracle sometimes has a choice of indexes, and the tuning professional must examine each index and ensure that Oracle is using the proper index.
Materialize your aggregations and summaries for static tables - One features of the Oracle 10g SQLAccess advisor is recommendations for new indexes and suggestions for materialized views. Materialized views pre-join tables and pre-summarize data, a real silver bullet for data mart reporting databases where the data is only updated daily.
