我有一个简单的序列，如下：

CREATE SEQUENCE my_sequence
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;

我试图插入某些东西，并且像这样使用这个序列：

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,
    FROM table2 tt
    LEFT JOIN table3 sx ON tt.col6 = sx.col6
    WHERE sx.col5 LIKE 'P%'
    ORDER BY tt.col1 ASC
);

现在，当我查看table1的内容时，它们的排序并非如预期。如果按col1排序，col0的数字显示为：

col0   col1     col2
1        1      ...
2        2
5        3
6        4
7        5
8        6
11       7
9        8
10       9
12       10
3        11
4        12
...

如果我清空表并重新运行，顺序又会再次不同。

为什么会这样？我该怎么做才能强制排序？

解决方案

如果你运行并行DML（必须显式开启，或在Autonomous Database上使用MEDIUM或 HIGH服务），每组在进入并发插入进程之前都会被正确排序，但它们会被并行发送到插入进程（P->P），因此一个可能在另一个之前完成，顺序就会随机。

ADMIN@o23_tp> select * from dbms_xplan.display_cursor(format=>'basic +parallel');

PLAN_TABLE_OUTPUT
___________________________________________________________________________________________________
EXPLAINED SQL STATEMENT:
------------------------
INSERT /*+ parallel(2) */ INTO employee_registry (emp_id, first_name,
last_name, hire_date, salary, dept_name)   SELECT emp_id_seq.NEXTVAL,
first_name, last_name, hire_date, salary, dept_name   FROM (
SELECT c.first_name,              c.last_name,
c.hire_date,              c.salary,              d.dept_name       FROM
candidates c       LEFT JOIN departments d ON c.dept_code = d.dept_code
      WHERE d.dept_name LIKE 'P%'       ORDER BY c.last_name ASC   )

Plan hash value: 3347350506

------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name              |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                      |                   |        |      |            |
|   1 |  PX COORDINATOR                       |                   |        |      |            |
|   2 |   PX SEND QC (RANDOM)                 | :TQ10002          |  Q1,02 | P->S | QC (RAND)  |
|   3 |    INDEX MAINTENANCE                  | EMPLOYEE_REGISTRY |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE                        |                   |  Q1,02 | PCWP |            |
|   5 |      PX SEND RANGE                    | :TQ10001          |  Q1,01 | P->P | RANGE      |
|   6 |       LOAD AS SELECT (HYBRID TSM/HWMB)| EMPLOYEE_REGISTRY |  Q1,01 | PCWP |            |
|   7 |        OPTIMIZER STATISTICS GATHERING |                   |  Q1,01 | PCWP |            |
|   8 |         SEQUENCE                      | EMP_ID_SEQ        |  Q1,01 | PCWC |            |
|   9 |          VIEW                         |                   |  Q1,01 | PCWP |            |
|  10 |           SORT ORDER BY               |                   |  Q1,01 | PCWP |            |
|  11 |            PX RECEIVE                 |                   |  Q1,01 | PCWP |            |
|  12 |             PX SEND RANGE             | :TQ10000          |  Q1,00 | P->P | RANGE      |
|  13 |              HASH JOIN                |                   |  Q1,00 | PCWP |            |
|  14 |               PX BLOCK ITERATOR       |                   |  Q1,00 | PCWC |            |
|  15 |                TABLE ACCESS FULL      | CANDIDATES        |  Q1,00 | PCWP |            |
|  16 |               TABLE ACCESS FULL       | DEPARTMENTS       |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------

如果你禁用并行DML，即使使用并行查询，也会像上面那样在协调器（P->S）处保持有序：

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name              |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT            |                   |        |      |            |
|   1 |  LOAD TABLE CONVENTIONAL    | EMPLOYEE_REGISTRY |        |      |            |
|   2 |   SEQUENCE                  | EMP_ID_SEQ        |        |      |            |
|   3 |    PX COORDINATOR           |                   |        |      |            |
|   4 |     PX SEND QC (ORDER)      | :TQ10001          |  Q1,01 | P->S | QC (ORDER) |
|   5 |      VIEW                   |                   |  Q1,01 | PCWP |            |
|   6 |       SORT ORDER BY         |                   |  Q1,01 | PCWP |            |
|   7 |        PX RECEIVE           |                   |  Q1,01 | PCWP |            |
|   8 |         PX SEND RANGE       | :TQ10000          |  Q1,00 | P->P | RANGE      |
|   9 |          HASH JOIN          |                   |  Q1,00 | PCWP |            |
|  10 |           TABLE ACCESS FULL | DEPARTMENTS       |  Q1,00 | PCWP |            |
|  11 |           PX BLOCK ITERATOR |                   |  Q1,00 | PCWC |            |
|  12 |            TABLE ACCESS FULL| CANDIDATES        |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------