Designing New SQL Statements

Although this part of the chapter is directed at tuning SQL statements associated with new applications, it may be appropriate for you to make these changes to existing applications if you have the flexibility to do so. The reason these guidelines are separate is because many of them involve not only tuning the SQL statements and application, but changing the database schema as well.

In the design stage, it is important to plan the application and the database design together. By properly designing the application to take advantage of the design and features of the database, you can take optimal advantage of both of them. At the same time, the database should be designed to function properly with the application that uses it. The design of the database should reflect the purpose of the application. The following sections look at some of the optimizations that are possible.

Using Indexes

One of the most powerful performance enhancing features available in an RDBMS is the index. The index can greatly reduce the number of I/Os necessary to retrieve the requested data and thus greatly improve performance. It is necessary, however, to coordinate the use of the index between the database designer and the application designer. By not carefully coordinating, the two indexes may be bypassed when a small change in SQL coding may be able to take advantage of the index. Remember that the more indexes on a table, the more overhead is incurred during updates, inserts, and deletes. It is important to index selectively.

What Tables Should Be Indexed?

Use the following guidelines to decide which tables to index:

•  Index tables when queries select only a small number of rows. Queries that select a large number of rows defeat the purpose of the index. Index the table when queries access less than 5 percent of the rows in the table.

•  Don’t index tables that are frequently updated. Updates, inserts, and deletes on indexed tables incur extra overhead. Base your decision about whether or not to index a table on the number of updates, inserts, and deletes relative to the total number of queries to the table. If the table is frequently accessed as well as updated, you may or may not choose to index it.

•  Index tables that don’t have duplicate values on the columns usually selected in WHERE clauses. Tables for which the selection is based on TRUE/FALSE values are not good candidates for an index.

•  Index tables that are queried with relatively simple WHERE clauses. Complex WHERE clauses may not be able to take advantage of indexes. This may be solved by creating a complex index, by simplifying the SQL statement, or by using a hint.

Once you decide to use an index, you must then decide on which columns to put the index. You may index one or more columns, depending on the table.

What Columns Should Be Indexed?

Use the following guidelines to decide which columns to index:

•  Choose columns frequently specified in WHERE clauses. Frequently accessed columns can most benefit from indexes.

•  Don’t index columns that do not have many unique values. Columns in which a good percentage of rows are duplicates cannot take advantage of indexing.

•  Columns that have mostly unique values are excellent candidates for indexing. Oracle automatically indexes columns that are unique or that are primary keys defined with constraints. These columns are most effectively optimized by indexes.

•  Index columns that are foreign keys of referential integrity constraints in cases where large numbers of concurrent INSERT, UPDATE, and DELETE statements access both the parent and child tables. Such an index allows the child table to be updated without having to lock the parent table.

•  Columns that are commonly used to join tables are good candidates for indexing.

•  Frequently modified columns probably should not be index columns because of the overhead involved in updating the index.

---

Note:  
Remember that some penalty is associated with performing INSERT, UPDATE, and DELETE statements on columns that are indexed. If you have a lot of those statements, an index may hurt more than help. Use SQL Trace on the SQL statements that access tables both with and without an index. Compare the results.

---

Composite Indexes

Composite indexes may be more effective than individual indexes in situations such as the following:

•  When two columns are not unique individually but are unique together, composite indexes may work very well. For example, columns A and B have few unique values, but rows with a particular A AND B are mostly unique. Look for WHERE clauses with AND operators.

•  If all values of a SELECT statement are in a composite index, the table is not queried; the result is returned from the index.

•  If several different queries select the same rows by using different WHERE clauses based on different columns, consider creating a composite index with all those columns used in the WHERE statements.

If carefully designed, composite indexes can be quite useful. As with single-column indexes, they are most effective if applications are written with the indexes in mind.

Once you create the index, periodically use SQL Trace to determine whether your queries are taking advantage of the index. It may be worth the effort to try the query with and without indexes and compare the results to see whether the index is worth the space it is using.

How to Avoid an Index

If you have an application that can take advantage of an index, but contains a few SQL statements that result in poor performance when they use an index, you can tell the optimizer to bypass the index. There are several ways this can be done:

•  Write the SQL statement to avoid using a SELECT statement on an indexed column. By not selecting the column or set of columns that are indexed, you avoid the index.

•  Use hints. When you use hints in your SQL statements, you can tell the optimizer not to use the index for this particular SQL statement.

In this manner, you can design your database to effectively use indexes and have the flexibility to avoid the index when it is not optimal to do so.