How do I tune 'star' outer join in ORA73?

Hello,

(ORACLE 7.3!) I have a situation in which I have a table A with the following columns

PK_A int not null primary key
FK_B int null foreign key(B)
FK_D int null foreign key(D)
FK_E int null foreign key (E)

I want to perform a 'star' outer join to the tables A, B, C and D like follows:

A ---> B ----> C
\____> D

|_____> E

That would result in a statement like
SELECT ....
FROM A, B, C, D, E

WHERE B.PK_B (+) = A.FK_B
  AND C.PK_C (+) = B.FK_C
  AND D.PK_D (+) = A.FK_D
  AND E.PK_E (+) = A.FK_E

That works, but it is horribly slow although A only has 1800 rows. B has only slightly less rows than A but the other tables have only a few 100s rows. I would like to improve the performance of this query, but have not found a solution. Has anyone previous experience with optimizing this kind of query in 7.3? I'm not an Oracle expert, just a caring developer 8?) so I need experts for this.

I tried ANALYZING all tables, columns and indexes that appear in the query, but that doubled execution time instead of reducing it. Duh!

(As a sidenote, I have the _impression_ that ORA73 outer join performance is not the best you can find... Is the situation better in ORA8? No flames please, I'm just curious)

Best regards,
Bart Received on Tue Mar 07 2000 - 00:00:00 CST