Re: find position of row in set of rows
Date: Mon, 1 Feb 2010 03:31:30 +0000 (UTC)
Message-ID: <hk5hui$1af$3_at_news.eternal-september.org>
On Sun, 31 Jan 2010 11:22:43 +1100, Tim X wrote:
> Agreed and well said. It is sometimes also useful to consider the
> alternative - this usually means retrieving an unordered result set into
> the application level and sorting it there. My feeling is that we can
> get better performance doing this at the database level than we can at
> the application level, even if it does represent a polution of the
> relational model.
Tim, the ordered lists are usually retrieved in order to aid pagination. Oracle can't skip N elements from the cursor, in contrast to other databases which have solved this problem long ago and one has to use "something completely different", as in the old but still legendary Monty Python's Flying Circus. First, one has to limit the size of the data returned from the database. What people coming from the MySQL world usually do is "SELECT * FROM TABLE" which, in case of Oracle RDBMS, can easily return tens of thousands of rows. Those "designers" then try to "paginate" say 300,000 rows and have the user browse through 300,000 results, sorts of what Google does.
On one hand, Oracle is rather bad at doing that sort of stuff while on the other hand, that is exactly the wrong thing to do. One should not force user to graze or browse through tens of thousands of results. The application design is wrong. That is what I attempted to tell to the OP, though in a rather convoluted and unclear way. He should return smaller sets to the application, not look how to paginate through the monstrous ones, with a decent performance.
-- http://mgogala.byethost5.comReceived on Sun Jan 31 2010 - 21:31:30 CST