Oracle Database 23ai 新特性： UPDATE 和 DELETE 语句的直接联接
Oracle Database 23c 引入了一系列令人振奋的新特性，其中一项尤为引人注目的是对 UPDATE 和 DELETE 语句支持直接联接（Direct Join）。这一新功能极大地简化了复杂数据操作的实现，提升了性能，并为数据库开发者提供了更强大的工具来管理数据。
一、背景
在传统的 SQL 操作中，当需要根据其他表中的数据更新或删除记录时，通常需要借助子查询或临时表来完成任务。这种方式不仅编写复杂，而且执行效率较低，尤其是在处理大规模数据集时。为了应对这些挑战，Oracle 在 23c 中引入了直接联接的支持，使得 UPDATE 和 DELETE 操作可以直接引用多个表的数据，从而实现了更加简洁高效的解决方案。
二、Direct Joins for UPDATE
2.1 准备示例表
drop table if exists t1 purge;
drop table if exists t2 purge;
drop table if exists t3 purge;
create table t1 as
select level as id,
'CODE' || level as code,
'Description for ' || level as description
from dual
connect by level <= 100;
alter table t1 add constraint t1_pk primary key (id);
create table t2 as
select level as id,
'CODE' || (level*10) as code,
'Updated description for ' || (level*10) as description
from dual
connect by level <= 100;
alter table t2 add constraint t2_pk primary key (id);
create table t3 as
select level as id,
'CODE' || (level*10) as code,
'Updated description for ' || (level*10) as description
from dual
connect by level <= 100;
alter table t3 add constraint t3_pk primary key (id);2.2 传统方法解决需求
我们有这样的一个需求：现在我们使用 T2 表的联接来更新 T1 中的数据。我们希望通过 ID 值中的联接使用 T2.CODE 和 T2.DESCRIPTION 中的值来更新 T1.CODE 和 T1.DESCRIPTION 值。
首先我们检查前五行的数据。
column code format a10
column description format a30
select * from t1 where id <= 5;
ID CODE DESCRIPTION
---------- ---------- ------------------------------
1 CODE1 Description for 1
2 CODE2 Description for 2
3 CODE3 Description for 3
4 CODE4 Description for 4
5 CODE5 Description for 5
SQL>按以往的经验，可以选择以下方案：
- 方案一：merge语句
merge into t1 a
using (select * from t2 b where b.id <= 5) b
on (a.id = b.id)
when matched then
update set a.code = b.code, a.description = b.description;- 方案二：update+exists
update t1 a
set (a.code, a.description) =
(select b.code, b.description from t2 b where a.id = b.id)
where exists (select 1
from t2 b
where a.id = b.id
and b.id <= 5);现在我们看到 T1.CODE 和 T1.DESCRIPTION 值已更新。
select * from t1 where id <= 5;
ID CODE DESCRIPTION
---------- ---------- ------------------------------
1 CODE10 Updated description for 10
2 CODE20 Updated description for 20
3 CODE30 Updated description for 30
4 CODE40 Updated description for 40
5 CODE50 Updated description for 50
rollback;
SQL>2.3 23ai中的新特性
使用示例如下：
update t1 a
set a.code = b.code,
a.description = b.description
from t2 b
where a.id = b.id
and b.id <= 5; 同样也可以完成数据更新
select * from t1 where id <= 5;
ID CODE DESCRIPTION
---------- ---------- ------------------------------
1 CODE10 Updated description for 10
2 CODE20 Updated description for 20
3 CODE30 Updated description for 30
4 CODE40 Updated description for 40
5 CODE50 Updated description for 50
rollback;
SQL>2.4 ANSI 连接语法
我们不能在 T1 和 T2 之间使用 ANSI 连接语法，但如果有多个表驱动更新，则可以使用 ANSI 连接将它们连接在一起。下面的例子不推荐，但是它通过将T2连接到T3来证明了一点。
update t1 a
set a.code = b.code,
a.description = b.description
from t2 b
join t3 c on b.id = c.id
where a.id = b.id
and b.id <= 5;
rollback;三、Direct Joins for DELETE
不仅更新可以直接连接，删除也可以。
首先我们检查前五行的数据。
column code format a10
column description format a30
select * from t1 where id <= 5;
ID CODE DESCRIPTION
---------- ---------- ------------------------------
1 CODE1 Description for 1
2 CODE2 Description for 2
3 CODE3 Description for 3
4 CODE4 Description for 4
5 CODE5 Description for 5
SQL>我们根据 T2 的查询从 T1 中删除行。请注意，我们使用 ID 列在两个表之间进行联接，并使用一个或多个谓词来确定 T2 中的哪些行用于驱动删除。
delete t1 a
from t2 b
where a.id = b.id
and b.id <= 5; 我们可以看到行已被删除。
select * from t1 where id <= 5;
no rows selected
SQL>我们可以在 DELETE 关键字后面添加 FROM 关键字
delete from t1 a
from t2 b
where a.id = b.id
and b.id <= 5;
rollback;我们不能在 T1 和 T2 之间使用 ANSI 连接语法，但如果有多个表驱动删除，则可以使用 ANSI 连接将它们连接在一起。下面的例子不推荐，但是它通过将T2连接到T3来证明了一点。
delete t1 a
from t2 b
join t3 c on b.id = c.id
where a.id = b.id
and b.id <= 5;
rollback;四、优势分析
- 简化代码：新的语法结构更加简洁明了，减少了嵌套子查询的复杂度，提高了代码的可读性和维护性。
- 提升性能：直接联接操作能够更好地利用索引和优化器，减少不必要的扫描次数，从而显著提高查询性能。
- 增强灵活性：允许在一个语句中同时处理多个表的数据，满足更多复杂的业务逻辑需求。
- 降低错误率：避免了由于子查询或临时表引起的潜在问题，如数据不一致等。
五、总结
Oracle Database 23c 的 UPDATE 和 DELETE 语句直接联接新特性，代表了关系型数据库技术的一个重要进步。它不仅简化了开发者的日常工作，还为高效管理和优化大规模数据处理提供了强有力的支持。随着越来越多的企业采用这一新技术，我们有理由相信，这将大大促进数据库应用的发展和创新。