Oracle 12c 新 SQL 特性

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

1. 概述

Oracle 12c 引入许多 SQL 新特性[1]：

特性：

FETCH 分页
IDENTITY
DEFAULT SEQUENCE
Temporal Validity
In-Database Archiving
Pattern Matching
Lateral Join
Cross Apply / Outer Apply

2. FETCH 分页

2.1 基本

SELECT * FROM employees ORDER BY id
OFFSET 100 ROWS FETCH NEXT 10 ROWS ONLY;

2.2 百分比

SELECT * FROM employees ORDER BY id
FETCH FIRST 10 PERCENT ROWS ONLY;

2.3 WITH TIES

SELECT * FROM employees ORDER BY salary DESC
FETCH FIRST 5 ROWS WITH TIES;

2.4 参数

-- 绑定变量
VARIABLE p_offset NUMBER;
VARIABLE p_limit NUMBER;
EXEC :p_offset := 100;
EXEC :p_limit := 10;

SELECT * FROM employees ORDER BY id
OFFSET :p_offset ROWS FETCH NEXT :p_limit ROWS ONLY;

3. IDENTITY 列

3.1 GENERATED ALWAYS

CREATE TABLE employees (
  id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR2(100)
);

-- 不允许指定 id
INSERT INTO employees (name) VALUES ('Alice');

3.2 BY DEFAULT

CREATE TABLE employees (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR2(100)
);

-- 允许指定
INSERT INTO employees (id, name) VALUES (100, 'Alice');

3.3 BY DEFAULT ON NULL

CREATE TABLE employees (
  id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
  name VARCHAR2(100)
);

-- NULL 时自动生成
INSERT INTO employees (id, name) VALUES (NULL, 'Alice');

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

4. DEFAULT SEQUENCE

CREATE SEQUENCE seq_emp_id;

CREATE TABLE employees (
  id NUMBER DEFAULT seq_emp_id.NEXTVAL PRIMARY KEY,
  name VARCHAR2(100)
);

INSERT INTO employees (name) VALUES ('Alice');
-- id 自动生成

5. Temporal Validity

5.1 创建

CREATE TABLE employees (
  id NUMBER PRIMARY KEY,
  name VARCHAR2(100),
  valid_start DATE,
  valid_end DATE,
  PERIOD FOR valid_time (valid_start, valid_end)
);

5.2 查询

-- 历史查询
DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_TIME(...);

SELECT * FROM employees 
AS OF PERIOD FOR valid_time TO_DATE('2026-07-21', 'YYYY-MM-DD');

6. In-Database Archiving

6.1 启用

ALTER TABLE employees ROW ARCHIVAL;

6.2 设置

UPDATE employees SET ora_archive_state = '1' WHERE id = 100;

6.3 查询

-- 默认仅活跃
SELECT * FROM employees;

-- 全部
ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;
SELECT * FROM employees;

详细见：Oracle 表压缩技术。

7. Pattern Matching（12c+）

7.1 MATCH_RECOGNIZE

SELECT *
FROM sales_history
MATCH_RECOGNIZE (
  PARTITION BY product_id
  ORDER BY sale_date
  MEASURES 
    STRT.sale_date AS start_date,
    LAST(sale_date) AS end_date,
    COUNT(*) AS days
  ONE ROW PER MATCH
  AFTER MATCH SKIP TO LAST UP
  PATTERN (STRT UP+)
  DEFINE
    UP AS UP.amount > PREV(UP.amount)
);

7.2 应用

V 形
上升趋势
双顶
股票分析

8. Lateral Join

8.1 LATERAL

SELECT e.name, d.dept_name
FROM employees e,
  LATERAL (SELECT * FROM departments WHERE id = e.dept_id) d;

8.2 CROSS APPLY

SELECT e.name, d.dept_name
FROM employees e
CROSS APPLY (SELECT * FROM departments WHERE id = e.dept_id) d;

8.3 OUTER APPLY

SELECT e.name, d.dept_name
FROM employees e
OUTER APPLY (SELECT * FROM departments WHERE id = e.dept_id) d;
-- 类似 LEFT JOIN

9. Partial Index

9.1 创建

CREATE TABLE sales (...)
PARTITION BY RANGE (sale_date) (
  PARTITION p2024 VALUES LESS THAN (...) INDEXING OFF,
  PARTITION p2025 VALUES LESS THAN (...) INDEXING ON,
  PARTITION p2026 VALUES LESS THAN (...) INDEXING ON
);

CREATE INDEX idx_sales ON sales(id) INDEXING PARTIAL;

详细见：Oracle 分区表设计。

10. Text 索引增强

10.1 MULTI_COLUMN_DATASTORE

BEGIN
  CTX_DDL.CREATE_SECTION_GROUP('mysec', 'AUTO_SECTION_GROUP');
  CTX_DDL.ADD_FIELD_SECTION('mysec', 'title', 'title', TRUE);
END;
/

CREATE INDEX idx_docs ON docs(content) INDEXTYPE IS CTXSYS.CONTEXT
  PARAMETERS ('SECTION GROUP mysec');

11. APPROXIMATE

11.1 APPROX_COUNT_DISTINCT

SELECT APPROX_COUNT_DISTINCT(customer_id) FROM sales;

11.2 性能

- 快
- 误差 < 5%
- 大数据友好

12. APPROX_RANK / APPROX_SUM

SELECT dept_id, 
  APPROX_RANK(PARTITION BY dept_id ORDER BY APPROX_SUM(salary) DESC)
FROM employees
GROUP BY dept_id
HAVING APPROX_RANK(...) <= 10;

13. JSON 增强

13.1 JSON_TABLE

SELECT jt.name, jt.salary
FROM json_table,
JSON_TABLE(data, '$' COLUMNS (
  name VARCHAR2(100) PATH '$.name',
  salary NUMBER PATH '$.salary'
)) jt;

详细见：Oracle JSON 处理。

14. 12c R2 新增

14.1 CONVERT TO CACHED

-- 物化视图
ALTER MATERIALIZED VIEW mv CONVERT TO CACHED;

14.2 Inline External Table

SELECT * FROM EXTERNAL (
  (id NUMBER, name VARCHAR2(100))
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_data
  ACCESS PARAMETERS (...)
  LOCATION ('emp.csv')
);

15. 常见坑与排错

15.1 FETCH 与 ROWNUM

-- ROWNUM 旧方式
SELECT * FROM (SELECT ROWNUM rn, t.* FROM (...) WHERE ROWNUM <= 110) WHERE rn > 100;

-- FETCH 推荐
SELECT ... OFFSET 100 ROWS FETCH NEXT 10 ROWS ONLY;

15.2 IDENTITY 性能

-- CACHE
CREATE TABLE t (id NUMBER GENERATED ALWAYS AS IDENTITY (CACHE 100), ...);

16. 最佳实践

FETCH 分页：替代 ROWNUM
IDENTITY：替代触发器
DEFAULT SEQUENCE：灵活
Temporal：历史
In-DB Archiving：归档
Pattern Matching：分析
APPLY：相关子查询
Partial Index：节省
APPROXIMATE：大数据
JSON：半结构化

17. 参考资料

[1] Oracle Database New Features Guide 12c https://docs.oracle.com/database/121/NEWFT/