SQL查询耗时超2小时且报ORA-08103错误，请求优化方案

一、解决ORA-08103: object no longer exists错误 #

这个错误通常是查询执行期间依赖的对象（表、索引、临时段等）被修改/删除，或执行计划异常导致的，结合你的场景（全量查询报错、少量数据正常），可按以下方向处理：

• 移除无效并行提示：查询中的PARALLEL(table64)是无效的（table64不存在），会导致Oracle解析执行计划时异常，直接删除该提示。同时并行查询本身可能因资源竞争触发对象版本问题，先改为串行执行验证。
• 规避并发DML/DDL操作：确认查询执行期间，是否有其他会话对table1~table8执行ALTER、DROP或大量DELETE/UPDATE（尤其是分区表维护）。尽量避开业务高峰执行，或给查询添加FOR READ ONLY提示保证一致性。
• 刷新表统计信息：过时的统计信息会导致Oracle生成错误执行计划，引发异常。执行以下命令刷新涉及表的统计信息：

  EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'table1', CASCADE => TRUE);
  EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'table2', CASCADE => TRUE);
  -- 依次对table3到table8执行同样命令
  

• 优化临时对象生成：子查询中的SELECT DISTINCT可能生成临时表，确保临时表空间充足，且无其他会话清理临时段。可提前将该子查询结果存入物化视图，或改为JOIN后去重。

二、性能优化（缩短2小时执行时间） #

400万行数据执行耗时过长，核心原因是执行计划不合理、缺少索引、子查询效率低下，具体优化点：

1. 添加复合索引 #

针对JOIN条件和WHERE过滤字段创建索引，大幅减少扫描数据量：

• table1：CREATE INDEX idx_cad_filter_join ON table1(cost_type, moa_code, resource_code, cumulative_amt, etl_foa_code, ca_wi_code, ca_cost_org_code, taskrsrc_id);
• table2：CREATE INDEX idx_rc_join_filter ON table2(etl_foa_code, resource_code, pm_budget_resource_code);
• table3：CREATE INDEX idx_wi_join ON table3(etl_foa_code, wi_code, task_id, p2_conv_task_id);
• table4：CREATE INDEX idx_pt_task ON table4(task_id, proj_id, wbs_id);
• 子查询关联表：table8创建INDEX idx_farv_type_order ON table8(fund_type_code, far_order_no, etl_foa_code, fund_acct_no);；table7创建INDEX idx_co_far_debtor ON table7(far_order_no, debtor_id);；table6创建INDEX idx_vv_addr_class ON table6(addr_id, debtor_class_code);

2. 重构低效子查询 #

将NOT IN改为NOT EXISTS，Oracle对NOT EXISTS的优化更高效，还能避免空值导致的意外结果：
原条件：

AND  (cad.cost_type != 'WIP' 
 OR  (cad.cost_type = 'WIP' 
AND  (cad.etl_foa_code, cad.fund_acct_no) NOT IN (SELECT farv.etl_foa_code, farv.fund_acct_no ...)))

修改为：

AND (cad.cost_type != 'WIP' 
     OR (cad.cost_type = 'WIP' 
         AND NOT EXISTS (
             SELECT 1 
             FROM table6 vv
             JOIN table7 co ON vv.addr_id = co.debtor_id
             JOIN table8 farv ON co.far_order_no = farv.far_order_no
             WHERE farv.fund_type_code = 'E'
               AND vv.debtor_class_code IN ('OC','ID')
               AND farv.etl_foa_code = cad.etl_foa_code
               AND farv.fund_acct_no = cad.fund_acct_no
         )
     )
)

3. 简化JOIN与分组逻辑 #

