Oracle 12c多节点路径遍历查询优化：替代循环实现方案咨询

Since Oracle 12c supports recursive common table expressions (CTEs), we can replace your PL/SQL loop with a set-based SQL query that's far more efficient for large datasets (like your 1M-row table1). This approach leverages recursion to traverse the path nodes while preserving the required order and including all relevant ports for each node.

WITH path_traversal AS (
    -- Anchor member: Start with the initial node/port from table2
    SELECT 
        t2.path_id,
        t2.start_node AS node_name,
        t2.start_node_port AS node_port,
        1 AS "order",
        t2.start_node AS last_node, -- Track previous node to avoid backtracking
        t2.end_node AS target_node
    FROM table2 t2

    UNION ALL

    -- Recursive member 1: Add the current node's other outbound port (if exists)
    SELECT 
        pt.path_id,
        t1.node_1 AS node_name,
        t1.node1_port AS node_port,
        pt."order" + 1 AS "order",
        pt.node_name AS last_node,
        pt.target_node
    FROM path_traversal pt
    JOIN table1 t1 ON t1.path_id = pt.path_id
    WHERE 
        t1.node_1 = pt.node_name
        AND t1.node1_port != pt.node_port
        AND t1.node_2 != pt.last_node -- Prevent backtracking to the previous node
        AND pt.node_name != pt.target_node -- Stop once we reach the end node
        -- Ensure we don't duplicate entries for the same node/port
        AND NOT EXISTS (
            SELECT 1 FROM path_traversal pt2
            WHERE pt2.path_id = pt.path_id 
              AND pt2.node_name = t1.node_1 
              AND pt2.node_port = t1.node1_port
        )

    UNION ALL

    -- Recursive member 2: Add the next node's inbound port
    SELECT 
        pt.path_id,
        t1.node_2 AS node_name,
        t1.node2_port AS node_port,
        pt."order" + 1 AS "order",
        pt.node_name AS last_node,
        pt.target_node
    FROM path_traversal pt
    JOIN table1 t1 ON t1.path_id = pt.path_id
    WHERE 
        t1.node_1 = pt.node_name
        AND t1.node1_port = pt.node_port
        AND t1.node_2 != pt.last_node -- Prevent backtracking
        AND pt.node_name != pt.target_node -- Stop once we reach the end node
        -- Ensure we don't duplicate entries for the same node/port
        AND NOT EXISTS (
            SELECT 1 FROM path_traversal pt2
            WHERE pt2.path_id = pt.path_id 
              AND pt2.node_name = t1.node_2 
              AND pt2.node_port = t1.node2_port
        )
)
-- Final output ordered by path and sequence
SELECT path_id, node_name, node_port, "order"
FROM path_traversal
ORDER BY path_id, "order";

How This Works #

1. Anchor Member: Starts with each path's initial node and port from table2, setting the initial order to 1, tracking the starting node as last_node (to avoid backtracking), and storing the end node as target_node.
2. Recursive Member 1: Handles adding the current node's alternate outbound port (if it exists and connects to a non-previous node). This ensures we include all ports for a node that are part of the path.
3. Recursive Member 2: Moves to the next node in the path by following the current node's port connection, adding the next node's inbound port to the sequence.
4. Duplicate Prevention: The NOT EXISTS clause ensures we don't reprocess the same node/port combination, which avoids loops and duplicate entries.
5. Termination: The recursion stops once we reach the target_node (the end node from table2).

Key Benefits Over PL/SQL Loops #

• Set-Based Processing: SQL operates on entire datasets at once, which is vastly more efficient than row-by-row loops for large tables.
• Maintainability: Pure SQL is easier to read, debug, and maintain than procedural code.
• Scalability: This approach will handle your 1M-row table1 much faster than a PL/SQL loop, especially with proper indexing.

Index Recommendations #

To further improve performance, consider adding these indexes to speed up the recursive joins:

CREATE INDEX idx_table1_path_node_port ON table1(path_id, node_1, node1_port);
CREATE INDEX idx_table2_path_id ON table2(path_id);

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