Re: Performance Questions Re-Indexes

In article <878uue$7lr$1_at_kermit.esat.net>,   "Keith Jamieson" <jamiesonk_at_phoenix.ie> wrote:

> Q1) Am I correct in assuming that the Indexes should be recreated on a
> regular basis?
>

 Depends the number of updates/deletes in your table - it causes index  "stagnation" ( a lot of dead space inside ) that affects INDEX SCAN  performance. But don't expect great improvements ( I got after  index reorg of 2.5M rows table and 20% of wasted space, before reorg,  improvement of about 5-7% only ).
 Try to verify instead that SQL statements use as much index fields as  possible during INDEX SCANs.

> 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.

 No. CBO may ( sometimes ) make better decisions based on data stats,  but if your query is already using correct access path with RBO - CBO  will NOT improve it.

 HTH. Michael

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Feb 02 2000 - 15:06:12 CST