Index Maintenance, when do we do rebuilds ?

[mwellbro]

Hi guys,
if I recall correctly there was a threshold in place that indexes would be rebuild on DML once DML changed more than x-% of data in a table - is that still correct with V.6.2.8 ? Stumbled upon a scenario where someone inserted a few billion records and I´d have thought that the INSERT part would finish and then there would be INDEX REBUILD steps but instead it took place as INDEX INSERT...or did the REBUILD-part only apply to UPDATEs ?

Cheers,
Malte

[exa-Uwe]

Automatic index rebuilds occur upon DELETE (over 25% rows marked as deleted triggers the rebuild, you can see DELETE_PERCENTAGE in exa_dba_tables) and upon UPDATE (over 15% rows affected by an update triggers the rebuild).

INSERT doesn't trigger index rebuild.

Best regards

Uwe

[mwellbro]

Ok, and since we are not "shifting data around" when INSERTs happen there probably isn´t much to gain if we were to rebuild instead of plainly INSERT´ing ( things like index node splits and the like probably don´t cost much in this case ).

@exa-Uwe : if you get rid of the "AFAIK" , I´ll mark your answer as solution - deal ? 😄

[exa-Uwe]

Deal (after having doublechecked with development guys 😉 )