BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation










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.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter