hint_merge
1,optimizer_secure_view_merging
2,如果查詢塊包括group by或在select中有distinct,且開啟complex view merging
最佳化器自動把查詢塊合併到整個訪問SQL語句中
3,如果子查詢不是關聯子查詢會把in子查詢合併到整個訪問SQL語句中
4,在11g中測試,exist與in(關聯與非聯)子查詢,皆可以用merge

SQL> explain plan for select emp.deptno from (select distinct deptno as deptno
from emp) emp,dept where emp.deptno=dept.deptno;

已解釋。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 2714056117

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3 | 48 | 3 (34)| 00:00:01 |

| 1 | NESTED LOOPS | | 3 | 48 | 3 (34)| 00:00:01 |

| 2 | VIEW | | 3 | 39 | 3 (34)| 00:00:01 |

| 3 | HASH UNIQUE | | 3 | 9 | 3 (34)| 00:00:01 |

| 4 | TABLE ACCESS FULL| EMP | 12 | 36 | 2 (0)| 00:00:01 |

|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 3 | 0 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

已選擇17行。

SQL> explain plan for select /*+ merge(emp) */ emp.deptno from (select distinc
t deptno as deptno from emp) emp,dept where emp.deptno=dept.deptno;

已解釋。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 2673482842

--------------------------------------------------------------------------------

--

--------------------------------------------------------------------------------

--

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3 | 39 | 3 (34)| 00:00:01

|

| 1 | VIEW | VM_NWVW_1 | 3 | 39 | 3 (34)| 00:00:01

|

| 2 | HASH UNIQUE | | 3 | 54 | 3 (34)| 00:00:01

|

| 3 | NESTED LOOPS | | 12 | 216 | 2 (0)| 00:00:01

|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| 4 | TABLE ACCESS FULL| EMP | 12 | 36 | 2 (0)| 00:00:01

|

|* 5 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 15 | 0 (0)| 00:00:01

|

--------------------------------------------------------------------------------

--

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("DEPTNO"="DEPT"."DEPTNO")

已選擇17行。

SQL>
SQL> explain plan for select /*+ merge(emp) */ emp.deptno from (select distinc
t deptno as deptno from emp where deptno in (select deptno from dept)) emp,dept
where emp.deptno=dept.deptno;

已解釋。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 2673482842

--------------------------------------------------------------------------------

--

--------------------------------------------------------------------------------

--

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3 | 39 | 3 (34)| 00:00:01

|

| 1 | VIEW | VM_NWVW_1 | 3 | 39 | 3 (34)| 00:00:01

|

| 2 | HASH UNIQUE | | 3 | 54 | 3 (34)| 00:00:01

|

| 3 | NESTED LOOPS | | 12 | 216 | 2 (0)| 00:00:01

|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| 4 | TABLE ACCESS FULL| EMP | 12 | 36 | 2 (0)| 00:00:01

|

|* 5 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 15 | 0 (0)| 00:00:01

|

--------------------------------------------------------------------------------

--

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("DEPTNO"="DEPT"."DEPTNO")

已選擇17行。

SQL>

SQL> explain plan for select /*+ merge(emp) */ emp.deptno from (select distinc
t deptno as deptno from emp where deptno in (select deptno from dept where deptn
o=emp.deptno)) emp,dept where emp.deptno=dept.deptno;

已解釋。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 2673482842

--------------------------------------------------------------------------------

--

--------------------------------------------------------------------------------

--

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3 | 39 | 3 (34)| 00:00:01

|

| 1 | VIEW | VM_NWVW_1 | 3 | 39 | 3 (34)| 00:00:01

|

| 2 | HASH UNIQUE | | 3 | 54 | 3 (34)| 00:00:01

|

| 3 | NESTED LOOPS | | 12 | 216 | 2 (0)| 00:00:01

|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|* 4 | TABLE ACCESS FULL| EMP | 12 | 36 | 2 (0)| 00:00:01

|

|* 5 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 15 | 0 (0)| 00:00:01

|

--------------------------------------------------------------------------------

--

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("DEPTNO" IS NOT NULL AND "DEPTNO" IS NOT NULL)
5 - access("DEPTNO"="DEPT"."DEPTNO")

已選擇18行。

SQL>

oracle_merge