Re: Help - Clustered Index to Keep the Table in Desired Order

diana, > This is a right approach ? Are you doing this in production environment ? i'm not doing that simply because i don't have that situation, but that is a good way to go about it. do you really need to do it every night though? how about once a week? > How long would a re-clustering take (We are at dev. phase and no this depends on so many factors. the only real way to know is to do it, but i would guess it shouldn't take more than an hour or so. that is a hard guess to make... > Is my assumption correct, i.e. Informix will re-use any deleted space > in a page for new rows inserted ? yes, it will resuse the space from deleted rows, so what you are saying is true, that your data will be out of order. > Is there any other caution I should take for implementing the batch job ? you might consider backing up the table, you never know what could happen. when doing any kind of work with data like this, it is always a good idea to have backups. > 500 MB, the clustering col. is of type "datetime year to fraction(3)" and note that when you build the table with the clustered index, the engine literally builds a new table, drops the old one, and renames the new one. this means that you have to have enough room for another table of this size, plus the space taken up by the index. if you don't have the space, you can download the table to tape or disk, create a new table with the clustered index, reload the table, drop the old table, and rename the new (essentially what the engine does). hope this helps mickm