Oracle Database Administration: The Essential Refe

Examples

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> ...