Help - Clustered Index to Keep the Table in Desired Order
Posted in 1996
I have a large table without a clustered index on the column that the application orders result set by that column. The reason I do not a clustered index on that column is the records are inserted in the same order (as the order by) and data for that column is static. The type of the column is "datetime year to fraction(3)" Howerver, the records in this table will be deleted by application once the related data is archived (usaully takes a week or two). Since I assume Informix will re-use the space in the page, new nows will be inserted into any empty space on a page causing rows in that table lost its desired physical order. Therefore I plan to implement a batch job runs nightly. The job will create a clustered index on the (order by) column causing the table to be sorted and rewritten in the dbspace. The job will drop the clustered index once the re-ordering is complete. My questions are - This is a right approach ? Are you doing this in production environment ? How long would a re-clustering take (We are at dev. phase and no production experience with Informix) given that the system load is very low when the job runs, the table size is 500 MB, the clustering col. is of type "datetime year to fraction(3)" and we are using OnLine 7.10 on AIX single CPU machine ? Is my assumption correct, i.e. Informix will re-use any deleted space in a page for new rows inserted ? Is there any other caution I should take for implementing the batch job ? Thanks for any response ! Regards, Diana Li