制造业质量追溯01：最小测试用例与 Oracle 层次查询演示

2026-09-23 07:25 AlfredZhao 阅读(63) 评论(0) 收藏举报

本文以大家都熟悉的汽车轮胎举例，在其对应的制造场景与供应链场景中（注：场景已简化，实际要更复杂），质量追溯是一个典型的多级父子结构问题。

追溯链条通常为：原材料（天然橡胶、炭黑、钢丝帘线批次）→ 胶料/半成品（混炼胶、胎面、胎圈）→ 成型轮胎成品（唯一 DOT 码/胎号）→ 销售客户。本文约定：正查（Forward Tracking）从原材料批次出发，找出流向的所有成品与客户，用于缺陷召回；反查（Backward Tracking）从问题轮胎出发，向上追查所用半成品与原材料，用于客诉归因。注意不同企业/标准对“正查/反查”的命名可能相反，请以所在组织的术语约定为准。

下面笔者用一套最小用例，在 Oracle 26ai 中把这条链路跑通。

追溯链条的层级关系可以用一张图概括，正查与反查恰好是沿这张图的两个相反方向遍历：

flowchart LR R1["RAW-RUBBER-001 天然橡胶-01批次"] --> C["SEMI-COMPOUND-101 混炼胶-101批次"] R2["RAW-CARBON-002 炭黑-02批次"] --> C C --> T["SEMI-TREAD-201 胎面-201批次"] T --> F1["FG-TIRE-DOT-8888 225/55R17 轮胎A"] T --> F2["FG-TIRE-DOT-9999 225/55R17 轮胎B"] F1 --> CU["CUST-TESLA-01 客户-特斯拉工厂"]

图中从原材料指向客户的方向即正查（Forward Tracking），从成品轮胎回溯到原材料的方向即反查（Backward Tracking）。

01 | 建表：批次节点与追溯关系

追溯的核心是记录物料与批次的层级演变关系。笔者用两张表表达：lot_master 记录批次属性，lot_genealogy 记录父批次到子批次的消耗关系。

CREATE TABLE lot_master (
    lot_id       VARCHAR2(50) PRIMARY KEY,
    item_name    VARCHAR2(100) NOT NULL,
    item_type    VARCHAR2(20) NOT NULL, -- RAW/SEMI/FG/CUST
    produce_date DATE DEFAULT SYSDATE
);

CREATE TABLE lot_genealogy (
    parent_lot_id VARCHAR2(50) NOT NULL,
    child_lot_id  VARCHAR2(50) NOT NULL,
    quantity_used NUMBER,
    CONSTRAINT pk_lot_genealogy PRIMARY KEY (parent_lot_id, child_lot_id),
    CONSTRAINT fk_parent_lot FOREIGN KEY (parent_lot_id) REFERENCES lot_master(lot_id),
    CONSTRAINT fk_child_lot  FOREIGN KEY (child_lot_id)  REFERENCES lot_master(lot_id)
);

两张表的分工可以这样理解：lot_master 是节点表，lot_genealogy 是边表，二者组合起来就是一张有向图。

