V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
pandago1
V2EX  ›  Oracle

Oracle 数据库 - 如何安全快速全表更新 20 亿条数据

  •  
  •   pandago1 · 2022-03-07 22:56:33 +08:00 · 1824 次点击
    这是一个创建于 973 天前的主题,其中的信息可能已经有所发展或是发生改变。

    最近项目遇到一个 Oracle 数据库的问题,发出来请教一下,希望专业人士指点一二。

    描述:已有数据表 table_a, 每天都有新数据插入,每天( business_Date )产生的新数据量都在 100 万以上( 100-500 万的数据量吧),已有 600 多天的数据, 现在 table_a 有约 20 亿条数据; 最近在这个表新增了一个字段 ACTIVE ,现在需要把这个字段的值全部更新为'N': 方法 1:update table_a set ACTIVE='N';--直观的理解是这样更新,但是这样会产生巨大的 undo tablespace,不可行。

    方法 2:遍历出所有的 business_Date,按天来 update ;--这种方法在测试环境感觉并没有很快,还有提升空间 代码如下: DECLARE CURSOR cur IS SELECT DISTINCT business_date FROM table_a;--单独这段 query 执行时间大约是 5 分钟 BEGIN FOR record IN cur LOOP--由于 table_a 已有 600 多天的数据,按天 update 批量更新的话,此循环需要执行 600 多次 UPDATE table_a SET ACTIVE ='N' WHERE business_date=record.business_date; COMMIT; END LOOP; COMMIT; END;

    方法 3:待定;貌似可以通过 rowid 来更新,但是还没试。

    11 条回复    2022-03-08 13:38:19 +08:00
    kingcanfish
        1
    kingcanfish  
       2022-03-07 23:39:15 +08:00
    仅供参考毕竟我也是菜鸡
    我不知道 oracle 有没有

    1. 通过 create table as select ... 方法创建一个表
    2. 新旧两个表表名互换
    seers
        2
    seers  
       2022-03-07 23:41:29 +08:00 via Android   ❤️ 2
    字段 drop 掉,加同名字段 default 为 n
    msg7086
        3
    msg7086  
       2022-03-08 00:57:53 +08:00
    开个视图返回正确的数据,然后背后再慢慢更新旧数据?
    xy90321
        4
    xy90321  
       2022-03-08 01:13:24 +08:00 via iPhone
    2# 正解。一律更新的话,直接 alter table
    LeeReamond
        5
    LeeReamond  
       2022-03-08 05:11:48 +08:00
    LZ 有 base64 联系方式吗,我使用 oracle 数据量跟你相似,运维可否交流
    pandago1
        6
    pandago1  
    OP
       2022-03-08 09:39:08 +08:00
    @kingcanfish 这种方式被 leader 否掉了,因为涉及删表,leader 不让在生产环境这么干
    pandago1
        7
    pandago1  
    OP
       2022-03-08 09:49:03 +08:00
    @msg7086 ALTER TABLE table_a ADD Active VARCHAR2(1 CHAR) DEFAULT 'N';--这样属于直接对这张表进行更新, 会产生巨大的 undo tablespace, 也会对这张表加锁,其他 session 所有插入 /更改都会等待,测试环境 3000 万+数据跑了两个小时还没结束。。。
    pandago1
        8
    pandago1  
    OP
       2022-03-08 09:52:38 +08:00
    @LeeReamond amZlbmdsaUBmb3htYWlsLmNvbQ
    7654
        9
    7654  
       2022-03-08 10:36:10 +08:00
    @pandago1 有按时间建 PARTITION table 吗
    pandago1
        10
    pandago1  
    OP
       2022-03-08 11:38:31 +08:00
    @7654 有基于 business_date 的 partition ,PARTITION BY RANGE ("business_date") INTERVAL (NUMTODSINTERVAL (1, 'DAY'))
    msg7086
        11
    msg7086  
       2022-03-08 13:38:19 +08:00 via Android
    @pandago1 嗷,我以为字段已经加完了。
    对 undo 不太熟,如果只加字段不加默认值呢?
    然后建一个视图,对于旧数据返回 n ,新数据返回真实值,然后再在背后批量更新。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   3590 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 27ms · UTC 04:33 · PVG 12:33 · LAX 20:33 · JFK 23:33
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.