1.熟悉环境

jcq0>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

2.辨明是非

究竟merge是merge还是merge

2.1 merge statement

在项目开发过程中,尤其是ETL过程中经常有这样的需求,有一个表A(通常为源表),另有一个表B(通常为目标表),本来A表和B表的数据是同步的,一段时间之后,A表的数据发生了新增和修改,如果现在要将A表的数据同步到B表就要分两步来做了:
(1) 把B表现有的数据根据A表update
(2) 把A表有B表没有的数据insert
但是黑暗的历史终于过去了,自从有了merge,我腰不酸了,背也不疼了,脚也不抽筋了,走路也有劲了
怎么merge呢,示例如下:

MERGE INTO bonuses B
   USING (SELECT employee_id, salary, department_id FROM employees
   WHERE department_id = 80) A
   ON (B.employee_id = A.employee_id)
   WHEN MATCHED THEN UPDATE SET B.bonus = B.bonus + A.salary*.01
   WHEN NOT MATCHED THEN INSERT (B.employee_id, B.bonus)
   VALUES (A.employee_id, A.salary*0.1);

这样不但把两步合二为一了,而且效率还很高.

2.2 merge hints

在大数据量的开发,测试或生产环境中,基于cbo的sql优化器有时会选择错误的执行计划而引发各种问题,这时就需要我们使用hints来固定执行计划.
用上hints之后,sql语句就变乖了,执行计划你想让它怎么走它就怎么走,绝对服从命令.

当存在view或者nest view时,有时表间的连接顺序可能并不是你想象的那样(下面例子加了hints merge仅为模拟视图合并的情况):

SQL> SELECT /*+ merge(v) */ t1.x, v.y
2 FROM t1 ,
3     (SELECT t2.x, t3.y
4     FROM t2, t3
5     WHERE t2.x = t3.x) v
6 WHERE t1.x = v.x and t1.y = 1;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=7 Card=1 Bytes=65)
   1    0   HASH JOIN (Cost=7 Card=1 Bytes=65)
   2    1     MERGE JOIN (CARTESIAN) (Cost=4 Card=67 Bytes=3484)
   3    2       TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=1 Bytes=26)
   4    2       BUFFER (SORT) (Cost=2 Card=1 Bytes=26)
   5    4         TABLE ACCESS (FULL) OF 'T3' (Cost=2 Card=1 Bytes=26)
   6    1     TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=82 Bytes=1066)

可以看到最外层的表t1先和nest view中的表t3 join,然后再于nest view中的另一个表t2 join

对应得如果不想视图合并

SQL> SELECT /*+ no_merge(v) */ t1.x, v.y
2 FROM t1 ,
3     (SELECT t2.x, t3.y
4     FROM t2, t3
5     WHERE t2.x = t3.x) v
6 WHERE t1.x = v.x and t1.y = 1;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   MERGE JOIN
   2    1     MERGE JOIN
   3    2       SORT (JOIN)
   4    3         TABLE ACCESS (FULL) OF 'T3'
   5    2       SORT (JOIN)
   6    5         TABLE ACCESS (FULL) OF 'T2'
   7    1     SORT (JOIN)
   8    7       TABLE ACCESS (FULL) OF 'T1'

对于merge来说,一个指的是sql语句,一个指的是hints,这回清楚了吧

3.问题描述

merge /*+use_nl(t s)*/into jcq0_t t
using
(
    select /*+use_nl(a b) leading(a)*/a.*
     from jcq0_a a,jcq0_b b
     where a.aa=b.bb
   ) s
when ...
....

我想让a做驱动表,跟b做nest loop, 查询的结果集s再跟t表做nest loop

我加了hint,但是执行的结果是a先关联了t表,最后关联了b表

是不是可以加点什么来改变一下?

4.问题解决

说到这里凡是有脑子的都应该知道答案了吧

哦?还不知道?
兄弟初学,不知道也是可以理解滴

教科书式的标准答案如下

merge /*+ no_merge(s) use_nl(t s) */into jcq0_t t
using
(
    select /*+use_nl(a b) leading(a)*/a.*
     from jcq0_a a,jcq0_b b
     where a.aa=b.bb
   ) s

.......

5.相关参考

如果想进一步了解hints merge的某些特性
http://space.itpub.net/14130873/viewspace-521121
如果想进一步了解hints请参考
http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/hintsref.htm#8326

(需要引用, 请注明出处:痴情甲骨文http://space.itpub.net/14130873)

ITPub博客

优化小case--究竟是merge还是merge

最新文章