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

From: <michael_bialik_at_my-deja.com>
Date: 2000/03/08
Message-ID: <8a6h1p$7dq$1@nnrp1.deja.com>#1/1

Hi.

I just recreated your case.
The problem is COST BASED OPTIMIZATION.

Try using hint /*+ RULE */ or deleting statistics for ALL involved tables.
You are supposed to get :
  NESTED LOOP ( OUTER )
    TABLE ACCESS ( BY INDEX ROWID ) OF A       INDEX ( FULL SCAN DESCENDING ) PKC_A ( UNIQUE )     TABLE ACCESS ( BY INDEX ROWID ) OF J       INDEX ( UNIQUE SCAN ) OF PKC_J ( UNIQUE )   etc (
   NESTED LOOP ( OUTER )    TABLE ACCESS ( BY INDEX ROWID ) OF x

INDEX ( UNIQUE SCAN ) OF PKC_x ( UNIQUE )

)

In my case ( Oracle 8i/NT )

  Without stats/with /*+ RULE */ hint -  30 msec
  Without any hints ( CBO )           - 640 msec
  With INDEX hints                    - 960 msec.

HTH. Michael.

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