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: joel garry on 29 Jan 2010 16:41 On Jan 28, 10:49 am, cate <catebekens...(a)yahoo.com> wrote: > I have a table ordered by date. When I find a specific record in this > set, I want to know what its position is in this ordered list. > > I could get the date from the record found and count dates above or > below, but is there a better way? > > Thank you. You might want to test if counting the dates above and below might be the better way. Ordering comes from sorts or indices, since you say the table is ordered by date, that implies you have an index on date. So if you count the dates, Oracle might be able to get that from the index, which may be smaller and faster than even analytics on the original table (or index?), with its different possible ways of extracting then sorting. Dependent on actual data distribution and layout and code and usage, of course. Not sure why you'd need above, wouldn't you just need below? jg -- @home.com is bogus. At least it doesn't have flash... http://www.signonsandiego.com/news/2010/jan/28/shiny-gadget-icky-name-ipad-jokes-fly-on-web/
From: Mark D Powell on 30 Jan 2010 11:24 On Jan 28, 11:46 pm, Galen Boyer <galen_bo...(a)yahoo.com> wrote: > Mladen Gogala <gogala.mla...(a)gmail.com> writes: > > If you need a database to return you an ordered list, you are having > > an application design issue. > > I thought you just added an "order the naive set" clause. :-) > > -- > Galen Boyer > > --- news://freenews.netfront.net/ - complaints: n...(a)netfront.net --- "If you need a database to return you an ordered list, you are having an application design issue." I think that this statement is a little overly broad. After all the order by clause exists for a reason. It is often desired to return an ordered set of data to make the data more useful. After all it is much easier to find the desired account, name, or other valid value off an ordered drop down list than using a list in random order! Mgogala, has a point in that how you order a list can be both of questionable value and the ordering itself can be questionable based on how the dasta is stored and retrieved. Nevertheless, I will respectively disagree that ordering a set is wrong or in any way invalid in itself. It is how you order it and what you try to do with it that may be invalid. IMHO -- Mark D Powell --
From: Tim X on 30 Jan 2010 19:22 Mark D Powell <Mark.Powell2(a)hp.com> writes: > On Jan 28, 11:46 pm, Galen Boyer <galen_bo...(a)yahoo.com> wrote: >> Mladen Gogala <gogala.mla...(a)gmail.com> writes: >> > If you need a database to return you an ordered list, you are having >> > an application design issue. >> >> I thought you just added an "order the naive set" clause. :-) >> >> -- >> Galen Boyer >> >> --- news://freenews.netfront.net/ - complaints: n...(a)netfront.net --- > > > "If you need a database to return you an ordered list, you are having > an application design issue." > > I think that this statement is a little overly broad. After all the > order by clause exists for a reason. It is often desired to return an > ordered set of data to make the data more useful. After all it is > much easier to find the desired account, name, or other valid value > off an ordered drop down list than using a list in random order! > > Mgogala, has a point in that how you order a list can be both of > questionable value and the ordering itself can be questionable based > on how the dasta is stored and retrieved. Nevertheless, I will > respectively disagree that ordering a set is wrong or in any way > invalid in itself. It is how you order it and what you try to do with > it that may be invalid. > 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 -- tcross (at) rapttech dot com dot au
From: Mladen Gogala on 31 Jan 2010 14:06 On Sat, 30 Jan 2010 08:24:28 -0800, Mark D Powell wrote: > "If you need a database to return you an ordered list, you are having an > application design issue." > > I think that this statement is a little overly broad. After all the > order by clause exists for a reason. It is often desired to return an > ordered set of data to make the data more useful. After all it is much > easier to find the desired account, name, or other valid value off an > ordered drop down list than using a list in random order! > > Mgogala, has a point in that how you order a list can be both of > questionable value and the ordering itself can be questionable based on > how the dasta is stored and retrieved. Nevertheless, I will > respectively disagree that ordering a set is wrong or in any way invalid > in itself. It is how you order it and what you try to do with it that > may be invalid. Mark, I do agree with the statement that ordered lists can be really useful. Ordering and ordering relations are, after all, an integral part of both the set theory and the RDBMS software. There is, however, an issue of understanding here: what you retrieve from the RDBMS is a subset of a relation, possibly an ordered one. All other characteristics should be superimposed on that subset by the application, not the database itself. Having said that, I do find Oracle exceptionally limited in its syntax. Take a look at this: Command: SELECT Description: retrieve rows from a table or view Syntax: [ WITH [ RECURSIVE ] with_query [, ...] ] SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] * | expression [ [ AS ] output_name ] [, ...] [ FROM from_item [, ...] ] [ WHERE condition ] [ GROUP BY expression [, ...] ] [ HAVING condition [, ...] ] [ WINDOW window_name AS ( window_definition ) [, ...] ] [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ] [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ] [ LIMIT { count | ALL } ] [ OFFSET start [ ROW | ROWS ] ] [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ] [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ] where from_item can be one of: [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ] --More-- This is from PostgreSQL 8.4.2, obtained by executing "\h select" in psql. Observe the "LIMIT" and OFFSET clauses, ideal for implementing pagination. That is precisely the reason why many people are attempting to return "an ordered list". Oracle Corp. has made a duct tape fix of the situation with the ROWNUM pseudo-column and row_number() function, but that is not the same as having this capability built into the SELECT statement itself. This is very well known to Oracle Corp., the first comparison of this type was made with MySQL a long time ago. That was precisely the reason why MySQL was deemed a "web database". It was easier to manage and understand than the complex Oracle RDBMS and programming pagination was a breeze. The same thing is with cursors. -- http://mgogala.freehostia.com
From: Mladen Gogala on 31 Jan 2010 22:31
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.com |