Hibernate version: 3.1.x
Name and version of the database you are using:Oracle 9x

I have a large collection that I can't afford to load into memory. I would say around 100,000 objects in the collection and I have many(100s) of these collections. That collection needs to be pageable as well as sortable in many different ways. The objects gets deleted/updated quite often and new objects gets added to the collection often as well.

I have decided to run a query against the database on each http request.

So far I have optimized the query with all the possible sorting order with a corresponding db indexes and using setMaxResult and setFirstResult in my query interface in order to page it.

The query is relatively fast.

The problem now is that it is getting too expensive to query the large table with the volumn of http request. Ideally I would like to cache the data.

The first thing that comes to my mind is Query Cache, well, the default implementation is quite useless in my case as the collection gets updated/deleted/inserted quite often. I thought of writing my own Query Cache implementation by making it more stale tolerant(e.g, ignore updates, let the query cache timeout, inserts only expire certain query result). It seems like a good idea until I realize that the multiple sorting requirement will make the expiration of query cache quite difficult, since new objects might appear on pages in the middle of collection.

I would think that issues like this would appear quite often with such a large number of people using Hibernate. If you guys have encounter similar problem before I would love to hear what solution you have.

I want to thank those of you who spend the time to read my issue.