Re: find position of row in set of rows

> 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.

We probably all need to be a bit clearer!

I don't think my point contradicts yours. I wasn't referring to any attempt to only return part of the results from a cursor. As you indicate, the design should facilitate result sets that are results of interest and not large result sets that only have a few results of interest. If you cannot restrict the result set to the actual set of interest, then there is a design problem.

In addition to that, my point was that sorting the result set at the database will almost always be faster than sorting it in some general application language.

Probably really preaching to the (largely) converted, but developers need to be encourage to use the facilities of the db rather than do a poorer and likely bug filled duplication of existing functionality.

The other point was that in some cases, adding certain capabilities to the database which may be seen by some as a polution of the relational model can be justified if the functionality provides something that is frequently needed and can be done more efficiently and it does not completely break the underlying relational model.

In the end, there doesn't seem to be any significant differences in the majority of the opinions here.

Tim

-- 
tcross (at) rapttech dot com dot au