CHAPTER 4
Tuning Oracle SQL

While several books have been devoted to the efficient use of Oracle SQL, only a few general rules and guidelines are actually effective in guaranteeing the most effective performance from your Oracle systems. This chapter will focus on the basic techniques for quickly achieving the maximum SQL performance with the least amount of effort. Topics include:

•  Tips for efficient SQL

•  Index predicates

•  Concatenated indexes

•  Using temporary tables to improve performance

•  Tuning with the rule-based optimizer

•  Using hints with the cost-based optimizer

Tuning Oracle SQL Syntax

Because SQL is a declarative language, we can write a query many ways. And although each query will return identical results, the execution time can be dramatically different. To illustrate this concept, let’s consider a small employee table with only 15 rows and an index on sex and hiredate. We issue the following query to retrieve all female employees who have been hired within the last 90 days. Here we are assuming that we are using Oracle’s rule-based optimizer:

SELECT emp_name
FROM EMPLOYEE
WHERE
sex = 'F'
AND
hiredate BETWEEN sysdate-90 AND sysdate;

Since the table has only 15 rows, the most efficient way to service this request would be to use a full-table scan. However, Oracle will walk the index that exists, performing extra I/Os as it reads the index tree to access the rows in the table. While this is a simplistic example, it serves to illustrate the concept that the execution time of SQL is heavily dependent upon the way that the query is stated as well as the internal index structures within Oracle.

Entire books are devoted to some of the subtle nuances of SQL behavior, most notably Joe Celko’s best selling book SQL for Smarties. For our purposes, we will omit discussion of tuning some of the more obscure queries such as outer joins and nullable foreign key queries. Instead, we will concentrate on tuning some of the common SQL queries that rely on Oracle’s cost-based and rule-based optimizer.

It is true that each dialect of SQL is different, but general rules apply that can be used to keep database queries running efficiently. And while these guidelines are no substitute for the SQL EXPLAIN PLAN facility, they can reduce the chances that a database query will consume large amounts of system resources.

The first step is to look at the relative costs for each type of SQL access. Oracle has published the cost list shown in Table 4.1 that describes the relative cost of each type of row access.

As we can see, the fastest way to retrieve a row is by knowing its row ID. A row ID (called a ROWID in Oracle) is the number of the database block followed by the “displacement” or position of the row on the block. For example, 1221:3 refers to a row on block number 1221, the row being the third on the page. Many savvy programmers capture the ROWID for a row if they plan to retrieve it again. In Oracle, ROWID is a valid column statement, such that we can select the ROWID along with our data in a single statement:

SELECT ROWID FROM EMPLOYEE INTO :myvar;

On the other end of the cost spectrum we see the full-table scan. As I have noted earlier in this book, a full-table scan is acceptable for small tables, but it can wreak havoc on Oracle when a full-table scan is invoked against a large table. Therefore, more than any other SQL tuning technique, avoiding full-table scans is a primary consideration. In short, full-table scans can always be avoided by using indexes and index hints. However, another issue must be considered. While a full-table scan may be the fastest for an individual query with many complex WHERE conditions, the full-table scan is done at the expense of other SQL on the system. The question then becomes: Do we tune an individual query for performance, or do we tune the database as a whole?