Conference ulysse::rdb_vms_competition

983.0. "outer join in Rdb" by BER::MOENCH (Andreas Moench @BEO, Berlin) Tue Sep 03 1991 17:54

 Hello World -

      A partner of ours (SystemConslut, Berlin) is doing a conversion
      from Oracle(5.2??) to Rdb (4.0).

      Among others, they ran into a problem emulating oracles OUTER JOIN	
      functionallity.

      (
      Oracle does provide a build in outer join , syntax is something
      like
      SELECT TABLE_OUTER.F1, TABLE_OUTER.F2, TABLE_INNER.F2
      FROM TABLE_OUTER, TABLE_INNER
      WHERE TABLE_OUTER.F1 = TABLE_INNER.F1(+)
      ORDER BY TABLE_OUTER.F1;
      
      where the (+) marks the inner relation, adding temporary NULL colums
      to it wich are matched with NULLS in the outer table, so that all records
      of that table are displayed, too
      {hope I din't mix "outer" and "inner" here...]
      )

      I presented them with a solution using two UNIONed selects 
      like

      SELECT TABLE_OUTER.F1, TABLE_OUTER.F2, TABLE_INNER.F3
      FROM TABLE_OUTER, TABLE_INNER
      WHERE TABLE_OUTER.F1 = TABLE_INNER.F1
      UNION
      SELECT TABLE_OUTER.F1
      FROM TABLE_OUTER,
      WHERE TABLE_OUTER.F1 NOT IN 
      	( SELECT TO.F1 FROM TABLE_OUTER TO,  TABLE_INNER TI
      		WHERE TO.F1 = TI.F1)
      ORDER BY 2;


      Well - for 5000-10000 records in the outer table, oracel needs about 
      10-15 seconds;  rdb needs about 1 hour....

      Sure there are some ways to tune the DB (he didn't konw if he had all
      needed indexes definied ect.), but in generell, I'd bet the 
      "select-union-select" solution will be sicnificantly slower than the
      oracle implementation.

      What SystemConsult did now was splitting up the query into two, 
      building two loops (using a 3gl and ESQL)

      read the inner table record
      	for each found key
      	read the outer table till no record is found.
      read next inner record

      Problem with that solution: it is reasonable fast (20 seconds), 
      but the sort-order gets mixed up if you don't not want to sort after 
      the key-field (F2 instead of F1 here).

      Question:

      Has anybody any ideas how else an outer join could be 
      represented in one (faster) query?

      BTW: Who has experience with oracle: Does oracles outer-join return
      	   the correct results?


      All input will be appreciated!
      

Regards, Andreas
      

      PS: the actual querys look a little bit more complicated (lots of where
          clauses, lots of SQL*PLUS syntax ...

      (Crossposted in : RDB40, SQL, RDB_COMPETITION, DB_CONVERSION)

Your replacement SQL asks for more work than it needs.
I don't know if this version would perform better, but it should:

      SELECT TABLE_OUTER.F1, TABLE_OUTER.F2, TABLE_INNER.F3
      FROM TABLE_OUTER, TABLE_INNER
      WHERE TABLE_OUTER.F1 = TABLE_INNER.F1
      UNION
      SELECT TABLE_OUTER.F1 (, ...more?...)
      FROM TABLE_OUTER
      WHERE not exists 
      	( select ti.f1 from table_inner ti
      		where t1.f1 = table_outer.f1)
      ORDER BY 1;
               ^== I assume you meant 1 here

Your version was asking Rdb to search a joined table to see if F1 values were
present.  This version asks it to search TABLE_INNER instead.  What indices
did you provide, and how large were the relations?  What indices did the Oracle
database have?  If you split up the UNIONs, how long does each part take?