merge into的效率问题，寻求帮助

xwwcm 2009-03-12 04:12:49
merge into A
using B
on(条件)
。。。。
A表是7千万的大表（分区表，每天这么多数据量），B表数据量在400万左右，on条件为
A的分区字段=一个值以及3个主键的关联关系
现在每天执行一下要2个多小时，请问有没有好的办法该进下？
B表由于每天数据都要truncate和重新insert，所以不好建索引
请哪位大拿帮帮忙，thanks
...全文
2466 25 打赏 收藏 转发到动态 举报
写回复
用AI写文章
25 条回复
切换为时间正序
请发表友善的回复…
发表回复
yaoqingongzhu 2011-07-18
  • 打赏
  • 举报
 回复
学习！
zhangdp_neu 2009-03-18
  • 打赏
  • 举报
 回复
感觉楼主的业务逻辑是每天对上来的数据做分析,应该是有个和历史数据比对的过程,
我前段时间也遇到过一个这样的问题,但是数据量在 1000万左右.
但是由于比对的过程比较复杂,所以就没有用merge语句.
采用的是内存数据库,在内存中进行分析,让后将分析的结果保存到历史库中.
duwei1116 2009-03-18
  • 打赏
  • 举报
 回复
[Quote=引用 17 楼 xiaoxiao1984 的回复:]
看着很迷惑

1.按月分区，然后先清空当前这个月分区内所有数据，根据上个月的数据先插入数据
2.清空表TEMP_DQ_USER_SVC数据，重新生成表TEMP_DQ_USER_SVC的数据
3.merge into 表A

楼主尝试一下在开始的时候计算时间到底耗费大概是怎么样的
v_date1 number;
v_date2 number;
v_date3 number;

