What are Optimizer Modes available in ORACLE?
Within two Optimizers RBO and CBO, Oracle
provides several optimizer modes. An optimizer mode may be set for the entire
database, or it may be set for a specific Oracle session.
![]() |
| IMG Source- https://docs.oracle.com |
WHERE clause.SELECT FIRST_NAME, LAST_NAME FROM Employees
WHERE UPPER(LAST_NAME) LIKE :B1; /*Even if there is an Index on LAST_NAME it will not be used by ORACLE*/
DEGREE column in ALL_TABLES for the table to determine the degree of parallelism.SQL> SELECT EMP_NO, ENAME
2 From EMPLOYEES
3 Where DEPTNO IN(10,20);
EMP_NO ENAME
---------- ----------
1001 Ravi
1002 Surya
1006 Rajan
1007 Manu
1008 Karan
Execution Plan
----------------------------------------------------------
Plan hash value: 522316573
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 42 | 2 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 3 | 42 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IND_DEP | 3 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPTNO"=10 OR "DEPTNO"=20)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
702 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
SQL> SELECT /*+ FULL(EMP) */ EMP_NO, ENAME
2 From EMPLOYEES EMP
3 Where DEPTNO IN(10,20);
EMP_NO ENAME
---------- ----------
1001 Ravi
1002 Surya
1006 Rajan
1007 Manu
1008 Karan
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 42 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 3 | 42 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO"=10 OR "DEPTNO"=20)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
702 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
INDEX(table_alias index_name) specifies the index to use.SQL> SELECT /*+ INDEX(EMP IND_DEP) */ EMP_NO, ENAME, SALARY, DEPTNO, DNAME
2 From EMPLOYEES EMP
3 Where DEPTNO IN(10,20,30);
EMP_NO ENAME SALARY DEPTNO DNAME
---------- ---------- ---------- ---------- ----------
1001 Ravi 48000 10 ACCOUNTS
1002 Surya 89000 20 SALES
1006 Rajan 77770 20 SALES
1007 Manu 98888 20 BANKING
1008 Karan 48000 20 BANKING
1003 Ankit 89000 30 ACCOUNTS
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 522316573
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 135 | 2 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 5 | 135 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IND_DEP | 5 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPTNO"=10 OR "DEPTNO"=20 OR "DEPTNO"=30)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
1072 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
SQL> SELECT /*+ INDEX_DESC(EMP IND_EMPNO) */ EMP_NO, ENAME, SALARY, DEPTNO, DNAME
2 From EMPLOYEES EMP
3 Where EMP_NO<1006 Order By EMP_NO DESC;
EMP_NO ENAME SALARY DEPTNO DNAME
---------- ---------- ---------- ---------- ----------
1005 Pritesh 48000 50 ACCOUNTS
1004 Nikhil 77770 40 SALES
1003 Ankit 89000 30 ACCOUNTS
1002 Surya 89000 20 SALES
1001 Ravi 48000 10 ACCOUNTS
Execution Plan
----------------------------------------------------------
Plan hash value: 507161224
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 162 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 6 | 162 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN DESCENDING| IND_EMPNO | 6 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMP_NO"<1006)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1037 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
USE_NL(table1 table2) hint,
where table1 and table2 are the aliases of the tables being joined.USE_HASH hint to advise the optimizer to use a hash
join when joining two tables together. If you are having trouble getting the
optimizer to use hash joins, investigate the values for the HASH_AREA_SIZE and HASH_JOIN_ENABLED parameters.SQL> SELECT /*+ Use_Hash(E,D) */ E.EMP_NO, E.ENAME, D.DEPTNAME
2 From EMP_TEST E, DEPT_TEST D
3 Where E.DEPTNO=D.DEPTNO;
EMP_NO ENAME DEPTNAME
---------- ---------- --------------------
1001 Ravi Accounts
1002 Surya Retail
1003 Ankit Insurance
1004 Nikhil Banking
1005 Pritesh Cloud
1006 Rajan Retail
1007 Manu Retail
1008 Karan Retail
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2815500728
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 200 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 8 | 200 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT_TEST | 5 | 55 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP_TEST | 8 | 112 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
867 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed
Level
|
When
Dynamic Sampling will be used
|
Sample
size (blocks)
|
0
|
Do
not use dynamic sampling.
|
N/A
|
1
|
Use
dynamic sampling with a 32-block sample size for all tables that have not
been analysed, but only if the unanalysed table has more than 32 blocks, no
indexes and is not partitioned.
|
32
|
2 (default)
|
Use
dynamic sampling with a 64-block sample size if at least one table in the
statement has no statistics. This is the default.
|
64
|
3
|
Use
dynamic sampling with a 64-block sample size if the statement meets level 2
criteria or has one or more expressions used in the WHERE clause predicates.
Example: Where SUBSTR(CUSTLASTNAME,1,3) or Where a + b =5
|
64
|
4
|
Use
dynamic sampling with a 64-block sample size if the statement meets level 3
criteria or uses complex predicates (an OR or AND operator between multiple
predicates on the same table).
|
64
|
5
|
Any statement that meets level 4 criteria
|
128
|
6
|
Any statement that meets level 4 criteria
|
256
|
7
|
Any statement that meets level 4 criteria
|
512
|
8
|
Any statement that meets level 4 criteria
|
1024
|
9
|
Any statement that meets level 4 criteria
|
4086
|
10
|
Use
dynamic sampling of all blocks for all statements.
|
All Blocks
|
SQL> CREATE Table MyTemp AS
SELECT OWNER, OBJECT_TYPE
FROM ALL_OBJECTS;
Table created.
SQL> SELECT Count(*) From MyTemp;
COUNT(*)
----------
71879
SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT /*+ Dynamic_Sampling(MyTemp,0) */ *
2 From MyTemp;
Execution Plan
----------------------------------------------------------
Plan hash value: 2234051319
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16908 | 462K| 58 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| MYTEMP | 16908 | 462K| 58 (0)| 00:00:01 |
SQL> SELECT * From MyTemp;
Execution Plan
----------------------------------------------------------
Plan hash value: 2234051319
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82504 | 2255K| 59 (2)| 00:00:01 |
| 1 | TABLE ACCESS FULL| MYTEMP | 82504 | 2255K| 59 (2)| 00:00:01 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> DELETE From MyTemp;
71879 rows deleted.
SQL> COMMIT;
Commit complete.
SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT /*+ Dynamic_Sampling(MyTemp,0) */ *
2 From MyTemp;
Execution Plan
----------------------------------------------------------
Plan hash value: 2234051319
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16908 | 462K| 58 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| MYTEMP | 16908 | 462K| 58 (0)| 00:00:01 |
----------------------------------------------------------------------------
SQL> SELECT * From MyTemp;
Execution Plan
----------------------------------------------------------
Plan hash value: 2234051319
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 58 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| MYTEMP | 1 | 28 | 58 (0)| 00:00:01 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> CREATE Table MyTable
2 AS SELECT
3 DECODE(MOD(ROWNUM,2),0,'N','Y') Flag1,
4 DECODE(MOD(ROWNUM,2),0,'Y','N') Flag2, AO.*
5 FROM ALL_OBJECTS AO;
Table created.
SQL> SELECT Count(*) From MyTable;
COUNT(*)
----------
71880
SQL> CREATE INDEX T_IND ON MyTable(Flag1,Flag2);
Index created.
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(USER,'MYTABLE',
3 METHOD_OPT=>'For All Indexed Columns Size 254');
4 END;
5 /
PL/SQL procedure successfully completed.
SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT * From MyTable Where Flag1='N' And Flag2='N';
Execution Plan
----------------------------------------------------------
Plan hash value: 1015944200
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17969 | 1772K| 299 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| MYTABLE | 17969 | 1772K| 299 (1)| 00:00:04 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FLAG1"='N' AND "FLAG2"='N')
SQL> SELECT /*+ Dynamic_Sampling(MyTable 3) */ * From MyTable Where Flag1='N' And Flag2='N';
Execution Plan
----------------------------------------------------------
Plan hash value: 270124127
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 707 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MYTABLE | 7 | 707 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IND | 7 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FLAG1"='N' AND "FLAG2"='N')
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> DESC EMPLOYEE_DATA;
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------
EMP_NO NUMBER
ENAME VARCHAR2(10)
DEPTNO NUMBER
HIREDATE DATE
DNAME VARCHAR2(10)
DLOC VARCHAR2(10)
SALARY NUMBER(8,2)
JOB_ID VARCHAR2(20)
SQL> SET AUTOTRACE TRACENLY EXP
SQL> SELECT * From EMPLOYEE_DATA Where HIREDATE IS NULL;
Execution Plan
----------------------------------------------------------
Plan hash value: 4276744253
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 81 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEE_DATA | 1 | 81 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("HIREDATE" IS NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
In above
statement, Full Access path was followed which was obvious and expected that
Optimizer will use Full Table Scan as there were no Index no Constraint been used.
SQL> CREATE INDEX INDX_HD_NAME ON EMPLOYEE_DATA(HIREDATE,ENAME);
Index created.
SQL> SELECT * From EMPLOYEE_DATA Where HIREDATE IS NULL;
Execution Plan
----------------------------------------------------------
Plan hash value: 4276744253
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 81 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEE_DATA | 1 | 81 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("HIREDATE" IS NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
So, even
after creating Index on Column it has followed Full Scan access path. Why?SQL> Alter table EMPLOYEE_DATA modify ENAME NOT NULL;
Table altered.
SQL> DESC EMPLOYEE_DATA;
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------
EMP_NO NUMBER
ENAME NOT NULL VARCHAR2(10)
DEPTNO NUMBER
HIREDATE DATE
DNAME VARCHAR2(10)
DLOC VARCHAR2(10)
SALARY NUMBER(8,2)
JOB_ID VARCHAR2(20)
SQL> SELECT * From EMPLOYEE_DATA Where HIREDATE IS NULL;
Execution Plan
----------------------------------------------------------
Plan hash value: 1371098230
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 81 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE_DATA | 1 | 81 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX_HD_NAME | 2 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("HIREDATE" IS NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
As you can
see Optimizer has followed Index Scan path as Optimizer doesn’t need to Assume
about NULLs.SQL> Create Index INDX_EMPNO ON EMPLOYEE_DATA(EMP_NO);
Index created.
SQL> CREATE OR REPLACE FUNCTION RETURN_EMPNO(EID IN NUMBER) RETURN NUMBER
DETERMINISTIC
IS BEGIN
RETURN EID;
END;
/
Function created.
SQL> Drop Index INDX_HD_NAME;
Index dropped.
SQL> CREATE INDEX INDX_HD_NAME ON EMPLOYEE_DATA(HIREDATE,RETURN_EMPNO(EMP_NO));
Index created.
SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT * From EMPLOYEE_DATA Where HIREDATE IS NULL;
Execution Plan
----------------------------------------------------------
Plan hash value: 4276744253
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 81 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEE_DATA | 1 | 81 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("HIREDATE" IS NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> SELECT * From EMPLOYEE_DATA Where HIREDATE IS NULL AND RETURN_EMPNO(EMP_NO) IS NOT NULL;
Execution Plan
----------------------------------------------------------
Plan hash value: 1371098230
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 94 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE_DATA | 1 | 94 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX_HD_NAME | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("HIREDATE" IS NULL)
filter("MYUSER"."RETURN_EMPNO"("EMP_NO") IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> SELECT /*+ RULE */ * From EMPLOYEE_DETAILS E, DEPARTMENT D
Where E.DEPTNO=D.DEPTNO;
Execution Plan
----------------------------------------------------------
Plan hash value: 1994740439
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | MERGE JOIN | |
| 2 | SORT JOIN | |
| 3 | TABLE ACCESS FULL|DEPARTMENT |
|* 4 | SORT JOIN | |
| 5 | TABLE ACCESS FULL|EMPLOYEE_DETAILS|
------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
Note
-----
- rule based optimizer used (consider using cbo)
SQL> Create Index INDX_DEPT on DEPARTMENT(DEPTNO);
Index created.
SQL> SELECT /*+ RULE */ * From DEPARTMENT D,EMPLOYEE_DETAILS E
Where E.DEPTNO=D.DEPTNO;
Execution Plan
----------------------------------------------------------
Plan hash value: 1430033380
---------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS FULL | EMPLOYEE_DETAILS |
|* 4 | INDEX RANGE SCAN | INDX_DEPT |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT |
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"="D"."DEPTNO")
Note
-----
- rule based optimizer used (consider using cbo)
SQL> SELECT * From DEPARTMENT D, EMPLOYEE_DETAILS E
Where E.DEPTNO=D.DEPTNO;
Execution Plan
----------------------------------------------------------
Plan hash value: 1365186588
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 536 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 8 | 536 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 5 | 125 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | INDX_DEPT | 5 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 8 | 336 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMPLOYEE_DETAILS | 8 | 336 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> SELECT /*+ ordered */ * From EMPLOYEE_DETAILS E, DEPARTMENT D
Where E.DEPTNO=D.DEPTNO;
Execution Plan
----------------------------------------------------------
Plan hash value: 124983262
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 536 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 8 | 536 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMPLOYEE_DETAILS | 8 | 336 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPARTMENT | 5 | 125 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> SELECT /*+ ordered */ * From DEPARTMENT D, EMPLOYEE_DETAILS E
Where E.DEPTNO=D.DEPTNO;
Execution Plan
----------------------------------------------------------
Plan hash value: 1365186588
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 536 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 8 | 536 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 5 | 125 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | INDX_DEPT | 5 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 8 | 336 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMPLOYEE_DETAILS | 8 | 336 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2)