This blog is the fourth in a series about misconceptions surrounding SQL tuning that are quite common. It covers the misconception that the goal of SQL tuning is only to make a SQL return all the records faster.

I still remember around 20 years ago, I was using SQL*Plus to tune my SQL statements. At that time, it was my impression that a good SQL rewrite was one that normally would return the records faster.  No SQL tuning tools were available in the market at that time, so SQL*Plus was the only product I could use to tune my SQL statements. Sometimes I had to use my watch to keep track of the speed of each rewritten SQL. When I was working with SQL statements that returned many records, I had no patience to wait for all the records to be completely returned and I normally stopped the SQL execution once the first few records were returned. It saved me a lot of time so that I could test multiple rewrites in a short time. This testing approach was good, since at that time Oracle provided only the Nested Loop join. The Nested Loop was the lowest cost join method for the machines in that age. I still remember my first project was using an Oracle database (I forgot the version number) running on a Digital machine with 48M RAM. A Sort Merge join would need too much memory for this kind of machine.

Today, we normally have multiple gigabytes of memory in our Oracle servers. Oracle now provides more join methods such as Sort Merge and Hash Join to fully utilize our machine’s memory and CPU. We should not base our decision whether a SQL is a better alternative solely on the time it takes the SQL statement to execute. We should take into account how the SQL statement is used in the application. Let me use a simple SQL to explain:

select * from employee,
              department
 where emp_dept=dpt_id

Basically for this kind of simple SQL statements, the Oracle internal SQL optimizer can perform any join method from the basic Nested Loop, Sort Merge, or Hash Join. You can use the Oracle optimization hints to influence Oracle to use any execution plan you want. For a Nested Loop join, you use the USE_NL hint or FIRST_ROWS. You may be wonder why the FIRST_ROWS hint will result in a Nested Loop join. Hash Join has to build a hash table and Sort Merge has to sort a table before they can return any records at all, so when you tell Oracle to process a SQL statement to give you the first record as fast possible, Oracle will prefer a Nested Loop in spite of its relatively higher cost. For an online query that needs to display only the first one or two pages of records from a cursor, the Nested Loop join is definitely a good choice since it provides the first records faster than the other two join methods. For example; if you have a query in a Datawindow in PowerBuider or TQuery in Delphi and the end users will not scroll down to see more than one or two pages, you can use the FIRST_ROWS hint in your SQL statement to speed up the response time for the user and save the user from having to wait for almost the entire SQL to be processed with Sort Merge or Hash Join. For example, if a SQL returns 1000 records, a Nested Loop join can quickly give you 20 records in a SQL cursor and display them on the screen. The user may only want to review the first 20 records and then close the cursor immediately, This process only spent around 20/1000=2% of the total workload it would take to retrieve the entire 1000 records for this SQL. If a Sort Merge is used when you want the first 20 records to be displayed on screen, Oracle has to finish the Sort operation first and then return the records in the Merge stage, so, most of the heavy sort operation is already done before it can return any records. Therefore you will not save much workload by breaking the cursor fetching after 20 records when using a Sort Merge join.

Here is the SQL statement with the FIRST_ROWS hint and the resulting execution plan:

select /*+First_Rows */ * from employee,
                               department
 where emp_dept=dpt_id

If you are not quite sure how the SQL statement you are tuning is going to be use in your application, it is best to use the default Oracle behavior which is good in most cases. Of course, you can use our Quest SQL Optimizer to find a better rewrite with has both a good response time (retrieving the first few records) and a good elapsed time (retrieving all records).

Let’s get back to today’s topic. You can see that a SQL rewrite which has a fast response time is normally generated by a Nested Loop join operation. The Nested Loop join is good only in certain circumstances such as for a small result set and when the driving path is correct (that is when the smaller table or result-set is looping a large table or result-set using a unique index search). Other than that, the Sort Merge or Hash Join will be a safe choice. It is common in modern relational databases that the cost of the Nested Loop is relatively higher than the Sort Merge or Hash Join, since the driving path will not affect the performance too much in Sort Merge or Hash Join. But a wrong driving path in Nested Loop join may be a disaster in your application, therefore, database vendors prefer not to take this risk in designing their SQL optimizer and give the Nested Loop join a higher cost.

The SQL statement that I have used to illustrate this point is a simple SQL. For complex SQL, there is no single rule which is always true for you to follow in order to pick out the best join method to use. It is better that you try the different methods and see which one produces the best results in your specific database environment and application. Quest SQL Optimizer makes this search for performance a simple process by rewriting the SQL syntax for you and testing the SQL alternatives.