This is tested in 10gR2.
SQL> select * from v$version; BANNER ------------------------------------------------ Oracle Database 10g Enterprise Edition Release 1 0.2.0.5.0 - 64bi PL/SQL Release 10.2.0.5.0 - Production CORE 10.2.0.5.0 Production TNS for Solaris: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 - Production
First, we create a view.
SQL> create view v_dept as select deptno,dname from dept; View created.
Then check SQL
SQL> explain plan for select e.ename,d.dname from emp2 e, v_dept d where e.deptno=d.deptno;
Explained.
SQL> set linesize 180
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 827386235
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 308 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 308 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 126 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP2 | 14 | 126 | 3 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"="DEPTNO")
filter("E"."DEPTNO"="DEPTNO")
18 rows selected.
use no_merge
SQL> explain plan for select /*+ no_merge(d) */ e.ename,d.dname from emp2 e, v_dept d where e.deptno=d.deptno;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2242990105
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 434 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 434 | 7 (15)| 00:00:01 |
| 2 | VIEW | V_DEPT | 4 | 88 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP2 | 14 | 126 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
16 rows selected.
SQL>