Oracle 数据库设计原则

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

1. 概述

数据库设计原则保证系统合理[1]：

2. 范式

2.1 1NF

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

2.2 2NF

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

2.3 3NF

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

2.4 BCNF

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

2.5 反范式

- 仓库常用
- 性能
- 冗余

3. 表设计

3.1 命名

- 表名：单数，下划线，小写或大写
- 列名：清晰
- 索引：idx_xxx
- 约束：pk/uk/fk/ck_xxx
- 序列：seq_xxx

3.2 列

CREATE TABLE employees (
  id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR2(100) NOT NULL,
  email VARCHAR2(200) UNIQUE,
  dept_id NUMBER NOT NULL,
  salary NUMBER(10, 2) CHECK (salary > 0),
  hire_date DATE DEFAULT SYSDATE,
  created_at TIMESTAMP DEFAULT SYSTIMESTAMP,
  updated_at TIMESTAMP,
  
  CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES departments(id)
);

3.3 类型

- 字符串：VARCHAR2
- 数值：NUMBER(p,s)
- 日期：TIMESTAMP
- 大文本：CLOB
- 二进制：BLOB
- 短代码：CHAR
- 布尔（23c+）：BOOLEAN

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

4. 主键

4.1 设计

- 单列：id
- 复合：必要时
- 唯一
- 非空

4.2 自增

-- 12c+ IDENTITY
id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY

-- 序列
CREATE SEQUENCE seq_emp;
id NUMBER DEFAULT seq_emp.NEXTVAL PRIMARY KEY

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

5. 外键

5.1 设计

CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES departments(id)
CONSTRAINT fk_order_emp FOREIGN KEY (emp_id) REFERENCES employees(id) ON DELETE CASCADE
CONSTRAINT fk_emp_mgr FOREIGN KEY (mgr_id) REFERENCES employees(id) ON DELETE SET NULL

5.2 索引

CREATE INDEX idx_emp_dept ON employees(dept_id);

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

6. 索引

6.1 原则

- 高选择性列
- 查询常用
- 覆盖索引
- 复合合理

6.2 类型

- B-Tree：OLTP
- 位图：仓库
- 函数：函数查询
- 反向：热点
- 复合：多列

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

7. 分区

7.1 时机

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

7.2 策略

-- 时间
PARTITION BY RANGE (sale_date) ...
PARTITION BY RANGE (sale_date) INTERVAL (...) ...

-- 区域
PARTITION BY LIST (region) ...

-- 哈希
PARTITION BY HASH (customer_id) ...

-- 复合
PARTITION BY RANGE (sale_date) SUBPARTITION BY LIST (region) ...

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

8. 约束

8.1 完整性

- NOT NULL
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
- CHECK
- DEFAULT

8.2 命名

- PK_TABLE
- UK_TABLE_COL
- FK_CHILD_PARENT
- CK_TABLE_COL
- NN_TABLE_COL

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

9. 视图

-- 简化查询
CREATE VIEW emp_dept AS
SELECT e.id, e.name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.id;

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

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

10. 序列

-- 主键
CREATE SEQUENCE seq_emp START WITH 1 CACHE 20;

-- 12c+ IDENTITY（推荐）
id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY

11. 事务

11.1 隔离级别

- READ COMMITTED（默认）
- SERIALIZABLE
- READ ONLY

11.2 锁

- 行锁
- 表锁
- 死锁避免

详细见：Oracle 锁与闩锁诊断。

12. 安全

12.1 权限

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

12.2 审计

- 标准
- 细粒度
- FGA
- 统一审计

详细见：Oracle 审计详解。

13. 性能

13.1 设计

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

13.2 监控

- AWR
- ASH
- SQL 监控

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

14. 文档

14.1 注释

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

14.2 查看

SELECT comments FROM user_tab_comments WHERE table_name = 'EMPLOYEES';
SELECT comments FROM user_col_comments WHERE table_name = 'EMPLOYEES';

15. 命名规范

15.1 表

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

15.2 列

- 清晰
- 类型后缀（可选）
- id, name, code, date, time, amount

15.3 对象

- 视图：v_xxx
- 序列：seq_xxx
- 索引：idx_xxx
- 约束：pk/uk/fk/ck_xxx
- 过程：sp_xxx
- 函数：fn_xxx
- 包：pkg_xxx
- 触发器：trg_xxx

16. 应用场景

16.1 OLTP

- 范式
- B-Tree 索引
- 小分区
- 行锁

16.2 仓库

- 反范式
- 位图索引
- 大分区
- 物化视图
- 压缩

16.3 混合

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

17. 常见坑与排错

17.1 过度范式

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

17.2 索引过多

- DML 慢
- 空间
- 平衡

17.3 类型不当

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

17.4 主键设计

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

18. 最佳实践

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

19. 参考资料

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