SQL tunning task MES

2026-01-31 工作雜記 Oracle

第一步：MES 系統資料表設計 (DDL)

我們設計六張核心表，涵蓋工單、批次（Lot）、機台、過帳紀錄與參數採集。

-- 1. 工單表 (Work Orders)
CREATE TABLE MES_WORK_ORDERS (
    WO_ID NUMBER PRIMARY KEY,
    WO_NO VARCHAR2(50) NOT NULL,
    PART_NO VARCHAR2(50),
    TARGET_QTY NUMBER(10),
    CREATE_TIME DATE,
    STATUS CHAR(1) -- 'O': Open, 'C': Closed
);

-- 2. 批次主檔 (WIP Lots)
CREATE TABLE MES_WIP_LOTS (
    LOT_ID NUMBER PRIMARY KEY,
    LOT_NO VARCHAR2(50) UNIQUE,
    WO_ID NUMBER,
    CURR_STEP VARCHAR2(20),
    QTY NUMBER(10,2),
    IS_HOLD CHAR(1) DEFAULT 'N'
);

-- 3. 生產設備表 (Equipment)
CREATE TABLE MES_EQUIPMENT (
    EQP_ID NUMBER PRIMARY KEY,
    EQP_CODE VARCHAR2(50),
    EQP_TYPE VARCHAR2(20),
    LOCATION VARCHAR2(50),
    LAST_MAINTENANCE DATE
);

-- 4. 生產過帳歷史 (Move History) - 這是調校重點（大表）
CREATE TABLE MES_MOVE_HISTORY (
    TXN_ID NUMBER PRIMARY KEY,
    LOT_ID NUMBER,
    STEP_ID VARCHAR2(20),
    EQP_ID NUMBER,
    OP_ID NUMBER, -- 操作人員
    START_TIME DATE,
    END_TIME DATE,
    TXN_CODE VARCHAR2(10) -- 'TRACKIN', 'TRACKOUT'
);

-- 5. 品質量測數據 (Quality Data) - 數字密集
CREATE TABLE MES_QUALITY_DATA (
    DATA_ID NUMBER PRIMARY KEY,
    TXN_ID NUMBER,
    MEASURE_ITEM VARCHAR2(50),
    VALUE_NUM NUMBER(15,5),
    UPPER_LIMIT NUMBER(15,5),
    LOWER_LIMIT NUMBER(15,5),
    RESULT CHAR(1) -- 'P': Pass, 'F': Fail
);

-- 6. 系統異常日誌 (Error Logs) - 包含 CLOB
CREATE TABLE MES_SYSTEM_LOGS (
    LOG_ID NUMBER PRIMARY KEY,
    MODULE_NAME VARCHAR2(50),
    OCCUR_TIME TIMESTAMP,
    ERROR_CODE VARCHAR2(20),
    STACK_TRACE CLOB,
    REMARK VARCHAR2(1000)
);

第二步：快速產生 1,000 萬筆資料 (DML)

對於 10M 等級的資料，建議使用 /*+ APPEND */ 進行直接路徑插入 (Direct Path Insert)，並將表設為 NOLOGGING 以加快速度。

注意： 執行前請確認磁碟空間充足（預計約需 10GB~20GB 空間）。

-- 以生產歷史表為例 (產生 1000 萬筆)
INSERT /*+ APPEND */ INTO MES_MOVE_HISTORY
SELECT 
    LEVEL,
    TRUNC(DBMS_RANDOM.VALUE(1, 1000000)), -- 假設有 100 萬個批次
    'STEP_' || LPAD(TRUNC(DBMS_RANDOM.VALUE(1, 100)), 3, '0'),
    TRUNC(DBMS_RANDOM.VALUE(1, 5000)), -- 5000 台設備
    TRUNC(DBMS_RANDOM.VALUE(1, 1000)), -- 1000 個作業員
    SYSDATE - DBMS_RANDOM.VALUE(0, 365),
    SYSDATE - DBMS_RANDOM.VALUE(0, 364),
    CASE MOD(LEVEL, 2) WHEN 0 THEN 'TRACKIN' ELSE 'TRACKOUT' END
FROM DUAL CONNECT BY LEVEL <= 10000000;
COMMIT;

-- 務必收集統計資訊，否則 CBO 會誤判
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'MES_MOVE_HISTORY', CASCADE => TRUE);

(以此類推完成其他表，建議分批 Commit 或調整 Undo Segment)

第三步：10 組 MES 調校專用 SQL

1. 基礎查詢：索引失效測試

情境： 找出某個時間區段內的過帳紀錄，但 START_TIME 被套用了函數導致無法走 Index。

SELECT * FROM MES_MOVE_HISTORY 
WHERE TO_CHAR(START_TIME, 'YYYYMMDD') = '20250520';

2. 聚合分析：產線稼動率計算

情境： 計算每台設備在過去一週的總加工時數（End - Start）。這在 10M 筆資料下會觸發大量 Hash Aggregate。

SELECT EQP_ID, SUM((END_TIME - START_TIME) * 24) as WORK_HOURS
FROM MES_MOVE_HISTORY
WHERE START_TIME > SYSDATE - 7
GROUP BY EQP_ID;

