O'Reilly logo

Oracle SQL Tuning Pocket Reference by Mark Gurry

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Problems Common to Rule and Cost with Solutions

This section lists problems that are common to both the rule-based and cost-based optimizers. It is important that you are aware of these problems and avoid them wherever possible. Table 1-4 lists the problems and their occurrence rates.

Table 1-4. Common problems with both optimizers

Problems for both Rule and Cost

Occurrence %

1. Statement not written for indexes

25%

2. Indexes are missing or inappropriate

16%

3. Use of single-column index merge

15%

4. Misuse of nested loop, sort merge, or hash join

12%

5. Misuse of IN, EXISTS, NOT IN, NOT EXISTS, or table joins

8%

6. Unnecessary Sorts

4%

7. Too many indexes on a table

4%

8. Use of OR instead of UNION

3%

9. Tables and indexes with many deletes

3%

10. Other

10%

Problem 1: Statement Not Written for Indexes

Some SELECT statement WHERE clauses do not use indexes at all. Most such problems are caused by having a function on an indexed column. Oracle8i and later allow function-based indexes, which may provide an alternative method of using an effective index.

In the examples in this section, for each clause that cannot use an index, I have suggested an alternative approach that will allow you to get better performance out of your SQL statements.

In the following example, the SUBSTR function disables the index when it is used over an indexed column.

Do not use:

SELECT account_name, trans_date, amount  
FROM   transaction  
WHERE  SUBSTR(account_name,1,7) = 'CAPITAL';

Use:

SELECT ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required