2

The question is rather obvious. Suppose one has an INSERT ALL statement in following form:

INSERT ALL
INTO myTable (id, cola, colb, colc) VALUES (id, 'a', 'b', 'c')
INTO myTable (id, cola, colb, colc) VALUES (id, 'a', 'b', 'c')
INTO myTable (id, cola, colb, colc) VALUES (id, 'a', 'b', 'c')
...
SELECT * FROM dual;

Now suppose those three dots stood for, say 5k INTO clauses. All unique ids, and same number of columns.

What is the maximum number of rows one can insert into table in this fashion?

user1561358
  • 304
  • 3
  • 14
  • 1
    I don't think Oracle has a maximum number of rows. There are maximums on the length of a SQL statement -- and these might be in the database or in intermediate software. – Gordon Linoff Sep 30 '20 at 11:11
  • I agree with Gordon. However If this is a question, to check if this is the best way of loading data into a destination table, then there are alternatives available such as external tables, sql loader. – George Joseph Sep 30 '20 at 11:14
  • @GeorgeJoseph I am working with proprietary company tech, result of 7+ years of development. While there is an SQL server on the other side, running ORACLE database, there are number of limitations placed on how can I communicate with it. After consultation with my superiors it has become apparent that this is the most efficient way of getting it done. – user1561358 Oct 04 '20 at 15:37

2 Answers2

2

Although INSERT ALL does not have a theoretical maximum number of rows, in practice you will want to keep the number of rows to the low hundreds.

As I demonstrated in this answer, Oracle can easily handle hundreds of rows, but there's some magic number where the parse times start to grow exponentially. In older versions, things got really bad at 500 rows. With 19c, performance becomes an issue in the thousands of rows. Below are the results of a quick test:

# of Rows    Time in Seconds
---------    ---------------
     1000                0.4
     2000                1.7
     3000                4
     4000               12
     5000               24

And for reasons I don't understand, the UNION ALL approach tends to work faster. So you might want to limit your number of rows to several hundred and use a statement like this:

INSERT INTO myTable (id, cola, colb, colc)
SELECT 'a', 'b', 'c' FROM dual UNION ALL
SELECT 'a', 'b', 'c' FROM dual UNION ALL
...
SELECT 'a', 'b', 'c' FROM dual;
Jon Heller
  • 34,999
  • 6
  • 74
  • 132
1

Unlimited, as far as I can tell. Number of columns is limited to 999, but number of rows ... sky is the limit.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57