Re: Index Re-org

Rick Ward wrote: > Can anyone tell me if they re-organise indexes on tables automatically ? the engine does not re-organise indexes automatically. it does do some work in making sure that the leaves are balanced, but that is limited, and after time with lots of inserts and deletes you can end up with a tree that has leaves with a lot of free space. the bigger problem is when the index is fragmented across the disk, i.e. it's pages are intermingled with data pages all over the place. that makes for the heads to have to skip all over when reading the index pages, and this is especially bad for sequential reads of the index's, the same way it is bad for sequential reads of data. the idea with index's is the same as with data; you want them physically contiguous on disk. the best thing to do is to monitor them using on(tb)stat and look to see if the pages are scattered all over. the best thing then is simply to drop and rebuild the index. if you have tables that need this often, you can set up a cron job to run this nightly/weekly/monthly, whatever. the script is simply two lines: DROP INDEX foo CREATE INDEX foo ON table_name blah, blah, blah... by the way, it's indices, isn't it??? hope that helps. mickm