• 移除LEFT OUTER JOIN中的DISTINCT：若table5中old_org_code是唯一值，直接删除DISTINCT；若不唯一，提前通过物化视图完成去重。
• 提前计算分组用的CASE表达式：在子查询中预先计算CASE结果，避免分组时重复计算，减少资源消耗：

  FROM (
      SELECT cad.*, rc.pm_budget_resource_code,
             CASE WHEN rc.pm_budget_resource_code = 'LABOR' THEN COALESCE(rxl.new_org_code, cad.ca_cost_org_code)
                  ELSE rc.pm_budget_resource_code
             END AS rsrc_short_name
      FROM table1 cad
      JOIN table2 rc ON cad.etl_foa_code = rc.etl_foa_code AND cad.resource_code = rc.resource_code AND rc.pm_budget_resource_code != 'WKBOTHCOE'
      LEFT JOIN table5 rxl ON cad.ca_cost_org_code = rxl.old_org_code
      WHERE cad.cost_type != 'UFC'
        AND cad.cumulative_amt != 0
        AND cad.moa_code != 'R1'
        AND cad.resource_code NOT IN ('FLUX','CAPINT')
  ) cad_rc_rxl
  

4. 清理无效执行计划提示 #

查询中的ORDERED、USE_HASH等提示可能强制了不合理的连接顺序，建议先移除所有手动提示，让Oracle自动生成执行计划，再根据实际执行计划调整。

5. 提前过滤数据 #

将WHERE过滤条件尽可能前置到初始子查询中，减少后续JOIN的数据量，避免带着大量无效数据做关联操作。

三、修改后的完整SQL示例 #

SELECT 
    pt.proj_id,
    pt.wbs_id,
    pt.task_id,
    cad_rc_rxl.rsrc_short_name,
    cad_rc_rxl.taskrsrc_id,
    SUM(cad_rc_rxl.cumulative_amt) AS expend_amt
FROM (
    SELECT 
        cad.*,
        rc.pm_budget_resource_code,
        CASE WHEN rc.pm_budget_resource_code = 'LABOR' THEN COALESCE(rxl.new_org_code, cad.ca_cost_org_code)
             ELSE rc.pm_budget_resource_code
        END AS rsrc_short_name
    FROM table1 cad
    INNER JOIN table2 rc 
        ON cad.etl_foa_code = rc.etl_foa_code
        AND cad.resource_code = rc.resource_code
        AND rc.pm_budget_resource_code != 'WKBOTHCOE'
    LEFT JOIN table5 rxl 
        ON cad.ca_cost_org_code = rxl.old_org_code
    WHERE cad.cost_type != 'UFC'
      AND cad.cumulative_amt != 0
      AND cad.moa_code != 'R1'
      AND cad.resource_code NOT IN ('FLUX','CAPINT')
) cad_rc_rxl
INNER JOIN table3 wi 
    ON cad_rc_rxl.etl_foa_code = wi.etl_foa_code
    AND cad_rc_rxl.ca_wi_code = wi.wi_code
INNER JOIN table4 pt 
    ON COALESCE(wi.task_id, wi.p2_conv_task_id) = pt.task_id
WHERE (cad_rc_rxl.cost_type != 'WIP' 
       OR (cad_rc_rxl.cost_type = 'WIP' 
           AND NOT EXISTS (
               SELECT 1 
               FROM table6 vv
               INNER JOIN table7 co 
                   ON vv.addr_id = co.debtor_id
               INNER JOIN table8 farv 
                   ON co.far_order_no = farv.far_order_no
               WHERE farv.fund_type_code = 'E'
                 AND vv.debtor_class_code IN ('OC','ID')
                 AND farv.etl_foa_code = cad_rc_rxl.etl_foa_code
                 AND farv.fund_acct_no = cad_rc_rxl.fund_acct_no
           )
       )
)
GROUP BY 
    pt.proj_id,
    pt.wbs_id,
    pt.task_id,
    cad_rc_rxl.rsrc_short_name,
    cad_rc_rxl.taskrsrc_id
HAVING SUM(cad_rc_rxl.cumulative_amt) != 0;

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