SQL tunning task MES

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

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61


-- 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 空間）。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


-- 以生產歷史表為例 (產生 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。

1
2


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

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

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

1
2
3
4


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 以及它們所在的機台。

1
2
3
4
5
6


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)。

1
2
3


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。

1
2
3
4
5


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 效能）。

1
2
3


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’ 且發生在特定機台模組的錯誤。

1
2
3


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 欄位）。

1
2
3
4


-- 需先在 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。這在大表更新時非常耗時。

1
2
3
4
5
6


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)、機台類型」統計良率，並計算與前一天的增長率。

1
2
3
4
5
6
7
8


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 (範圍或雜湊分區) 來大幅提升查詢速度？

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

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

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

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

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

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

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

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

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

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

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

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

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

下一步建議 #