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

 

 

   
 

The Ordered Hint
Oracle Tips by Burleson
 

As I noted in detail in Chapter 12, Oracle must spend a great deal of time parsing n-way table joins to determine the optimal order to join the tables. The ordered hint can be used to reduce the parse time for queries that join more than five tables, and the ordered hint tells the CBO to join the tables in the same order in which they appear in the from clause. Hence, the first table after the from clause becomes the driving table, and the driving table should be the table that returns the smallest number of rows.

In the cost-based optimizer, the ordered hint requests that the tables should be joined in the order specified in the from clause, with the first table in the from clause specifying the driving table.

The ordered hint is commonly used in conjunction with other hints to ensure that multiple tables are joined in their proper order. For example, we may have a query that joins five tables together, and we want several of the joins to use a hash join and other tables to use a nested loop join. The ordered hint is very common in tuning data warehouse queries that join more than four tables together.

TIP: Large n-way table joins with seven or more tables can often take more than 30 minutes to parse the SQL. This is because Oracle must evaluate all possible table join orders. For example, with eight tables, Oracle must evaluate 8!, or 40,320, possible join combinations. Most people use the ordered hint to bypass this very expensive and time-consuming SQL parsing operation.

The ordered_predicates Hint

The ordered_predicates hint is a specialized hint that is specified in the where clause of a query; it directs the order in which the Boolean predicates in the where clause are evaluated. As we may know, Oracle should always evaluate the most restrictive predicate first, thereby reducing the size of the intermediate result sets.

Without the ordered_predicates hint, Oracle uses the following steps to evaluate the order of SQL predicates.

1.      Subqueries are evaluated before the outer Boolean conditions in the where clause.

2.      All Boolean conditions without built-in functions or subqueries are evaluated in their reverse order in the where clause, going from bottom-up, with the last predicate being evaluated first.

3.      With Boolean predicates with built-in functions, the optimizer computes the cost of each predicate and evaluates them in increasing order of their costs.

Whenever the CBO makes a mistake in the evaluation order of your predicates, these default evaluation rules can be overridden by using the ordered_predicates hint. This hint is the equivalent of resequencing Booleans in the where clause for the RBO, where the where clause items are evaluated in the order that they appear in the query. This hint is very useful in cases where you know the most restrictive predicates and you want to control the order in which Oracle evaluates conditions in the where clause.

The ordered_predicates hint is commonly used in cases where a PL/SQL function is used inside the where clause of a query.


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