V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
V2EX 提问指南
chihiro2014
V2EX  ›  问与答

兄弟们有没有 oracle 的奇淫巧技推荐下

  •  
  •   chihiro2014 · 61 天前 · 1017 次点击
    这是一个创建于 61 天前的主题,其中的信息可能已经有所发展或是发生改变。

    最近遇上一个比较奇葩的需求,需要统计日志表中,单次点击操作的记录条数。比如:选中一条,记录 touch id 为 1 ,选中 5 条,操作一次,那么这五条的 touch id 都是 2 。因为大致有三种 case ,所以我这里先查询出所有 case ,然后用 type 写个 abc 来区别是哪一类,用来后续根据查出来的条件查询对应的 id 列表,然后通过

    update audit_log set touch_id = rowNumber (也就是下方 SQL 末尾的 row_num ) where id in (根据下方 sql 的结果条件查出来的数据)
    

    数据量比较离谱,因为某个 touch id 可能对应了几千条记录,这个时候用 in 查询就会超出限制,而导致失败。 所以,通过代码手动拼接超出 800 个,就分成多条 SQL 保证不会失败。 目前的做法是通过 mybatis 流式处理将 update 语句都保存下来。然后多线程跑。但目前算下来查询 id 这部分就要查 44w 次,更新也要 44w 次。所以,兄弟们有没有快速执行大量 update 的方法。目前批量执行 500 条,oracle 要 3 分钟,感觉要死

    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY action_date) AS row_num
    FROM (
        -- 情况 1:updated_value 不为空
        SELECT 
            action,
            action_by,
            TO_CHAR(action_date, 'YYYY-MM-DD HH24:MI') AS action_date,
            updated_value,
            comments,
            'a' AS type,
            COUNT(1) AS total
        FROM 
            audit_log
        WHERE 
            updated_value IS NOT NULL
        GROUP BY 
            action,
            action_by,
            TO_CHAR(action_date, 'YYYY-MM-DD HH24:MI'),
            updated_value,
            comments
    
        UNION ALL
    
        -- 情况 2:updated_value 为空,comments 不为空
        SELECT 
            action,
            action_by,
            TO_CHAR(action_date, 'YYYY-MM-DD HH24:MI') AS action_date,
            updated_value,
            comments,
            'b' AS type,
            COUNT(1) AS total
        FROM 
            audit_log
        WHERE 
            updated_value IS NULL AND comments IS NOT NULL
        GROUP BY 
            action,
            action_by,
            TO_CHAR(action_date, 'YYYY-MM-DD HH24:MI'),
            updated_value,
            comments
    
        UNION ALL
    
        -- 情况 3:updated_value 和 comments 都为空
        SELECT 
            action,
            action_by,
            TO_CHAR(action_date, 'YYYY-MM-DD HH24:MI') AS action_date,
            updated_value,
            comments,
            'c' AS type,
            COUNT(1) AS total
        FROM 
            audit_log
        WHERE 
            updated_value IS NULL AND comments IS NULL
        GROUP BY 
            action,
            action_by,
            TO_CHAR(action_date, 'YYYY-MM-DD HH24:MI'),
            updated_value,
            comments
    ) subquery
    ORDER BY action_date;
    
    8 条回复    2024-10-24 20:26:31 +08:00
    jancing
        1
    jancing  
       61 天前 via Android
    关于 in 不能超过 1000 的限制:可以把 x in (1,2,3) 替换成 (1,x) in ((1,1), (1,2), (1,3))
    关于加快 update 执行:可以试试绑定变量,不要用字面量。Oracle 对于重复的 SQL 语句会有编译缓存,命中缓存的话可以提高效率
    yinmin
        2
    yinmin  
       61 天前 via iPhone
    用一个事务处理去执行多条 update
    yinmin
        3
    yinmin  
       61 天前 via iPhone
    也可以试试改用 1 条 sql 来实现,下面有一个 sql 示例抛砖引玉:

    MERGE INTO employees e
    USING (
    SELECT d.department_id, d.budget
    FROM departments d
    WHERE d.location = 'New York'
    ) dept_info
    ON (e.department_id = dept_info.department_id)
    WHEN MATCHED THEN
    UPDATE SET e.salary = e.salary + dept_info.budget * 0.1;
    yinmin
        4
    yinmin  
       60 天前 via iPhone
    接#2 ,如果一次性有几万几十万条 update ,不应该使用多线程,应该是单线程使用批处理( Batch Processing ),每次 3000 条,然后 commit 一次。原理是:( 1 )每次与数据库交互都有几毫秒的延时,改用批处理,每次交互能处理 3000 条,就能节约几秒的时间 ( 2 )每次 commit ,数据库都要处理一大堆的事务和日志工作很消耗资源,3000 条一次 commit ,也能节省很多
    chihiro2014
        5
    chihiro2014  
    OP
       60 天前
    @jancing 这个写法倒是第一次听说
    @yinmin merge into 也解决不了要暴力执行几十万条 sql 的问题
    yinmin
        6
    yinmin  
       60 天前 via iPhone
    @chihiro2014 你的需求用 merge 可以一句 sql 解决,不用写代码。晚上跑一个定时任务即可,很多商业系统晚上跑轧帐的 sql 脚本,都是 1 个 sql 脚本跑几个小时的。
    yinmin
        7
    yinmin  
       60 天前 via iPhone
    而且,如果你的 select 能小几秒出结果的话,update 几十万条记录小 case ,也就大几秒或者几十秒
    chihiro2014
        8
    chihiro2014  
    OP
       60 天前
    @yinmin 我们系统基本不能宕机,所以这个也没法搞
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2653 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 19ms · UTC 00:20 · PVG 08:20 · LAX 16:20 · JFK 19:20
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.