You will confuse people by calling this
a star join. What is the current execution
plan ? Nested Loops or merge/hash joins ?
--
Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
Bart Loosvelt wrote in message <38c4ddde.13346461_at_pchemmis>...
>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
