In the discussion about multiple row insert into the Oracle two approaches were demonstrated:
First:
insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
select 8000,0,'Multi 8000',1 from dual
union all select 8001,0,'Multi 8001',1 from dual
Second:
INSERT ALL
INTO t (col1, col2, col3) VALUES ('val1_1', 'val1_2', 'val1_3')
INTO t (col1, col2, col3) VALUES ('val2_1', 'val2_2', 'val2_3')
INTO t (col1, col2, col3) VALUES ('val3_1', 'val3_2', 'val3_3')
.
.
.
SELECT 1 FROM DUAL;
Could anyone argue the preference of using one over another?
P.S. I didn't do any research myself (even explanation plan), so any information or opinion would be appreciated.
Thanks.
The INSERT ALL
method has a problem with inserting bigger number of rows into a table.
I recently wanted to insert 1130 rows into a table with single SQL statement. When I tried to do this with INSERT ALL
method I got the following error:
ORA-24335 - cannot support more than 1000 columns
When I used INSERT INTO .. UNION ALL ..
approach everything went fine.
Btw. I didn't know about the UNION ALL method before I found this discussion :)
From performance's point of view, these queries are identical.
UNION ALL
won't hurt performance, since Oracle
estimates the UNION
'ed query only when it needs it, it doesn't cache the results first.
SELECT
syntax is more flexible in that sense that you can more easuly manupulate the SELECT
query if you want to change something.
For instance, this query:
insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
select 8000,0,'Multi 8000',1 from dual
union all select 8001,0,'Multi 8001',1 from dual
can be rewritten as
INSERT
INTO pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
SELECT 7999 + level, 0, 'Multi ' || 7999 + level, 1
FROM dual
CONNECT BY
level <= 2
By replacing 2
with appropriate number, you can get any number of rows you want.
In case of INSERT ALL
, you would have to duplicate the destination table description, which is less readable if you need, say, 40
rows.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With