DB / Oracle / SQL 开发

Oracle 数据库表设计

2026.09.06

Oracle 数据库表设计

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

1. 概述

数据库表设计是数据库基础[1]：

原则：

  • 范式
  • 性能
  • 可维护
  • 扩展

详细见：Oracle 表分区策略。

2. 范式

2.1 1NF

- 原子值
- 无重复组

2.2 2NF

- 满足 1NF
- 非主键列依赖完整主键

2.3 3NF

- 满足 2NF
- 无传递依赖

2.4 BCNF

- 3NF 严格版
- 每个决定因素都是候选键

3. 反范式

3.1 场景

- 数据仓库
- 性能优先
- 读多写少

3.2 方式

  • 冗余列
  • 汇总表
  • 物化视图

4. 主键设计

4.1 单列

CREATE TABLE employees (
  id NUMBER PRIMARY KEY,
  ...
);

4.2 复合

CREATE TABLE order_items (
  order_id NUMBER,
  item_id NUMBER,
  ...
  CONSTRAINT pk_oi PRIMARY KEY (order_id, item_id)
);

4.3 代理键

-- 序列
CREATE TABLE employees (
  id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  ...
);

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

5. 外键设计

5.1 基本

CREATE TABLE orders (
  id NUMBER PRIMARY KEY,
  emp_id NUMBER,
  CONSTRAINT fk_order_emp FOREIGN KEY (emp_id) REFERENCES employees(id)
);

5.2 ON DELETE

-- CASCADE
CONSTRAINT fk_... FOREIGN KEY ... REFERENCES ... ON DELETE CASCADE

-- SET NULL
CONSTRAINT fk_... FOREIGN KEY ... REFERENCES ... ON DELETE SET NULL

5.3 索引

-- FK 必须索引
CREATE INDEX idx_orders_emp ON orders(emp_id);

详细见：Oracle 约束管理。

6. 数据类型选择

6.1 字符

类型适用
VARCHAR2可变字符串
CHAR固定长度
CLOB大文本
NVARCHAR2国家字符集

6.2 数值

类型适用
NUMBER通用
NUMBER(p, s)精度
BINARY_FLOAT单精度
BINARY_DOUBLE双精度
BOOLEAN（23ai+）布尔

6.3 日期

类型适用
DATE日期 + 时间
TIMESTAMP纳秒
TIMESTAMP WITH TZ时区

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

7. 表组织

7.1 堆表（默认）

CREATE TABLE employees (...);

7.2 索引组织表

CREATE TABLE employees (
  id NUMBER PRIMARY KEY,
  name VARCHAR2(100)
) ORGANIZATION INDEX;

7.3 外部表

CREATE TABLE ext_emp (...) ORGANIZATION EXTERNAL (...);

详细见：Oracle 临时表与外部表。

7.4 集群表

CREATE CLUSTER emp_dept (dept_id NUMBER);
CREATE INDEX idx_emp_dept ON CLUSTER emp_dept;

CREATE TABLE employees (...) CLUSTER emp_dept (dept_id);
CREATE TABLE departments (...) CLUSTER emp_dept (dept_id);

8. 分区

8.1 Range

CREATE TABLE sales (...)
PARTITION BY RANGE (sale_date) (...);

8.2 List

CREATE TABLE customers (...)
PARTITION BY LIST (region) (...);

8.3 Hash

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

详细见：Oracle 分区表设计。

9. 约束

9.1 NOT NULL

CREATE TABLE t (id NUMBER NOT NULL, name VARCHAR2(100) NOT NULL);

9.2 UNIQUE

CREATE TABLE t (email VARCHAR2(100) UNIQUE);

9.3 CHECK

CREATE TABLE t (salary NUMBER CHECK (salary > 0));

9.4 DEFAULT

CREATE TABLE t (
  created_at TIMESTAMP DEFAULT SYSTIMESTAMP,
  status VARCHAR2(20) DEFAULT 'ACTIVE'
);

10. 索引设计

10.1 B-Tree

CREATE INDEX idx_emp_name ON employees(name);
CREATE INDEX idx_emp_dept_sal ON employees(dept_id, salary);

10.2 唯一

CREATE UNIQUE INDEX idx_emp_email ON employees(email);

10.3 函数

CREATE INDEX idx_emp_upper ON employees(UPPER(name));

10.4 位图

CREATE BITMAP INDEX idx_emp_gender ON employees(gender);

详细见：Oracle 索引类型与应用。

11. 压缩

11.1 OLTP

CREATE TABLE employees (...) COMPRESS FOR OLTP;

11.2 仓库

CREATE TABLE sales (...) COMPRESS FOR QUERY LOW;

11.3 归档

CREATE TABLE sales_archive (...) COMPRESS FOR ARCHIVE HIGH;

详细见：Oracle 表压缩技术。

12. 存储参数

12.1 PCTFREE / PCTUSED

CREATE TABLE t (...) PCTFREE 20 PCTUSED 40;

12.2 INITRANS

CREATE TABLE t (...) INITRANS 10 MAXTRANS 255;

12.3 STORAGE

CREATE TABLE t (...) 
  STORAGE (
    INITIAL 1M
    NEXT 1M
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
    PCTINCREASE 0
  );

详细见：Oracle 数据块结构。

13. 临时表

13.1 事务级

CREATE GLOBAL TEMPORARY TABLE temp_t (...) ON COMMIT DELETE ROWS;

13.2 会话级

CREATE GLOBAL TEMPORARY TABLE temp_t (...) ON COMMIT PRESERVE ROWS;

详细见：Oracle 临时表与外部表。

14. 命名规范

14.1 表

- 小写复数：employees, departments
- 业务前缀：hr_employees, fin_orders
- 历史后缀：employees_history

14.2 列

- 小写：id, name, created_at
- 布尔：is_active, has_xxx
- 时间：xxx_at, xxx_date

14.3 索引

- idx_表_列：idx_emp_name
- uk_表_列：uk_emp_email（唯一）
- pk_表：pk_emp（主键）

15. 设计原则

15.1 范式 vs 反范式

- OLTP：范式
- 仓库：反范式
- 平衡

15.2 主键

- 代理键：推荐
- 业务键：UNIQUE

15.3 外键

- 关系完整
- 索引
- ON DELETE 谨慎

15.4 类型

- 合适
- 避免过大
- 避免隐式转换

16. 性能考虑

16.1 大表

- 分区
- 压缩
- 索引

16.2 查询

- 索引覆盖
- 避免 SELECT *
- 分页

16.3 写入

- 批量
- APPEND
- 减少索引

17. 常见坑与排错

17.1 过度范式

- 多 JOIN
- 性能差
- 适度反范式

17.2 主键不当

- 业务键变化
- 长度大
- 代理键

17.3 类型不当

- NUMBER 存字符串
- DATE 存时间戳
- VARCHAR2 过大

18. 最佳实践

  1. 范式基础：3NF
  2. 反范式适度：性能
  3. 代理键：稳定
  4. FK + 索引：完整 + 性能
  5. 合适类型：精准
  6. 分区大表：管理
  7. 压缩历史：空间
  8. 命名规范：维护
  9. 约束完整：质量
  10. 文档化：设计

19. 参考资料

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