SQL tunning task common

2026-01-31 工作雜記 Oracle

以**「電商物流系統」**為主題。這個場景包含了高頻交易、大量文字描述以及複雜的關聯。

以下是針對 Oracle 11g 環境設計的實作腳本。

第一步：設計與建立資料表 (DDL)

我們設計五張表：客戶 (CUSTOMERS)、商品 (PRODUCTS)、訂單主檔 (ORDERS)、訂單明細 (ORDER_ITEMS)、以及物流追蹤備註 (LOGISTICS_DETAIL)。

-- 1. 客戶表
CREATE TABLE CUSTOMERS (
    CUST_ID NUMBER PRIMARY KEY,
    CUST_NAME VARCHAR2(100),
    CITY VARCHAR2(20),
    MEMBER_LEVEL NUMBER(1), -- 1-5 級
    REMARK CLOB
);

-- 2. 商品表
CREATE TABLE PRODUCTS (
    PROD_ID NUMBER PRIMARY KEY,
    PROD_NAME VARCHAR2(200),
    CATEGORY VARCHAR2(50),
    PRICE NUMBER(10,2),
    STATUS CHAR(1) -- 'Y': 上架, 'N': 下架
);

-- 3. 訂單主檔
CREATE TABLE ORDERS (
    ORDER_ID NUMBER PRIMARY KEY,
    CUST_ID NUMBER,
    ORDER_DATE DATE,
    TOTAL_AMOUNT NUMBER(12,2),
    PAYMENT_METHOD VARCHAR2(20)
);

-- 4. 訂單明細 (大表)
CREATE TABLE ORDER_ITEMS (
    ITEM_ID NUMBER PRIMARY KEY,
    ORDER_ID NUMBER,
    PROD_ID NUMBER,
    QUANTITY NUMBER(5),
    UNIT_PRICE NUMBER(10,2)
);

-- 5. 物流詳細追蹤 (含有大量文字與狀態)
CREATE TABLE LOGISTICS_DETAIL (
    LOG_ID NUMBER PRIMARY KEY,
    ORDER_ID NUMBER,
    TRACKING_NUM VARCHAR2(50),
    UPDATE_TIME TIMESTAMP,
    STATION_INFO VARCHAR2(500),
    FULL_LOG CLOB
);

第二步：產生 10 萬筆測試資料 (DML)

我們利用 CONNECT BY 與 dbms_random 快速生成模擬資料。

-- 插入商品 (10萬筆)
INSERT /*+ APPEND */ INTO PRODUCTS
SELECT 
    LEVEL,
    'Product_' || LEVEL,
    CASE MOD(LEVEL, 5) WHEN 0 THEN 'Electronics' WHEN 1 THEN 'Books' WHEN 2 THEN 'Clothing' WHEN 3 THEN 'Home' ELSE 'Food' END,
    DBMS_RANDOM.VALUE(10, 5000),
    'Y'
FROM DUAL CONNECT BY LEVEL <= 100000;

-- 插入客戶 (10萬筆)
INSERT /*+ APPEND */ INTO CUSTOMERS
SELECT 
    LEVEL,
    'User_' || LEVEL,
    CASE MOD(LEVEL, 4) WHEN 0 THEN 'Taipei' WHEN 1 THEN 'Taichung' WHEN 2 THEN 'Kaohsiung' ELSE 'Tainan' END,
    MOD(LEVEL, 5) + 1,
    'Customer preference notes for ID ' || LEVEL
FROM DUAL CONNECT BY LEVEL <= 100000;

-- 插入訂單主檔 (10萬筆)
INSERT /*+ APPEND */ INTO ORDERS
SELECT 
    LEVEL,
    TRUNC(DBMS_RANDOM.VALUE(1, 100000)),
    TO_DATE('2023-01-01','YYYY-MM-DD') + DBMS_RANDOM.VALUE(0, 365),
    0, -- 後續更新
    CASE MOD(LEVEL, 3) WHEN 0 THEN 'Credit Card' WHEN 1 THEN 'Transfer' ELSE 'Cash' END
FROM DUAL CONNECT BY LEVEL <= 100000;

-- 插入訂單明細 (10萬筆，讓每筆訂單至少對應一個商品)
INSERT /*+ APPEND */ INTO ORDER_ITEMS
SELECT 
    LEVEL,
    LEVEL, -- 這裡簡化為 1:1，你可以自行調整為 1:N
    TRUNC(DBMS_RANDOM.VALUE(1, 100000)),
    TRUNC(DBMS_RANDOM.VALUE(1, 10)),
    0
FROM DUAL CONNECT BY LEVEL <= 100000;

-- 插入物流明細 (10萬筆)
INSERT /*+ APPEND */ INTO LOGISTICS_DETAIL
SELECT 
    LEVEL,
    LEVEL,
    'TRK' || LPAD(LEVEL, 10, '0'),
    SYSTIMESTAMP - DBMS_RANDOM.VALUE(0, 10),
    'Arrived at station ' || MOD(LEVEL, 50),
    RPAD('Detailed log for shipment ' || LEVEL, 1000, '*')
