为何含UNION ALL与ORDER BY的复合查询仅排序最后部分？

核心原因 #

Oracle对INSERT语句中UNION ALL联合查询的ORDER BY解析规则导致了这个现象：当ORDER BY直接跟在多段UNION ALL连接的查询后时，它仅会作用于紧邻的最后一个查询块，而非整个联合后的结果集。

分场景对比验证 #

1. 直接执行带UNION ALL和ORDER BY的查询 #

select * from table(sys.OdciNumberList(2,1,3))
union all
select * from table(sys.OdciNumberList(5,6,4))
union all
select * from table(sys.OdciNumberList(9,7,8))
order by 1;

这里的ORDER BY属于顶层查询的排序指令，作用范围是整个UNION ALL合并后的完整结果集，因此会对所有数据全局排序，结果符合预期。

2. 单表查询排序后插入自增表 #

create table q1(id number generated always as identity, val number);

insert into q1 (val)
select * from table(sys.OdciNumberList(2,1,3))
order by 1;

select * from q1 order by id;

由于只有单个查询块，ORDER BY直接作用于该块的所有数据，插入时自增ID按排序后的顺序生成，查询结果符合预期。

3. 带UNION ALL的查询插入自增表（不符合预期场景） #

create table q3(id number generated always as identity, val number);

insert into q3 (val)
select * from table(sys.OdciNumberList(2,1,3))
union all
select * from table(sys.OdciNumberList(5,6,4))
union all
select * from table(sys.OdciNumberList(9,7,8))
order by 1;

select * from q3 order by id;

这里的ORDER BY被Oracle解析为仅对最后一个查询块select * from table(sys.OdciNumberList(9,7,8))生效：前两个UNION ALL的结果块保持原始数据顺序插入，仅最后一个块内部被排序后插入，最终得到2 1 3 5 6 4 7 8 9的结果。

规避方案 #

将整个UNION ALL的联合查询嵌套到一个子查询中，让ORDER BY作用于子查询的完整结果集：

create table q3(id number generated always as identity, val number);

insert into q3 (val)
select * from (
    select * from table(sys.OdciNumberList(2,1,3))
    union all
    select * from table(sys.OdciNumberList(5,6,4))
    union all
    select * from table(sys.OdciNumberList(9,7,8))
) order by 1;

select * from q3 order by id;

此时ORDER BY属于外层查询的排序指令，会对整个联合后的结果集全局排序，插入后自增ID的顺序与全局排序结果一致。

内容的提问来源于stack exchange，提问作者ESkri