Oracle 分區表與本地索引建管理實戰：從建立、查詢優化到分區維護完整範例

前言

Oracle 分區技術（企業版選購元件）能在特定條件下大幅提升查詢效能。本文透過完整實例，示範範圍分區表建立、本地索引跨表空間配置、資料載入、效能對比（分區 vs 非分區）、新增分區與索引自動維護全流程。

1. 建立範圍分區表

CREATE TABLE dbobjs (
  OBJECT_ID NUMBER NOT NULL,
  OBJECT_NAME VARCHAR2(128),
  CREATED DATE NOT NULL
)
PARTITION BY RANGE (CREATED) (
  PARTITION dbobjs_06 VALUES LESS THAN (TO_DATE('01/01/2007','DD/MM/YYYY')),
  PARTITION dbobjs_07 VALUES LESS THAN (TO_DATE('01/01/2008','DD/MM/YYYY'))
);

驗證分區段資訊

SELECT SEGMENT_NAME, PARTITION_NAME, TABLESPACE_NAME
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = 'DBOBJS';

2. 建立本地索引（指定各分區表空間）

CREATE INDEX dbobjs_idx ON dbobjs (CREATED) LOCAL
(
  PARTITION dbobjs_06 TABLESPACE USERS,
  PARTITION dbobjs_07 TABLESPACE USERS
);

亦可簡寫為統一表空間子句：

CREATE INDEX dbobjs_idx ON dbobjs (CREATED) LOCAL
(
  PARTITION dbobjs_06 TABLESPACE USERS,
  PARTITION dbobjs_07 TABLESPACE USERS
) TABLESPACE USERS;

驗證索引分區

3. 載入測試資料與分區裁剪驗證

INSERT INTO dbobjs SELECT OBJECT_ID, OBJECT_NAME, CREATED FROM DBA_OBJECTS;
COMMIT;

SELECT COUNT(*) FROM DBOBJS PARTITION (DBOBJS_06);  -- 6154 筆
SELECT COUNT(*) FROM DBOBJS PARTITION (DBOBJS_07);  -- 73 筆

4. 查詢效能對比：分區表 vs 非分區表

分區表查詢計畫 (AUTOTRACE)

SELECT COUNT(DISTINCT(OBJECT_NAME))
FROM DBOBJS
WHERE CREATED < TO_DATE('01/01/2007','DD/MM/YYYY');

• 執行計畫顯示：PARTITION RANGE (ALL) → INDEX (RANGE SCAN) OF 'DBOBJS_IDX'
• Consistent Gets：101

非分區表查詢計畫

CREATE TABLE dbobjs2 AS SELECT * FROM DBA_OBJECTS;
CREATE INDEX dbobjs_idx2 ON dbobjs2 (CREATED);

SELECT COUNT(DISTINCT(OBJECT_NAME))
FROM DBOBJS2
WHERE CREATED < TO_DATE('01/01/2007','DD/MM/YYYY');

• 執行計畫：TABLE ACCESS (BY INDEX ROWID) → INDEX (RANGE SCAN)
• Consistent Gets：2,670（約 26 倍）

效能差異結論

分區裁剪使掃描範圍大幅縮減，Consistent Gets 從 2,670 降至 101，效能提升顯著。隨資料量增長，優勢更明顯。

5. 新增分區與本地索引自動維護

ALTER TABLE DBOBJS
ADD PARTITION DBOBJS_08 VALUES LESS THAN (TO_DATE('01/01/2009','DD/MM/YYYY'));

執行後查詢 DBA_SEGMENTS 可見：

• 表新增分區 DBOBJS_08（預設表空間 EYGLE）
• 本地索引 自動同步新增 分區 DBOBJS_08（預設表空間 USERS）