背景: A表有将近10亿数据,现每天需要根据B表主键更新B表 字段属性于A表中,B表数据量也在千万级别。更新遇到了瓶颈,故设计了以下更新方案。
以下是实现该方案之一:
前提:A表必须为分区表,且执行用户有create job 权限。
模拟业务场景:
1.创建A、B表和中间表:
-- A表
create table test_t1(id number,flag varchar2(10))
partition by range(id)
(partition p1 values less than (5000000),
partition p2 values less than (10000000),
partition p3 values less than (15000000),
partition p4 values less than (20000000),
partition p5 values less than (25000000),
partition p6 values less than (30000000),
partition p7 values less than (35000000),
partition p8 values less than (40000000),
partition p9 values less than (45000000),
partition p10 values less than (50000000),
partition p11 values less than (55000000),
partition p12 values less than (60000000),
partition p13 values less than (65000000),
partition p14 values less than (70000000),
partition p15 values less than (75000000),
partitio