Boost Oracle Performance by forcing Query Order!!

 Author: Jim D Powell

Oracle has blessed database administrators with a large number of hints for tuning SQL queries. These include index hints, join hints, locking hints, query hints and the list goes on.

In general we trust optimizer that it will take the right decision. And this is really true that almost 95 percent of the time we see optimizer making the right decision. However things do not go smooth always. Same is the case with optimizer where it encounters nasty SQL queries and has a hard time with it.

Optimizer Plans:

No matter how hard time your query is giving to optimizer but if you have proper knowledge of the application, you know how the code is used and you know the desired output then you can easily guide the optimizer to choose the right plan. You can use Oracle stored outlines that will help you force optimizer to use a given execution plan whenever a particular query is executed.

Query Hints:

You can use query hints to ease this task. Today we will discuss how you can improve performance by forcing optimizer to execute queries in a specified order. You can find this hint as ‘ORDERED' hint in Oracle and ‘OPTION (FORCE ORDER)' query hint in SQL Server.

Complex Queries:

Consider a scenario where you are running a reporting system based off an OLTP database schema. You have complex queries that join about nine tables. As there are large number of joins so your optimizer will have to do a number of computations so as to choose the right plan. It will most probably compute the right join order based on the data distribution, statistics, indexes, filter criteria and so on. Normally your optimizer will have to compute n! different ways of joins between the tables in the FROM clause.

A nine table join will most probably require more than 360000 different combinations to be evaluated. Amongst them many combinations will be ruled out based on different criteria like filters, indexes on those filtered and joined columns, columns selected etc. Even if you have to evaluate x number of such permutations, still it takes time.

Oracle ORDERED Hint:

The ORDERED hint in Oracle can make your life easy by forcing optimizer to join tables in the order as they are listed in the FROM clause of the SQL statement. Remember in order to benefit from this hint you should have an eagle eye on the system so that you should make right decision for the table you want to start the execution plan from.

This eliminates the cumbersome and time-consuming operation by forcing optimizer to follow a pre-defined path. Below code snippet shows the usage of Oracle Ordered hint.

select /*+ ordered */
   myColumn1, myColumn2, . . .
from myTable1 A
   inner join myTable2 B
      on A.myColumnx = B.myColumny
         inner join…
         .
         .
         .
where ….

In SQL Server you can use the OPTION(FORCE ORDER) hint to accomplish the same task.

select
   myColumn1, myColumn2, . . .
from myTable1 A
   inner join myTable2 B
      on A.myColumnx = B.myColumny
         inner join…
         .
         .
         .
where ….
option (force order)

Oracle ordered_predicates hint:

You can also use the Oracle ‘ordered_predicates' hint to force the optimizer to evaluate the boolean predicates in a specific order. The ordered_predicates hint is specified in the WHERE clause of a query Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Great Tips to Resolve Tablespace Fragmentation Issues!!
   Oracle Recovery: Missing Redo Logs & Controlfiles!!
   Performance Boost by improving Optimizer Statistics!
   Reusing Databases - A good approach