Oracle 唯一性約束、主鍵、Sequence、索引與同義詞完整指南

Sequence (序列)

連續數值產生器，常用於人造鍵。語法：

CREATE SEQUENCE [SCHEMA.] SEQUENCE_NAME
[START WITH N]
[INCREMENT BY N]
[{MAXVALUE N|NOMAXVALUE}]
[{MINVALUE N|NOMINVALUE}]
[{CYCLE|NOCYCLE}]
[{CACHE N|NOCACHE}];
-- 刪除
DROP SEQUENCE SEQUENCE_NAME;

參數說明：START WITH 起始值 (預設 1)；INCREMENT BY 正數遞增、負數遞減；MAXVALUE/MINVALUE 界限；CYCLE 循環；CACHE 預載記憶體數量 (預設 20)。

索引

提升查詢效能的資料結構。Oracle 提供：

• B-Tree (預設)
• Hash
• Bitmap
• Function-based
• Domain

實務以 B-Tree 與 Bitmap 為主。

B-Tree 索引

• 適用高基數欄位 (值分佈廣)
• 由分支塊與葉塊組成，葉塊存關鍵字與 ROWID
• 篩選比例超過 10% 效能遜於全表掃描

Bitmap 索引

• 適用決策支援/靜態資料，低~中基數欄位
• 不支援列級鎖定
• 多 Bitmap 索引可結合 AND/OR
• 壓縮儲存，空間遠小於 B-Tree

同義詞

資料物件別名。PUBLIC 對所有用戶可見；PRIVATE 僅擁有者/受授者可見。可指向表、視圖、序列、程序、函數、套件，亦可跨 DB Link 指向遠端物件。

CREATE [PUBLIC] SYNONYM SYNONYM_NAME FOR [SCHEMA.]OBJECT[@DB_LINK];
-- 範例
CREATE PUBLIC SYNONYM POLICIES FOR POLADM.POLICIES@PROD;
CREATE SYNONYM PLAN_TABLE FOR SYSTEM.PLAN_TABLE;

唯一性約束

保護單欄或多欄組合值不重複。由 B-Tree 索引強制執行。

-- 單欄
COL_NAME DATA_TYPE CONSTRAINT CONS_NAME UNIQUE
-- 多欄 (表層級)
CONSTRAINT CONS_NAME UNIQUE (COL1, COL2)
  USING INDEX TABLESPACE IDX_TS STORAGE (INITIAL 1M NEXT 10M PCTINCREASE 0)

可停用/啟用/刪除：

ALTER TABLE T DISABLE CONSTRAINT CONS_NAME;
ALTER TABLE T ENABLE CONSTRAINT CONS_NAME;
ALTER TABLE T DROP CONSTRAINT CONS_NAME;

注意：被外鍵參考的唯一鍵不可直接刪除，需先處理外鍵。

避免頻繁停用導致索引遺失效能：先建非唯一索引再加約束。

主鍵約束

表僅一主鍵，可含多欄，隱含 NOT NULL + UNIQUE，同由 B-Tree 索引強制。

-- 單欄
COL_NAME DATA_TYPE CONSTRAINT PK_NAME PRIMARY KEY
-- 多欄 (表層級)
CONSTRAINT PK_NAME PRIMARY KEY (COL1, COL2)
  USING INDEX TABLESPACE IDX_TS STORAGE (...);