Have Geek Brain

Understanding database inputs

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

insert into a
select value from b;

How is this achieved?

My understanding: the rows that are present at the point in time when the statement starts executing can be picked up and inserted into table a. At the same time, new values ​​can be inserted into table b without being "read" because the query has already started executing.

As far as I understand, as far as I understand? Any reference documents on this are greatly appreciated.

Thanks!

+1


a source to share


5 answers


The answer for most modern databases is multiversion concurrency control .



Basically, each line is timestamped from the moment it is visible. The isolation level is then selected to see if rows added by transactions that were committed before the current statement (for read with fixed isolation) or before the current transaction (for serialization) should be visible for selection.

+1


a source


Since you are not talking about any engine in particular, this can happen. There could also be a point where the database just fetches a row at a time. It all depends on the mechanism and locks applied to the database.



+1


a source


"New values ​​can be inserted" depending on the isolation level; for example, if it's serializable, which won't happen.

0


a source


I think there is a difference in the database, but I can give a general answer for most of them. When performing "insert as select", the RDBMS is sent and executes the SELECT statement. Like any other SELECT statements, the results will be stored in a "virtual table" in memory (each database and its own cache and memory management). The INSERT statement then becomes a normal multi-line INSERT statement, since the results in memory behave exactly like the data that would be provided via the command line. At this point, if any new row is inserted into the "selected" table, it will not affect the INSERT statement. Finally, if the SELECT outputs too many rows in the result, or refers to a locked table, things can change.as RDBMS will select values ​​differently.

0


a source


SQL Server Selectivity (uses indexes you might 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 are about index selectivity) - http://www.akadia.com/services/ora_index_selectivity.html - http://courses.csusm.edu/cs643yo/slides/optimization.htm (tell us about the architecture you might find it helpful here)

0


a source






More articles:


All Articles