O'Reilly logo

Oracle Database Administration: The Essential Refe by Brian Laskey, David C. Kreines

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required