优化APEX报表产品当前位置查询耗时及安全触发器实现方案

当前的加载耗时高，本质是逐行函数调用带来的性能缺陷：对product表扫描的每一行，数据库都会单独执行一次product_pos函数逻辑，也就是对product_position表执行一次按产品ID过滤、按移动时间排序、取第一条的查询。当两张表数据量较大时，这种逐行触发的查询会产生大量随机IO，完全无法发挥批量查询的执行效率，30秒的加载耗时基本全部来自这部分冗余开销。

这类优化不需要新增列、不需要配置触发器，只需要改写报表查询、搭配必要索引，通常就能把30秒的耗时压到1秒以内：

• 把逐行调用product_pos(id)的逻辑，替换为一次性关联聚合的写法，让数据库只对product_position表做一次扫描，就能为所有产品匹配到最新位置：

SELECT p.id, p.serial_n, p.product_name, pp.new_pos AS position
FROM product p
LEFT JOIN (
    SELECT 
        product_id, 
        new_pos,
        -- 按产品ID分组，取移动时间最新的1条记录
        ROW_NUMBER() OVER (
            PARTITION BY product_id 
            ORDER BY date_move DESC, id DESC
        ) AS rn
    FROM product_position
) pp 
    ON pp.product_id = p.id 
    AND pp.rn = 1

• 配套创建覆盖索引，让上面的查询完全不需要回表读product_position的原始数据：

CREATE INDEX idx_pp_prod_date_pos 
ON product_position(product_id, date_move DESC, id DESC, new_pos);

• 额外修复原函数的隐患：当产品没有任何位置移动记录时，原product_pos函数会抛出NO_DATA_FOUND异常直接中断报表加载，换成上面的LEFT JOIN写法后，无位置记录的产品会自动显示为空值，不会触发报表报错。

如果上述查询优化后的性能仍达不到要求，完全可以在product表新增冗余的position列，通过product_position的DML触发器做同步，所谓“手动修改列导致数据不一致”的风险可以通过两层控制完全规避：

• 权限层面隔离：收回所有普通业务账号、APEX工作区账号、开发临时账号对product表position列的INSERT/UPDATE权限，只保留高权限管理员账号的列修改权限。普通用户不管是写SQL、还是通过APEX的工作表编辑功能，都根本没有权限对这个列做修改。
• 触发器层面兜底校验：在product表上创建前置UPDATE/INSERT触发器，拦截所有非同步流程发起的position列修改：
  • 给product_position的同步触发器设置会话级的合法操作标记：当用户在product_position表插入、更新、删除记录，触发器触发同步更新product.position列之前，先通过dbms_session.set_context给当前会话打一个“合法同步操作”的标记，同步完成后再把标记重置。
  • 在product表的前置触发器里，先校验当前会话有没有这个合法同步标记：如果没有标记就检测到position列被修改，直接抛出应用错误回滚事务，彻底堵死手动篡改的路径。

• 如果product_position表中同个产品的date_move存在相同值的情况，排序的时候一定要追加主键ID作为第二排序字段，保证每次取到的“最新记录”是确定的，不会出现相同条件下位置值随机跳变的问题。
• 若product_position表数据量达到千万级以上，除了覆盖索引，还可以搭配增量刷新的物化视图存储每个产品的最新位置，进一步降低查询时的计算开销。

内容的提问来源于stack exchange，提问作者imstuckaf