Chapter 8, describes how Oracle creates a plan for a particular SQL statement. Oracle currently uses one of two methods for determining how to execute a SQL statement:

The keys to tuning a SQL statement are understanding how the Oracle query optimizers work and knowing how to change Oracle’s behavior so it will process the statement more efficiently.

Of course, before you can tune a SQL statement, you must know what it is doing and how. There are many tools on the market today that will help with this task, and one of the most useful (if not the flashiest) is the EXPLAIN PLAN command available in SQL*Plus. By creating a plan table (usually known as PLAN_TABLE) and examining the result of an EXPLAIN PLAN statement, you’ll easily see how Oracle executes a particular statement. For example, the SQL statement:

SELECT ename,loc,sal,hiredate
FROM   scott.emp, scott.dept
WHERE  emp.deptno=dept.deptno;

can be explained with the following command:

EXPLAIN PLAN SET STATEMENT_ID='DEMO' FOR
SELECT ename,loc,sal,hiredate
FROM   scott.emp, scott.dept
WHERE  emp.deptno=dept.deptno;

The results stored in PLAN_TABLE can be selected using a simple query:

SELECT LPAD(' ',2*level) || operation || '' || options || ' '||
       object_name EXPLAIN_PLAN
FROM plan_table
CONNECT BY PRIOR id = parent_id
START WITH id=1

and will look like this:

EXPLAIN_PLAN
-------------------------------
NESTED LOOPS
  TABLE ACCESSFULL DEPT
  TABLE ACCESSFULL EMP

This plan shows that both the DEPT and EMP tables will be accessed using a full table scan. This is fine for two small tables like EMP and DEPT; in fact, we want them to be full table scans, because the tables will be cached in memory and no disk I/O will be required (after the first execution, at least). However, if the tables were large, this query could run for a long time, and so we would want to change the way this query is performed.

There are three basic ways to modify the behavior of Oracle’s query optimizer:

If we try the first option and add an index on EMP(deptno), the plan will change as follows:

EXPLAIN_PLAN
---------------------------------------------
  NESTED LOOPS
    TABLE ACCESSFULL DEPT
    TABLE ACCESSBY ROWID EMP
      INDEXRANGE SCAN EMPDEPT_IX

You can now see that Oracle will use the index to retrieve rows from EMP via the ROWID, which was obtained from the newly created index, and a full table scan is no longer necessary.

There is often more than one way to perform a particular function using SQL, and it is good programming practice to try several methods (with appropriate benchmarking) before settling on the correct SQL statement to use. Chapter 8, provides more detailed information on SQL tuning.