From this output, we can see the dreaded TABLE ACCESS FULL on the SALESNET table. To diagnose the reason, we return to the SQL and look for any salesnet columns in the WHERE clause. There we see that the salesnet column called “mgc” is being used as a join column in the query, indicating that an index is necessary on salesnet.mgc to alleviate the full-table scan.

While the plan table is useful for determining the access path to the data, it does not tell the entire story. The configuration of the data is also a consideration. While the SQL optimizer is aware of the number of rows in each table (the cardinality), and the presence of indexes on fields, the SQL optimizer is not aware of data distribution factors such as the number of expected rows returned from each query component.

The other tool that is used with the plan table is an SQL trace facility. Most database management systems provide a trace facility that shows all of the resources consumed within each query component. The trace table will show the number of I/Os that were required to perform the SQL, as well as the processor time for each query component.

Some other relational databases such as DB2 allow the DBA to specify the physical sequence for storing the rows. Generally, this sequence will correspond to the column value that is most commonly used when the table is read sequentially by an application. If a customer table is frequently accessed in customer ID order, then the rows should be physically stored in customer ID sequence.

The explain plan output will display many database access methods. The major access techniques include:

•  AND-EQUAL—This means that tables are being joined and that Oracle will be able to use the values from the indexes to join the rows.

•  CONCATENATION—This indicates an SQL UNION operation.

•  COUNTING—This indicates the use of the SQL COUNT function.

•  FILTER—This indicates that the WHERE clause is removing unwanted rows from the result set.

•  FIRST ROW—This indicates that a cursor has been declared for the query.

•  FOR UPDATE—This indicates that returned rows were write locked (usually by using the SELECT . . . FOR UPDATE OF . . . ).

•  INDEX (UNIQUE)—This indicates that an index was scanned for a value specified in the WHERE clause.

•  INDEX (RANGE SCAN)—This indicates that a numeric index was scanned for a range of values (usually with the BETWEEN LESS_THAN or GREATER_THAN specified).

•  INTERSECTION—This indicates a solution set from two joined tables.

•  MERGE JOIN—This indicates that two result sets were used to resolve the query.

•  NESTED LOOPS—This indicates that the last operation will be performed n times, once for each preceding operation. For example, below the INDEX (UNIQUE), these operations will be performed for each row returned by TABLE ACCESS (FULL): NESTED LOOPS, TABLE ACCESS (FULL) OF ‘CUSTOMER’, and INDEX (UNIQUE) OF SY_01_IDX.

•  PROJECTION—This indicates that only certain columns from a selected row are to be returned.

•  SORT—This indicates a sort, either into memory or the TEMP tablespace.

•  TABLE ACCESS (ROWID)—This indicates a row access by ROWID that is very fast.

•  TABLE ACCESS (FULL)—This is a full-table scan and is usually cause for concern unless the table is very small.

•  UNION—This indicates that the DISTINCT SQL clause was probably used.

•  VIEW—This indicates that a SQL view was involved in the query.

Database statistics packages can be made to capture this information, but they tend to be very resource intensive. Turning on SQL trace statistics for a very short period of time during processing is a good practice to follow in order to gather a representative sample of the SQL access.

Using Temporary Tables

The prudent use of temporary tables can dramatically improve Oracle performance. Consider the following example: We want to identify all users who exist within Oracle, but have not been granted a role. We could formulate the following query:

SELECT USERNAME FROM dba_users
WHERE USERNAME NOT IN
(SELECT GRANTEE FROM dba_role_privs);

This query runs in 18 seconds. Now, we rewrite the same query to utilize temporary tables:

CREATE TABLE temp1 AS
  SELECT DISTINCT USERNAME FROM dba_users;

CREATE TABLE temp2 AS
  SELECT DISTINCT GRANTEE FROM dba_role_privs;

SELECT USERNAME FROM temp1
WHERE USERNAME NOT in
(SELECT GRANTEE FROM temp2);

This query runs in less than three seconds.

Tuning With The Rule-Based Optimizer

In Oracle’s rule-based optimizer, the ordering of the table names in the FROM clause determines the driving table. The driving table is important because it is retrieved first, and the rows from the second table are then merged into the result set from the first table. Therefore, it is essential that the second table return the least amount of rows based on the WHERE clause. This is not always the table with the least amount of rows (i.e., the smallest cardinality).

For example, consider two emp_tables—one in London and another in New York, as shown in Table 4.2.

In this example, a total select from the emp_table should specify the New York table first, since London has the least amount of returned rows:

SELECT *
FROM emp@new_york, emp@london;

If the SQL specifies a WHERE condition to include only Department 100, the order of table names should be reversed:

SELECT *
FROM emp@london, emp@new_york
WHERE
    dept = 100;