Oracle工单递归SQL查询构建及现有查询性能优化求助

Hey there! Let's work through optimizing your recursive SQL query for item/lot traceability. First, let's recap your core requirement to make sure we're aligned:

Your Requirement Breakdown #

• Input: ITEM_ID + LOT
• Query Flow:
  • 1. Run the RECEIVING subquery with the input; return results and stop if there's a match.
  • 2. If RECEIVING has no results, run the BATCH subquery to get a BATCH_NO.
  • 3. Use that BATCH_NO to run the INGREDIENT subquery, get a new ITEM_ID+LOT, then loop back to step 1.

Your current working but slow query is:

select BATCH_ID, BATCH_NO, PRODUCT_ITEM_ID, PRODUCT_ITEM PRODUCT_LOT, INGREDIENT_ITEM_ID, INGREDIENT_ITEM INGREDIENT_LOT, LEVEL 
FROM ( 
  SELECT b.batch_id, b.batch_no, b.inventory_item_id as PRODUCT_ITEM_ID, b.item as PRODUCT_ITEM, b.lot as PRODUCT_LOT, i.inventory_item_id AS INGREDIENT_ITEM_ID, i.item as INGREDIENT_ITEM, i.lot as INGREDIENT_LOT 
  FROM batch b 
  JOIN ingredients i ON i.batch_id = b.batch_id 
) 
CONNECT BY NOCYCLE PRIOR PRODUCT_ITEM_ID = INGREDIENT_ITEM_ID AND PRODUCT_LOT = INGREDIENT_LOT 
START WITH PRODUCT_ITEM_ID = 1765716 AND PRODUCT_LOT = '1EP17171590' ;

Key Issues with the Current Query #

• Upfront Full Join: Your base subquery joins BATCH and INGREDIENTS before recursion even starts, creating a massive intermediate dataset. This forces the database to process way more rows than needed for each recursive step.
• Missing RECEIVING Check: The query skips your initial RECEIVING validation, jumping straight into batch/ingredient recursion even when the input might exist in RECEIVING.
• Potential Index Gaps: Without targeted indexes on join/filter columns, the database will repeatedly run full table scans, which kills performance for recursive logic.

Optimization Recommendations #

1. Add the RECEIVING Check & Split Logic

First, check RECEIVING—if it returns results, stop immediately. Only proceed with recursion if there's no match. Use UNION ALL to combine these two paths:

(Note: Adjust syntax if you're using Oracle's CONNECT BY instead of standard WITH RECURSIVE)

-- Step 1: Check RECEIVING first (return results and stop if found)
SELECT 
  NULL AS BATCH_ID, 
  NULL AS BATCH_NO, 
  r.inventory_item_id AS PRODUCT_ITEM_ID, 
  r.lot AS PRODUCT_LOT, 
  NULL AS INGREDIENT_ITEM_ID, 
  NULL AS INGREDIENT_LOT, 
  1 AS LEVEL
FROM RECEIVING r
WHERE r.inventory_item_id = 1765716 AND r.lot = '1EP17171590'

UNION ALL

-- Step 2: Only run recursion if RECEIVING has no results
WITH RECURSIVE batch_ingredient_trace AS (
  -- Anchor: Get initial batch for input item/lot
  SELECT 
    b.batch_id, 
    b.batch_no, 
    b.inventory_item_id AS PRODUCT_ITEM_ID, 
    b.lot AS PRODUCT_LOT, 
    i.inventory_item_id AS INGREDIENT_ITEM_ID, 
    i.lot AS INGREDIENT_LOT, 
    1 AS LEVEL
  FROM BATCH b
  JOIN INGREDIENTS i ON i.batch_id = b.batch_id
  WHERE b.inventory_item_id = 1765716 AND b.lot = '1EP17171590'

  UNION ALL

  -- Recursive step: Trace ingredients, stop if found in RECEIVING
  SELECT 
    b.batch_id, 
    b.batch_no, 
    b.inventory_item_id AS PRODUCT_ITEM_ID, 
    b.lot AS PRODUCT_LOT, 
    i.inventory_item_id AS INGREDIENT_ITEM_ID, 
    i.lot AS INGREDIENT_LOT, 
    bit.LEVEL + 1 AS LEVEL
  FROM batch_ingredient_trace bit
  JOIN BATCH b ON b.inventory_item_id = bit.INGREDIENT_ITEM_ID AND b.lot = bit.INGREDIENT_LOT
  JOIN INGREDIENTS i ON i.batch_id = b.batch_id
  WHERE NOT EXISTS (
    SELECT 1 FROM RECEIVING r 
    WHERE r.inventory_item_id = bit.INGREDIENT_ITEM_ID AND r.lot = bit.INGREDIENT_LOT
  )
)
SELECT * FROM batch_ingredient_trace;

2. Add Targeted Indexes

Indexes are non-negotiable for recursive queries to avoid full table scans. Create these if they don't exist:

• RECEIVING: CREATE INDEX idx_receiving_item_lot ON RECEIVING(inventory_item_id, lot);
• BATCH: CREATE INDEX idx_batch_item_lot_id ON BATCH(inventory_item_id, lot, batch_id);
• INGREDIENTS: CREATE INDEX idx_ingredients_batch_item ON INGREDIENTS(batch_id, inventory_item_id, lot);

3. Prune Unnecessary Recursion

• Keep NOCYCLE (for Oracle) to prevent infinite loops from circular dependencies.
• If you know a maximum trace depth for your business logic, add a LEVEL <= X clause to limit recursion.

4. Analyze Query Execution Plan

Use EXPLAIN PLAN (Oracle) or EXPLAIN ANALYZE (PostgreSQL) to identify bottlenecks. Look for full table scans or high row counts in intermediate steps—this will tell you where indexes or logic tweaks are needed most.

内容的提问来源于stack exchange，提问作者John H.