Oracle 同義詞與索引實戰筆記：私有/公共同義詞、B樹/反向/位圖索引

Oracle 同義詞與索引實戰筆記：私有/公共同義詞、B樹/反向/位圖索引

本文整理同義詞創建權限、作用域、屏蔽對象所有者機制，以及三類索引 (B-Tree、反向鍵、位圖) 的適用場景、創建語法與存儲原理，附帶實戰代碼模板。

一、 同義詞

1. 權限準備

-- SYS/SYSTEM 執行
GRANT CREATE SYNONYM TO test_user;
GRANT CREATE PUBLIC SYNONYM TO test_user; -- 公共同義詞需額外權限

2. 私有同義詞 — 僅擁有者可見

CREATE PUBLIC SYNONYM public_sy_test1 FOR gcs95.test1;

• 不屬於任何 Schema，PUBLIC 為特殊命名空間。
• 所有擁有底層對象權限的用戶均可直接 SELECT * FROM public_sy_test1。
• 核心優勢：屏蔽對象所有者，應用代碼無硬編碼 Schema 名，便於遷移/多租戶。

4. 刪除同義詞

DROP SYNONYM sy_test1;
DROP PUBLIC SYNONYM public_sy_test1;

二、 索引類型深度解析

三、 實戰代碼模板

-- 1. 建表示例
CREATE TABLE Depositor (
  actid      NUMBER PRIMARY KEY,
  cardid     NUMBER,
  lastname   VARCHAR2(10),
  firstname  VARCHAR2(10),
  balance    NUMBER(10,2)
);

-- 2. B-Tree 索引 (cardid 高基數，範圍查詢)
CREATE INDEX cd_idx ON Depositor(cardid);

-- 3. 反向鍵索引 (假設 cardid 為序列生成，INSERT 熱點)
CREATE INDEX cd_idx_rev ON Depositor(cardid) REVERSE;

-- 4. 位圖索引 (假設新增 status 列，值集合小)
ALTER TABLE Depositor ADD status VARCHAR2(10);
CREATE BITMAP INDEX status_bmx ON Depositor(status);

-- 5. 查詢驗證 (cardid 範圍)
SELECT * FROM Depositor WHERE cardid BETWEEN 1 AND 100000;

四、 表分區 (補充)

原文提及表分區但未展開。核心點：

• 分區將大表拆分為多個段，配合 本地索引 (Local Index) 實現分區級維護自動化。
• 範圍分區 (時間)、列表分區 (地區)、雜湊分區 (均勻分佈)、組合分區 (Range-Hash/List)。
• 分區剪枝 是查詢加速核心，SQL 需帶分區鍵過濾條件。

五、 避坑提醒

1. 同義詞遞歸：同義詞指向同義詞，最多遞歸 16 層，超過報 ORA-01775。
2. 公共同義詞命名衝突：不同 Schema 同名表創建公共同義詞會報錯，需加前綴區分。
3. 位圖索引鎖升級：單行 DML 可能鎖定大量位圖片段，導致嚴重爭用，嚴禁用於高併發 OLTP 核心表。
4. 反向鍵索引失效範圍查詢：WHERE id > 1000 無法使用反向索引，需權衡 INSERT 性能與查詢靈活性。