Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 6 down vote accepted

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

share|improve this answer
    
Interesting, that makes sense now. Thanks! –  Alex Dec 14 '11 at 20:11

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
share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.