SQL> alter session set optimizer_mode = rule;
Session altered
SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string RULE
SQL> select rowid, e.* from scott.emp e;
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- -------- ---------- ---------- -------
AAAHW7AABAAAMUiAAA 7369 SMITH CLERK 7902 80/12/17 800 20
AAAHW7AABAAAMUiAAB 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
AAAHW7AABAAAMUiAAC 7521 WARD SALESMAN 7698 81/02/22 1250 500 30
AAAHW7AABAAAMUiAAD 7566 JONES MANAGER 7839 81/04/02 2975 20
AAAHW7AABAAAMUiAAE 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
AAAHW7AABAAAMUiAAF 7698 BLAKE MANAGER 7839 81/05/01 2850 30
AAAHW7AABAAAMUiAAG 7782 CLARK MANAGER 7839 81/06/09 2450 10
AAAHW7AABAAAMUiAAH 7788 SCOTT ANALYST 7566 87/04/19 3000 20
AAAHW7AABAAAMUiAAI 7839 KING PRESIDENT 81/11/17 5000 10
AAAHW7AABAAAMUiAAJ 7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
AAAHW7AABAAAMUiAAK 7876 ADAMS CLERK 7788 87/05/23 1100 20
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- -------- ---------- ---------- -------
AAAHW7AABAAAMUiAAL 7900 JAMES CLERK 7698 81/12/03 950 30
AAAHW7AABAAAMUiAAM 7902 FORD ANALYST 7566 81/12/03 3000 20
AAAHW7AABAAAMUiAAN 7934 MILLER CLERK 7782 82/01/23 1300 10
14 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 TABLE ACCESS (FULL) OF 'EMP'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1621 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
SQL> select * from scott.emp where rowid = 'AAAHW7AABAAAMUiAAF';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 81/05/01 2850 30
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 TABLE ACCESS (BY USER ROWID) OF 'EMP' --우선순위 1로 table access하는것이 확인.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
783 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from scott.emp where empno = 7698;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 81/05/01 2850 30
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' --인덱스의 rowid로 access
2 1 INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE) --인덱스를 사용
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
1 physical reads
0 redo size
783 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from scott.emp where ename = 'BLAKE';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 81/05/01 2850 30
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 TABLE ACCESS (FULL) OF 'EMP' --인덱스가 없어 Full Scan. 우선순위 15번
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
783 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed