Dynamic temporary index creation

Question

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?

Answer 1

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

Answer 2

Try to improve your google foo, here are some results

Here are some terms:

• it is a query planner/optimizer's job to understand when it is best to use an index, which means it should understand when it is can be better to even build one temporary; see here
• try to check also not only for "temporary indexes", but also for "hash tables" (well, depending on your definition of temporary index); see some details here

Answer 3

This kind of behaviour is pretty much what you get with spooling. SQL Server can push data onto a spool, and then pull it back off again elsewhere in the query plan, taking advantage of the order that it's in.