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:
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.