On Wed, Sep 3, 2008 at 9:36 AM, <SCassidy@xxxxxxxxxxxxxxxxxxx> wrote: > > I don't know if Oracle changed recently, but the last few times I used it, > it was incredibly annoying having to put everything in a subquery to get a > LIMIT-type operation to work AFTER the sort, so that you could use their > ROWNUM. For example, to get the first 50 rows of a SELECT result. Their > ROWNUM worked BEFORE the ORDER BY, so to get the 1st 50 rows, you had to put > the query in a subselect, and say SELECT ... where ROWNUM <= 50. > > I love OFFSET ... LIMIT in PostgreSQL! I do a lot of web applications, and > it is incredibly handy to page output with. Yeah, those kinds of things drove me a little crazy. I think PostgreSQL's equivalent parsing peculiarity is the need to put certain things in an extra set of parens for creating an index. But really that's pretty minor compared to rownum versus order by firing order. One of the things an Oracle DBA coworker of mine found really bothersome at first in pgsql was that suddenly on large queries that he'd never needed an order by on Oracle, he was getting randomly ordered reporting data. He was flabbergasted that something would use an aggregation method that wouldn't result in an ordered set. He was sure it would be a loser with an order by but it was very very fast on large numbers of rows. I think that if you're really used to one database you'll find a new one bothersome in some ways, and a pleasure to use in others. Once I had installed rlwrapper on sqlplus I was ok. It wasn't in the same league as psql, which is the greatest command line sql monitor ever created, but having line editing and history made sqlplus usable. It was cool to hit !alter sess and get my previous default schema statement. All the Oracle DBAs around me were working in guis and they'd walk by and think I in psql, I'd ask them to look at something, like a query, and it would take them about 10 seconds to realize I was working on oracle from the command line. If I could ask for one thing from Oracle it would be that they borrow the basic operational characteristics of psql and stuff it into sqlplus or whatever they use. Having the \ commands and expanded versions in sqlplus would have been awesome.