FROM DUAL CONNECT BY LEVEL <= 100000;

COMMIT;

-- 收集統計資訊 (Oracle 調校必備)
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(USER);

第三步：10 組不同難度的 SQL 練習

這裡提供從簡單到「效能殺手級」的 SQL，你可以嘗試查看執行計畫 (Explain Plan) 並建立索引或改寫 SQL 來優化。

1. 簡單：精確匹配與排序

需求： 找出所有住在「Taipei」且會員等級為 5 的客戶名稱。

SELECT CUST_NAME FROM CUSTOMERS WHERE CITY = 'Taipei' AND MEMBER_LEVEL = 5;

2. 中等：基本 Join 與 聚合

需求： 計算每個分類 (Category) 的商品平均價格，且只顯示平均價大於 1000 的分類。

SELECT CATEGORY, AVG(PRICE) FROM PRODUCTS GROUP BY CATEGORY HAVING AVG(PRICE) > 1000;

3. 中等：三表關聯

需求： 查詢客戶 ID 為 5000 的所有訂單日期及其購買的商品名稱。

SELECT o.ORDER_DATE, p.PROD_NAME 
FROM ORDERS o
JOIN ORDER_ITEMS i ON o.ORDER_ID = i.ORDER_ID
JOIN PRODUCTS p ON i.PROD_ID = p.PROD_ID
WHERE o.CUST_ID = 5000;

4. 進階：子查詢與 IN (潛在效能問題)

需求： 找出所有購買過「Electronics」類別商品的客戶清單。

SELECT * FROM CUSTOMERS 
WHERE CUST_ID IN (
    SELECT o.CUST_ID FROM ORDERS o 
    JOIN ORDER_ITEMS i ON o.ORDER_ID = i.ORDER_ID
    WHERE i.PROD_ID IN (SELECT PROD_ID FROM PRODUCTS WHERE CATEGORY = 'Electronics')
);

5. 進階：Exists 替代 Join

需求： 找出有物流更新記錄，且物流訊息包含 ‘station 10’ 的訂單主檔。

SELECT * FROM ORDERS o
WHERE EXISTS (
    SELECT 1 FROM LOGISTICS_DETAIL l 
    WHERE l.ORDER_ID = o.ORDER_ID AND l.STATION_INFO LIKE '%station 10%'
);

6. 複雜：分析函數 (Window Functions)

需求： 找出每個城市中，消費金額最高的前三名客戶 (不建立實體表，動態計算)。

SELECT * FROM (
    SELECT c.CUST_NAME, c.CITY, SUM(o.TOTAL_AMOUNT) as spent,
           RANK() OVER (PARTITION BY c.CITY ORDER BY SUM(o.TOTAL_AMOUNT) DESC) as rnk
    FROM CUSTOMERS c
    JOIN ORDERS o ON c.CUST_ID = o.CUST_ID
    GROUP BY c.CUST_NAME, c.CITY
) WHERE rnk <= 3;

7. 複雜：CLOB 文字檢索與過濾

需求： 在物流日誌 (CLOB) 中搜尋特定關鍵字，並結合大表關聯。這通常會導致全表掃描。

SELECT o.ORDER_ID, l.TRACKING_NUM 
FROM ORDERS o
JOIN LOGISTICS_DETAIL l ON o.ORDER_ID = l.ORDER_ID
WHERE DBMS_LOB.INSTR(l.FULL_LOG, 'shipment 99999') > 0;

8. 超級複雜：WITH 子句與多重聚合 (CTE)

需求： 找出「高價值客戶」：定義為總消費額高於該城市平均消費額的客戶，並列出他們最常購買的商品類別。

WITH CityAvg AS (
    SELECT c.CITY, AVG(o.TOTAL_AMOUNT) as city_avg_spent
    FROM CUSTOMERS c JOIN ORDERS o ON c.CUST_ID = o.CUST_ID
    GROUP BY c.CITY
)
SELECT c.CUST_NAME, p.CATEGORY, COUNT(*) 
FROM CUSTOMERS c
JOIN ORDERS o ON c.CUST_ID = o.CUST_ID
JOIN ORDER_ITEMS i ON o.ORDER_ID = i.ORDER_ID
JOIN PRODUCTS p ON i.PROD_ID = p.PROD_ID
JOIN CityAvg ca ON c.CITY = ca.CITY
GROUP BY c.CUST_NAME, p.CATEGORY, ca.city_avg_spent
HAVING SUM(o.TOTAL_AMOUNT) > ca.city_avg_spent;

9. 超級複雜：關聯子查詢 (Correlated Subquery)

需求： 找出每一筆訂單中，價格高於該訂單內所有商品平均價格的明細。

