Oracle 23c 新特性 SQL

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

---

1. 概述

Oracle 23ai 引入大量 SQL 新特性[1]：

特性：

• AI Vector Search
• JSON Relational Duality
• SQL Firewall
• Boolean 数据类型
• Hybrid Partitioned Tables

详细见：Oracle 23ai 新特性。

---

2. AI Vector Search

2.1 向量数据类型

CREATE TABLE documents (
  id NUMBER PRIMARY KEY,
  content CLOB,
  embedding VECTOR(768, FLOAT32)
);

2.2 向量索引

CREATE VECTOR INDEX vec_idx ON documents(embedding)
  ORGANIZATION INMEMORY NEIGHBOR PARTITIONS;

2.3 相似查询

-- 余弦相似
SELECT id, content
FROM documents
ORDER BY VECTOR_DISTANCE(embedding, :query_vec, COSINE)
FETCH FIRST 10 ROWS ONLY;

-- 内积
SELECT id, content
FROM documents
ORDER BY VECTOR_DISTANCE(embedding, :query_vec, DOT)
FETCH FIRST 10 ROWS ONLY;

2.4 向量函数

-- 转换
VECTOR_SERIALIZE(v)
VECTOR_DESERIALIZE(str)

-- 计算
VECTOR_DISTANCE(v1, v2, COSINE)
VECTOR_NORM(v)

---

3. Boolean 数据类型

3.1 表

CREATE TABLE products (
  id NUMBER,
  name VARCHAR2(100),
  is_active BOOLEAN DEFAULT TRUE,
  is_featured BOOLEAN DEFAULT FALSE
);

3.2 查询

-- 插入
INSERT INTO products (id, name, is_active) 
VALUES (1, 'Widget', TRUE);

-- 查询
SELECT * FROM products WHERE is_active = TRUE;
SELECT * FROM products WHERE is_active;  -- 简写
SELECT * FROM products WHERE NOT is_active;

3.3 PL/SQL

DECLARE
  v_flag BOOLEAN := TRUE;
BEGIN
  IF v_flag THEN
    DBMS_OUTPUT.PUT_LINE('Yes');
  END IF;
END;
/

---

4. JSON Relational Duality

4.1 创建视图

-- 表
CREATE TABLE employees (id NUMBER PRIMARY KEY, name VARCHAR2(100), salary NUMBER);
CREATE TABLE departments (id NUMBER PRIMARY KEY, name VARCHAR2(100), emp_id NUMBER REFERENCES employees(id));

-- 双重视图
CREATE JSON DUALITY VIEW employees_dv AS
  SELECT e.id, e.name, e.salary,
    JSON_ARRAYAGG(
      JSON_OBJECT(d.id, d.name)
    ) AS departments
  FROM employees e
  LEFT JOIN departments d ON e.id = d.emp_id
  GROUP BY e.id, e.name, e.salary;

4.2 JSON 操作

-- 插入（JSON）
INSERT INTO employees_dv VALUES (
  JSON '{id: 1, name: "Alice", salary: 5000, departments: [{id: 10, name: "IT"}]}'
);

-- 查询
SELECT e.name, e.salary FROM employees_dv e WHERE e.name = 'Alice';

-- 更新
UPDATE employees_dv e SET e.salary = 6000 WHERE e.id = 1;

---

5. SQL Firewall

5.1 启用

BEGIN
  DBMS_SQL_FIREWALL.CREATE_PROFILE('app_profile');
  DBMS_SQL_FIREWALL.ENABLE_PROFILE('app_profile');
END;
/

5.2 训练

-- 学习模式
EXEC DBMS_SQL_FIREWALL.SET_MODE('app_profile', 'LEARN');

-- 业务运行，学习 SQL 模式

-- 强制模式
EXEC DBMS_SQL_FIREWALL.SET_MODE('app_profile', 'ENFORCE');

5.3 监控

SELECT * FROM dba_sql_firewall_capture;
SELECT * FROM dba_sql_firewall_violations;

---

6. Hybrid Partitioned Tables

6.1 概述

• 内部 + 外部分区
• 历史数据外部

6.2 创建

CREATE TABLE sales (
  id NUMBER,
  sale_date DATE,
  amount NUMBER
)
PARTITION BY RANGE (sale_date) (
  PARTITION p2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')) 
    EXTERNAL DEFAULT DIRECTORY ext_dir 
    LOCATION ('sales_2024.csv'),
  PARTITION p2025 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD')),
  PARTITION p2026 VALUES LESS THAN (TO_DATE('2027-01-01', 'YYYY-MM-DD'))
);

---

7. Annotations（注释）

7.1 表注释

