Oracle 442 個應用場景之邏輯對象管理篇：表、視圖、索引、物化視圖、叢集、序列、同義詞完整實戰

Oracle 442 個應用場景之邏輯對象管理篇：表、視圖、索引、物化視圖、叢集、序列、同義詞完整實戰

本文整理「Oracle 442 個應用場景」系列的邏輯對象管理章節 (場景 126-177)，系統涵蓋 表、視圖、索引、物化視圖、叢集、哈希叢集、序列、同義詞 的建立、修改、查詢、刪除全生命週期操作，並輔以數據類型、約束、存儲參數核心知識，構建 DBA/開發人員的對象管理技能樹。

一、數據類型核心知識 (建表前必讀)

字串類型

• CHAR(n)：固定長度，空格填充，2000 位元組上限。適合固定代碼 (ISO 國碼、MD5)。
• VARCHAR2(n BYTE|CHAR)：可變長度，4000 位元組上限。**建議明確指定 CHAR 語義**。空字串視為 NULL。
• NCHAR / NVARCHAR2：國家字元集 (AL16UTF16) 存儲，長度以字元計。NVARCHAR2(10) 固定佔 20 位元組/字元，移植性最強。
• CLOB / NCLOB：大文本，(4GB-1)×Block Size。

數值類型

• NUMBER(p,s)：p 精度 1~38，s 刻度 -84~127。NUMBER(5,2) 整數 3 位、小數 2 位；NUMBER(5,-2) 萬位四捨五入。
• INTEGER：= NUMBER(38,0)，自動四捨五入。
• BINARY_FLOAT / BINARY_DOUBLE：IEEE 754 浮點數，科學計算用。

日期類型

• DATE：7 位元組，精度至秒，範圍公元前 4712 ~ 公元 9999。
• TIMESTAMP(n)：7/11 位元組，含小數秒 (最多 9 位)。
• TIMESTAMP WITH TIME ZONE / LOCAL TIME ZONE：帶時區資訊。
• INTERVAL YEAR TO MONTH / DAY TO SECOND：時間間隔。

二進制/LOB/ROWID

• RAW(n) / LONG RAW：二進制，2000/2GB。
• BLOB：二進制大對象。
• BFILE：外部檔案指標，唯讀。
• ROWID / UROWID：物理/邏輯行地址。

二、約束完整操作指南

約束狀態控制

-- 延遲驗證 (事務提交時檢查)
CREATE TABLE t (id INT PRIMARY KEY DEFERRABLE INITIALLY DEFERRED);
SET CONSTRAINT pk_id DEFERRED;  -- 會話級開啟
SET CONSTRAINT pk_id IMMEDIATE; -- 關閉

-- 啟用/停用
ALTER TABLE t ENABLE VALIDATE CONSTRAINT pk_id;   -- 驗證現有數據
ALTER TABLE t ENABLE NOVALIDATE CONSTRAINT pk_id; -- 僅約束未來數據
ALTER TABLE t DISABLE CONSTRAINT pk_id;           -- 停用

-- 刪除 (CASCADE 級聯刪除子表 FK)
ALTER TABLE t DROP CONSTRAINT pk_id CASCADE;

三、索引類型與使用原則

索引維護

ALTER INDEX idx_emp_name UNUSABLE;   -- 標記不可用
ALTER INDEX idx_emp_name REBUILD;    -- 重建 (可 ONLINE)
ALTER INDEX idx_emp_name RENAME TO idx_emp_name_v2;
DROP INDEX idx_emp_name_v2;

四、視圖與物化視圖

標準視圖

CREATE VIEW hrman.v_emp AS
SELECT e.emp_id, e.emp_name, d.dep_name
FROM hrman.employees e, hrman.departments d
WHERE e.dep_id = d.dep_id;

CREATE OR REPLACE VIEW hrman.v_emp AS
SELECT e.emp_id, e.emp_name, e.sex, e.wage, d.dep_name
FROM hrman.employees e, hrman.departments d
WHERE e.dep_id = d.dep_id;

DROP VIEW hrman.v_emp;

物化視圖 (快照/複製表)

CREATE MATERIALIZED VIEW hrman.mv_emp
REFRESH FORCE ON DEMAND
AS SELECT e.emp_id, e.emp_name, d.dep_name
FROM hrman.employees e, hrman.departments d
WHERE e.dep_id = d.dep_id;

-- 修改刷新模式
ALTER MATERIALIZED VIEW hrman.mv_emp REFRESH COMPLETE ON DEMAND;

-- 手動刷新
EXEC DBMS_MVIEW.REFRESH('HRMAN.MV_EMP', 'C');  -- C=Complete, F=Fast, ?=Force

DROP MATERIALIZED VIEW hrman.mv_emp;

五、叢集表

索引叢集 (共享塊存儲關聯表)

CREATE CLUSTER hrman.hr_cluster (dep_id NUMBER)
PCTUSED 80 PCTFREE 5 SIZE 500 TABLESPACE users
STORAGE (INITIAL 200K NEXT 300K MINEXTENTS 2 MAXEXTENTS UNLIMITED PCTINCREASE 33);

CREATE TABLE hrman.dept_info (dep_id NUMBER PRIMARY KEY, dep_name VARCHAR2(100))
CLUSTER hrman.hr_cluster(dep_id);

CREATE TABLE hrman.emp_info (emp_id NUMBER PRIMARY KEY, emp_name VARCHAR2(50), dep_id NUMBER)
CLUSTER hrman.hr_cluster(dep_id);

-- 必須建立叢集索引才能插入數據
CREATE INDEX hrman.idx_emp_dept ON CLUSTER hrman.hr_cluster TABLESPACE users;

哈希叢集 (無索引，哈希函數直接定位)

CREATE CLUSTER hrman.hash_cluster (id NUMBER) SIZE 512 SINGLE TABLE HASHKEYS 500;
CREATE TABLE hrman.area (area_id NUMBER PRIMARY KEY, area_name VARCHAR2(200))
CLUSTER hrman.hash_cluster(area_id);

六、序列

CREATE SEQUENCE hrman.emp_seq
MINVALUE 1 MAXVALUE 999999 START WITH 1 INCREMENT BY 1 NOCYCLE CACHE 20;

ALTER SEQUENCE hrman.emp_seq MAXVALUE 9999999;
DROP SEQUENCE hrman.emp_seq;

-- 使用
INSERT INTO hrman.users VALUES (hrman.user_seq.NEXTVAL, 'admin', 'pass');

七、同義詞

CREATE PUBLIC SYNONYM hr_employees FOR hrman.employees;
SELECT * FROM dba_synonyms WHERE table_name = 'EMPLOYEES';
SELECT emp_name, title FROM hr_employees;  -- 任意用戶可用
DROP PUBLIC SYNONYM hr_employees;

八、系統視圖速查 (對象元數據)

總結

邏輯對象管理是 DBA 與開發的日常核心功課。關鍵在於：選對數據類型 (VARCHAR2 CHAR 語義)、約束完整落地 (PK/FK/UK/CK/NOT NULL)、索引策略匹配查詢模式 (B*Tree/位圖/函數/反向)、視圖簡化複雜 JOIN、物化視圖加速報表、叢集優化關聯表存取、序列替代觸發器產生 ID、同義詞屏蔽 Schema 細節。將上述 DDL 模板化、參數化，納入版本控管與 Code Review 清單，即可實現對象管理的標準化與自動化。