Originally posted by ssinha
SOmoene suggested that inline views are very slow and degrades performance. Is this true ?
Uh, no.

As for the optimal solution, I would still propose the ROWNUM solution. However, it would depend upon certain variables.

The reason the ROWNUM solution works so well is that the optimizer utilizes a STOPKEY method, which simply means that it will stop sorting once it reaches the upper limit (in your case, 600). So, for a single page, a lot of sorting has been avoided. However, if your user is going to end up paging through *the entire* resultset, then we will have actually performed more querying and sorting than if we only pulled the entire resultset once. In other words, if you simply selected all 100,000 rows into a local buffer and scrolled the user through that buffer, you would only have run the query once and sorted the resultset once. However, it is generally true that in *most* searches, where windowing is usually used, very few users will page through more than 5 pages. Most will only page once, maybe twice. Therefore, only sorting and returning the bare minimum number of rows is a better solution. If the user will always use a majority of the resultset, then a single mass query is preferred.

However, one must ask oneself how much use 100,000 rows of information will be to *anyone* on-line. Such massive queries are best left to nightly batch reporting, not on-line queries that the user scrolls through (windowing).

Food for thought,

- Chris