Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Evaluating Table Join Order
Oracle Tips by Burleson

One of the most time-consuming phases of SQL parsing is determining the join order for large n-way table joins. For example, in a 7-way table join, Oracle must evaluate 7! possible table join combinations. Seven factorial is 7*6*5*4*3*2*1 = 5,040 possible table join combinations. It is no wonder that the CBO can often take an entire hour to evaluate the join combinations for large n-way table joins.

When evaluating table join orders, the CBO builds a decision tree (Figure 16-12). For each possible table join combination, Oracle estimates the cost from the CBO statistics.

Figure 12:A CBO decision tree for table join ordering

Once the CBO has completed all permutations (or optimizer_max_permutations is exceeded), Oracle will choose the table join orders with the lowest estimated cost.

Of course, the solution is to manually tune the query and re-sequence the table names in the from clause and use the ordered hint to direct the CBO to join the tables in the order specified in the from clause. If you cannot alter the source code for the SQL because it is being generated from a dynamic SQL generator, you can create a stored outline to join the tables in the fastest order and completely bypass the parsing process. For details on using optimizer plan stability (stored outlines), see Chapter 13.

You can also tweak the optimizer_max_permutations parameter to limit the amount of time spent evaluating table join orders, and you can get details on this procedure in Chapter 12. In general, you should manually determine the join order from the size of the result sets from each table.

Tuning Distributed SQL Table Joins

With Oracle databases commonly being distributed across the globe, there are some important considerations regarding table joins for distributed queries. There are two permutations of distributed joins:

  • Remote-to-remote join  This is a case where both remote tables reside at the remote site and are joined at the remote site. For these remote joins, you should create a view on the remote database that specifies the join, thereby forcing the remote site to be the driving site for the table join (Figure 16-13).

Figure 13: A remote-to-remote table join

  • Remote-to-local join  This is a case where one table resides locally and the other table resides at a remote site. For this class of queries, we always use the CBO and carefully ensure that an unnecessary full-table scan is not being performed at the remote site. This is most commonly done by ensuring that an index exists on the join column and verifying the execution plan by explaining the subquery on the remote site (Figure 16-14).

Figure 14: A remote-to-local table join

To illustrate a distributed join, consider the following query that joins a local table to a remote table using an equi-join condition in the where clause:

   customer.customer_nbr = order.customer_nbr
   customer_nbr = :var1
order by

Here we join a local customer table with a remote table using Net8 and a database link to reference the remote table. Of course, the local database will not be aware of the statistics or dictionary information on the remote table, and we also have the issue of sorting the result set.

In a distributed SQL query, the rows are fetched from the remote database and sorted on the Oracle database that initiated the request. Hence, you may see severe impact on the local TEMP tablespace when many remote queries are issued. You can often circumvent this sorting issue by forcing access against the remote table with an index hint.

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


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



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

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter