no_merge/merge vs no_unnest/unnest

最新推荐文章于 2022-06-24 20:01:10 发布
转载 最新推荐文章于 2022-06-24 20:01:10 发布 · 1.2k 阅读 · 0
文章标签：

#no_merge #unnest #no_unnest #merge

I sometimes get confused about the difference between (no_)merge and(no_)unnest. I just do some test here to make the difference clearly.

The original sql and its plan are below. I’ll hint the sql with no_merge andno_unnest. You will find the difference quickly.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
select * from emp where deptno in (select deptno from dept where dname='SALES');
 
----------------------------------------------------------
Plan hash value: 844388907
 
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     4 |   208 |     6  (17)| 00:02:09 |
|   1 |  MERGE JOIN                  |         |     4 |   208 |     6  (17)| 00:02:09 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     2   (0)| 00:00:43 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:22 |
|*  4 |   SORT JOIN                  |         |    13 |   507 |     4  (25)| 00:01:26 |
|   5 |    TABLE ACCESS FULL         | EMP     |    13 |   507 |     3   (0)| 00:01:05 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("DNAME"='SALES')
   4 - access("DEPTNO"="DEPTNO")
       filter("DEPTNO"="DEPTNO")

I try the hint no_merge in order to avoid merging the subquery. This obviously don’t work.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SQL> select * from emp where deptno in (select /*+ no_merge */ deptno from dept where dname='SALES');
 
----------------------------------------------------------
Plan hash value: 844388907
 
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     4 |   208 |     6  (17)| 00:02:09 |
|   1 |  MERGE JOIN                  |         |     4 |   208 |     6  (17)| 00:02:09 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     2   (0)| 00:00:43 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:22 |
|*  4 |   SORT JOIN                  |         |    13 |   507 |     4  (25)| 00:01:26 |
|   5 |    TABLE ACCESS FULL         | EMP     |    13 |   507 |     3   (0)| 00:01:05 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("DNAME"='SALES')
   4 - access("DEPTNO"="DEPTNO")
       filter("DEPTNO"="DEPTNO")

Then I try the hint no_unnest. It works now. That means (no_)unest works only in the where clause. So I guess (no_)merge is only suitable after the from.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SQL> select * from emp where deptno in (select /*+ no_unnest */ deptno from dept where dname='SALES');
 
----------------------------------------------------------
Plan hash value: 2809975276
 
----------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |         |     4 |   156 |     6   (0)| 00:02:09 |
|*  1 |  FILTER              |         |       |       |        |          |
|   2 |   TABLE ACCESS FULL      | EMP     |    13 |   507 |     3   (0)| 00:01:05 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     1   (0)| 00:00:22 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "DEPT" "DEPT" WHERE
          "DEPTNO"=:B1 AND "DNAME"='SALES'))
   3 - filter("DNAME"='SALES')
   4 - access("DEPTNO"=:B1)

I do another test with the original sql below:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SQL> select * from emp, (select * from dept where dname = 'SALES') dept where dept.deptno = emp.deptno;
 
----------------------------------------------------------
Plan hash value: 844388907
 
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     4 |   236 |     6  (17)| 00:02:09 |
|   1 |  MERGE JOIN                  |         |     4 |   236 |     6  (17)| 00:02:09 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     2   (0)| 00:00:43 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:22 |
|*  4 |   SORT JOIN                  |         |    13 |   507 |     4  (25)| 00:01:26 |
|   5 |    TABLE ACCESS FULL         | EMP     |    13 |   507 |     3   (0)| 00:01:05 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("DNAME"='SALES')
   4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
       filter("DEPT"."DEPTNO"="EMP"."DEPTNO")

you will see the no_unest hint doesn’t work.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SQL> select * from emp, (select /*+ no_unnest */ * from dept where dname = 'SALES') dept where dept.deptno = emp.deptno;
 
----------------------------------------------------------
Plan hash value: 844388907
 
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     4 |   236 |     6  (17)| 00:02:09 |
|   1 |  MERGE JOIN                  |         |     4 |   236 |     6  (17)| 00:02:09 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     2   (0)| 00:00:43 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:22 |
|*  4 |   SORT JOIN                  |         |    13 |   507 |     4  (25)| 00:01:26 |
|   5 |    TABLE ACCESS FULL         | EMP     |    13 |   507 |     3   (0)| 00:01:05 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("DNAME"='SALES')
   4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
       filter("DEPT"."DEPTNO"="EMP"."DEPTNO")

Now I hint the original sql with the hint no_merge. It works. That’s it!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> select * from emp, (select /*+ no_merge */ * from dept where dname = 'SALES') dept where dept.deptno = emp.deptno;
 
