Re: Performance for sub-select or joins

eugen wrote:
>
> Hi
>
> we just had an arguement about to use sub-selects instade of joins and
> i'm wondering what your opinions are, regarding this matter.
> The tables are about 100 rows large and the OS is UNIX.
> In my opinion the join is faster and according the explain plan
> i'm still better of with the join, but is there any reason why i
> should use a sub-select ?
>
> I know that there could be a problem when the sub-select returns
> more than one row.
>
> any suguestions ??
>
> tia
> eugen
>
> view1 with join
>
> create or replace view xyz (field1, field2, ....)
> as select a.field1, b.field2, ...
> from table a, table b, table c
> where a.primary_key = b.foreign_key1
> and b.foreign_key2 = c.field
> and c.primary_key = variable;
>
> against
>
> create or replace view xyz (field1, field2, ....)
> as select a.field1, b.field2, ...
> from table a, table b
> where a.primary_key = b.foreign_key1
> and b.foreign_key2 = (select c.field
> from table c
> where c.primary_key = variable);
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

There are always some exceptions on a case by case basic, but I'm a big fan of joins, since typically a sub-select reduces you to nested loop, whereas joins can take advantage of hash joins which tend to fly along.

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse