Oracle 12c：Insert Select中实现一行拆分为两行插入

Got it, let's work through this problem. You've got a 1M-row INSERT...SELECT operation, and need to split rows where both performance_total and mechanical_total have valid values into two separate records—all while avoiding UNION (which can tank performance with large datasets, since it often scans the source table twice).

Core Approach #

Instead of using UNION to combine two separate queries, we'll use a cross join with a tiny virtual table that generates 2 rows (one for each "type": performance and mechanical). This expands each source row into up to 2 rows, then we filter out any rows where the corresponding total column is null. The key win here: we only scan the source table once, cutting I/O overhead in half compared to UNION.

Example Implementations by SQL Dialect #

Below are working examples for the most common databases:

MySQL/MariaDB

INSERT INTO target_table (total, record_type, col1, col2, /* all other 28 source columns */)
SELECT
  -- Pick the correct total based on the generated type
  CASE WHEN gen.record_type = 'performance' THEN src.performance_total ELSE src.mechanical_total END AS total,
  gen.record_type,
  -- Include all other columns from your source table
  src.col1, src.col2, src.col3 /* ... list remaining columns */
FROM source_table src
-- Cross join with a virtual table that defines our two record types
CROSS JOIN (
  SELECT 'performance' AS record_type UNION ALL SELECT 'mechanical' AS record_type
) gen
-- Filter out rows where the corresponding total is null
WHERE
  (gen.record_type = 'performance' AND src.performance_total IS NOT NULL)
  OR (gen.record_type = 'mechanical' AND src.mechanical_total IS NOT NULL);

PostgreSQL

PostgreSQL supports a cleaner VALUES clause for the virtual table:

INSERT INTO target_table (total, record_type, col1, col2, /* other columns */)
SELECT
  CASE gen.record_type
    WHEN 'performance' THEN src.performance_total
    WHEN 'mechanical' THEN src.mechanical_total
  END AS total,
  gen.record_type,
  src.col1, src.col2 /* ... list other columns */
FROM source_table src
CROSS JOIN (
  VALUES ('performance'), ('mechanical')
) gen(record_type)
WHERE
  (gen.record_type = 'performance' AND src.performance_total IS NOT NULL)
  OR (gen.record_type = 'mechanical' AND src.mechanical_total IS NOT NULL);

SQL Server

Similar to PostgreSQL, use the VALUES clause for the virtual table:

INSERT INTO target_table (total, record_type, col1, col2, /* other columns */)
SELECT
  CASE gen.record_type
    WHEN 'performance' THEN src.performance_total
    WHEN 'mechanical' THEN src.mechanical_total
  END AS total,
  gen.record_type,
  src.col1, src.col2 /* ... list other columns */
FROM source_table src
CROSS JOIN (
  VALUES ('performance'), ('mechanical')
) gen(record_type)
WHERE
  (gen.record_type = 'performance' AND src.performance_total IS NOT NULL)
  OR (gen.record_type = 'mechanical' AND src.mechanical_total IS NOT NULL);

Key Performance Notes #

• Single Source Scan: Unlike UNION (which runs two separate SELECT queries, each scanning the source table), this approach only reads your 1M-row source table once. This is a huge win for I/O-bound systems.
• Index Optimization: If you add indexes on performance_total and mechanical_total, the WHERE clause filter will run even faster.
• Column Listing: Make sure to explicitly list all non-total columns instead of using src.* (where possible) to avoid accidental schema mismatches between source and target tables.

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