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










Cost-Based Table Joins

Oracle Tips by Burleson Consulting

If we have a query that joins multiple tables together, we want a look at the wealth of hint options available to us for changing the method by which the join takes place within Oracle. As you will remember from earlier chapters, Oracle provides three basic methods for joining tables together, the nested loop join (use_nl), the sort merge join (use_merge), and the hash join (use_hash). In addition to these basic join techniques, we can also add parallelism to improve table join speeds for sort merge queries.

One of the shortcomings of the cost-based optimizer prior to Oracle8i was that when more than four tables were joined, the cost-based optimizer almost always invoked a full-table scan on one of the tables. In it is still possible to see these suboptimal execution plans with the CBO in Oracle8i, depending upon the complexity of the table joins that you are performing.

As a general rule, nested loop joins and hash joins should always use indexes on all of the tables, and a full-table scan should never be invoked in order to service the table join, except in the case of a sort merge join. When tuning cost-based SQL statements that performs table joins, the following steps are commonly executed:

  1. Search the existing execution plan for full-table scans. If you see that the execution plan invokes a full-table scan against one of the tables, one of the first things you might want to try is to use an index hint or a rule hint in order to force all of the tables to use indexes.
  2. Try timing the execution of the query using different join techniques. For example, the use_hash hint may be used in cases where we are performing a not in subquery and we have enough RAM available to perform a hash join against the specified tables. If the query retrieves the majority of blocks in the table as determined by the Boolean conditions in the where clause, we might consider invoking a use_merge hint combined with parallel hint in order to improve the speed of the query.

Once the optimal execution plan has been verified by using the SQL*Plus set timing on command, we make the change permanent by creating a stored outline for the SQL statement. The complete procedure for doing this is fully described in Chapter 13.

Next, let's take a look at tuning cost-based SQL statements that involve subqueries.

Tuning Cost-Based SQL Statements with Subqueries

When we see a SQL statement that specifies a subquery, we first need to carefully check the where clause and determine if the subquery is a noncorrelated subquery or a correlated subquery. A correlated subquery is a query whereby the key in the subquery is correlated (using the = operator) with a column that is selected in the outer query. On the other hand, a noncorrelated subquery is a query where the subquery executes independently of the outer query, passing a result set to the outer query at the end of its execution. Noncorrelated subqueries are commonly seen when using the IN, NOT IN, EXISTS, and NOT EXISTS SQL clauses.

TIP: The use_hash hint was originally designed for equi-joins, such as NOT IN joins, anti-joins, and joins with equality conditions (such as where cust.cust_nbr = order.cust_nbr). You should always try the use_hash hint when tuning these types of queries.

Rewriting Subqueries into Standard Joins

To understand sub-queries, let’s examine three equivalent SQL queries that produce the same result with different syntax. In many cases, sub-queries can be structured to use either of the subquery types, and they can sometimes be replaced by a standard join. For example, consider a query to display all students who received an “A” in any class. This can be written as a standard join, a noncorrelated subquery, or a correlated subquery. The standard join will always run faster than a subquery, and individual SQL statements should be carefully evaluated to see if the subquery can be replaced with a standard join.

A Standard Join

    student.student_id = registration.student_id
    registration.grade = 'A';

A Non-correlated Subquery

    student_id =
    (select student_id
        from registration
        grade = 'A'

A Correlated Subquery

    0 <
    (select count(*)
        from registration
        grade = 'A'
        student_id = student.student_id

Here we see the same query result, specified in three ways. This ability to specify the same query in many different forms is very important to those tuning SQL statements. Whenever possible, you want to replace subqueries with a standard join, and you must be familiar with all of the possible ways to manually rewrite a query to make the execution plan more efficient.

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