The following examples using the SCOTT.EMP and SCOTT.DEPT tables show how the rule-based optimizer will process a query based upon the 15 rules listed in Table 8.2:

SQL> 
SQL> rem
SQL> rem  use the standard emp and dept tables
SQL> rem  create new copies without indexes.
SQL> rem
SQL> 
SQL> create table emp as select * from scott.emp;

Table created.

SQL> create table dept as select * from scott.dept;

Table created.

SQL> 
SQL> rem
SQL> rem force rule based optimization and turn on
SQL> rem autotrace
SQL> rem
SQL> 
SQL> 
SQL> alter session set optimizer_mode = rule;

Session altered.

SQL> set autotrace traceonly explain
SQL> 
SQL> 
SQL> rem
SQL> rem  show that with no indexes, there are no real differences
SQL> rem  but the order of the driving tables is based on the last
SQL> rem  table in the from clause
SQL> rem
SQL> 
SQL> select empno,ename,job,dname
  2  from emp, dept
  3  where emp.deptno = dept.deptno
  4  /

Execution Plan
----------------------------------------------------------        
   0      SELECT STATEMENT Optimizer=RULE                         
   1    0   MERGE JOIN          
   2    1     SORT (JOIN)       
   3    2       TABLE ACCESS (FULL) OF 'DEPT'                     
   4    1     SORT (JOIN)       
   5    4       TABLE ACCESS (FULL) OF 'EMP'                      

SQL> 
SQL> select empno, ename, job, dname
  2  from dept,emp
  3  where emp.deptno = dept.deptno
  4  /

Execution Plan
----------------------------------------------------------        
   0      SELECT STATEMENT Optimizer=RULE                         
   1    0   MERGE JOIN          
   2    1     SORT (JOIN)       
   3    2       TABLE ACCESS (FULL) OF 'EMP'                      
   4    1     SORT (JOIN)       
   5    4       TABLE ACCESS (FULL) OF 'DEPT'                     

SQL> 
SQL> rem
SQL> rem create an index on emp
SQL> rem in this case, since there is an index,
SQL> rem the nested loop will use the index, no matter
SQL> rem the order of the tables in the from clause
SQL> rem
SQL> 
SQL> create index empi on emp(deptno);

Index created.

SQL> 
SQL> select empno,ename,job,dname
  2  from emp, dept
  3  where emp.deptno = dept.deptno
  4  /

Execution Plan
----------------------------------------------------------        
   0      SELECT STATEMENT Optimizer=RULE                         
   1    0   NESTED LOOPS        
   2    1     TABLE ACCESS (FULL) OF 'DEPT'                       
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP'              
   4    3       INDEX (RANGE SCAN) OF 'EMPI' (NON-UNIQUE)         

SQL> 
SQL> select empno, ename, job, dname
  2  from dept,emp
  3  where emp.deptno = dept.deptno
  4  /

Execution Plan
----------------------------------------------------------        
   0      SELECT STATEMENT Optimizer=RULE                         
   1    0   NESTED LOOPS        
   2    1     TABLE ACCESS (FULL) OF 'DEPT'                       
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP'              
   4    3       INDEX (RANGE SCAN) OF 'EMPI' (NON-UNIQUE)         

SQL> 
SQL> rem
SQL> rem  now create an index on dept.
SQL> rem  with an index on both tables,
SQL> rem  the rule-based optimizer will now
SQL> rem  perform nested loops using the
SQL> rem  last table in the from clause as the
SQL> rem  driving table
SQL> rem
SQL> 
SQL> 
SQL> 
SQL> create index depti on dept(deptno);

Index created.

SQL> 
SQL> select empno,ename,job,dname
  2  from emp, dept
  3  where emp.deptno = dept.deptno
  4  /

Execution Plan
----------------------------------------------------------        
   0      SELECT STATEMENT Optimizer=RULE                         
   1    0   NESTED LOOPS        
   2    1     TABLE ACCESS (FULL) OF 'DEPT'                       
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP'              
   4    3       INDEX (RANGE SCAN) OF 'EMPI' (NON-UNIQUE)         

SQL> 
SQL> select empno, ename, job, dname
  2  from dept,emp
  3  where emp.deptno = dept.deptno
  4  /

Execution Plan
----------------------------------------------------------        
   0      SELECT STATEMENT Optimizer=RULE                         
   1    0   NESTED LOOPS        
   2    1     TABLE ACCESS (FULL) OF 'EMP'                        
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'             
   4    3       INDEX (RANGE SCAN) OF 'DEPTI' (NON-UNIQUE)        

SQL> 
SQL> 
SQL> rem
SQL> rem  now drop the index on emp.  with only an
SQL> rem  index on dept, the optimizer will always
SQL> rem  perform a nested loop using emp as the
SQL> rem  driving table.
SQL> rem
SQL> rem
SQL> 
SQL> 
SQL> drop index empi;

Index dropped.

SQL> 
SQL> select empno,ename,job,dname
  2  from emp, dept
  3  where emp.deptno = dept.deptno
  4  /

Execution Plan
----------------------------------------------------------        
   0      SELECT STATEMENT Optimizer=RULE                         
   1    0   NESTED LOOPS        
   2    1     TABLE ACCESS (FULL) OF 'EMP'                        
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'             
   4    3       INDEX (RANGE SCAN) OF 'DEPTI' (NON-UNIQUE)        

SQL> 
SQL> select empno, ename, job, dname
  2  from dept,emp
  3  where emp.deptno = dept.deptno
  4  /

Execution Plan
----------------------------------------------------------        
   0      SELECT STATEMENT Optimizer=RULE                         
   1    0   NESTED LOOPS        
   2    1     TABLE ACCESS (FULL) OF 'EMP'                        
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'             
   4    3       INDEX (RANGE SCAN) OF 'DEPTI' (NON-UNIQUE)