oracle_merge
hint_merge
1,optimizer_secure_view_merging
2,如果查询块包括group by或在select中有distinct,且开启complex view merging
优化器自动把查询块合并到整个访问SQL语句中
3,如果子查询不是关联子查询会把in子查询合并到整个访问SQL语句中
4,在11g中测试,exist与in(关联与非联)子查询,皆可以用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;
from emp) emp,dept where emp.deptno=dept.deptno;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan hash value: 2714056117
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 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;
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
--------------------------------------------------------------------------------
--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
|
--------------------------------------------------------------------------------
--
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> 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
--------------------------------------------------------------------------------
--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
|
--------------------------------------------------------------------------------
--
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
--------------------------------------------------------------------------------
--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
|
--------------------------------------------------------------------------------
--
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")
5 - access("DEPTNO"="DEPT"."DEPTNO")
已选择18行。
SQL>
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/9240380/viewspace-751381/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- redhat要持续学习哟加加油
- 升级redhat 6.8 默认gdb 7.2到gdb 8.3.1操作指南
- redhat 6.8升级默认cmake 2.8到cmake 3.9
- mysql source code源代安装的前提条件requirement
- mysql handler语句之一
- mysql show open tables相关知识体系之一
- mysql snapshot read快照读及current read当前读与锁lock之一
- mysql 5.7 _rowid虚列之一
- mysql innodb lock锁之record lock之一
- mysql隐式提交事务transaction一点笔记
