Re: Help about joins

Hi,

I worked with DB2 (as non dba) on OS/390 since 1996, it also had the outer join syntax (starting with version 4, if i remember right). When I started with Oracle, I really missed this syntax... Perhaps therfore my little sarcasm :-)
Btw. (no it doesn't relate with oracle), in DB2 a left/right outer join could be performed much faster using a full outer join and then throw away the data which was too much
i.e.
select cola,colb
from a full outer join b on cola=colb
where cola is not NULL -- throws away all rows too much

was (in much cases) faster than/then/as (heaven, english lessons are far away)

select cola,colb
from a left outer join b on cola=colb

The reason was, that the optimizer used nested loop joins in the secdond case, and merge join in the first ...
In one case, a query with left outer join needs more the 10millions buffer gets, with the outer join it was just 200.000 (factor 50!) I'm really curious if there are such impacts in Oracle 9i too ... However, offtopic ...

Greetings
Thomas

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:<tonuv3ahnleab8_at_news.demon.nl>... ...
> > thats right for oracle before 9i.
> > But it's unbelievable, oracle just made it to get more and more ansi
> conform :-)
> > and itroduced left/right/full outer join
>
> As SQL92 has several levels, and all vendors except one (guess which one,
> right : Microsux) conform to the lowest level, I wouldn't state Oracle
> doesn't conform to Ansi SQL. It does, albeit to the entry level only.
> I definitely find the join syntax in sqlserver very confusing, and I will
> probably never use it, unless forced too.
> But then, as the join syntax is too difficult and too confusing to remember,
> Microsux provides a query builder.
>
> Regards,
>
> Sybrand Bakker, Senior Oracle DBA
Received on Wed Aug 29 2001 - 02:29:20 CDT