Re: Performance Questions Re-Indexes

Depending on the version of Oracle you could regularly Rebuild the Indexes. This is more efficient and easily achieved.

You assumption about CBO is not exactly true. You will need to respond to the tendancy of the optimiser to perform more sequencial scans. Each operation in isolation may perform quicker, but at the cost of nicely stored data cached in the data buffers of the SGA. I would guess that the rule of thumb is that CBO likes far more memory available to work well than RBO ever did.

Regards

--
Kevin A Lewis (BOCM PAULS LTD) - Animal Feed Manufacturer - Ipswich United Kingdom)

                        <Kevin_A_Lewis_at_Hotmail.com>

The views expressed herein by the author of this document are not necessarily those of BOCM PAULS Ltd. Keith Jamieson <jamiesonk_at_phoenix.ie> wrote in message news:878uue$7lr$1_at_kermit.esat.net...
> I have a rapidly growing table at a customer site(Currently contains 1.2
> million rows). Some indexes were created for this table.
> When they were put on initially the query times returned were acceptable,
> but as the table has grown the query times have got worse. I am also
aware
> of some coding issues which need to be resolved, but initially I would
just
> like the benefits of this groups
> experience.
>
> Q1) Am I correct in assuming that the Indexes should be recreated on a
> regular basis?
>
> Q2) If we switched to the cost based optimiser, could I assume that as
long
> as the statistics were regenerated on a regular basis, then
> the performance would be better than the rule based optimiser.
>
>
>
>
>
Received on Wed Feb 02 2000 - 08:56:05 CST