Oracle 19c层级查询性能异常：耗时过长问题排查求助

• 遍历方向完全搞反了：你当前的查询是从所有根工单（TICKET_VORGAENGER_ID IS NULL）出发，向下遍历整个工单树，再过滤出目标工单。这种逻辑等于要把所有工单树都扫一遍才能找到目标节点，哪怕列上有索引，优化器也没法有效利用，只能触发全表扫描。
• 索引未被正确调用：因为是从根节点往下遍历，数据库得先抓取所有根节点，再逐层向下搜索，TICKET_VORGAENGER_ID的索引根本没法直接定位到目标工单的路径，自然只能走全表扫描。

核心优化：反转遍历方向 #

改成从目标工单向上追溯根节点，这样直接用TICKET_ID主键索引定位目标工单，再依靠TICKET_VORGAENGER_ID索引逐层往上查找，直到找到根节点。修改后的SQL如下：

SELECT CONNECT_BY_ROOT TICKET_ID AS TICKET_ID
FROM TICKET
START WITH TICKET_ID = :ticketId
CONNECT BY PRIOR TICKET_VORGAENGER_ID = TICKET_ID;

逻辑说明： #

• START WITH TICKET_ID = :ticketId：通过主键索引直接定位目标工单，一步到位
• CONNECT BY PRIOR TICKET_VORGAENGER_ID = TICKET_ID：从目标工单开始，向上关联父工单（当前行的父ID等于上一行的工单ID）
• CONNECT_BY_ROOT会返回这条遍历路径最顶端的根工单ID

额外优化建议 #

• 更新表统计信息：确认TICKET_VORGAENGER_ID的索引为正常B树索引，执行ANALYZE TABLE TICKET COMPUTE STATISTICS;更新表统计数据，帮助优化器选择正确的执行计划。
• 冗余根节点字段（高频查询场景推荐）：如果查询根节点的操作非常频繁，可以给TICKET表新增ROOT_TICKET_ID字段，在创建或修改工单时同步更新该字段。后续查询根节点直接读取该字段即可，无需遍历树结构：

  -- 新增根节点字段
  ALTER TABLE TICKET ADD COLUMN ROOT_TICKET_ID NUMBER;
  -- 创建触发器同步根节点（可根据实际业务调整细节）
  CREATE OR REPLACE TRIGGER TRG_TICKET_ROOT
  BEFORE INSERT OR UPDATE OF TICKET_VORGAENGER_ID ON TICKET
  FOR EACH ROW
  DECLARE
      v_root_id NUMBER;
  BEGIN
      IF :NEW.TICKET_VORGAENGER_ID IS NULL THEN
          :NEW.ROOT_TICKET_ID := :NEW.TICKET_ID;
      ELSE
          SELECT ROOT_TICKET_ID INTO v_root_id FROM TICKET WHERE TICKET_ID = :NEW.TICKET_VORGAENGER_ID;
          :NEW.ROOT_TICKET_ID := v_root_id;
      END IF;
  END;
  /
  -- 后续查询根节点只需执行：
  SELECT ROOT_TICKET_ID FROM TICKET WHERE TICKET_ID = :ticketId;
  

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