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




Table Access Hints
Oracle Tips by Burleson

The following hints are used for specialized table access methods. In sophisticated queries, this class of hints can be used to direct each step in the access of multiple table accesses within a query.

The full Hint

The full hint requests the bypassing of indexes and invokes a full-table scan. This is often used in conjunction with a parallel hint. The most common use of the full hint is in cases where you have determined that an index range scan retrieves a significant number of table blocks and a parallel full-table scan will improve the speed of the query.

Cluster Table Hints

Oracle provides a specialized table storage mechanism as an alternative to row-sequenced tables. Oracle clusters are used in cases where two tables have a one-to-many relationship and the vast majority of queries access the tables from owner to member (Figure 12-5).

Figure 5: An Oracle cluster with multiple tables

This table storage technique stores the owner and member table rows on adjacent blocks such that a single block I/O will retrieve rows from both tables. In order to improve the speed of table joins in a cluster, Oracle provides a hash hint and a cluster hint.

The use_hash Hint

The use_hash hint explicitly chooses a hash scan to access the specified cluster table.

select /*+ hash */
   emp e,
   dept d
  e.deptno = d.deptno
   deptno = 20;

The cluster Hint

The cluster hint explicitly chooses a cluster scan to access the specified table.

select /*+ cluster */
   emp e,
   dept d
  e.deptno = d.deptno
   deptno = 20;

The no_expand Hint

The no_expand hint prevents the cost-based optimizer from considering OR expansion for queries having OR conditions or IN lists in the where clause. Usually, the optimizer considers using OR expansion and uses the no_expand method if it decides the cost is lower than not using it. This OR expansion is related to optimizer internals and does not mean that the logic itself will be changed and return a different result set.

The nocache Hint

In Oracle8 and beyond, the nocache hint directs that table blocks specified for the KEEP pool be placed  at the midpoint of the DEFAULT pool instead of the KEEP pool.

This hint is rarely used in SQL tuning because all of the small tables should properly be placed in the KEEP pool, and there is never a need to change this behavior.

The ordered Hint

In the cost-based optimizer, the ordered hint requests that the tables should be joined in the order that they are 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.

For example, the following query uses the ordered hint to join the tables in their specified order in the from clause (emp, dept, sal, bonus). We further refine the execution plan by specifying that the emp to dept join use a hash join and the sal to bonus join use a nested loop join.

select /*+ ordered use_hash (emp, dept) use_nl (sal, bonus) */
where . . .

The ordered_predicates Hint

The ordered_predicates hint is a specialized hint that is specified in the where clause of a query, and it directs the order in which the Boolean predicates in the where clause are evaluated. To see how this hints works, let’s review the standard method used by the CBO to evaluate 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 order in the where clause.

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.

These evaluation rules can be overridden by using the ordered_predicates hint. This hint is the equivalent of re-sequencing Booleans in the where clause for the RBO, where the where clause items are evaluated in the order that they appear in the query.

The push_subq Hint

The push_subq hint causes all subqueries in the query block to be executed at the earliest possible place in the execution plan. Normally, subqueries that are not merged are executed as the last step in the execution plan. If the subquery is relatively inexpensive and reduces the number of rows significantly, then it improves the overall performance to evaluate the subquery as soon as possible. The push_subq hint has no effect if the subquery is using a sort merge join, or when the subquery references a remote table. For more details on this hint for SQL tuning, see Chapter 19.

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