DB / Oracle / SQL 开发

Oracle 数据库模式设计

2026.09.06

Oracle 数据库模式设计

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

数据库模式设计是系统基础[1]：

详细见：Oracle 数据库设计原则详解。

2. ER 建模

2.1 实体

- 强实体：独立存在
- 弱实体：依赖
- 复合属性
- 多值属性

2.2 关系

- 1:1
- 1:N
- M:N（中间表）

2.3 示例

实体：
- 员工（Employee）
- 部门（Department）
- 项目（Project）

关系：
- 员工 N:1 部门
- 员工 M:N 项目（中间表：员工项目）

3. 范式

3.1 1NF

- 原子性
- 不可分割
- 无重复组

3.2 2NF

- 1NF + 非主键完全依赖主键
- 消除部分依赖

3.3 3NF

- 2NF + 非主键不传递依赖
- 消除传递依赖

3.4 BCNF

- 3NF + 每个决定因素是候选键

3.5 反范式

- 仓库常用
- 性能
- 冗余

4. 表设计

4.1 主键

-- 代理键（推荐）
CREATE TABLE employees (
  id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  ...
);

-- 业务键
CREATE TABLE departments (
  code VARCHAR2(10) PRIMARY KEY,
  ...
);

详细见：Oracle 序列与自增列详解。

4.2 外键

CREATE TABLE employees (
  id NUMBER PRIMARY KEY,
  dept_id NUMBER,
  CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES departments(id)
);

4.3 约束

CREATE TABLE employees (
  id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email VARCHAR2(200) UNIQUE NOT NULL,
  salary NUMBER(10, 2) CHECK (salary > 0),
  hire_date DATE DEFAULT SYSDATE NOT NULL,
  status VARCHAR2(20) DEFAULT 'ACTIVE' CHECK (status IN ('ACTIVE', 'INACTIVE'))
);

详细见：Oracle 约束管理详解。

5. 索引

5.1 主键 / 唯一

- 自动创建
- B-Tree

5.2 外键

CREATE INDEX idx_emp_dept ON employees(dept_id);

5.3 查询常用

-- 高选择性
CREATE INDEX idx_emp_email ON employees(email);

-- 复合
CREATE INDEX idx_emp_dept_sal ON employees(dept_id, salary);

-- 函数
CREATE INDEX idx_emp_upper_name ON employees(UPPER(name));

详细见：Oracle 索引优化策略详解。

6. 分区

6.1 时机

- 大表（> 10GB）
- 历史数据
- 易管理
- 性能

6.2 策略

-- 时间
CREATE TABLE sales (...) 
PARTITION BY RANGE (sale_date) (...);

-- Interval 自动
CREATE TABLE sales (...) 
PARTITION BY RANGE (sale_date) 
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) (...);

-- 区域
CREATE TABLE customers (...) 
PARTITION BY LIST (region) (...);

-- 哈希
CREATE TABLE orders (...) 
PARTITION BY HASH (customer_id) PARTITIONS 8;

详细见：Oracle 表分区策略详解。

7. 视图

7.1 简化

CREATE VIEW emp_dept AS
SELECT e.id, e.name, e.salary, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.id;

7.2 安全

CREATE VIEW emp_public AS
SELECT id, name FROM employees
WITH READ ONLY;

7.3 物化视图

CREATE MATERIALIZED VIEW mv_dept_avg
  REFRESH COMPLETE ON DEMAND
  ENABLE QUERY REWRITE
  AS SELECT dept_id, AVG(salary) FROM employees GROUP BY dept_id;

详细见：Oracle 视图与物化视图详解。

8. 序列

-- 序列
CREATE SEQUENCE seq_emp START WITH 1 CACHE 20;

