Systems Engineering and RDBMS

Forcing the optimizer to execute the query in a specific order

Posted by decipherinfosys on November 13, 2007

There are a lot of different kinds of hints (query hints, index hints, join hints, locking hints etc.) available in Oracle and SQL Server to tune those particular offensive SQL queries that the optimizer is having a hard time with. More than 95% of the time, we have seen the optimizer making the right decision, however at times, knowledge of the application, how the code is being used and what the desired output is can help us to guide the optimizer into coming up with the right plan – especially since we would like it to come up with that same plan each time. There are a lot of ways of doing this – in Oracle, one can use plan stability feature (stored outlines), in SQL Server 2005, one can use the plan guides feature to force the optimizer into always using a given execution plan whenever a particular query text gets executed.

One such option also is to make use of the query hints. In this particular blog post, we will discuss one such hint. This hint is called the ORDERED hint in Oracle and is called the “OPTION (FORCE ORDER)” query hint in SQL Server. Consider that in a reporting system based off an OLTP database schema, you have a query that joins more than 8/9 tables (this is not unlikely since OLTP systems have a normalized schema). When you have that many joins, the optimizer has to compute the right join order based on the data distribution, the statistics available for it to use, the indexes in place, the filter criteria used in the query etc.. The optimizer has to go through the n! (n factorial) differnet ways of joins between the tables in the from clause. So, for a 8 table join, there are over 40,000 different combinations to evaluate – of course, many of those are ruled out very soon by the optimizer based on the different criterias – filter criteria, indexes on those filtered and joined columns, columns selected etc., however, even evaluating x number of such permutations takes time.

This is where the hint mentioned in this post comes to the rescue. The ORDERED hint in Oracle or the “OPTION (FORCE ORDER)” hint in SQL Server tells the optimizer that the tables that are listed in the FROM clause of the SQL statement need to be joined in the order that they are listed. This also means that you need to properly understand your system and need to know which table do you want to start the execution plan from i.e. which table will serve ass your driver table. This hint will help in by-passing the time consuming parsing operation by forcing the optimizer to go in a pre-defined path. Here are some sample executions to illustrate the usage of this hint in Oracle and SQL Server:

Oracle:

select /*+ ordered */
col1,
col2,
.
.
.
from tableA A
inner join tableB B
on A.colx = B.coly
inner join…
.
.
.
where ….

SQL Server:

select
col1,
col2,
.
.
.
from tableA A
inner join tableB B
on A.colx = B.coly
inner join…
.
.
.
where ….
option (force order)

In addition, Oracle also has another hint called the “ordered_predicates” hint. This hint is specified in the WHERE clause of the query and can be used to force the optimizer into evaluating the boolean predicates in a specific order.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: