Re: Fav. Urban Legend...

Jonathan Lewis wrote:

>
> So - when you get to that tricky query which
> looks as if it could be unnested, but you can't
> quite figure out how, maybe all you need to
> do is turn:
> select
> from
> where
> ..... (select colx
> from ....
> where ...
> )
>
> into
> select
> from
> where
> ..... (select /*+ unnest */ colx
> from ....
> where ...
> )
>
> and if Oracle can unnest the query, Oracle
> will unnest the query;

Jonathan, beware, you are sowing urban legends of your own. I think that if you can't figure out how to unnest, you'd rather try harder or subcontract to somebody else. You know like me that most applications currently running started development with Oracle 7 or Oracle 6, but for those which were migrated from Oracle 5 or earlier. Which means that today's developments will probably last till Oracle 12 or beyond. I wouldn't bet on the future of hints, when you see how few (documented) init.ora parameters have survived since the early times. I appreciate the magical hint as much as anybody, especially in the hit-and-run kind of consulting assignment. But when you rely on hints or init.ora parameters for performance at some critical sections, you're taking a risk from a development (as opposed to crisis tuning) point of view. When the next Oracle release is applied, I am ready to bet that the folks who will try to solve the performance problem then (long after the departure of the initial developers), under stress and in a stormy atmosphere will spend much more time that those lazy programmers would have in the first place. Moreover, I am not sure that everybody makes a difference, even on this list, between 'I cannot figure out how to unnest, let's add a hint' and 'I cannot figure out how to remove duplicates, let's add a DISTINCT'.

As far as urban legends are concerned, I don't think that _all_ deserve the scorn which has been heaped on them so far. There are some fallacies which are such only because they are taken as an absolute truth. Some may be true 80% of the time, which is not that bad, and most may be idiotic but innocuous (granted, that's when you come to problem solving that you are lost). Of course taking first degree approximations for the ultimate truth is wrong, but as long as you stay on the straight and narrow path ... Newtonian mechanics has it flaws, but in any case was enough for sending men to the Moon.

My 0.02 euros.

-- 
Regards,

Stephane Faroult
Oriole Ltd
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).