v_date1 := dbms_utility.get_time ;
dbms_output.put_line('---------------Begin Time: ' || v_date1 || ' ---…
[/Quote]
正解
hyee 2009-03-16
  • 打赏
  • 举报
 回复
你需要列举你Merge的执行计划才能确定分区判断有没有起预计的作用。另外,你可以试试如下改动会不会对速度有提高：
1.不用将A的数据复制到临时表
2.在表A建立USER_NO,SERVICE_ID,START_DATE,PART_ID的局部(LOCAL)复合索引
3.将MERGE /*+parallel(a,4) parallel(b,4)*/改为MERGE /*+leading(b) use_nl(b a)*/
xwwcm 2009-03-13
  • 打赏
  • 举报
 回复
V_DAY := SUBSTR(V_ACCT_DAY, 7, 2);
V_ROWCOUNT := 0;
V_SQL := 'ALTER TABLE DWD_DQ_USER_SVC TRUNCATE PARTITION PART' || V_DAY;
EXECUTE IMMEDIATE V_SQL;

--老数据的插入
INSERT /*+APPEND*/
INTO DWD_DQ_USER_SVC NOLOGGING
SELECT /*+PARALLEL(A,4)*/
V_ACCT_DAY,
A.USER_NO,
A.SERVICE_ID,
A.MAIN_FLAG,
A.START_DATE,
A.END_DATE,
A.ITEM_ID,
A.PACKAGE_ID,
A.USER_NO_A,
V_DAY
FROM DWD_DQ_USER_SVC A
WHERE PART_ID =
SUBSTR(TO_CHAR(TO_DATE(V_ACCT_DAY, 'YYYYMMDD') - 1, 'YYYYMMDD'),
7,
2)
AND NVL(END_DATE, V_ACCT_DAY) >= V_ACCT_DAY;
V_ROWCOUNT := SQL%ROWCOUNT;
COMMIT;
V_SQL := 'TRUNCATE TABLE TEMP_DQ_USER_SVC';
EXECUTE IMMEDIATE V_SQL;
INSERT /*+APPEND*/
INTO TEMP_DQ_USER_SVC NOLOGGING
SELECT /*+PARALLEL(A,4)*/
USER_ID,
SERVICE_ID,
MAIN_TAG,
START_DATE,
END_DATE,
ITEM_ID,
PACKAGE_ID,
USER_ID_A
FROM STAGE.TF_F_USER_SVC_INC A
WHERE DAY_ID = V_ACCT_DAY
AND ROWID = (SELECT MAX(ROWID)
FROM STAGE.TF_F_USER_SVC_INC B
WHERE B.USER_ID = A.USER_ID
AND B.SERVICE_ID = A.SERVICE_ID
AND B.START_DATE = A.START_DATE);
COMMIT;
--插入增量数据
MERGE /*+parallel(a,4) parallel(b,4)*/INTO DWD_DQ_USER_SVC A
USING TEMP_DQ_USER_SVC B
ON (A.USER_NO = B.USER_ID AND A.SERVICE_ID = B.SERVICE_ID AND A.START_DATE = B.START_DATE AND A.PART_ID = V_DAY)
WHEN MATCHED THEN
UPDATE SET A.END_DATE = B.END_DATE
WHEN NOT MATCHED THEN
INSERT
(A.ACCT_DAY,
A.USER_NO,
A.SERVICE_ID,
A.MAIN_FLAG,
A.START_DATE,
A.END_DATE,
A.ITEM_ID,
A.PACKAGE_ID,
A.USER_NO_A,
A.PART_ID)
VALUES
(V_ACCT_DAY,
B.USER_ID,
B.SERVICE_ID,
B.MAIN_TAG,
B.START_DATE,
B.END_DATE,
B.ITEM_ID,
B.PACKAGE_ID,
B.USER_ID_A,
SUBSTR(V_ACCT_DAY, 7, 2));

--计算插入数据条数
V_ROWCOUNT := V_ROWCOUNT + SQL%ROWCOUNT;
COMMIT;

V_SQL := 'TRUNCATE TABLE TEMP_DQ_USER_SVC';
EXECUTE IMMEDIATE V_SQL;
这个是我现在用的sql，昨天晚上跑了9000秒，A表和B表都是没有索引的表，A表是list分区表，按月循环建的，楼上的写法我也写了一点，但好像forall和merge一起写不行，不知道为什么？能帮我看下现在的代码有啥新思路或好办法优化一下，或者我的sql是不是写得有问题？
xiaoxiao1984 2009-03-13
  • 打赏
  • 举报
 回复
A表不是有主键么，如果A表和B表的主键一样，考虑根据根据主键查找应该非常快的，只扫描索引，没有涉及到表

cursor c is select * from B order by pk_col ;
type t_type is table of pk_col%type;
t_array t_type;
t_num number;

begin
t_num := 0;
open c;
loop
fetch c bulk collect into t_array limit 3000;
t_num := t_num + 1;
forall i in t_array.first .. t_array.last
merge into A
using B...
on (... and A.pk=t_array(i) ) //A.pk=t_array(i) 还是 B.pk=t_array(i) 具体那个比较快，可以找一个具体的值测试一下
...
exit when c%notfound;
end loop;
close c;
commit;
exception
when others then
rollback;
dbms_output.put_line(sqlerrm);
dbms_output.put_line('From '|| t_num * 3000 || ' records failed!!');
end;





xwwcm 2009-03-13
  • 打赏
  • 举报
 回复
[Quote=引用 11 楼 xiaoxiao1984 的回复:]
批量处理吧，数据太多了


SQL code
cursor c is select * from B;
type t_type is table of pk_col%type;
t_array t_type;

open c;
loop;
fetch c bulk collect into t_array limit 3000;
forall i in t_array.first .. t_array.last
merge into A
using B...
on (... and B.pk=t_array(i) )
...
exit when c%notfound;
end loop;
close c;
[/Quote]
写的时候才发现这个方法是把B表分解了，但关键是A表很大，原来A表需要全表一次，现在不是全表扫描N次吗？这个方法是不是不行？
xwwcm 2009-03-13
  • 打赏
  • 举报
 回复
[Quote=引用 11 楼 xiaoxiao1984 的回复:]
批量处理吧，数据太多了


SQL code
cursor c is select * from B;
type t_type is table of pk_col%type;
t_array t_type;

open c;
loop;
fetch c bulk collect into t_array limit 3000;
forall i in t_array.first .. t_array.last
merge into A
using B...
on (... and B.pk=t_array(i) )
...
exit when c%notfound;
end loop;
close c;
[/Quote]
我用这个方法测试一下，效率ok立刻来给分
xwwcm 2009-03-13
  • 打赏
  • 举报
 回复
改了一个方案后，速度有了很大提升，20分钟左右跑完，我是用一个临时表把前一天的数据取过来，merge的时候对临时表操作，最后把该临时表的数据插入目标表。
这样做了以后，发现有个疑问，原来我merge目标表时，on条件是加了A表的分区判断，但从速度比较来看，貌似分区判断没起作用，能请高手帮忙看看原来的过程，是不是写
得有问题？
xwwcm 2009-03-13
  • 打赏
  • 举报
 回复
[Quote=引用 18 楼 xiaoxiao1984 的回复:]
问题在v_pk(i).v_serviceid ，这个样子的语法是不支持的
不是merge into
[/Quote]
那如果用forall，这个地方应该怎么写？帮忙告诉下
xwwcm 2009-03-13
  • 打赏
  • 举报
 回复
[Quote=引用 17 楼 xiaoxiao1984 的回复:]
看着很迷惑

1.按月分区，然后先清空当前这个月分区内所有数据，根据上个月的数据先插入数据
2.清空表TEMP_DQ_USER_SVC数据，重新生成表TEMP_DQ_USER_SVC的数据
3.merge into 表A

楼主尝试一下在开始的时候计算时间到底耗费大概是怎么样的
v_date1 number;
v_date2 number;
v_date3 number;

v_date1 := dbms_utility.get_time ;
dbms_output.put_line('---------------Begin Time: ' || v_date1 || ' ---…
[/Quote]
这个数据是需要每天都跑，清空的也是当天的分区数据（如果数据有错，过程需要重新跑，要把今天已经插入的数据truncate掉）
时间消耗早前已测试，是merge into的时间消耗
xiaoxiao1984 2009-03-13
  • 打赏
  • 举报
 回复
问题在v_pk(i).v_serviceid ，这个样子的语法是不支持的
不是merge into
xiaoxiao1984 2009-03-13
  • 打赏
  • 举报
 回复
看着很迷惑

1.按月分区，然后先清空当前这个月分区内所有数据，根据上个月的数据先插入数据
2.清空表TEMP_DQ_USER_SVC数据，重新生成表TEMP_DQ_USER_SVC的数据
3.merge into 表A

楼主尝试一下在开始的时候计算时间到底耗费大概是怎么样的
v_date1 number;
v_date2 number;
v_date3 number;

v_date1 := dbms_utility.get_time ;
dbms_output.put_line('---------------Begin Time: ' || v_date1 || ' ------------------') ;
1.按月分区，然后先清空当前这个月分区内所有数据，根据上个月的数据先插入数据
v_date2 := dbms_utility.get_time ;
dbms_output.put_line('---------------First Step: ' || (v_date2 - v_date1) || ' ------------------') ;

2.清空表TEMP_DQ_USER_SVC数据，重新生成表TEMP_DQ_USER_SVC的数据
v_date3 := dbms_utility.get_time ;
dbms_output.put_line('---------------Second Step: ' || (v_date3 - v_date2) || ' ------------------') ;

3.merge into 表A
dbms_output.put_line('---------------Third Step: ' || (dbms_utility.get_time - v_date3) || ' ------------------') ;

统计一下时间耗费状况

对楼主的业务逻辑非常困惑，每次都需要把整个分区的数据清空，每次都要处理总数据1／12的数据


xwwcm 2009-03-13
  • 打赏
  • 举报
 回复
create or replace procedure p_test_a(v_acct_day in varchar2,
v_flag out varchar2) is
type pktype is record(
v_userno test_xww_temp_user_svc.user_id%type,
v_serviceid test_xww_temp_user_svc.service_id%type,
v_startdate test_xww_temp_user_svc.start_date%type);
type t_array is table of pktype;
v_pk t_array;
cursor c is
select user_id,service_id,start_date from test_xww_temp_user_svc;

begin
open c;
loop
fetch c bulk collect
into v_pk limit 3000;
for i in v_pk.first .. v_pk.last loop
merge into test_xww_user_svc A using
(select * from test_xww_temp_USER_SVC where user_id=v_pk(i).v_userno
and service_id=v_pk(i).v_serviceid and start_date=v_pk(i).v_startdate) B
on (A.USER_NO = B.USER_ID AND A.SERVICE_ID = B.SERVICE_ID AND A.START_DATE = B.START_DATE AND A.PART_ID = '12')
WHEN MATCHED THEN UPDATE SET A.END_DATE = B.END_DATE
WHEN NOT MATCHED THEN
INSERT(A.ACCT_DAY, A.USER_NO, A.SERVICE_ID, A.MAIN_FLAG, A.START_DATE, A.END_DATE, A.ITEM_ID, A.PACKAGE_ID, A.USER_NO_A, A.PART_ID)
VALUES('20090312', B.USER_ID, B.SERVICE_ID, B.MAIN_TAG, B.START_DATE, B.END_DATE, B.ITEM_ID, B.PACKAGE_ID, B.USER_ID_A, SUBSTR('20090312', 7, 2));
exit when c%notfound;
end loop;
end loop;
close c;
end;
我这样写不报错，但是如果把for i in v_pk.first .. v_pk.last loop这一段用
forall i in v_pk.first .. v_pk.last
就不行，
Error: PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
Line: 20
Text: and service_id=v_pk(i).v_serviceid and start_date=v_pk(i).v_startdate) B

Error: PLS-00382: expression is of wrong type
Line: 20
Text: and service_id=v_pk(i).v_serviceid and start_date=v_pk(i).v_startdate) B

