Optimizing dbspace
Elena Korol purged a table gaining 3 million rows a month by unloading and deleting, but the dbspace kept growing despite update statistics. Rob Wilson cited an HPL bug where an express-mode initial load stops later loads reusing space; reload with deluxe mode. Savio Pereira suggested altering an index to cluster. Patricia said space is only reclaimed by dropping and recreating or clustering, and suggested fragmentation. Not confirmed.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hello, I have a quite large table that acquires about 3 million rows a month. Every two weeks we purge the data by unloading it to a backup and deleting the "old" information. We do run 'update statistics' every time when deletes are performed. But it looks like it does not optimize the dbspace and I keep adding new chunks. What will be the best way do deal with this situation? Sincerely, Elena.
If you are loading with HPL, there is a bug (I do not have the number) where if the initial load of the table is done in express mode subsequent loads with hpl will not re-use existing space. Eventually you can run into some real BAD problems involving indexes built on these "empty" pages (we were seeing some serious disk thrashing). The fix is to drop the table, re-create it, and load it using deluxe mode (or some other tool) and do incremental loads with deluxe as well. Of course, it could be something else entirely. Elena Korol (ekorol@styleclick.com) wrote: : Hello, : I have a quite large table that acquires about 3 million rows a month. Every : two weeks we purge the data by unloading it to a backup and deleting the : "old" information. : We do run 'update statistics' every time when deletes are performed. : But it looks like it does not optimize the dbspace and I keep adding new : chunks. : What will be the best way do deal with this situation? : Sincerely, : Elena. -- Rob Wilson rwilson@ntsource.com
U could alter one of ur indexes to cluster. This will physically move and reorganize the rows so that free space will be used. Though this could take some time. Savio Pereira Eesoft India Ltd. saviop75@hotmail.com Elena Korol <ekorol@styleclick.com> wrote in message news:8n1ccq$h1f$1@news.xmission.com... > > Hello, > > I have a quite large table that acquires about 3 million rows a month. Every > two weeks we purge the data by unloading it to a backup and deleting the > "old" information. > We do run 'update statistics' every time when deletes are performed. > > But it looks like it does not optimize the dbspace and I keep adding new > chunks. > What will be the best way do deal with this situation? > > Sincerely, > Elena. > >
Hi Elena, If you are just unloading the data and deleting the records from the "old" table you will never have your space reclaimed. The only way to do that is to drop and recreate the table (It will take some time since your table is quite large) or you can (as someone else said) alter your index to cluster (which can also take some time). Either way, you will have to spend time reclaimimg your space so...good luck ! Regards, - Patricia PS: You may want to try to implement some kind of fragmentation strategy in order to prevent this sort of problem in the future. In article <8n1ccq$h1f$1@news.xmission.com>, Elena Korol <ekorol@styleclick.com> wrote: > > Hello, > > I have a quite large table that acquires about 3 million rows a month. Every > two weeks we purge the data by unloading it to a backup and deleting the > "old" information. > We do run 'update statistics' every time when deletes are performed. > > But it looks like it does not optimize the dbspace and I keep adding new > chunks. > What will be the best way do deal with this situation? > > Sincerely, > Elena. > > Sent via Deja.com http://www.deja.com/ Before you buy.