4

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 (?, ?, ?);
Ori Marko
  • 56,308
  • 23
  • 131
  • 233

2 Answers2

2

Best practice is, that you ALWAYS define the columns in insert statements, NOT for the performance sake(there is no difference), but for situation like this:

  • You create table test1 with columns col1, col2;
  • You insert data in that table in your procedures/etc, without naming the columns;
  • You add new columns, col3, col4;
  • The current logic will fail with insert, errors will be raised.

So, to avoid the failure, always name the columns, then your code doesn't brake,when you modify the table structure.

Ychdziu
  • 435
  • 5
  • 10
  • By refer, you mean actual links in documentation? P.S. You don't trust our shared hard gained experience? Joking.. This can be discussed as the one of "those" questions - "Better to use OR operator or IN. Who performs better?" – Ychdziu Aug 08 '18 at 06:27
  • I think you are correct, but I want to know specifically is performance effected – Ori Marko Aug 08 '18 at 06:31
2

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?

Kamil Gosciminski
  • 16,547
  • 8
  • 49
  • 72
  • The question/answers are about `MySQL and PostgreSQL` – Ori Marko Aug 08 '18 at 06:38
  • I really can't imagine Oracle working the other way. Columns have their constraints and modifiers: foreign keys, not null, ... I don't see how database should know about all this without explicitly checking that. Also, documentation does not mention this. There's nothing about it in here either: http://www.dba-oracle.com/t_insert_tuning.htm – Kamil Gosciminski Aug 08 '18 at 06:51