Re: Oracle and Postgresql

On Wed, Sep 3, 2008 at 9:36 AM, <SCassidy(at)overlandstorage(dot)com> 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.