painter@sequoia.execu.com (Tom Painter) (03/21/90)
Is there any quick easy way to reduce the size of the .dat and .idx
files in an Informix databases when all of the records are deleted?
Every 3 months, we load a database from tapes. There are over 60,000
records that get split across eight tables. As a result the .dat and .idx
files are very large. When the new tapes arrive, I need to clear out all
of the records and load the empty files.
The problem, of course, is that the files are not reduced in size when
rows are deleted (I assume that they are merely marked as deleted). So
after a few rounds of this, the files are 3-4 times as large as they
should be.
I know of two ways that this could be done, neither is particularly
pleasant:
1. Delete all records. Alter the table and rebuild. Then repair the
alteration and rebuild.
Problem: The delete takes hours. And the end users are not
sophisticated enough to do the alterations on the
eight tables without error. And I hope that they
don't need to be.
2. Use dbschema to create the appropriate sql statements. Drop the
tables, and rebuild.
Problem: The dbschema output would have to be editted each time
and, again the end users don't have the training for
that.
My Solution:
Any thoughts on problems caused if I built the identical tables in another
directory, left them empty, and moved them on top of the existing live
data files. My thought is that the empty data files would have the same
structure as the live ones and that the database wouldn't be able to
detect any difference, since (If I'm correct in this) the database updates
statistics each time isql in invoked.
Thanks for any and all help. I can summarize to anyone that wants it.
Tom
--
-----------------------------------------------------------------------------
Tom Painter UUCP: ...!cs.utexas.edu!execu!painter
Execucom Systems Corp., Austin, Texas Internet: painter@execu.com
(512) 327-7070 execu!painter@cs.utexas.edu
Disclaimer: My Company? They'll claim my all waking hours, not my opinions.
-----------------------------------------------------------------------------bochner@speed.harvard.edu (Harry Bochner) (03/22/90)
In article <11507@sequoia.execu.com> painter@sequoia.execu.com (Tom Painter) writes: >Every 3 months, we load a database from tapes. There are over 60,000 >records that get split across eight tables. As a result the .dat and .idx >files are very large. When the new tapes arrive, I need to clear out all >of the records and load the empty files. > >The problem, of course, is that the files are not reduced in size when >rows are deleted (I assume that they are merely marked as deleted). So >after a few rounds of this, the files are 3-4 times as large as they >should be. You mean the deleted rows aren't being reused at all? I'm pretty sure INFORMIX 2.10.03 does reuse them: I have lots of tables that are hold temporary data for applications, and get emptied at the end of a run. They don't keep growing indefinitely, they just hold a 'high water mark' of the maximum data they ever had at one time. That's what I'd expect in your case, too. Is your version of INFORMIX up to date? >2. Use dbschema to create the appropriate sql statements. Drop the > tables, and rebuild. > Problem: The dbschema output would have to be editted each time > and, again the end users don't have the training for > that. Assuming the table structure doesn't change, there's no reason to edit the sql scripts. Just use dbschema once to get the current definitions, and edit the script (once) to include a drop statement before each create statement. Then run this sql script each time you need to flush the database. The advantage of this approach is the it's much faster to drop a large table and redefine it than to delete all its rows. >My Solution: >Any thoughts on problems caused if I built the identical tables in another >directory, left them empty, and moved them on top of the existing live >data files. Sounds to me like asking for trouble. I prefer not to use backdoor methods on anything I don't have source code for ... -- Harry Bochner bochner@endor.harvard.edu