Re: Performance Question

Hi.

 I think that the problem is inside the outer SELECT :   It calls sub-select 2152377 times !!!

 The sub-select itself is OK :
   each time it retrievs 175M / 2M ~ 90 rows.

 Check the join conditions of table A and B of outer select and try  to limit the number of joined rows ( may be by using another index  or by specifying additional fields in join ).

 HTH. Michael.

In article <947717605.20731.0.nnrp-10.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> Spot on with that one.
> Given the size of the numbers, and the fact
> that the report says 'ANALYZED' and the optimiser
> mode is CHOOSE, I'd guess that the
> analysis was done when the dataset was
> very small, otherwise Oracle would (almost
> certainly) not choose to used an indexed access
> if it expected to pick up this many rows.
>
> 1st step - analyze the table to 1 percent, or
> even 2000 rows and see what happens.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Sybrand Bakker wrote in message
> <947711881.2539.0.pluto.d4ee154e_at_news.demon.nl>...
> >from the explain it is clear your problem is in the subquery.
> >This is the explain for the subquery
> >2152377 SORT (AGGREGATE)
> >> 2152377 NESTED LOOPS
> >> 174874176 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF
> >> 'RT_RATE_TBL'
> >> 174913848 INDEX GOAL: ANALYZED (RANGE SCAN) OF
> >> 'RT_RATE_TBL_IDX01' (NON-UNIQUE)
> >> 2160396 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
> >> 'RT_INDEX_TBL'
> >> 2160396 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
> >> 'RT_INDEX_TBL' (UNIQUE)
> >>
> >Looks like there is some inefficient indexing.
> >Without looking at the definition of that view (which is resolved
into the
> >query) I can't provide a more detailed answer.
> >
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jan 13 2000 - 15:18:13 CST