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

From: <michael_bialik_at_my-deja.com>
Date: 2000/03/07
Message-ID: <8a3q1k$80c$1@nnrp1.deja.com>#1/1

Hi.

1. It is NOT "star" type of query.
2. Post EXPLAIN PLAN.
3. Try using hints: SELECT /*+ ORDERED INDEX( B B_PK ) INDEX( C C_PK ) INDEX( D D_PK ) INDEX( E E_PK ) */ .... 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

  ( Keep the order of tables in FROM list. I assumed that the PK's names     for all tables are <tab_name>_PK ).

  HTH. Michael.

In article <38c4ddde.13346461_at_pchemmis>,   bl_at_REMOVE_THIS_TO_GET_REAL_ADDRESS.hemmis.be (Bart Loosvelt) wrote:
> 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
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Mar 07 2000 - 00:00:00 CST