SELECT * FROM ORDER_ITEMS i1
WHERE UNIT_PRICE > (
    SELECT AVG(UNIT_PRICE) FROM ORDER_ITEMS i2 WHERE i2.ORDER_ID = i1.ORDER_ID
);

10. 終極挑戰：笛卡兒積風險與反模式

需求： 故意寫出一個沒有適當 Join 條件或是在大型非索引欄位上做 OR 運算的查詢，觀察 Oracle 的 CBO (Cost-Based Optimizer) 如何掙扎。

SELECT c.CUST_NAME, p.PROD_NAME, l.STATION_INFO
FROM CUSTOMERS c, PRODUCTS p, LOGISTICS_DETAIL l
WHERE (c.CUST_ID = l.ORDER_ID OR c.CUST_NAME = l.TRACKING_NUM)
AND p.PRICE > 4900;

調校建議步驟

  1. 開啟追蹤： 使用 SET AUTOTRACE ON 或 EXPLAIN PLAN FOR...。
  2. 觀察數據分布： 10 萬筆資料下，某些欄位（如 CITY）重複率高，適合建立 Bitmap Index（如果是 Data Warehouse 環境）或 B-Tree Index。
  3. 針對 CLOB 優化： 考慮是否需要 Full Text Index (Oracle Text)。
  4. 改寫 SQL： 試著將 IN 改成 EXISTS 或 JOIN，觀察 Cost 的變化。

調校紀錄

EXPLAIN PLAN FOR SELECT CUST_NAME FROM CUSTOMERS WHERE CITY = 'Taipei' AND MEMBER_LEVEL = 5;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT                                                              |
-------------------------------------------------------------------------------+
Plan hash value: 2008213504                                                    |
                                                                               |
-------------------------------------------------------------------------------|
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     ||
-------------------------------------------------------------------------------|
|   0 | SELECT STATEMENT  |           | 54445 |  4625K|  6207   (1)| 00:01:15 ||
|*  1 |  TABLE ACCESS FULL| CUSTOMERS | 54445 |  4625K|  6207   (1)| 00:01:15 ||
-------------------------------------------------------------------------------|
                                                                               |
Predicate Information (identified by operation id):                            |
---------------------------------------------------                            |
                                                                               |
   1 - filter("MEMBER_LEVEL"=5 AND "CITY"='Taipei')                            |
                                                                               |
Note                                                                           |
-----                                                                          |
   - dynamic sampling used for this statement (level=2)                        |

CREATE INDEX idx_cust_city_level ON CUSTOMERS (CITY, MEMBER_LEVEL) NOSEGMENT;

Query	CREATE INDEX idx_cust_city_level ON CUSTOMERS (CITY, MEMBER_LEVEL) NOSEGMENT
Updated Rows	0
Execute time	0.014s
Start time	Fri Feb 06 13:39:05 CST 2026
Finish time	Fri Feb 06 13:39:05 CST 2026

ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

EXPLAIN PLAN FOR SELECT CUST_NAME FROM CUSTOMERS WHERE CITY = 'Taipei' AND MEMBER_LEVEL = 5;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT                                                                                  |
---------------------------------------------------------------------------------------------------+
Plan hash value: 4017564175                                                                        |
                                                                                                   |
---------------------------------------------------------------------------------------------------|
| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     ||
---------------------------------------------------------------------------------------------------|
|   0 | SELECT STATEMENT            |                     | 54445 |  4625K|     9   (0)| 00:00:01 ||
|   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS           | 54445 |  4625K|     9   (0)| 00:00:01 ||
|*  2 |   INDEX RANGE SCAN          | IDX_CUST_CITY_LEVEL | 10729 |       |     1   (0)| 00:00:01 ||
---------------------------------------------------------------------------------------------------|
                                                                                                   |
Predicate Information (identified by operation id):                                                |
---------------------------------------------------                                                |
                                                                                                   |
   2 - access("CITY"='Taipei' AND "MEMBER_LEVEL"=5)                                                |
                                                                                                   |
Note                                                                                               |
-----                                                                                              |
   - dynamic sampling used for this statement (level=2)                                            |

-- 刪除虛擬索引
DROP INDEX idx_cust_city_level;

Query	-- 刪除虛擬索引
	DROP INDEX idx_cust_city_level
Updated Rows	0
Execute time	0.014s
Start time	Fri Feb 06 13:42:57 CST 2026
Finish time	Fri Feb 06 13:42:57 CST 2026

-- 建立實體索引（正式讓查詢變快）
CREATE INDEX idx_cust_city_level ON CUSTOMERS (CITY, MEMBER_LEVEL) ;

Query	-- 建立實體索引（正式讓查詢變快）
	CREATE INDEX idx_cust_city_level ON CUSTOMERS (CITY, MEMBER_LEVEL) 
Updated Rows	0
Execute time	4s
Start time	Fri Feb 06 13:43:02 CST 2026
Finish time	Fri Feb 06 13:43:07 CST 2026

Relevant Topics


Recent Posts