erDiagram LOT_MASTER ||--o{ LOT_GENEALOGY : "作为父批次" LOT_MASTER ||--o{ LOT_GENEALOGY : "作为子批次" LOT_MASTER { VARCHAR2 lot_id PK VARCHAR2 item_name VARCHAR2 item_type DATE produce_date } LOT_GENEALOGY { VARCHAR2 parent_lot_id PK,FK VARCHAR2 child_lot_id PK,FK NUMBER quantity_used }

02 | 造数：一条完整生产链

笔者构造一条简单链路：天然橡胶 RAW-RUBBER-001 与炭黑 RAW-CARBON-002 混炼成胶料 SEMI-COMPOUND-101，再做成胎面 SEMI-TREAD-201，组装成两条轮胎 FG-TIRE-DOT-8888、FG-TIRE-DOT-9999，其中 8888 发给客户 CUST-TESLA-01。

-- 前置条件：已执行第 01 节的建表语句；本脚本会清空 lot_genealogy 与 lot_master，
-- 仅可在测试库/测试 schema 中执行，请勿在生产库运行。
BEGIN
    DELETE FROM lot_genealogy;
    DELETE FROM lot_master;

    INSERT INTO lot_master VALUES ('RAW-RUBBER-001','天然橡胶-01批次','RAW',DATE '2026-01-01');
    INSERT INTO lot_master VALUES ('RAW-CARBON-002','炭黑-02批次','RAW',DATE '2026-01-01');
    INSERT INTO lot_master VALUES ('SEMI-COMPOUND-101','混炼胶-101批次','SEMI',DATE '2026-01-02');
    INSERT INTO lot_master VALUES ('SEMI-TREAD-201','胎面-201批次','SEMI',DATE '2026-01-03');
    INSERT INTO lot_master VALUES ('FG-TIRE-DOT-8888','225/55R17 轮胎A','FG',DATE '2026-01-05');
    INSERT INTO lot_master VALUES ('FG-TIRE-DOT-9999','225/55R17 轮胎B','FG',DATE '2026-01-05');
    INSERT INTO lot_master VALUES ('CUST-TESLA-01','客户-特斯拉工厂','CUST',DATE '2026-01-10');

    INSERT INTO lot_genealogy VALUES ('RAW-RUBBER-001','SEMI-COMPOUND-101',500);
    INSERT INTO lot_genealogy VALUES ('RAW-CARBON-002','SEMI-COMPOUND-101',200);
    INSERT INTO lot_genealogy VALUES ('SEMI-COMPOUND-101','SEMI-TREAD-201',300);
    INSERT INTO lot_genealogy VALUES ('SEMI-TREAD-201','FG-TIRE-DOT-8888',1);
    INSERT INTO lot_genealogy VALUES ('SEMI-TREAD-201','FG-TIRE-DOT-9999',1);
    INSERT INTO lot_genealogy VALUES ('FG-TIRE-DOT-8888','CUST-TESLA-01',1);
    COMMIT;
END;
/

03 | 正查与反查

Oracle 的 CONNECT BY 层次查询很适合这类树形结构。正查从缺陷原材料出发，找出所有下游成品与客户：

SELECT LEVEL AS hierarchy_level,
       SYS_CONNECT_BY_PATH(parent_lot_id,' -> ') || ' -> ' || child_lot_id AS full_trace_path,
       parent_lot_id AS source_lot,
       child_lot_id  AS target_lot,
       m.item_name   AS target_item_name,
       m.item_type   AS target_item_type
FROM lot_genealogy g
JOIN lot_master m ON g.child_lot_id = m.lot_id
START WITH g.parent_lot_id = 'RAW-RUBBER-001'
CONNECT BY PRIOR g.child_lot_id = g.parent_lot_id;

反查从问题轮胎出发，向上追查所用半成品与原材料：

SELECT LEVEL AS hierarchy_level,
       SYS_CONNECT_BY_PATH(child_lot_id,' <- ') || ' <- ' || parent_lot_id AS reverse_trace_path,
       child_lot_id  AS current_lot,
       parent_lot_id AS component_lot,
       m.item_name   AS component_name,
       m.item_type   AS component_type
FROM lot_genealogy g
JOIN lot_master m ON g.parent_lot_id = m.lot_id
START WITH g.child_lot_id = 'FG-TIRE-DOT-8888'
CONNECT BY PRIOR g.parent_lot_id = g.child_lot_id;

两条查询的差别只在 START WITH 的起点和 CONNECT BY PRIOR 的连接方向，可以用一张对照图记住这个规律：

flowchart TB subgraph FW["正查 Forward Tracking"] direction TB A1["START WITH parent_lot_id = 缺陷原材料"] --> A2["CONNECT BY PRIOR child_lot_id = parent_lot_id"] A2 --> A3["结果：下游半成品 / 成品 / 客户"] end subgraph BW["反查 Backward Tracking"] direction TB B1["START WITH child_lot_id = 问题轮胎DOT码"] --> B2["CONNECT BY PRIOR parent_lot_id = child_lot_id"] B2 --> B3["结果：上游半成品 / 原材料"] end

04 | 最佳实践

防止环路。 返工回炉可能造成 A→B→A 的循环，触发 ORA-01436。建议加上 NOCYCLE，并配合 CONNECT_BY_ISCYCLE 识别环路。

CONNECT BY NOCYCLE PRIOR g.child_lot_id = g.parent_lot_id

索引必建。 递归性能取决于索引。本表主键 (parent_lot_id, child_lot_id) 已可支撑正查方向；反查方向需要额外索引 (child_lot_id, parent_lot_id)。若主键定义不同，请按实际执行计划确认所需索引。

-- 1. 主键自动创建正查复合索引：(parent_lot_id, child_lot_id)
ALTER TABLE lot_genealogy ADD CONSTRAINT pk_lot_genealogy 
PRIMARY KEY (parent_lot_id, child_lot_id);

-- 2. 只需要手动创建反查复合索引：(child_lot_id, parent_lot_id)
CREATE INDEX idx_genealogy_bw ON lot_genealogy(child_lot_id, parent_lot_id);

控制深度。 只需追到直接半成品时，用 WHERE LEVEL <= N 限定层数，减少开销。

路径拼接注意。 SYS_CONNECT_BY_PATH 的分隔符不能出现在字段值中，拼接结果也不能超过 VARCHAR2 长度上限；层级过深导致超限时会报错，此时可改用 CONNECT_BY_ROOT 或只保留关键层级，避免拼接完整路径。

05 | 何时升级到属性图

CONNECT BY 能覆盖多数单树与简单 DAG 追溯。但遇到多关系网状结构时，关系型递归会吃力，可考虑 Oracle 26ai 内置的 Operational Property Graph。

典型场景是设备交叉污染：混炼机发生润滑油泄漏后，未彻底清洗前生产的所有批次都受影响。此时设备、批次、人员、车间都是节点，用图模式匹配能一句查出跨实体的影响范围。另一类是残料回用与复配形成的多对多网状交织，关系型递归容易因大量去重与 Join 出现性能瓶颈，而图引擎的邻接索引在高并发下更稳定。

关注我，和AI一起成长~

刷新页面返回顶部

Alfred Zhao 始于 Oracle，却远不止于 Oracle。