Error: PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
Line: 20
Text: and service_id=v_pk(i).v_serviceid and start_date=v_pk(i).v_startdate) B

Error: PLS-00382: expression is of wrong type
Line: 20
Text: and service_id=v_pk(i).v_serviceid and start_date=v_pk(i).v_startdate) B

Error: PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
Line: 19
Text: (select * from test_xww_temp_USER_SVC where user_id=v_pk(i).v_userno

Error: PLS-00382: expression is of wrong type
Line: 19
Text: (select * from test_xww_temp_USER_SVC where user_id=v_pk(i).v_userno

Error: PL/SQL: ORA-22806: not an object or REF
Line: 20
Text: and service_id=v_pk(i).v_serviceid and start_date=v_pk(i).v_startdate) B




xiaoxiao1984 2009-03-12
  • 打赏
  • 举报
 回复
批量处理吧，数据太多了



cursor c is select * from B;

type t_type is table of pk_col%type;

t_array t_type;



open c;

loop;

  fetch c bulk collect into t_array limit 3000; 

  forall i in t_array.first .. t_array.last 

    merge into A 

      using B...

      on (... and B.pk=t_array(i) ) 

    ...

  exit when c%notfound;

end loop;

close c;

xwwcm 2009-03-12
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 zcs_1 的回复:]
引用 7 楼 xwwcm 的回复:
引用 5 楼 zcs_1 的回复:
对a和b表的关联列各建一个组合索引,至于b表的truncate和大量insert问题,我想是可以忍受的.

