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


 

HTML Text

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:

select
   customer_name,
   order_nbr
from
   customer,
   order@new_york
where
   customer.customer_nbr = order.customer_nbr
and
   customer_nbr = :var1
order by
   customer_name,
   order_nbr;

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