Oracle Insert All vs Insert

Question

In Oracle I came across two types of insert statement

1) Insert All: Multiple entries can be inserted using a single sql statement

2) Insert : One entry will be updated per insert.

Now I want to insert around 100,000 records at a time. (Table have 10 fields with includes a primary key). I am not concerned about any return value.

I am using oracle 11g.

Can you please help me with respect to performance which is better "Insert" or "Insert All".

Answer 1

Insert statement and insert all statement are practically the same conventional insert statement. insert all, which has been introduced in 9i version simply allows you to do insertion into multiple tables using one statement. Another type of insert that you could use to speed up the process is direct-path insert - you use /*+ append*/ or /*+ append_values*/(Oracle 11g) hints

insert /*+ append*/ into some_table(<<columns>>)
  select <<columns or literals>>
    from <<somwhere>> 

or (Oracle 11g)

insert /*+ append_values*/ into some_table(<<columns>>)
   values(<<values>>)

to tell Oracle that you want to perform direct-path insert. But, 100K rows it's not that many rows and conventional insert statement will do just fine. You wont get significant performance advantage using direct-path insert with that amount of data. Moreover direct-path insert wont reuse free space, it adds new data after HWM(high water mark), hence require more space. You wont be able to use select statement or other DML statement, if you has not issued commit.

Answer 2

I know this is kind of a Necro but it's pretty high on the google search results so I think this is a point that worth making.

Insert All can give dramatic performance benefits if you are building a web application because it is a single SQL statement that requires only one round trip to your database. In most cases although far from all cases. the majority of the cost of a query is actually latency. Depending on what framework you are using, this syntax can help you avoid unnecessary round trips.

This might seem incredibly obvious but I have seen many, many production web applications in large companies that have forgotten this simple fact.