Idxcompact Compaction Factor

[jdpjamesp]

Is there an ideal compaction factor for idxcompact? Is the default 80% sufficient or should I set it higher?

 

[Rob Fitzpatrick]

I'd say it really depends on your data. If a table was static then a higher factor would be better as you'd get more keys per block, and more efficient I/O. Mind you, static tables tend to be small so it may not make too much of a difference.

For fast-growing tables, which will have relatively large indexes, you want to allow space within the blocks so you can later do creates without incurring a lot of expensive block splits. That's why the default is 80 and not 100. In my opinion 80 should also be the default value of -pfactor on idxbuild, although the current default there is 100.

 

[jdpjamesp]

Thanks Rob. That's helpful. Unhelpful at the same time as I was hoping to get a one-stop shop on it so I could easily write a script!

 

[TheMadDBA]

80 is usually acceptable in the majority of cases. Indexes on small static tables almost never get decent index compaction because of the small number of records and the numerous distinct values.

 

[jdpjamesp]

Thanks Keith. Much appreciated.

 

[jdpjamesp]

Is idxcompact something you'd run out of hours to avoid any performance issues for users?

 

[TheMadDBA]

Off hours would be much better for larger to medium sized indexes. Reading the records will have minimal impact but if your index is fragmented (lots of updates/deletes) idxcompact can generate quite a bit of BI/AI activity.

The more often you run idxompact the impact of each run should diminish. Except for highly volatile indexes.

Make sure to give it a test run on a restored copy first to see just how many index blocks it decides to rewrite for that first run.

 

[jdpjamesp]

Oh wow yeah AI activity off the charts! Unfortunately that does mean I'm going to have to run it during office hours so I can actually monitor it as we're at risk of running out of space if I don't...

 

[jdpjamesp]

Thanks for the input guys. 180 indexes compacted. Only one complaint about performance whilst it was happening. Doubt anyone will notice an improvement in performance though!