Dynamic temporary index creation


In my undergrad databases course, my professor mentioned that some DBMS software is so advanced that it can detect when a long-running query would benefit from an index. The DBMS can then create that index for the duration of the query to increase performance.

However, I don't think that's possible, and some initial Googling seems to agree with me. Does anyone know of a DBMS that actually implements this strategy?

Best Answer

Hardly "so advanced"; this is quite a common feature. Your professor is behind the times! :-) SQLite is one of the simplest databases, yet see section 11 on this page.

Since the cost of constructing the automatic index is O(NlogN) (where N is the number of entries in the table) and the cost of doing a full table scan is only O(N), an automatic index will only be created if SQLite expects that the lookup will be run more than logN times during the course of the SQL statement