Search code examples
oracleoracle12cdatabase-performanceinsert-into

Is insert statement without column approve performance in Oracle


I'm using Oracle 12. When you define insert statement there is option not to state column list

If you omit the column list altogether, then the values_clause or query must specify values for all columns in the table.

Also it's describe in Ask TOM when suggesting a best performance solution for bulk:

 insert into insert_into_table values ( data(i) ); 

My question, is not stating columns really produce a better or at least equal performance than stating column in statement as

insert table A (col1, col2, col3) values (?, ?, ?);

Solution

  • From my experience there is no gain in omitting column names - also it's a bad practice to do so, since if column order changes (sometimes people do that, for clarity, but they really don't need to) and their definition allows to insert the data, you will get wrong data in wrong columns.

    As a rule of thumb it's not worth the trouble. Always specify column list that you're putting values into. Database has to check that anyways.

    Related: SQL INSERT performance omitting field names?