INSERT ALL加并行提示时用表别名报‘missing SELECT keyword’，怎么解决？

Got it, let's sort out that INSERT ALL issue you're facing. The error pops up because you can't assign aliases to target tables in the INTO clauses of INSERT ALL—Oracle's syntax doesn't support this, which is why it throws that confusing "missing SELECT keyword" message.

Here's the Corrected SQL #

We'll remove the invalid aliases and adjust the parallel hints to target actual table names instead:

INSERT /*+ APPEND NOLOGGING PARALLEL(SCHEMA.TABLE1,4) PARALLEL(SCHEMA.TABLE2,4) */
ALL
  WHEN COL1 NOT LIKE '123%' THEN
    INTO SCHEMA.TABLE1
    -- Pro tip: Explicitly list columns here instead of relying on implicit matching
    (COL1, COL2, COL3) -- Add all your target columns as needed
  WHEN COL1 LIKE '123%' OR COL2 LIKE '5%' THEN
    INTO SCHEMA.TABLE2
    (COL1, COL2, COL3) -- Match columns to the source table's structure
SELECT /*+ PARALLEL(C,4) */
  *
FROM SCHEMA.EXTERNAL_TABLE C;

Key Fixes & Explanations #

1. Removed table aliases (A, B) from INTO clauses
   INSERT ALL doesn't use aliases for target tables—you're only specifying where rows should be inserted, not joining or querying those tables. Aliases are only valid in SELECT statements, not INSERT targets.

2. Adjusted parallel hints
   Instead of referencing non-existent aliases like PARALLEL(A,4), we directly target schema-qualified table names (PARALLEL(SCHEMA.TABLE1,4)). If you prefer a simpler approach, you can use a global PARALLEL(4) hint to apply the same parallelism to the entire INSERT operation.

3. Optional but recommended: Explicit column lists
   While you can omit column lists and rely on column order matching between the source external table and targets, listing columns explicitly makes your SQL more robust. It prevents breakage if either table's schema changes (e.g., new columns added, column order modified).

4. APPEND & NOLOGGING notes
   Keep in mind that APPEND uses direct-path inserts (skipping the buffer cache) and NOLOGGING reduces redo generation—both boost performance, but ensure your recovery strategy accounts for NOLOGGING (since these operations aren't fully logged). Also, direct-path inserts require the target table isn't being updated by other sessions during the insert.

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