面對 TB 級資料量，單純建立索引已無法解決查詢效能瓶頸。Oracle 分區技術將大表邏輯切分為獨立段，配合分區索引、叢集表、函數索引、位圖索引、反序索引等多維策略，可實現「掃描更少資料、維護更靈活、平行度更高」的三重效能紅利。本文聚焦實戰落地模式。
-- 本地分區索引：每個表分區對應一個索引分區（1:1）
CREATE INDEX idx_orders_cust_local ON orders(customer_id) LOCAL;
-- 全域分區索引：自定義分區鍵，可跨表分區
CREATE INDEX idx_orders_dt_global ON orders(order_date)
GLOBAL PARTITION BY RANGE (order_date)
(PARTITION p_2023 VALUES LESS THAN (DATE '2024-01-01'),
PARTITION p_2024 VALUES LESS THAN (DATE '2025-01-01'),
PARTITION p_max VALUES LESS THAN (MAXVALUE));| 場景 | 推薦類型 | 理由 |
|---|---|---|
| 分區鍵 = 索引鍵（如 order_date） | LOCAL | 分區裁剪自動生效、維護最簡單（TRUNCATE/DROP 分區同步） |
| 頻繁按非分區鍵查詢（如 customer_id） | GLOBAL | 避免掃描所有本地分區，單一 B-tree 範圍掃描 |
| 唯一性約束包含分區鍵 | LOCAL | 唯一性在分區內強制，效能最佳 |
| 唯一性約束不含分區鍵 | GLOBAL (非分區或全域分區) | 跨分區強制唯一需全域索引 |
| 資料清理按時間滾動 | LOCAL | ALTER TABLE ... DROP PARTITION 瞬間完成，索引自動維護 |
ALTER INDEX ... REBUILD PARTITION p_2023 單獨重建單分區，不影響其他分區查詢ALTER TABLE ... MOVE PARTITION ... UPDATE INDEXES 線上移動分區同步維護全域索引（12c+）資料直接儲存在 B-tree 索引葉節點，無 heap table，主鍵順序即儲存順序。
CREATE TABLE order_summary (
order_id NUMBER,
cust_id NUMBER,
total_amt NUMBER,
CONSTRAINT pk_order_summary PRIMARY KEY (order_id)
) ORGANIZATION INDEX
PCTTHRESHOLD 20 -- 超過 20% 區塊移到溢出段
OVERFLOW TABLESPACE iot_overflow;適用：主鍵查詢佔 90%+、資料相對靜態、需避免「表+索引」雙重 I/O。
避坑：非主鍵查詢需建立二級索引（儲存 PK 值而非 ROWID）、大量 DML 導致分裂、不支援分區（12c+ 支援）。
多張表共用同一叢集鍵儲存於同一區塊，預先 Join 好。
CREATE CLUSTER cust_order_cluster (cust_id NUMBER)
SIZE 8000 -- 預估每鍵資料大小
TABLESPACE cluster_ts;
CREATE TABLE customers (...) CLUSTER cust_order_cluster (cust_id);
CREATE TABLE orders (...) CLUSTER cust_order_cluster (cust_id);
CREATE INDEX idx_cust_order_cluster ON CLUSTER cust_order_cluster;適用：父子表高頻等值 Join、資料量可控、插入頻率低。
| 索引類型 | 建立語法 | 適用場景 | 限制 |
|---|---|---|---|
| 函數索引 | CREATE INDEX idx_upper_name ON users(UPPER(name)); | WHERE UPPER(name) = 'JOHN'、SUBSTR(id,1,2)='AB' | 查詢必須完全匹配函數表達式 |
| 位圖索引 | CREATE BITMAP INDEX idx_gender ON users(gender); | 低基數欄位（性別、狀態、類型）、星型查詢多維組合 | DML 鎖定整個位圖段，嚴禁 OLTP 高併發表 |
| 反序索引 | CREATE INDEX idx_rev_id ON logs(id) REVERSE; | 遞增序列/時間戳導致右側熱塊爭用 | 範圍掃描失效、不支援位圖 |
| 部分索引 (12c+) | CREATE INDEX idx_active_orders ON orders(status) INDEXING PARTIAL; | 僅索引 PARTITION 中 INDEXING ON 的分區 | 需表分區定義 INDEXING ON/OFF |
場景：10 萬客戶 × 100 訂單 = 1000 萬 Join 結果，每日報表重複計算。
CREATE MATERIALIZED VIEW mv_cust_order_summary
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE AS
SELECT c.cust_id, c.name, COUNT(o.order_id) order_cnt, SUM(o.total_amt) total_amt
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
WHERE o.status = 'COMPLETED'
GROUP BY c.cust_id, c.name;
-- 建立 MV 索引
CREATE INDEX idx_mv_cust ON mv_cust_order_summary(cust_id);QUERY REWRITE 自動將相容查詢導向 MVREFRESH FAST 依賴 MV LOG：CREATE MATERIALIZED VIEW LOG ON orders WITH ROWID, SEQUENCE (cust_id, total_amt, status) INCLUDING NEW VALUES;SELECT /*+ RESULT_CACHE */ c.name, COUNT(*) FROM customers c, orders o
WHERE c.cust_id = o.cust_id AND o.order_date > SYSDATE-30
GROUP BY c.name;適合：頻繁執行、資料變動較少、結果集較小的報表查詢。
DROP PARTITION 舊資料、ADD PARTITION 新分區、REBUILD 碎片分區索引DBMS_STATS.GATHER_TABLE_STATS(..., GRANULARITY=>'PARTITION')PSTART/PSTOP）、全域索引碎片度、MV 刷新耗時分區技術的精髓在於將維護複雜度轉移到 DBA 排程，換取查詢端的極致簡單與高效。設計時多問一句：「這個分區鍵能否支援 80% 以上的核心查詢自動裁剪？」
