專案背景
醫院資訊系統 (HIS) 核心醫囑表 (`ORDERS`) 日增萬筆，查詢效能下降。採用 Oracle 8.1.6 實作分區策略，達成查詢加速、維護彈性、備份效率三大目標。
第一階段：識別需分區的表
1. 基於存取頻率 (Auditing)
- 啟用物件審計：`AUDIT SELECT ON schema.table BY ACCESS;`
- 收集 24 小時 `DBA_AUDIT_OBJECT` 統計，彙總 `HITS` (存取次數)。
- 關閉審計並清理 `SYS.AUD$`。
2. 結合資料量與存取熱度決策
| 表名 | 列數 | HITS | 決策 |
|---|
| DOCTOR_ORDERS | 2,052,709 | 30,309 | 分區 |
| ORDERS | 1,633,010 | 40,421 | 分區 (核心) |
| ORDERS_COSTS | 2,403,214 | 10,109 | 分區 |
| GROUP_ORDER_MASTER | 186 | 1,196 | 否 (太小) |
| DRUG_STOCK | 2,511 | 11,094 | 否 (太小) |
選擇 `ORDERS` 為核心分區對象：結構與 `DOCTOR_ORDERS` 相似，且與藥品、收費、物資關聯最緊密。
第二階段：選擇分區鍵 (Partition Key)
執行 `ANALYZE TABLE ... ESTIMATE STATISTICS SAMPLE 20 PERCENT`，查詢 `DBA_TAB_COLUMNS.NUM_DISTINCT`：
| 欄位 | NDV (Distinct Values) | 分布特性 | 評估 |
|---|
| ORDER_CODE | 825 | 非均勻 (熱門代碼極多) | 不適合 Range |
| ORDER_CLASS | 9 | 均勻 | 可 Hash，但 Range 易傾斜 |
| ORDERING_DEPT | 15 | 均勻 | 適合 Hash 子分區 |
| START_DATE_TIME | 1,194,176 | 時間序列自然遞增 | 最佳 Range Key |
| STOP_DATE_TIME | 636,798 | 時間序列 | 備選 |
決策：Range-Hash 複合分區
- Range Partition：`START_DATE_TIME`，每月一個分區 (符合業務統計週期：月/季/年)。
- Hash Subpartition：`ORDERING_DEPT` (或 `ORDER_CODE`)，3 個子分區分散 I/O。
- 優勢：
• 單月資料獨立管理 (增刪改查僅掃描 1-2 個分區)。
• 子分區平衡 I/O，避免熱點科室集中單一磁碟。
• 歷史分區可 `DROP`/`TRUNCATE`/`MOVE` 至廉價儲存，不影響線上分區。
第三階段：分區表 DDL 實作
CREATE TABLE ORDERS (
PATIENT_ID VARCHAR2(10) NOT NULL,
ORDER_NO NUMBER(4),
ORDER_SUB_NO NUMBER(2),
ORDER_CLASS CHAR(1),
ORDER_TEXT VARCHAR2(80),
ORDER_CODE VARCHAR2(10),
START_DATE_TIME DATE,
STOP_DATE_TIME DATE,
ORDERING_DEPT VARCHAR2(8),
-- 其他欄位...
)
STORAGE (INITIAL 100M NEXT 1M)
PARTITION BY RANGE (START_DATE_TIME)
SUBPARTITION BY HASH (ORDERING_DEPT)
SUBPARTITIONS 3 STORE IN (ORDER_SUB1, ORDER_SUB2, ORDER_SUB3)
(
PARTITION ORDERS_200212 VALUES LESS THAN (TO_DATE('2003-01-01','YYYY-MM-DD')) TABLESPACE TSP_ORDERS,
PARTITION ORDERS_200301 VALUES LESS THAN (TO_DATE('2003-02-01','YYYY-MM-DD')) TABLESPACE TSP_ORDERS,
PARTITION ORDERS_200302 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE TSP_ORDERS,
-- ... 至 200402
PARTITION ORDERS_200402 VALUES LESS THAN (TO_DATE('2004-03-01','YYYY-MM-DD')) TABLESPACE TSP_ORDERS
);
-- 主鍵索引 (Global 或 Local 視查詢模式)
ALTER TABLE ORDERS ADD CONSTRAINT PK_ORDERS PRIMARY KEY (PATIENT_ID, VISIT_ID, ORDER_NO, ORDER_SUB_NO)
USING INDEX PCTFREE 5 TABLESPACE TSP_ORDERS; -- Global Index
第四階段：分區維護操作
| 操作 | 語法範例 | 用途 |
|---|
| 新增分區 | `ALTER TABLE ORDERS ADD PARTITION ORDERS_200403 VALUES LESS THAN (TO_DATE('2004-04-01','YYYY-MM-DD')) TABLESPACE TSP_ORDERS;` | 每月例行擴充 |
| 刪除分區 | `ALTER TABLE ORDERS DROP PARTITION ORDERS_200212;` | 清除歷史資料 (瞬間完成，無 Delete 開銷) |
| 截斷分區 | `ALTER TABLE ORDERS TRUNCATE PARTITION ORDERS_200301 DROP STORAGE;` | 保留分區結構，清空資料並釋放空間 |
| 移動分區 | `ALTER TABLE ORDERS MOVE PARTITION ORDERS_200301 TABLESPACE TSP_ARCHIVE COMPRESS;` | 冷熱資料分級儲存 |
| 分割分區 | `ALTER TABLE ORDERS SPLIT PARTITION ORDERS_200312 AT (TO_DATE('2004-01-01','YYYY-MM-DD')) INTO (PARTITION ORDERS_200312, PARTITION ORDERS_200401);` | 調整分區粒度 |
| 交換分區 | `ALTER TABLE ORDERS EXCHANGE PARTITION ORDERS_200301 WITH TABLE ORDERS_STG INCLUDING INDEXES WITHOUT VALIDATION;` | ETL 快速載入/卸載 |
效果驗證
| 分區名 | 列數 | 備註 |
|---|
| ORDERS_200301 | 87,311 | 分區列數均勻分布 |
| ORDERS_200302 | 87,553 | |
| ORDERS_200303 | 107,533 | 業務高峰月 |
| ORDERS_200401 | 124,812 | |
- 月度統計查詢：從全表掃描 200 萬列 → 僅掃描單分區 ~10 萬列，**速度提升 20 倍以上**。
- 跨月/跨年查詢：Partition Pruning 僅掃描相關分區，仍大幅優於未分區。
- 備份窗口：單分區備份可並行，總時間縮減 60%。
分區核心優勢總結
- 可用性增強：單分區故障不影響其他分區存取。
- 停機時間縮減：僅修復受損分區，非全表重建。
- 維護輕量化：分區級 `REBUILD`/`MOVE`/`COMPRESS` 取代全表操作。
- I/O 平衡：分區/子分區分散不同磁碟/表空間。
- 查詢效能：Partition Pruning + Partition-wise Join 實現邏輯分治。
- 透明性：應用端無感知，SQL 不需修改。
適用建議
- 表大小 > 2GB 或列數 > 1,000 萬考慮分區。
- 明顯的時間序列、地域、業務代碼等篩選維度優先選 Range/List。
- 複合分區 (Range-Hash, Range-List) 解決單一維度傾斜問題。
- Global Index 維護成本高，若查詢多為分區鍵驅動，優先用 Local Index。
- Oracle 11g+ 引入 Interval Partitioning (自動擴充)、Reference Partitioning (父子表關聯)、System Partitioning (應用控制)，進一步降低維護負擔。
