The main alternative to the merge-join is the nested loop. In the large, multi-table queries you often see with a large data warehouse, you almost always want to see nested loop joins being performed rather than the merge-joins. I mentioned earlier in this chapter about some 30-minute queries that turned into 30-second queries. These were queries that initially used the merge-join algorithm and were converted to use the nested loop approach. The basic improvement comes from using the results of the first query to narrow the search of the second, as opposed to performing two independent queries and then trying to mesh the results together. Figure 25.7 shows the nested loop approach.

Figure 25.7.  The nested loop approach.

There are a number of other algorithms that could be called into play when using compound queries (UNION, UNION ALL, and INTERSECT). Depending on the details of your individual query, some of these can improve your performance. You may have to adjust the SQL in your query to get Oracle to use these statements, but I have seen examples where it is definitely worth it. Figure 25.8 illustrates the concepts behind these algorithms.

Figure 25.8.  Compound query approaches.

There’s a lot more to the process of optimizing queries than can be covered in these pages, and there are many books on the subject. Some good information can be gathered from your server concepts manual and the applications developers guide that normally comes with the complete Oracle documentation set. This is a complex topic with which at least some of your more senior developers should be familiar. Perhaps you may want to loan them copies of these books if you are still having performance problems after you finish tuning your instance (it could save you some time).

Hints

Some people out there just can’t take a hint. Oracle, however, will almost always listen to your hints whenever it can follow them. Hints are your primary weapon when you have a query that is basically sound and exactly as you want it to be, but Oracle still is not processing it the way you want. (This usually means that the query is too slow.) You merely have to put the correct hint syntax just after the select statement to get Oracle to change its mind. For example, the following SQL statement will be optimized to obtain the first couple of rows in the fastest manner (a goal as described earlier).

select /*+ FIRST_ROWS */ last_name,first_name,golf_score

   from golf_scores

   where last_name=‘GREENE’;

Basically, you use a construct that looks like the comment block sections in many common programming languages (/*...*/) with a plus sign to signify the hint (+). The key here is to know what kind of hints you can give. The most common hints are shown in the following list (as with everything in Oracle, there are a lot of lightly used options):

•  ALL_ROWS Set the optimizer goal to complete the entire query in the most rapid manner possible.

•  FIRST_ROWS Set the optimizer goal to get at least the first couple of rows of the query back quickly, even if it takes a little longer to retrieve all the rows.

•  FULL(table) Use a full table scan on the table indicated. You can use either the table name or an alias to specify the table of interest.

•  INDEX(table index) Use an indexed search on the specified table using the specified index. There may be cases where there are multiple indexes that could be used to find the values, and you can speed things up by choosing the right one.

•  INDEX_ASC(table index) Use the specified index to scan the specified table, but do it in ascending order. This will help if the values you want are likely to be at the beginning of the index.

•  INDEX_DESC(table index) Use the specified index to scan the specified table, but do it in descending order. This will help if the values you want are likely to be at the end of the index.

•  AND_EQUAL (table index ...) Merge the results from the specified single column indexes to determine which rows of the table are needed.

•  ORDERED Join the tables in the order in which they appear in the FROM clause.

•  USE_NL (table table ...) Use the nested loop algorithm to join tables together, with the first table specified being the inner table on the join.

•  USE_MERGE (table table ...) Use the merge-join algorithm to join the tables together.

Here are a few more examples of queries that use hints:

select /*+ ALL_ROWS */ analysis,date_run,result

   from lab_tests

   where analysis_type = ‘CHEMICAL’;



select /*+ USE_NL(a) */ analysis,result,action_to_take

   from lab_tests a,test_actions b

   where a.result = b.result and

      a.analysis_type = ‘CHEMICAL’;



select /*+ INDEX(lab_tests lab_tests_types) analysis,date_run,result

   from lab_tests

   where analysis_type = ‘CHEMICAL’; 

The application developers guide in your Oracle documentation set contains a pretty good discussion of the options available to you with hints. You can do a lot with hints to make Oracle do what you want it to do, rather than what it thinks it should be doing. The nice thing about tuning queries is that you can isolate the SQL and run multiple timing tests using a variety of hints until you find the one that works best. You do not have to worry about damaging the data because you would be testing update, insert, or delete statements. You may want to be considerate about the impact your testing might have on user response time if you are issuing some really nasty test queries.

Index Design

Indexes are the easiest and often best techniques to consider when you want to improve the performance of a query. Most access speed problems in new applications are due to a lack of an appropriate index. Once again, it can make a night-and-day difference in your performance. The best thing is that it’s relatively simple to determine which indexes you need. You merely look at the SQL statements you issue and see what you are using in the where clause.

You do have to be judicious regarding what you build the indexes on—indexes consume disk space that might be tight. You do not get much benefit from indexes that are almost as large as the tables themselves. Finally, indexes do have an impact on the performance of updates, insertions, and deletions from the table. With these points aside, an index can be the simplest and best way to improve query performance when you have a few key fields on which you’re always searching.