Oracle序列NEXTVAL生成值无序？原因及解决方法问询

Oracle优化器执行带ORDER BY的子查询时，不会保证序列NEXTVAL的生成时机和子查询的排序顺序完全同步。实际执行中，优化器可能先扫描底层表并生成序列值，之后才执行排序操作——这就导致插入的序列值和你预期的col1排序顺序不匹配。哪怕子查询写了ORDER BY，Oracle也可能调整执行步骤，优先分配序列值再排序，最终打乱两者的对应关系。

方法1：嵌套视图强制先排序再生成序列 #

在排序子查询外层再套一层视图，让Oracle先完成排序，再读取结果生成序列：

INSERT INTO table1(col0, col1, col2, col3, col4, col5)
SELECT my_sequence.NEXTVAL, col1, col2, col3, col4, col5
FROM (
    SELECT *
    FROM (
        SELECT tt.col1,
               tt.col2,
               tt.col3,
               tt.col4,
               sx.col5
        FROM table2 tt
        LEFT JOIN table3 sx ON tt.col6 = sx.col6
        WHERE sx.col5 LIKE 'P%'
        ORDER BY tt.col1 ASC
    )
);

内层子查询的排序会被优先执行，外层视图读取已排序的结果后再生成序列，就能保证序列值和col1顺序对应。

方法2：用CTE固化排序结果 #

通过公共表表达式先固定排序后的数据集，再从中读取数据生成序列：

WITH sorted_data AS (
    SELECT tt.col1,
           tt.col2,
           tt.col3,
           tt.col4,
           sx.col5
    FROM table2 tt
    LEFT JOIN table3 sx ON tt.col6 = sx.col6
    WHERE sx.col5 LIKE 'P%'
    ORDER BY tt.col1 ASC
)
INSERT INTO table1(col0, col1, col2, col3, col4, col5)
SELECT my_sequence.NEXTVAL, col1, col2, col3, col4, col5
FROM sorted_data;

方法3：结合ROWNUM强制执行顺序 #

在排序子查询中加入ROWNUM，迫使Oracle必须先完成排序才能生成行号，从而固定数据顺序，之后再生成序列：

INSERT INTO table1(col0, col1, col2, col3, col4, col5)
SELECT my_sequence.NEXTVAL, col1, col2, col3, col4, col5
FROM (
    SELECT tt.col1,
           tt.col2,
           tt.col3,
           tt.col4,
           sx.col5,
           ROWNUM rn
    FROM (
        SELECT tt.col1,
               tt.col2,
               tt.col3,
               tt.col4,
               sx.col5
        FROM table2 tt
        LEFT JOIN table3 sx ON tt.col6 = sx.col6
        WHERE sx.col5 LIKE 'P%'
        ORDER BY tt.col1 ASC
    ) tt
);

ROWNUM会强制内层排序先执行，外层读取已排序的结果集时生成序列，就能保证序列值按col1的顺序递增。

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