Re: Baffling SQL failure in Oracle

From: Paul <paul_at_see.my.sig.com>
Date: Sat, 13 Aug 2005 21:08:43 +0100
Message-ID: <onjsf1l4pc4snhjvccubg705g0r9vs1pdf@4ax.com>

Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote:

> >Now, I can't for the life of me figure out why this query is failing
> >in Oracle - I think that it's perfectly reasonable to sort on a column
> >that one isn't selecting on.

> I don't agree. It is pretty useless to order on sal when you don't
> display the sal. Oracle agrees with me.

As Maxim pointed out, no it doesn't.

> Oracle != Sqlserver and whatever toy products are available .

A good explanation of why one might want such a thing.

Well, obviously you want the order, but don't care about the value. Anyone can imagine such situations. Selecting the value will of course add to the volume of data that needs to be transferred as well, and if you're on a slow internet connection and is interested in an integer value, but sorted on long (var)char values (I almost forgot that an order by may involve several fields), I guess it could be significant (imagine wanting only the phone number, but sorted on country, town, address, name and date of birth).

Xho's post was also helpful and thought provoking. The point about the aggregate DISTINCT function and which one(s) get arbitrarily eliminated is good - I had stumbled on this myself and I'm going to report it as a bug to the good folks on the Firebird site.

Firebird is not a toy db - it is a system with a long history (only being 4 or 5 years younger than Oracle), and can cope with many many situations (100's of GB, 100's of concurrent users - though I've never seen references to anything over 1 TB). That is more than adequate for a high percentage of db apps out there. I would also have to comment on its elegance - My Oracle install has eaten up the guts of 3 GB on my machine - Firebird with everything incl. sample db is not even 10 MB - IMHO, there has to something to be said for a system that can do an awful lot in such a small space.

This is not the forum for knocking Oracle, and in many ways it's a fantastic system, but I do regret at times the tendency of some Oracle professionals to look down on anything that's not Oracle and call it "toy". Firebird is not a toy (OK, well maybe MySQL is! 8-) ) and neither is PostgreSQL for example. These (and other) open source db's have a place, and that place is getting bigger AFAICS. With 64 bit computing around the corner, such systems will increasingly IMHO eat into the market share of the 3 biggies in the db market. Sure, Oracle will have the prestige blue chip clients, but it also needs the bread and butter ones to do well. Just a few thoughts.

Paul...

-- 

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2, 

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0;

When asking database related questions, please give other posters 
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.
 
Furthermore, as a courtesy to those who spend 
time analysing and attempting to help, please 
do not top post.

Received on Sat Aug 13 2005 - 15:08:43 CDT

Re: Baffling SQL failure in Oracle - anyone explain?