How to generate data with Union ALL and Insert ALL in Oracle?

OracleSoftware & CodingProgramming

Problem:

You wanted to know the difference between Union ALL and Insert ALL to generate small amounts of data.

Solution:

I have been recently generating small amounts of data for testing some functionality and came across a couple of options in Oracle. Union All and Insert All are two options commonly used for generating small amounts of data in Oracle.

The most common set operators in Oracle are UNION and UNION ALL. These operators are used to combine sets of data, even if there are no relationships between these sets.

UNION creates a distinct set, whereas UNION ALL allows for duplicates. Removing duplicates can have a huge performance impact, so it is ideal to use UNION ALL by default and later on handle the duplicates.

The most common use of UNION ALL is to generate data. In Oracle, we must always select from something, so there is a special pseudo-table named DUAL.

The DUAL table only has one row, so if we want to create multiple rows, we must combine statements with UNION ALL like this

Example

SELECT 'something' FROM DUAL UNION ALL SELECT 'something_else' FROM DUAL UNION ALL SELECT 'something_more' FROM DUAL UNION ALL

I have used UNION ALL as shown below to generate the data and insert into my target table

Example

-- CREATE a table
CREATE TABLE tennis_stats
( tennis_player    VARCHAR2(100),
  grandslam_titles NUMBER(2) );
COMMIT;

Example

-- Generating data using Union All
INSERT INTO tennis_stats
SELECT REGEXP_SUBSTR(t.tennis_stats, '[^,]+', 1, 1) tennis_player,
       REGEXP_SUBSTR(t.tennis_stats, '[^,]+', 1, 2) grandslam_titles
  FROM (
SELECT 'ROGER FEDERER'  || ',' || 20 AS tennis_stats FROM DUAL  
UNION ALL
SELECT 'RAFAEL NADAL'   || ',' || 19 AS tennis_stats FROM DUAL
UNION ALL
SELECT 'NOVAK DJOKOVIC' || ',' || 17 AS tennis_stats FROM DUAL ) t ;
COMMIT;

INSERT ALL

Oracle SQL has a multi-table insert operation that lets us insert data into multiple tables in one single statement. Multi-table inserts are useful when we have one source that populates multiple destinations.

The INSERT ALL syntax was intended to add data to multiple tables, but I commonly use to generate data and insert multiple rows into the same table.

For example, let’s say we want to load the same data shown above using Insert All Option.

Example

-- CREATE a table CREATE TABLE tennis_stats ( tennis_player    VARCHAR2(100),   grandslam_titles NUMBER(2) ); COMMIT;

Example

-- Generating data using Insert All
INSERT ALL
INTO tennis_stats VALUES ( 'ROGER FEDERER', 20)
INTO tennis_stats VALUES ( 'RAFAEL NADAL', 19)
INTO tennis_stats VALUES ( 'NOVAK DJOKOVIC', 17)
SELECT * FROM dual;
COMMIT;

Note INSERT ALL does not use sequences. INSERT ALL only increments the sequence once per the statement, not once per reference.

There’s a minor issue with the INSERT ALL statement – the above code doesn’t list all the column names. In a real-world SQL statement, we wouldn’t want to waste so much space repeating the same list of columns. Also, excluding the column names future-proofs the query.

If a future version of the table tennis_stats adds a new column, the preceding statement would raise an exception, instead of silently writing bad data. On the other hand, listing the column names ensures the values go in the right columns, even if the column order changes. Excluding column names is a debatable style choice.

Conclusion

The INSERT ALL looks neat and easy, but the UNION ALL is overall better for generating data. The INSERT ALL statement is slower to parse than UNION ALL, especially for large statements with hundreds of rows.

If our applications are generating lots of data, an INSERT ALL is better than multiple INSERT statements.

In conclusion, Insert ALL looks neat but really not meant for generating large amounts of data.

raja
Published on 04-Dec-2020 11:02:22
Advertisements