Plase help: outer join referencing multiple columns

I'm writing a multi-database-platform application utilizing alternatively
MS-Access, SQL-Server or Oracle.

My SQL statements so far work very well on the first two databases but performance on Oracle is dreadful.

I have isolated the origin of my performance problems: The outer join referencing multiple colums.

The offending statement runs like this:

select t1.col1, t1.col2, t1.col3, t2.col1 from table1 t1, table2 t2 where (( t1.colx (+) = t2.colu ) and ( t1.coly (+) = t2.colv ) and ( t1.colz (+) = t2.colw ))
and ( t1.time1 between '01-JAN-96' and '31-JAN-96' )

t1.colx, t1.coly, t1.colz is a combined index on table1 as well as t2.colu, t2.colv, t2.colu
is on table2. time1 in an index on table1.

Consider there are 20 entries in table1 satisfying the time1 restriction and 10 of these
have values in their colx .. colz corresponding to colu .. colw in table2.

What I would expect as a result of this query are 20 rows with 10 of them having a null
in t2.col1.

What I get (after a lenghty silence of Oracle) are those 10 rows which satisfy the inner
join conditions.

I get the same results when I edit out the (+)'es-only in much shorter time.

Since my approach works nicely on MS-Access and SQL-Server ( I get my 20 rows in no time
at all) it can not be fundamentally wrong.

There is, of cause, a misunderstanding of the working of Oracle on my side, which perhaps
some willing Oracle guru can help me sort out.

The manual (SQL-Language-Reference) is no help since it only mentions one column outer joins. Received on Sat Jun 29 1996 - 00:00:00 CEST