Hints to Improve Subquery Execution Speed Oracle Tips by Burleson Consulting

As I noted in detail in Chapter 12, we can add hints to improve the speed of subqueries. These include the merge_aj hint to force a merge anti-join and the hash_aj hint to force a hash anti-join. For details, see Chapter 12.

• merge_aj hint   The merge_aj hint is placed in a NOT IN subquery to perform an anti-join where full-table access is preferred over index access.

• hash_aj hint   The hash_aj hint is placed in a NOT IN subquery to perform a hash anti-join in cases where a hash join is desired.

• push_subq hint   The push_subq hint causes all subqueries in the query block to be executed at the earliest possible place in the execution plan. Normally, subqueries that are not merged are executed as the last step in the execution plan. If the subquery is relatively inexpensive and reduces the number of rows significantly, then it improves performance to evaluate the subquery earlier. The push_subq hint has no effect if the subquery is using a sort merge join, or when the subquery references a remote table.

Next, let’s take a look at how queries can be tuned to use indexes.

Tuning SQL with Indexes

One of the most common techniques for removing an unwanted large-table full-table scan is to add a new index to a table. Of course, we must be cautious when adding indexes because a new index may change the execution plan for many other SQL statements. It is always a good idea to make all SQL tuning changes by using optimizer plan stability or by adding hints to the SQL. If we determine that the query is accessing a small portion of the table rows (< 40 percent of a row-sequenced table, or 12 percent of an unordered table), then adding an index will almost always improve the speed of the query. Of course, the overall speed of the index range scan as opposed to the full-table scan is not always easy to determine and is influenced by the db_block_size, the avg_row_len, and the degree of parallelism for the full-table scan. I will present a more detailed description of the estimation of query speed later in this chapter.

When tuning by adding indexes, there are two special cases of indexes that are especially useful:

• Function-based indexes   Whenever a SQL query must use a function (e.g., where upper(last_name)=’JONES’), a function-based index can remove a full-table scan. We also see a great improvement of index access of DATE datatypes by indexing DATE columns with the to_char function. One of the shortcoming of Oracle SQL is that using a built-in function on an  index will make the index unusable, and function-based indexes are a godsend for improving the access speed of queries that reference a built-in function in the SQL.

• Bitmap indexes It was a common misconception that bitmap indexes were only appropriate for columns with a very small number of distinct values—say, less than 50. Current research in Oracle8i has shown that bitmap indexes can substantially improve the speed of queries using columns with up to 1000 distinct values, because retrieval from a bitmap index is done in RAM and is almost always faster than using a traditional B-tree index. Most experienced Remote DBAs will look for columns with fewer than 1000 distinct values, build a bitmap index on this column, and then see if the query is faster.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.