Re: Example code for an outer join for Oracle?

From: Farah Ali <ALIF_at_erols.com>
Date: 1997/10/20
Message-ID: <344C12EF.4674@erols.com>#1/1

Beware of Outer-joins!!

These are very useful. However, at times cause full table scans on the table being outer-joined. That is, in the example below, the table2 will result in a full-table scan even if you specify an index hint.

In order to get around this performance problem, you can use in-line SQL functions. For example,

1. Create a stored function, such as,

create function get_field2 ( vid in table2.id%type ) return field2type
as
  cursor c_field2 is
    select /*+ index(table2 table2-index-name) */ field2

      from table2
      where id = vid;

  output_field2 table2.field2%type;
begin
  open c_field2;
  fetch c_field2 into output_field2;
  close c_field2;   

  return output_field2;
end;
/

2. modify the select statement to make a call to the above function:

  select table1.fielda, get_field2( table1.id ) fieldb     from table1;

This will produce the same result. But, the performance gain in this case is quiet significant.

-Farah

R.Schierbeek wrote:
>
> Hey Mark,
>
> How about:
>
> select table1.fielda, table2.fieldb
> from table1
> ,table2
> where table1.id = table2.id(+)
>
> ------------------------------
> Roelof Schierbeek, DBA
> Bytelife BV
> The Hague, Holland
> email: bytelife AT worldonline.nl http://home.worldonline.nl/~bytelife
> check out : http://home.worldonline.nl/~bytelife/storage.htm
>
> Mark Cain <mark_at_alimar.demon.co.ukNOSPAM> schreef in artikel
> <3448d967.3775955_at_news.demon.co.uk>...
> > Can anyone give me a simple example of an outer right join, using 2
 tables?
> >
> > I'm trying something like:
> >
> > select table1.fielda, table2.fieldb from table1 outer join table2 on
 table1.id = table2.id
> >
> >
> > Thanks in anticipation
> > ---------------------------------------------------
> > | Mark Cain mark_at_alimar.demon.co.uk (remove NOPSAM)
> > | www.alimar.demon.co.uk
> > ---------------------------------------------------
> >
  Received on Mon Oct 20 1997 - 00:00:00 CDT