3. 多表關聯：WIP 在製清單

情境： 找出所有狀態為 ‘Open’ 的工單中，目前被 ‘Hold’ 住的 Lot 以及它們所在的機台。

SELECT w.WO_NO, l.LOT_NO, e.EQP_CODE
FROM MES_WORK_ORDERS w
JOIN MES_WIP_LOTS l ON w.WO_ID = l.WO_ID
JOIN MES_MOVE_HISTORY m ON l.LOT_ID = m.LOT_ID
JOIN MES_EQUIPMENT e ON m.EQP_ID = e.EQP_ID
WHERE w.STATUS = 'O' AND l.IS_HOLD = 'Y' AND m.TXN_CODE = 'TRACKIN';

4. 視窗函數：Lead/Lag 分析 (進程時效)

情境： 計算每個 Lot 從上一個站點到下一個站點的「移轉時間」(Transfer Time)。

SELECT LOT_ID, STEP_ID, START_TIME,
       LAG(END_TIME) OVER (PARTITION BY LOT_ID ORDER BY START_TIME) as PREV_END_TIME
FROM MES_MOVE_HISTORY;

5. 品質數據追蹤：離群值檢索

情境： 找出所有量測值 (VALUE_NUM) 超出上下限且屬於 ‘Electronics’ 類別設備生產的 Lot。

SELECT l.LOT_NO, q.MEASURE_ITEM, q.VALUE_NUM
FROM MES_QUALITY_DATA q
JOIN MES_MOVE_HISTORY m ON q.TXN_ID = m.TXN_ID
JOIN MES_WIP_LOTS l ON m.LOT_ID = l.LOT_ID
WHERE q.RESULT = 'F' AND q.VALUE_NUM > q.UPPER_LIMIT;

6. 複雜子查詢：尚未完成量測的批次

情境： 找出已過帳 (TRACKOUT) 但在 MES_QUALITY_DATA 表中卻沒有對應數據的紀錄（測試 NOT EXISTS 效能）。

SELECT * FROM MES_MOVE_HISTORY m
WHERE TXN_CODE = 'TRACKOUT'
AND NOT EXISTS (SELECT 1 FROM MES_QUALITY_DATA q WHERE q.TXN_ID = m.TXN_ID);

7. CLOB 全文檢索與效能瓶頸

情境： 在 1000 萬筆日誌中，搜尋 Stack Trace 包含 ‘NullPointerException’ 且發生在特定機台模組的錯誤。

SELECT * FROM MES_SYSTEM_LOGS 
WHERE MODULE_NAME = 'WAFER_ETCH' 
AND STACK_TRACE LIKE '%NullPointerException%';

8. 階層式查詢 (Hierarchical)：批次拆分追溯 (Split/Merge)

情境： 在 MES 中，Lot 會被拆分或合併，利用 CONNECT BY 找出某個祖先批次的所有子孫批次（假設有 Parent_Lot 欄位）。

-- 需先在 MES_WIP_LOTS 增加 PARENT_LOT_ID 欄位
SELECT LEVEL, LOT_NO FROM MES_WIP_LOTS
START WITH LOT_NO = 'ROOT_LOT_001'
CONNECT BY PRIOR LOT_ID = PARENT_LOT_ID;

9. 關聯子查詢更新 (Correlated Update)

情境： 根據最新的 MES_MOVE_HISTORY 更新 MES_WIP_LOTS 的當前站點 CURR_STEP。這在大表更新時非常耗時。

UPDATE MES_WIP_LOTS l
SET CURR_STEP = (
    SELECT MAX(STEP_ID) FROM MES_MOVE_HISTORY m 
    WHERE m.LOT_ID = l.LOT_ID
)
WHERE EXISTS (SELECT 1 FROM MES_MOVE_HISTORY m WHERE m.LOT_ID = l.LOT_ID);

10. 終極挑戰：多維度生產報表 (Complex Pivot/Rollup)

情境： 產生一份報表，按「日、班別 (Shift)、機台類型」統計良率，並計算與前一天的增長率。

SELECT TRUNC(START_TIME) as PROD_DATE, 
       e.EQP_TYPE,
       COUNT(DISTINCT m.LOT_ID) as TOTAL_LOTS,
       SUM(CASE WHEN q.RESULT = 'P' THEN 1 ELSE 0 END) / COUNT(*) as YIELD_RATE
FROM MES_MOVE_HISTORY m
JOIN MES_EQUIPMENT e ON m.EQP_ID = e.EQP_ID
LEFT JOIN MES_QUALITY_DATA q ON m.TXN_ID = q.TXN_ID
GROUP BY ROLLUP(TRUNC(START_TIME), e.EQP_TYPE);

下一步建議

在 1,000 萬筆資料下，索引的選擇（B-Tree vs Bitmap）以及 Partitioning (分區) 會是效能的關鍵。

您是否希望我針對其中一個 SQL，示範如何建立 Partition (範圍或雜湊分區) 來大幅提升查詢速度？

Relevant Topics


Recent Posts