----------------------------------------------------------
Plan hash value: 2910064727
 
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     4 |   276 |     7  (15)| 00:02:19 |
|*  1 |  HASH JOIN          |      |     4 |   276 |     7  (15)| 00:02:19 |
|   2 |   VIEW              |      |     1 |    30 |     3   (0)| 00:01:05 |
|*  3 |    TABLE ACCESS FULL| DEPT |     1 |    20 |     3   (0)| 00:01:05 |
|   4 |   TABLE ACCESS FULL | EMP  |    13 |   507 |     3   (0)| 00:01:05 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
   3 - filter("DNAME"='SALES')
Oracle知识总结（三）| Oracle表查询变换优化思路
xiaobinhxb的博客
08-12 1056
Oracle表查询变换优化思路，包括子查询非嵌套、视图合并、谓词推入、让半连接和反连接作驱动表
解决Oracle SQL语句性能问题（10.5）——常用Hint及语法（5）（查询转化相关Hint）
数据库生态圈（RDB & NoSQL & Bigdata）——专注于关系库优化（Oracle&Mysql&PG&SQL Server ）
01-09 584
本文对解决OracleSQL语句性能问题——常用Hint及语法（查询转化相关Hint），结合实例进行了详尽的说明和讲解，并对注意事项、关键知识点和选项等进行了重点标注和详尽解析，以便于读者进行深入学习和理解。
利用NO_MERGE解决数据字典视图访问低效 ZT
csdn_201601的博客
06-30 188
=========================================================== 作者: yangtingkun(http://yangtingkun.itpub.net)发表于: 2009...
oracle no_merge,利用NO_MERGE解决数据字典视图访问低效
weixin_42299244的博客
04-09 1662
数据库的数据字典视图的访问一般都比较迅速，因为Oracle已经做了专门的优化工作，但是有些时候，几个数据字典进行关联的时候，CBO就没有那么智能了，有时候会给出一些效率很低的执行计划。比如在尝试构造一个大表的时候，想要构造DBA_OBJECTS和另一张数据量不大的表DBA_SEQUENCES的笛卡儿积：[oracle@localhost ~]$ sqlplus test/testSQL*Plus:...
no_merge的含义
cotchte0421的博客
08-01 557
create table wxh_tbd1 as select * from dba_objects;create table wxh_tbd2 as select * from dba_objects; create ind...
MERGE 与NO_MERGE
u010719917的专栏
11-18 6567
19. /*+ MERGE(TABLE) */     能够对视图的各个查询进行相应的合并.     例如:     SELECT /*+ MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELET DPT_NO     ,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO)
Oracle Hint no_merge(merge)、no_unnest(unnest)的作用对象陷阱
PiscesCanon的博客
10-27 230
Oracle Hint no_merge(merge)、no_unnest(unnest)的作用对象陷阱 Oracle的查询转换中有两个概念，子查询展开和视图合并。 关于两者的概念这里不赘述，可以看看崔华的《基于Oracle的SQL优化》这本书。 其中，no_unnest(unnest)这个Hint显式控制是否对子查询做展开，no_unnest表示该子查询不展开，unnest表示子查询展开。 而...
oracle三组难缠的hint no_unnest/unnest,push_subq,push_pred
Colin Lau Oracle
11-30 6194
最近在看一个哥们优化sql里面,使用到了几个特殊的hint，但我总是对此不解，所以针对此问题自己做了一些测试(参考了一些资料)  1. no_unnest, unnest  unnest我们称为对子查询展开，顾名思义，就是别让子查询孤单地嵌套(nest)在里面。  所以un_unnest双重否定代表肯定，即让子查询不展开，让它嵌套(nest)在里面。  如果一个查询中的where 中出现
6.4常见的Hint《基于Oracle的优化（学习笔记）》
Zhengyixiao的专栏
11-14 384
与优化器相关的Hint /*+ ALL_ROWS */ 它的含义是启用CBO   /*+ FIRST_ROWS(n) */ 优化器启用CBO，而且依据返回头n条的记录的响应时间来决定SQL的执行计划。   注意，first_rows(n)与first_rows_n并不是一一对应的   /*+ RULE */ 如果目标SQL使用了并行执行，就意味着其中的RULE Hint会失效，...
Oracle SQL谓词推进、视图合并
07-07 1689
Oracle SQL谓词推进、视图合并
SQL中Merge用法详解
12-15
MERGE语句是SQL语句的一种。在SQL Server、Oracle数据库中可用，MySQL、PostgreSQL中不可用。MERGE是Oracle9i新增的语法，用来合并UPDATE和INSERT语句。通过MERGE语句，根据一张表（原数据表，source table）或子查询的连接条件对另外一张（目标表，target table）表进行查询，连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT+UPDATE。 merge主要用于两表之间的关联操作 oracle中 merge: 从oracle 9i开始支持
提示NO_MERGE的用法
南山南的Python和大数据学习笔记
12-17 1649
这两天优化SQL时候，常用到NO_MERGE，但是估计很多人不太明白，先简单说一下它的含义： NO_MERGE(V)：就是告诉oracle在解析一个SQL的执行计划的时候，不要将视图V中的内容和其他部分做MERGE合并整理生成执行计划，而是将视图V作为单独的一部分执行之后将结果再和其他部分进行关联 比如SQL0： WITH V AS( SELECT * FROM TAB_
oracle hint no_merge
12-18 2000
oracle hint no_merge强制oracle不把子查询inline view和outer view外层查询合并为一个单一的查询,即子查询运行出结果集再和外层查询的其它结果集进行关联操作下为测试结...
利用no_merge优化
weixin_30886233的博客
12-05 300
SQL> select a.unit3_code 机构编码, 2 a.unit3_name 机构名称, 3 a.dept1_code 部门编码, 4 a.dept1_name 部门名称, 5 a.mgr_code 客户经理编码, 6 a.mgr_name 客户经理名称...
lightdb21.3-新增优化器提示no_merge
紫无之紫的博客
06-24 242
新增no_merge hint用来建议优化器不提升子查询，即子查询运行出结果集再和外层查询的其它结果集进行关联操作 。一般适用于子查询结果集很小等情况，如果此时提升子查询会导致查询变慢。具体用法如下：...
使用no_merge hint优化MERGE JOIN CARTESIAN
weixin_33725515的博客
07-28 629
开发说下面这个sql语句跑不出结果SELECT t1.order_id, t2.order_name, t1.order_flow_no, t1.order_type, t1.agent_id, t1.money, t1.order_create_time FROM(SELECT...
oracle distinct 优化 no_merge,“no_merge”hints优化视图访问低效问题
weixin_35080040的博客
04-05 586
今天在itpub论坛看到一则贴子，楼主反应以下sql执行缓慢：create table test_20140230 asselect to_number(201401) deal_date, e.src_terminal_id, e.cont_typefrom (select *from test1 awhere a.forward_time >= 201401 || '00000000'a...
Oracle三组难缠的hint no_unnest/unnest，push_subq，push_pred
jarry_gao的专栏
05-30 3025
Oracle三组难缠的hint no_unnest/unnest，push_subq，push_pred 2012-02-24   友情使欢欣倍增，使疾苦减半。经常有人把这三个hint搞混，主如果因为对三种重写道理不清楚。特总结如下。（实验景象为10204） 1. no_unnest， unnest unnest我们称为对子查询展开，顾名思义，就是别让子查询孤单地嵌套（ne
mysql 介绍一下hint /*+no_merge*/
最新发布
03-25
<think>好的，我现在需要回答用户关于MySQL中的/*+ NO_MERGE */提示的问题。首先，我要确认用户的问题是什么。用户想了解这个特定的优化器提示的作用和用法。 首先，我应该回忆一下MySQL中的优化器提示（Optimizer Hints）的基本知识。优化器提示是用来指导查询优化器如何执行查询的指令，通常以注释的形式嵌入到SQL语句中。这些提示可以影响执行计划，比如选择索引、连接顺序等。 接下来，我需要确定/*+ NO_MERGE */的具体作用。根据之前的了解，NO_MERGE通常用于阻止优化器将视图或派生表合并到外部查询中。也就是说，当查询中包含子查询、派生表或者视图时，优化器可能会选择将其与外部查询合并，以便优化执行计划。但有时候这种合并可能导致性能问题，这时候使用NO_MERGE可以保持派生表或视图的独立性，让优化器单独处理它们。 然后，我需要考虑用户可能的实际应用场景。例如，当子查询很复杂，或者合并后导致执行计划不佳时，使用NO_MERGE可以强制优化器单独处理该部分，可能会提高性能。或者，在某些情况下，用户可能希望明确控制执行流程，避免优化器的自动优化带来的不可预测结果。 还需要验证语法是否正确。在MySQL中，优化器提示的格式通常是/*+ ... */，放在SELECT、UPDATE等关键字之后。例如：SELECT /*+ NO_MERGE(derived_table) */ ... FROM (SELECT ...) AS derived_table ...; 另外，要注意不同MySQL版本的支持情况。可能某些提示只在特定版本中有效，或者行为有所不同。需要确认当前文档或官方资料中关于NO_MERGE的说明，确保信息的准确性。 同时，应该举例说明如何使用这个提示。比如，一个包含派生表的查询，添加NO_MERGE后，执行计划的变化。可以比较有无该提示时的EXPLAIN输出，说明差异。 还要提醒用户使用时需要注意的地方，比如是否会影响性能，是否适用于特定场景，以及可能的副作用。例如，阻止合并可能导致临时表的生成，增加内存或磁盘的使用，需要权衡利弊。 最后，总结NO_MERGE的作用、适用场景、使用方法和注意事项，帮助用户全面理解该提示的用途。</think>以下是关于MySQL中`/*+ NO_MERGE */`优化器提示的详细解析，帮助你理解其作用和使用场景： --- ### **1. 核心作用** `/*+ NO_MERGE */` 是一种 **优化器提示（Optimizer Hint）**，用于阻止MySQL将 **派生表（Derived Table）** 或 **视图（View）** 合并（Merge）到外层查询中。 通过强制优化器单独处理子查询或视图，可以避免某些情况下因自动合并导致的性能问题或执行计划偏差。 --- ### **2. 使用场景** #### **(1) 保留派生表的独立性** 默认情况下，MySQL会尝试将派生表（如子查询生成的临时表）与外层查询合并以优化执行计划。但以下场景需手动阻止合并： - 派生表包含复杂计算或聚合函数，合并后可能导致性能下降。 - 需要明确控制执行顺序或强制使用临时表。 #### **(2) 调试对比执行计划** 通过对比使用`NO_MERGE`前后的执行计划（`EXPLAIN`），分析合并操作对性能的影响。 --- ### **3. 语法格式** 将提示直接嵌入SQL语句的注释中，格式为`/*+ NO_MERGE(表别名) */`： ```sql SELECT /*+ NO_MERGE(derived_table) */ derived_table.id, users.name FROM ( SELECT user_id AS id, COUNT(*) AS order_count FROM orders GROUP BY user_id ) AS derived_table -- 必须为派生表指定别名 JOIN users ON derived_table.id = users.id; ``` --- ### **4. 执行计划对比** 通过`EXPLAIN`观察优化器行为变化： #### **(1) 未使用`NO_MERGE`时** ```sql EXPLAIN SELECT derived_table.id, users.name FROM ( SELECT user_id AS id, COUNT(*) AS order_count FROM orders GROUP BY user_id ) AS derived_table JOIN users ON derived_table.id = users.id; ``` - 结果可能显示派生表被合并到外层查询（`select_type: PRIMARY`，无临时表）。 #### **(2) 使用`NO_MERGE`后** ```sql EXPLAIN SELECT /*+ NO_MERGE(derived_table) */ derived_table.id, users.name FROM (...) AS derived_table JOIN users ...; ``` - 结果中派生表会以独立步骤处理（`select_type: DERIVED`，生成临时表）。 --- ### **5. 实际应用示例** **场景**：统计用户订单数量，并筛选出订单数大于10的用户及其姓名。 **问题**：当派生表被合并时，可能无法有效利用索引或导致全表扫描。 **解决方案**：强制独立处理派生表： ```sql SELECT /*+ NO_MERGE(order_stats) */ order_stats.user_id, users.name, order_stats.total_orders FROM ( SELECT user_id, COUNT(*) AS total_orders FROM orders GROUP BY user_id ) AS order_stats -- 派生表别名 JOIN users ON order_stats.user_id = users.id WHERE order_stats.total_orders > 10; ``` --- ### **6. 注意事项** 1. **别名必填**：必须为派生表或视图指定别名，并在提示中引用该别名。 2. **性能权衡**： - ✅ 优点：可能避免低效的合并操作，减少全表扫描。 - ❌ 缺点：强制生成临时表可能增加内存或磁盘开销。 3. **版本兼容性**：优化器提示需MySQL 5.7+支持，部分版本可能需要调整语法（如Percona分支）。 --- ### **总结对比表** | **行为** | 默认合并派生表 | 使用`/*+ NO_MERGE */` | |-------------------------|-------------------------|-----------------------------| | **派生表处理方式** | 合并到外层查询 | 独立生成临时表 | | **执行计划中的`select_type`** | 可能显示`PRIMARY` | 显示`DERIVED` | | **适用场景** | 简单子查询优化 | 复杂聚合、强制独立处理 | 合理使用`NO_MERGE`可以帮助优化复杂查询，但需结合`EXPLAIN`分析验证效果！
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值