想问一下，如果只在A表建索引，B表不建，有没有效果？


B表也要建，现在是对A表全表扫描，找与B表对应的数据，所以b表也要建索引。如果能把A表的数据缩小一个范围就好了，7千万可不是个小数量
[/Quote]
A表是全量数据沉淀，B表是增量数据，所以数据缩小是不可能的。
不用merge的思路，有没有其他好的思路比它效率高？
zcs_1 2009-03-12
  • 打赏
  • 举报
 回复
[Quote=引用 7 楼 xwwcm 的回复:]
引用 5 楼 zcs_1 的回复:
对a和b表的关联列各建一个组合索引,至于b表的truncate和大量insert问题,我想是可以忍受的.

想问一下，如果只在A表建索引，B表不建，有没有效果？
[/Quote]

B表也要建，现在是对A表全表扫描，找与B表对应的数据，所以b表也要建索引。如果能把A表的数据缩小一个范围就好了，7千万可不是个小数量
xwwcm 2009-03-12
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 hdhai9451 的回复:]
这样不是好办法的,我觉得按时间(如每天一次)段过滤数据,这样查询的时候数据量会小一些;
[/Quote]
现在A表就是按时间建的list分区，每天的数据单独放在一个分区里
xwwcm 2009-03-12
  • 打赏
  • 举报
 回复
[Quote=引用 5 楼 zcs_1 的回复:]
对a和b表的关联列各建一个组合索引,至于b表的truncate和大量insert问题,我想是可以忍受的.
[/Quote]
想问一下，如果只在A表建索引，B表不建，有没有效果？
zcs_1 2009-03-12
  • 打赏
  • 举报
 回复
