no_merge hint

阅读原文时间：2023年07月11日阅读：1

This is tested in 10gR2.

SQL> select * from v$version;

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 |