Understanding database inserts

Question

Most databases support some form of "insert into select..." statement.

insert into a
select value from b;

How is this being achieved?

My understanding: The rows that are present at that point of time when the statement starts execution qualify to be picked up, and they are inserted into table a. At the same-time new values can be inserted into table b and they would not be "considered" since the query has already started execution.

Is my understanding close to being accurate? Any reference docs on this greatly appreciated.

Thanks!

Answer 1

"New values can be inserted" depending on your isolation level; for example if it is serializable that will not happen.

Answer 2

Since you are not talking about any engine in particular, that could be happening. Also there could be a point where the database just pick a row at the time.. It's all depends on the engine, and the locks applied to the database.

Answer 3

I guess there are database specific difference, but I can provide a general answer for most of them. When performing a "insert as select", the RDBMS would go and execute the SELECT statement. Like any other SELECT statements, the results would be stored in a "virtual table" in the memory (each database and its own cache and RAM management). Then, the INSERT statement becomes a normal multi-rows INSERT statement, as the results in the memory behaves exactly like data which would be provided via the command line. At this stage, if any new row would be inserted to the "selected" table, it will not affect the INSERT statement. Finally, if the SELECT yields too many rows as a result, or would refer to a locked table, things could change, as the RDBMS would select the values differently.

Answer 4

SQL Server selectivity (makes use of indexes which you'll want to look at as well) -http://blog.namwarrizvi.com/?p=157 -http://www.sqlsolutions.com/articles/articles/How_Values_with_Irregular_Selectivity_Impact_SQL_Server_Database_Performance.htm -http://sqlserverpedia.com/blog/sql-server-bloggers/index-columns-selectivity-and-inequality-predicates/

Oracle selectivity (again these articles refer to index selectivity) -http://www.akadia.com/services/ora_index_selectivity.html -http://courses.csusm.edu/cs643yo/slides/optimization.htm (talks about architecture, might be more useful for you here)

Answer 5

The answer for most modern databases is multiversion concurrency control.

Basically each row has a timestamp from what instant it is visible. The select then considers the isolation level to see if rows added by transactions that have committed before the current statement (for read committed isolation) or before the current transaction (for serializable isolation) should be visible to the select.