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










SQL Join Operations

Oracle Tips by Burleson Consulting

While we will be exploring SQL tuning with joins in Chapter 14, we should have a brief review so that you understand how the type of join operation affects the execution plan for the SQL. While we will investigate each of these join techniques in detail in Chapter 16, let’s take a quick survey of the most common join techniques.

Nested Loops Join

A nested loop join is an operation that has two tables, a smaller inner table and an outer table. Oracle compares each row of the inner set with each row of the outer set and returns those rows that satisfy a condition (see Figure 3-7). A nested loop join is commonly seen in conditions where an index exists on the inner table. If we use nested loop joins, we need to make sure that the proper driving table and the proper driving set are used by the query.

Figure 7: A nested loop join

The nested loop join has the fastest response time in many cases (especially with small intermediate result row sets), but the hash join often offers the best overall throughput and faster performance where the intermediate row set is very large.

Hash Join

A hash join is an operation that performs a full-table scans on the smaller of the two tables (the driving table) and then builds a hash table in RAM memory. The hash table is then used to retrieve the rows in the larger table (see Figure 3-8). There are several types of hash joins, including the hash anti-join for cases of SQL that contains a not in clause followed by a subquery, and a hash semi-join. A hash join is a special case of a join that joins the table in RAM memory. In a hash join, both tables are read via a full-table scan (normally using multiblock reads and parallel query), and the result set is joined in RAM. This procedure can sometimes be faster than a traditional join operation.

Figure 8: A hash join operation

Oracle provides the hash_multiblock_io_count initialization parameter to determine the number of multiblock reads that are performed by hash joins. We also have the hash anti-join. In the case of a hash anti-join, the SQL optimizer uses a nested loops algorithm for not in subqueries by default, unless the initialization parameter always_anti_join is set to merge or hash and various required conditions are met that allow the transformation of the not in subquery into a sort-merge or hash anti-join. You can place a merge_aj or hash_aj hint in the not in subquery to specify which algorithm the optimizer should use.

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