I think in one of Philipp's books I saw a note about oracle automatically caching the results of frequently used queries and that a web application benefits from that without having to worry about caching query results itself most of the times. At least that's what my weak memory made of it.
I guess wether in-database caching happens and how it performs depends on a myriad of circumstances - but could somebody give me a clue, maybe by answering some of the random questions below that come to my mind:
What kind of queries are more likely to be cached by the database?
Propably database queries are more expensive in an setup like at greenpeace, where the web server is on a different machine as the database. Or is the impact of the additional network traffic neglible?
How does the database know that a particular cached query result may have been invalidated by an update? Is there some reference to table/row id's in each cache item, that are looked up upon each dml that happens in the db and in case they match, the cache item gets removed?
How do oracle and postgresql compare in that regard? Does postgresql cache query results at all?
In a ideal situation, when aolserver requests exactly the same query again and none of the involved tables have changed at all - how much faster if at all would it be to fetch the results from the aolserver cache using the -cache switch, compared to getting it from the database?
Am I totally off and in-database result caching does not exist at all?
