表對象全生命周期管理
創建表語法規範
CREATE TABLE employees (
emp_id NUMBER(6) NOT NULL,
first_name VARCHAR2(20) NOT NULL,
last_name VARCHAR2(25) NOT NULL,
email VARCHAR2(25) NOT NULL UNIQUE,
hire_date DATE DEFAULT SYSDATE NOT NULL,
salary NUMBER(8,2) CHECK (salary > 0),
dept_id NUMBER(4),
CONSTRAINT pk_employees PRIMARY KEY (emp_id),
CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
) TABLESPACE users_data
PCTFREE 10 PCTUSED 40;
利用子查詢創建表 (CTAS)
-- 結構+數據複製
CREATE TABLE emp_archive AS
SELECT * FROM employees WHERE hire_date < DATE '2020-01-01';
-- 僅複製結構
CREATE TABLE emp_staging AS
SELECT * FROM employees WHERE 1=0;
修改表結構 (ALTER TABLE)
| 操作 | 語法範例 | 注意事項 |
|---|
| 添加列 | ALTER TABLE emp ADD (phone VARCHAR2(20) DEFAULT 'N/A'); | 大表添加非空列需指定 DEFAULT 值 |
| 修改列 | ALTER TABLE emp MODIFY (salary NUMBER(10,2) DEFAULT 0); | 數據類型收窄需確保現有數據兼容 |
| 刪除列 | ALTER TABLE emp DROP COLUMN phone; | 12c+ 支持 DROP UNUSED COLUMNS 釋放空間 |
| 重命名列 | ALTER TABLE emp RENAME COLUMN phone TO mobile; | 12c+ 新特性 |
| 設置未用 | ALTER TABLE emp SET UNUSED COLUMN old_col; | 標記為未用，後續批量物理刪除 |
刪除與清空表
-- DROP：結構+數據+索引+約束+權限 全刪除，不可回滾
DROP TABLE emp PURGE; -- PURGE 直接釋放空間，不進回收站
-- TRUNCATE：保留結構，高速清空數據，不產生 UNDO，不可回滾
TRUNCATE TABLE emp REUSE STORAGE; -- 保留高水位標記
-- DELETE：產生 UNDO，可回滾，觸發器生效
DELETE FROM emp WHERE dept_id = 10;
表註釋
COMMENT ON TABLE employees IS '核心員工主檔，含薪資敏感字段';
COMMENT ON COLUMN employees.salary IS '月薪，單位：元，加密存儲';
約束管理體系
約束類型與創建
| 類型 | 關鍵字 | 列級定義 | 表級定義 |
|---|
| 主鍵 | PRIMARY KEY | col NUMBER PRIMARY KEY | CONSTRAINT pk_tbl PRIMARY KEY (col1, col2) |
| 外鍵 | FOREIGN KEY | col NUMBER REFERENCES parent(pk) | CONSTRAINT fk_tbl FOREIGN KEY (col) REFERENCES parent(pk) |
| 唯一 | UNIQUE | col VARCHAR2(20) UNIQUE | CONSTRAINT uk_tbl UNIQUE (col1, col2) |
| 檢查 | CHECK | col NUMBER CHECK (col > 0) | CONSTRAINT chk_tbl CHECK (col1 + col2 < 100) |
| 非空 | NOT NULL | col DATE NOT NULL | （僅列級） |
約束狀態控制
-- 啟用/禁用
ALTER TABLE emp ENABLE CONSTRAINT pk_emp;
ALTER TABLE emp DISABLE CONSTRAINT fk_emp_dept;
-- 驗證/不驗證現有數據 (ENABLE VALIDATE / ENABLE NOVALIDATE)
ALTER TABLE emp ENABLE NOVALIDATE CONSTRAINT uk_email; -- 新數據驗證，舊數據不檢查
-- 級聯操作
ALTER TABLE emp DISABLE PRIMARY KEY CASCADE; -- 同時禁用引用此主鍵的外鍵
查詢約束信息
SELECT constraint_name, constraint_type, table_name, status, validated
FROM user_constraints
WHERE table_name = 'EMPLOYEES';
SELECT constraint_name, column_name, position
FROM user_cons_columns
WHERE table_name = 'EMPLOYEES';
視圖管理
創建視圖語法全集
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view_name [(alias_list)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint_name]]
[WITH READ ONLY [CONSTRAINT constraint_name]];
視圖類型對比
| 類型 | 特性 | 可更新性 | 用途 |
|---|
| 簡單視圖 | 單表、無聚合/分組/去重 | 可更新 | 安全控制、列過濾 |
| 複雜視圖 | 多表 JOIN、聚合、集合運算 | 通常不可更新 | 報表、數據整合 |
| WITH CHECK OPTION | 限制 DML 滿足視圖 WHERE 條件 | 受限更新 | 業務規則強制 |
| WITH READ ONLY | 完全禁止 DML | 只讀 | 報表保護、歷史歸檔 |
視圖更新限制規則
- 不包含：集合操作符、DISTINCT、聚合/分析函數、GROUP BY、CONNECT BY、子查詢、JOIN（關鍵保留表除外）
- 關鍵保留表：視圖基於主鍵/唯一鍵 JOIN，且只更新單表列時可更新
- 不可更新視圖可通過
INSTEAD OF TRIGGER 實現邏輯更新
視圖管理操作
-- 重定義
CREATE OR REPLACE VIEW v_emp_dept AS
SELECT e.emp_id, e.name, d.dept_name
FROM employees e JOIN departments d ON e.dept_id = d.dept_id;
-- 刪除
DROP VIEW v_emp_dept;
-- 內聯視圖 (查詢中內嵌)
SELECT * FROM (SELECT dept_id, AVG(salary) avg_sal FROM employees GROUP BY dept_id) v
WHERE avg_sal > 5000;
序列對象
創建序列完整參數
CREATE SEQUENCE seq_order_id
INCREMENT BY 1
START WITH 10000
MAXVALUE 9999999999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;
序列參數決策指南
| 參數 | 推薦設置 | 理由 |
|---|
| CACHE | 20-100 (高併發調大) | 減少字典鎖爭用，實例重啟會丟失緩存值造成間隙 |
| CYCLE | NOCYCLE | 防止業務邏輯錯誤循環，超限報錯更安全 |
| ORDER | NOORDER (RAC 必須 ORDER) | 全局有序需 ORDER，單實例 NOORDER 性能更好 |
序列使用模式
-- 獲取下一值
SELECT seq_order_id.NEXTVAL FROM dual;
-- 獲取當前值 (需先調用 NEXTVAL)
SELECT seq_order_id.CURRVAL FROM dual;
-- 插入語句中使用
INSERT INTO orders (order_id, ...) VALUES (seq_order_id.NEXTVAL, ...);
-- 修改序列
ALTER SEQUENCE seq_order_id INCREMENT BY 10 MAXVALUE 9999999999;
索引策略與管理
索引類型選擇矩陣
| 類型 | 適用場景 | 創建語法 |
|---|
| B-Tree (標準) | 高基數、OLTP 精確查找/範圍掃描 | CREATE INDEX idx_emp_name ON emp(last_name); |
| Bitmap | 低基數、DSS 多條件組合、星型模型 | CREATE BITMAP INDEX bmx_emp_dept ON emp(dept_id); |
| Function-Based | 函數/表達式查詢 | CREATE INDEX idx_emp_upper ON emp(UPPER(last_name)); |
| Composite | 多列組合過濾/排序 | CREATE INDEX idx_emp_dept_hire ON emp(dept_id, hire_date); |
| Partitioned | 大表分區裁剪、維護 | CREATE INDEX ... LOCAL/GLOBAL PARTITION ... |
索引創建原則 (核心)
- WHERE 條件高頻列、JOIN 關聯列、高選擇性列
- 主鍵/唯一約束自動創建索引
- 避免：極低選擇性列 (無直方圖時)、頻繁更新列、函數列 (除非 FBI)
- 外鍵列建索引防止父表 DML 鎖升級
- 復合索引列序：
高頻率 = 高選擇性 > 排序順序
索引監控與維護
-- 監控索引使用情況 (11g+)
ALTER INDEX idx_emp_name MONITORING USAGE;
SELECT * FROM v$object_usage WHERE index_name = 'IDX_EMP_NAME';
-- 重建索引 (消除碎片、恢復性能)
ALTER INDEX idx_emp_name REBUILD ONLINE;
-- 刪除未用索引
DROP INDEX idx_unused;
同義詞管理
類型與創建
| 類型 | 擁有者 | 可見性 | 創建語法 |
|---|
| PUBLIC | PUBLIC 組 | 所有用戶 | CREATE PUBLIC SYNONYM emp FOR hr.employees; |
| PRIVATE | 創建用戶 | 創建者+授權用戶 | CREATE SYNONYM emp FOR hr.employees; |
同義詞核心價值
- 位置透明性：屏蔽 Schema 名、DB Link、遠程對象複雜性
- 簡化 SQL：
SELECT * FROM emp@dblink → SELECT * FROM emp_syn
- 平滑遷移：表重命名/移動 Schema 時，只需修改同義詞定義
- 安全隔離：授予同義詞權限而非底層表權限
同義詞操作
-- 創建遠程同義詞 (需先創建 DB Link)
CREATE SYNONYM remote_emp FOR employees@prod_dblink;
-- 授權同義詞
GRANT SELECT ON emp_syn TO app_user;
-- 刪除
DROP PUBLIC SYNONYM emp;
DROP SYNONYM emp_syn;
-- 查看所有
SELECT * FROM dba_synonyms WHERE table_owner = 'HR';
綜合實戰：標準化表創建腳本模板
-- 1. 表空間預留
CREATE TABLESPACE biz_data DATAFILE '+DATA' SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE 100G;
-- 2. 表創建 (含註釋、約束、索引)
CREATE TABLE customer (
cust_id NUMBER(12) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
cust_code VARCHAR2(20) NOT NULL UNIQUE,
cust_name VARCHAR2(100) NOT NULL,
id_type VARCHAR2(2) CHECK (id_type IN ('01','02','99')),
id_number VARCHAR2(30),
status VARCHAR2(1) DEFAULT 'A' CHECK (status IN ('A','I','D')),
created_by VARCHAR2(30) DEFAULT USER,
created_dt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_by VARCHAR2(30),
updated_dt TIMESTAMP
) TABLESPACE biz_data;
COMMENT ON TABLE customer IS '客戶主檔，核心業務實體';
COMMENT ON COLUMN customer.id_number IS '證件號碼，脫敏存儲';
-- 3. 業務索引
CREATE INDEX idx_cust_name ON customer(cust_name) TABLESPACE biz_idx;
CREATE INDEX idx_cust_status ON customer(status) TABLESPACE biz_idx;
-- 4. 同義詞暴露給應用層
CREATE SYNONYM app_customer FOR customer;
GRANT SELECT, INSERT, UPDATE ON app_customer TO app_role;
