考虑创建以下索引：

• 索引（CHILD_ORDER，PLAN_LEVEL，ROW_NUMBER）：以提高根据CHILD_ORDER、PLAN_LEVEL和ROW_NUMBER筛选行的子查询的性能。
• 索引（ROW_NUMBER）：以帮助ORDER BY子句中的排序操作。

例如：

CREATE INDEX idx_child_plan_row ON your_table (CHILD_ORDER, PLAN_LEVEL, ROW_NUMBER);
CREATE INDEX idx_row_number ON your_table (ROW_NUMBER);

字符串
如果您运行Oracle 12c或更高版本，您还可以尝试使用OUTER APPLY（或“横向连接”）来运行子查询，这里有2种变体需要考虑：

SELECT
     T.*, oa.*
FROM your_table T
OUTER APPLY (
        SELECT T2.SERIAL_NUMBER as PARENT_SERIAL_NUMBER
        FROM your_table T2
        WHERE T2.ROW_NUMBER = (
                SELECT MAX(ROW_NUMBER) MAX_ROW
                FROM your_table T1
                WHERE T1.CHILD_ORDER = T.PARENT_ORDER
                    AND T1.PLAN_LEVEL = T.PLAN_LEVEL - 1
                    AND T1.ROW_NUMBER < T.ROW_NUMBER
                )
        ) oa
ORDER BY
      ROW_NUMBER
    , PLAN_LEVEL

型

或者

SELECT
     T.*, oa.*, T2.SERIAL_NUMBER as PARENT_SERIAL_NUMBER
FROM your_table T
OUTER APPLY (
                SELECT MAX(ROW_NUMBER) MAX_ROW
                FROM your_table T1
                WHERE T1.CHILD_ORDER = T.PARENT_ORDER
                    AND T1.PLAN_LEVEL = T.PLAN_LEVEL - 1
                    AND T1.ROW_NUMBER < T.ROW_NUMBER
        ) oa
LEFT JOIN your_table T2 on oa.max_row = T2.row_number
ORDER BY
      T.ROW_NUMBER
    , T.PLAN_LEVEL

型
看到这些在this dbfiddle中工作
我建议你在添加索引之前和之后比较一下你的查询的解释计划（以及替代方案）。

您可以使用分层查询（因为数据中存在分层关系）和过滤器，使用ROW_NUMBER分析函数查找最大行数。这只需要从表中SELECT一次（消除自连接）：

SELECT row_number,
       plan_level,
       parent_order,
       child_order,
       serial_number,
       parent_serial_number,
       parent_row_number
FROM   (
  SELECT CONNECT_BY_ROOT row_number AS row_number,
         CONNECT_BY_ROOT plan_level AS plan_level,
         CONNECT_BY_ROOT parent_order AS parent_order,
         CONNECT_BY_ROOT child_order AS child_order,
         CONNECT_BY_ROOT serial_number AS serial_number,
         CASE LEVEL WHEN 2 THEN serial_number END AS parent_serial_number,
         CASE LEVEL WHEN 2 THEN row_number END AS parent_row_number,
         ROW_NUMBER() OVER (PARTITION BY CONNECT_BY_ROOT row_number ORDER BY row_number DESC) AS rn
  FROM   table_name
  WHERE  (LEVEL = 1 AND CONNECT_BY_ISLEAF = 1)
  OR     LEVEL = 2
  CONNECT BY
         PRIOR parent_order = child_order
  AND    PRIOR plan_level = plan_level + 1
  AND    PRIOR row_number > row_number
)
WHERE rn = 1
ORDER BY row_number;

字符串
其中，对于样本数据：

CREATE TABLE table_name (row_number, plan_level, parent_order, child_order, serial_number) AS
  SELECT 1, 1, 'P1', 'X1', 'S' FROM DUAL UNION ALL
  SELECT 2, 1, 'P2', 'X2', 'S1' FROM DUAL UNION ALL
  SELECT 3, 2, 'X2', 'N1', 'S2' FROM DUAL UNION ALL
  SELECT 4, 3, 'N1', 'Y1', 'S4' FROM DUAL UNION ALL
  SELECT 5, 3, 'N1', 'Y1', 'S5' FROM DUAL UNION ALL
  SELECT 6, 2, 'X2', 'N1', 'S22' FROM DUAL UNION ALL
  SELECT 7, 3, 'N1', 'Y1', 'S6' FROM DUAL UNION ALL
  SELECT 8, 3, 'N1', 'Y2', 'S7' FROM DUAL UNION ALL
  SELECT 9, 1, 'P2', 'X3', 'S8' FROM DUAL UNION ALL
  SELECT 10, 2, 'X3', 'N1', 'S33' FROM DUAL UNION ALL
  SELECT 11, 3, 'N1', 'Y1', 'S9' FROM DUAL;

型
输出：
| 平面_标高|母订单|儿童订单|序列号|父序列号|父行编号| PARENT_ROW_NUMBER |
| --|--|--|--|--|--| ------------ |
| 一个|P1| X1| S型| * 空 | 空 *| null |
| 一个|P2| X2| S1| * 空 | 空 *| null |
| 二个|X2| N1| S2| S1|二个| 2 |
| 三个|N1| Y1| S4| S2|三个| 3 |
| 三个|N1| Y1| S5| S2|三个| 3 |
| 二个|X2| N1| S22| S1|二个| 2 |
| 三个|N1| Y1| S6| S22|六个| 6 |
| 三个|N1| Y2| S7| S22|六个| 6 |
| 一个|P2| X3| S8| * 空 | 空 *| null |
| 二个|X3| N1| S33| S8|九个| 9 |
| 三个|N1| Y1| S9| S33|十个| 10 |
fiddle