对a和b表的关联列各建一个组合索引,至于b表的truncate和大量insert问题,我想是可以忍受的.
加载更多回复（4）
一次性精MySQL核心技术
本课程目前总计105课时，并且不定期的进行新知识点的补充，目的是打造一部围绕MySQL的全体系课程。课程涵盖11大章节，分别是：第1章基础&技巧：这部分的重点是会讲解一些容易被开发人员忽略的技巧，例如utf8mb4字符集问题、如何使用外部临时表提高查询效率、快速创建同结构表及快速复制数据、截断表和删除数据使用和差异、以及怎样使用help语句查看帮助文档。第2章六大数据类型：这部分的重点是对MySQL的8种数字类型、5种日期和时间类型、10种字符串类型、枚举类型、集合类型和时间戳类型的区别和使用进行深入讲解。第3章数据库函数大全：MySQL中有上百种函数之多，使用函数可以快速的解决我们很多开发问题，但是由于我们掌握的函数不够多，往往没有办法实际应用，本章节重点是让你掌握更多好用而你不知道的函数使用。第4章数据库引擎精讲：本章节带您深入到MySQL的体系架构，深入理解innoDB、MyISAM、MEMORY、ARCHIVE引擎的区别和使用原则。第5章数据库索引精讲：索引是保障我们查询效率的重点，本章节从逻辑存储和物理存储的底层入手，深入剖析索引的存储结构和查找方法，掌握聚簇索引、非聚簇索引、前缀索引等的存取原理和使用技巧。第6章调优工具：工欲善其事必先利其器，本章节带你掌握读写比例查询、缓存设置、执行计划和Profile调优工具。第7章参数调优和索引调优：怎么样让SQL执行的更快、数据库的性能更强，怎样充分利用索引进行不断的优化。本章节会为您讲解16种MySQL的优化策略。第8章SQL调优：SQL语句是我们日常使用的重点，怎么样写出一手高性能的SQL语句，其实是具有一定技巧的，本章节讲解8种优化策略，让数据SQL执行性能更强。第9章分库分表：在面对海量数据的时候单表和单个数据库的性能始终会存在瓶颈，本章节为您讲解分库分表的原理和技巧，怎么样使用Merge引擎分表、深入掌握MySQL数据库分区表的能力。第10章高可用架构和安全管理：本部分涵盖MySQL的高可用架构，主备架构、主从架构、主从从架构、互为主从架构。数据的同步复制、半同步复制、异步复制。主从复制原理和主从延迟的问题，以及在管理和开发层面怎样保证数据库安全。第11章MySQL日志：对MySQL的7种日志进行讲解，包括errorlog错误日志、general log查询日志、slow log慢日志、binlog 二进制日志、redlog重做日志。课程会附带配套文档和SQL脚本。有问题可以直接联系作者，24小时线上答疑。
oracle-sql系统学习-ddl-dml
e41084-04　　oracle database sql language reference 11g release 2   sql语句类型 ddl 　　alter ...除了alter session和alter system 　　analyze 　　associate statistics,disassociate statistics 　　audit,noaudit 　　comment...
深入理解 Delta Lake 的 DML 实现原理 (Update, Delete, Merge)
Delta Lake 支持 DML 命令，包括 DELETE, UPDATE, 以及 MERGE，这些命令简化了 CDC、审计、治理以及 GDPR/CCPA 工作流等业务场景。在这篇文章...
Clickhouse-MergeTree原理解析
MergeTree原理解析表引擎是ClickHouse设计实现中的一大特色。可以说，是表引擎决定了一张数据表最终的“性格”，比如数据表拥有何种特性、数据以何种形式被存储以及如何被加载。ClickHouse拥有非常庞大的表引擎体系，截至本书完成时，其共拥有合并树、外部存储、内存、文件、接口和其他6大类20多种表引擎。而在这众多的表引擎中，又属合并树（MergeTree）表引...
MySQL进阶 | SQL优化 | 按关键字分类及原理 | 无知的我费曼笔记（图文排版无水印）
无知的我正在复习MySQL进阶知识。。。。 笔记特点是 我重新整理了涉及资料的一些语言描述、排版，而使用了自己比较容易理解的描述、同样是回答了一些常见关键问题 如果有遇到有任何无法进展问题或者疑惑的地方，应该在讨论区留言 或者 其他途径以寻求及时的帮助，以加快学习效率 或者 培养独立解决问题的能力、扫清盲点、补充细节 目录SQL 优化insert 优化主键 优化数据组织方式InnoDB的逻辑结构图页分裂主键顺序插入效果主键乱序插入效果页合并索引设计原则order by 优化group by 优化imit 优
Oracle 高级技术

3,491

社区成员

18,714

社区内容

发帖
与我相关
我的任务
社区描述
Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章