CREATE TABLE employees (
  id NUMBER ANNOTATIONS ('Primary key', 'Employee ID'),
  name VARCHAR2(100) ANNOTATIONS ('Full name', 'Required')
) ANNOTATIONS ('Employee master table');

7.2 查看

SELECT * FROM user_annotations;

---

8. Schema Annotations

CREATE SCHEMA AUTHORIZATION scott
  ANNOTATIONS ('Application schema', 'v1.0');

---

9. SQL Domain

9.1 创建

CREATE DOMAIN salary_domain AS NUMBER 
  CONSTRAINT sal_check CHECK (salary_domain BETWEEN 0 AND 1000000)
  ANNOTATIONS ('Valid salary range');

-- 使用
CREATE TABLE employees (
  id NUMBER,
  salary salary_domain
);

9.2 多列

CREATE DOMAIN date_range AS (
  start_date DATE,
  end_date DATE
)
CONSTRAINT dr_check CHECK (end_date >= start_date);

CREATE TABLE projects (
  id NUMBER,
  start_date DATE,
  end_date DATE,
  DOMAIN date_range (start_date, end_date)
);

---

10. IF NOT EXISTS

10.1 DDL

CREATE TABLE IF NOT EXISTS employees (...);
CREATE INDEX IF NOT EXISTS idx_emp ON employees(id);
DROP TABLE IF EXISTS employees;
ALTER TABLE IF EXISTS employees ADD COLUMN age NUMBER;

---

11. GROUP BY 简化

11.1 GROUP BY 列别名

SELECT EXTRACT(YEAR FROM sale_date) AS sale_year,
       COUNT(*) AS cnt
FROM sales
GROUP BY sale_year
ORDER BY sale_year;

---

12. SELECT WITHOUT FROM

12.1 23ai

SELECT 1 + 1;
SELECT SYSDATE;
SELECT 'Hello, World';
-- 不需要 FROM dual

---

13. JavaScript in MLE

13.1 嵌入

CREATE MLE MODULE js_module LANGUAGE JAVASCRIPT AS
  module.exports = function(a, b) {
    return a + b;
  };
/

CREATE FUNCTION js_add(a NUMBER, b NUMBER) RETURNS NUMBER
AS MODULE js_module;
/

SELECT js_add(1, 2) FROM dual;

---

14. Property Graphs

14.1 创建

CREATE PROPERTY GRAPH hr_graph
  VERTEX TABLES (
    employees KEY (id) LABEL employee PROPERTIES (name, salary),
    departments KEY (id) LABEL department PROPERTIES (name)
  )
  EDGE TABLES (
    emp_dept AS 
      employees AS source KEY (id) 
      REFERENCES departments AS destination KEY (id)
      LABEL works_in
  );

14.2 查询

SELECT * FROM GRAPH_TABLE (hr_graph
  MATCH (e:employee) -[works_in]-> (d:department)
  WHERE e.salary > 5000
  COLUMNS (e.name, d.name)
);

---

15. SQL 其他增强

15.1 GROUPING SETS

SELECT dept_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS ((dept_id, job_id), (dept_id), ());

15.2 ROLLUP / CUBE

SELECT dept_id, job_id, SUM(salary)
FROM employees
GROUP BY ROLLUP (dept_id, job_id);

15.3 APPROX_COUNT

SELECT APPROX_COUNT_DISTINCT(customer_id) FROM sales;
SELECT APPROX_RANK(...);
SELECT APPROX_SUM(...);

---

16. 数据类型增强

16.1 VECTOR

VECTOR(768, FLOAT32)
VECTOR(*, INT8)  -- 任意维度

16.2 JSON

JSON  -- 内置
JSON_OBJECT, JSON_ARRAY, JSON_VALUE, JSON_QUERY

16.3 XML

XMLType
XMLQuery, XMLTable

---

17. 性能

17.1 向量搜索

- 向量索引
- 快速相似
- 机器学习集成

17.2 Duality

- JSON 灵活
- 关系性能
- 双重

---

18. 常见坑与排错

18.1 CDB 强制

- 23ai 仅 CDB
- 非 CDB 废弃

18.2 Vector 内存

- INMEMORY
- 内存充足

18.3 SQL Firewall

- 训练充分
- 强制可能影响

---

19. 最佳实践

1. AI Vector Search：现代
2. JSON Duality：灵活
3. SQL Firewall：安全
4. Boolean：现代
5. Hybrid Partition：归档
6. Annotations：文档
7. Domain：约束
8. IF NOT EXISTS：安全
9. SELECT 简化：简洁
10. JavaScript：现代

---

20. 参考资料

[1] Oracle Database New Features Guide 23ai https://docs.oracle.com/en/database/oracle/oracle-database/23/nfnew/