| Previous | Table of Contents | Next |
Explain plans can be difficult to interpret. Indenting the explain steps greatly aids in the understanding of the order of the explain steps. The more common explain steps are discussed in Table 24.3.
| Access Rule | Description |
|---|---|
| AND-EQUAL | Index values will be used to join rows. |
| CONCATENATION | SQL statement UNION command. |
| FILTER | FILTERs apply “other criteria” in the query to further qualify the matching rows. The “other criteria” include correlated subqueries, and HAVING clause. |
| FIRST ROW | SQL statement will be processed via a cursor. |
| FOR UPDATE | SQL statement clause “for update of” placed row level locks on affected rows. |
| INDEX (UNIQUE) | SQL statement utilized a unique index to search for a specific value. |
| INDEX (RANGE SCAN) | SQL statement contains a nonequality or BETWEEN condition. |
| HASH JOIN | SQL statement initiated a hash-join operation. |
| MERGE JOIN | SQL statement references two or more tables, sorting the two result sets being joined over the join columns and then merging the results via the join columns. |
| NESTED LOOPS | This operation is one form of joining tables, as opposed to a merge join. One row is retrieved from the row source identified by the first child operation, and then joined to all matching rows in the other table, identified in the second child operation. |
| NONUNIQUE INDEX (RANGE SCAN) | The RANGE SCAN option indicates that Oracle expects to return multiple matches (ROWIDs) from the index search. |
| PARTITION (CONCATENATED) | SQL statement will access a partitioned object and merge the retrieved rows from the accessed partitions. |
| PARTITION (SINGLE) | SQL statement will access a single partition. |
| PARTITION (EMPTY) | The SQL statement makes reference to an empty partition. |
| SORT (ORDER BY) | SQL statement contains an ORDER BY SQL command. |
| SORT (AGGREGATE) | SQL statement initiated a sort to resolve a MIN or MAX type function. |
| SORT (GROUP BY) | SQL statement contains a GROUP BY SQL command. |
| TABLE ACCESS (FULL) | All rows are retrieved from the table without using an index. |
| TABLE ACCESS (BY ROWID) | A row was retrieved from a table based on the ROWID of the row. |
| TABLE ACCESS (CLUSTER) | A row is retrieved from a table that is part of an indexed cluster. |
| UNION | SQL statement contains a DISTINCT SQL command. |
Oracle8 has introduced three new columns: partition_start, partition_stop, and partition_id. These three new fields will aid in the tuning of SQL statements that access partitioned objects. partition_start and partition_stop show the range of partitions affected by this explain step. The partition_id is the identification number for that particular explain step.
Finally, there are both good and poor ways to code SQL statements. Here are some guidelines for SQL statement coding that will help both the rule-based and the cost-based optimizers.
DON’T use calculations in the WHERE clause on indexed columns. Unless the intended behavior is to disable the index use, any function on indexed columns will ignore the index.
DO use the IN operator instead of NOT. Try to avoid using the NOT command by using >=, <=, and so on.
DON’T use an index if more than 20 percent of the rows will be returned by the query.
DO use array processing whenever possible (Export and Pro*C applications).
DON’T use subqueries if other solutions exist (PL/SQL loop, for example).
DO use hints to ensure the desired execution-plan results.
DON’T write applications that use SQL execution-plan defaults. The Oracle Corporation makes no guarantees that default behavior will be maintained in future releases, or even between different hardware platforms.
This chapter supplies the reader with an in-depth study of the four tuning levels associated with any Oracle environment: operating system tuning, Oracle RDBMS tuning, database design tuning, and SQL statement tuning. Each level depends on the other levels. Each level of tuning should be evaluated per application needs in the order listed in this chapter. There is no single magical solution to any performance issue, but Oracle provides a rich environment of options so that most any Oracle application can access data in the most optimal manner.
| Previous | Table of Contents | Next |