find position of row in set of rows
in [Oracle]
|
Prev: performance comparision
Next: adding a constraint to child table that references a column of theparent table that is not part of the primary key
From: Tim X on 1 Feb 2010 01:45 Mladen Gogala <gogala.mladen(a)gmail.com> writes: > 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
From: Robert Klemme on 1 Feb 2010 12:37 On 29.01.2010 20:58, Mladen Gogala wrote: > On Fri, 29 Jan 2010 18:01:18 +0100, Robert Klemme wrote: > >> I don't fully agree: certainly relational DB and SQL started out at >> relational algebra, but there are significant features (even in standard >> SQL) that are not really covered by set theory (just to name ORDER BY >> and the Oracle specific CONNECT BY). > > That is because of the infidels who have polluted our, otherwise pure, > mathematical theory. That's nothing that a good jihad couldn't sort out. > Kantor, Zermelo and Goedel must be turning in their graves. I wonder whether we can make better use of that kinetic energy than jihad... How come I never noticed that you are such an idealist? Actually, I would have thought that idealism is rather a hindrance for a DBA and that the job rather calls for pragmatism. :-) Cheers robert -- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/
From: Mladen Gogala on 1 Feb 2010 12:50 On Mon, 01 Feb 2010 18:37:29 +0100, Robert Klemme wrote: > I wonder whether we can make better use of that kinetic energy than > jihad... > > How come I never noticed that you are such an idealist? Actually, I > would have thought that idealism is rather a hindrance for a DBA and > that the job rather calls for pragmatism. :-) > > Cheers > > robert I was kidding. I explained my remark in the discussion with Tim X. Sometimes my irony gets better of me and I behave like a cynical brat, sort of lord Baldemart. I am not a jihad type. I am a chubby middle aged computer engineer, wearing glasses and with weird sense of humor. In unlikely case that my "lord Baldemort" remark needs explanation, the explanation is here: http://www.dilbert.com/2010-01-31/ -- http://mgogala.byethost5.com
From: Robert Klemme on 1 Feb 2010 16:49
On 01.02.2010 18:50, Mladen Gogala wrote: > On Mon, 01 Feb 2010 18:37:29 +0100, Robert Klemme wrote: > >> I wonder whether we can make better use of that kinetic energy than >> jihad... >> >> How come I never noticed that you are such an idealist? Actually, I >> would have thought that idealism is rather a hindrance for a DBA and >> that the job rather calls for pragmatism. :-) > > I was kidding. I know, I know - hence the smiley. I just tried to continue the joke a bit - obviously with moderate success only. :-) > I explained my remark in the discussion with Tim X. > Sometimes my irony gets better of me and I behave like a cynical brat, > sort of lord Baldemart. I am not a jihad type. I am a chubby middle aged > computer engineer, wearing glasses and with weird sense of humor. In > unlikely case that my "lord Baldemort" remark needs explanation, the > explanation is here: http://www.dilbert.com/2010-01-31/ :-))) robert -- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/ |