Re: Performance Question

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 12 Jan 2000 22:45:01 -0000
Message-ID: <947717605.20731.0.nnrp-10.9e984b29@news.demon.co.uk>

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.
>
Received on Wed Jan 12 2000 - 16:45:01 CST