-- 12c+ IDENTITY（推荐）
CREATE TABLE employees (
  id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

9. 触发器

-- 审计
CREATE OR REPLACE TRIGGER trg_audit_emp
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO emp_audit (...) VALUES (...);
END;
/

详细见：Oracle PL/SQL 触发器详解。

10. PL/SQL 对象

10.1 包

CREATE OR REPLACE PACKAGE emp_pkg AS
  PROCEDURE hire_emp(...);
  FUNCTION get_emp_count(...) RETURN NUMBER;
END;
/

详细见：Oracle PL/SQL 包设计与最佳实践。

10.2 过程 / 函数

CREATE OR REPLACE PROCEDURE hire_emp(...) IS ... END;
CREATE OR REPLACE FUNCTION get_count(...) RETURN NUMBER IS ... END;

详细见：Oracle 存储过程与函数详解。

11. 数据类型

11.1 选择

- VARCHAR2：字符串
- NUMBER(p,s)：数值
- DATE / TIMESTAMP：日期
- CLOB：大文本
- BLOB：大二进制
- 23c+ BOOLEAN：布尔
- 23c+ VECTOR：AI

详细见：Oracle 数据类型详解。

11.2 示例

CREATE TABLE employees (
  id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR2(100) NOT NULL,
  email VARCHAR2(200) UNIQUE,
  phone VARCHAR2(20),
  salary NUMBER(10, 2) CHECK (salary > 0),
  hire_date TIMESTAMP DEFAULT SYSTIMESTAMP,
  status VARCHAR2(20) DEFAULT 'ACTIVE',
  bio CLOB,
  photo BLOB,
  is_active BOOLEAN DEFAULT TRUE,  -- 23c+
  dept_id NUMBER NOT NULL
);

12. 命名规范

12.1 表

- 单数
- 业务前缀
- 下划线
- < 30 字符
- 示例：emp_employee, ord_order

12.2 列

- id：主键
- xxx_id：外键
- name / title：名称
- xxx_date：日期
- xxx_amount：金额
- status：状态

12.3 对象

类型前缀
表-
视图v_
序列seq_
索引idx_
约束pk_/uk_/fk_/ck_
过程sp_
函数fn_
包pkg_
触发器trg_

详细见：Oracle SQL 开发规范详解。

13. 应用场景

13.1 OLTP

- 范式（3NF）
- B-Tree 索引
- 小分区
- 行锁
- 短事务

13.2 仓库

- 反范式（星型/雪花）
- 位图索引
- 大分区
- 物化视图
- 压缩

13.3 混合

- 模式分离
- 分区
- 物化视图
- HTAP

详细见：Oracle 数据仓库 ETL 详解。

14. 性能

14.1 设计

- 范式 + 反范式
- 索引合理
- 分区
- 数据类型

14.2 监控

- AWR
- ASH
- SQL 监控

详细见：Oracle SQL 调优最佳实践。

15. 安全

15.1 权限

- 最小权限
- 角色
- 视图
- VPD

15.2 审计

- 标准
- FGA
- 统一审计

详细见：Oracle 同义词与权限详解。

16. 文档

16.1 注释

COMMENT ON TABLE employees IS '员工信息表';
COMMENT ON COLUMN employees.salary IS '员工月薪（元）';

16.2 ER 图

- 工具
- 维护
- 同步

17. 常见坑与排错

17.1 过度范式

- 复杂 JOIN
- 性能
- 适当反范式

17.2 索引过多

- DML 慢
- 空间
- 平衡

17.3 主键设计

- 业务键 vs 代理键
- 代理键推荐

17.4 类型不当

- LONG 避免改 CLOB
- DATE 升 TIMESTAMP
- 选择合适

18. 最佳实践

  1. 3NF：基础
  2. 反范式：仓库
  3. 代理主键：简单
  4. IDENTITY：12c+
  5. TIMESTAMP：现代
  6. VARCHAR2：通用
  7. 约束：完整
  8. 索引合理：覆盖
  9. 分区：大表
  10. 文档化：注释

19. 参考资料

[1] Oracle Database Administrator’s Guide 19c, “Schema Design” https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/