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

[chihiro2014]

最近遇上一个比较奇葩的需求，需要统计日志表中，单次点击操作的记录条数。比如：选中一条，记录 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;

[jancing]

关于 in 不能超过 1000 的限制：可以把 x in (1,2,3) 替换成 (1,x) in ((1,1), (1,2), (1,3))
关于加快 update 执行：可以试试绑定变量，不要用字面量。Oracle 对于重复的 SQL 语句会有编译缓存，命中缓存的话可以提高效率

[yinmin]

用一个事务处理去执行多条 update

[yinmin]

也可以试试改用 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]

接#2 ，如果一次性有几万几十万条 update ，不应该使用多线程，应该是单线程使用批处理（ Batch Processing ），每次 3000 条，然后 commit 一次。原理是：（ 1 ）每次与数据库交互都有几毫秒的延时，改用批处理，每次交互能处理 3000 条，就能节约几秒的时间 （ 2 ）每次 commit ，数据库都要处理一大堆的事务和日志工作很消耗资源，3000 条一次 commit ，也能节省很多

[chihiro2014]

@jancing 这个写法倒是第一次听说
@yinmin merge into 也解决不了要暴力执行几十万条 sql 的问题

[yinmin]

@chihiro2014 你的需求用 merge 可以一句 sql 解决，不用写代码。晚上跑一个定时任务即可，很多商业系统晚上跑轧帐的 sql 脚本，都是 1 个 sql 脚本跑几个小时的。

[yinmin]

而且，如果你的 select 能小几秒出结果的话，update 几十万条记录小 case ，也就大几秒或者几十秒

[chihiro2014]

@yinmin 我们系统基本不能宕机，所以这个也没法搞