Re: Query Optimization

On Thu, 14 May 1998 18:51:19 GMT, sanjay.raj_at_roche.com wrote:

>In article <355A7DE1.C77D5423_at_spacestar.com>,
> John Flipse <jflipse_at_spacestar.com> wrote:
>>
>> Hello -
>>
>> I am trying to optimize an Oracle query, but am running into issues with
>>
>> the optimizer. We are running on Oracle v7.1, using the cost based
>> optimizer.
>>
>> I have put indices on all three tables involved in the query, but there
>> always seems to be a full table scan on at least one of the tables. I
>> have
>> executed the query plan on the tables both empty and full of data, with
>> statistics and without, but none of these factors seem to make any
>> difference.
>>

I've run into a similar issue with 7.3.2. If I use CBO, OLTP type queries run very fast but report type queries very slow. If I switch to RBO, OLTP runs slow and reports fast. I don't have a good solution yet.

One suggestion for you is if your indexed values are not evenly distributed, create histograms on the indexes. This may help.

--
Chuck Hamilton
chuckh_at_dvol.com

If at first you don't succeed, sky diving isn't for you.