Oracle 虚拟列（Virtual Column）

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

---

1. 概述

虚拟列 是基于表达式的列[1]：

特点：

• 不存储数据
• 查询时计算
• 可索引
• 可分区

---

2. 创建

2.1 基本语法

CREATE TABLE employees (
  id NUMBER,
  salary NUMBER,
  bonus NUMBER,
  total_salary AS (salary + NVL(bonus, 0))
);

2.2 显式类型

CREATE TABLE employees (
  id NUMBER,
  salary NUMBER,
  bonus NUMBER,
  total_salary NUMBER GENERATED ALWAYS AS (salary + NVL(bonus, 0)) VIRTUAL
);

---

3. 使用

3.1 查询

SELECT id, salary, bonus, total_salary FROM employees;
-- total_salary 自动计算

3.2 WHERE

SELECT * FROM employees WHERE total_salary > 8000;

3.3 索引

CREATE INDEX idx_emp_total ON employees(total_salary);

3.4 分区

CREATE TABLE sales (
  id NUMBER,
  sale_date DATE,
  sale_year AS (EXTRACT(YEAR FROM sale_date)),
  amount NUMBER
)
PARTITION BY RANGE (sale_year) (
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION p2026 VALUES LESS THAN (2027)
);

---

4. 限制

• 不能 INSERT/UPDATE 虚拟列
• 表达式必须确定性
• 不能引用其他虚拟列（11g）
• 数据类型限制

---

5. 添加虚拟列

ALTER TABLE employees ADD (
  annual_salary AS (salary * 12)
);

---

6. 修改/删除

-- 修改
ALTER TABLE employees MODIFY (
  total_salary AS (salary + NVL(bonus, 0) + NVL(commission, 0))
);

-- 删除
ALTER TABLE employees DROP COLUMN total_salary;

---

7. 查看

SELECT column_name, data_type, data_default, virtual_column
FROM user_tab_cols
WHERE table_name = 'EMPLOYEES';

-- VIRTUAL_COLUMN = YES

---

8. 应用场景

8.1 计算列

-- 年薪
annual_salary AS (salary * 12)

-- BMI
bmi AS (weight / (height/100 * height/100))

8.2 派生数据

-- 全名
full_name AS (first_name || ' ' || last_name)

-- 年龄
age AS (FLOOR(MONTHS_BETWEEN(SYSDATE, birth_date) / 12))

8.3 分区键

-- 按年分区
sale_year AS (EXTRACT(YEAR FROM sale_date))

---

9. 优势

• 节省存储
• 自动计算
• 数据一致
• 可索引

---

10. 常见坑与排错

10.1 ORA-54013: 不允许 INSERT

-- 虚拟列不能插入
INSERT INTO employees (id, salary, total_salary) VALUES (1, 5000, 5000);  -- 错误
INSERT INTO employees (id, salary) VALUES (1, 5000);  -- 正确

10.2 ORA-54033: 不能引用其他虚拟列

-- 11g 不允许
total AS (salary + bonus)
extra AS (total + 100)  -- 错误

-- 12c+ 允许

10.3 性能

-- 虚拟列每次查询计算
-- 1. 加索引提升
-- 2. 频繁使用考虑存储列

---

11. 最佳实践

1. 派生数据用虚拟列：一致性
2. 频繁查询加索引：性能
3. 分区键用虚拟列：简化
4. 避免复杂表达式：性能
5. 存储列考虑物化：高频
6. 测试性能：验证

---

12. 参考资料

[1] Oracle Database SQL Language Reference 19c, “Virtual Columns” https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-TABLE.html