The Fastest Way To Delete All Rows In A Table

Informix On-Line 7.3 on Digital UNIX. I am looking for the quickest way to delete all rows from a table without losing database statistics on that table, as gathered by UPDATE STATISTICS. Currently, I lock the table in exclusive mode and "DELETE * FROM <table>" within a single unit or work. This is not too bad, taking about 20 minutes for a 200,000 row table with 2 indexes. However, if I DROP the table and re-CREATE it then the same effect can be achieved in less than 5 seconds! Unfortunately, I lose the associated database statistics. Does anyone have a faster method of removing rows while preserving the statistics?

John Goodbody wrote: > > Informix On-Line 7.3 on Digital UNIX. > > I am looking for the quickest way to delete all rows from a table without > losing database statistics on that table, as gathered by UPDATE STATISTICS. > > Currently, I lock the table in exclusive mode and "DELETE * FROM <table>" > within a single unit or work. This is not too bad, taking about 20 minutes > for a 200,000 row table with 2 indexes. > > However, if I DROP the table and re-CREATE it then the same effect can be > achieved in less than 5 seconds! Unfortunately, I lose the associated > database statistics. > > Does anyone have a faster method of removing rows while preserving the > statistics? I agree with Neil there is little point to keeping the stats telling the optimizer that there are 200,000 rows with a particular key distribution when those rows are gone and the distribution may no longer be valid for the new rows I presume you will be adding. However, in the interests of being helpful: 1) Unload the data distributions and other stats for the table: output to index_stats.out select i.* from sysindexes i, systables t where i.tabid = t.tabid and tabname = "sometablename"; unload to distributions.unl select d.* from sysdistrib d, systables t where d.tabid = t.tabid and tabname = "sometablename"; 2) Drop the table. 3) Create the table. 4) Reload data distributions load from distributions.unl insert into sysdistrib; 5) Repopulate the index stat values. Sorry you have to do this manually. Read the output file from the sysindexes query and UPDATE the last four columns (levels, leaves, nunique, clust) to the saved values for each index. BE CAREFUL THE TABLE'S tabid will have changed and if you used myschema.ec to create the schema file that recreated the table any constraint indexes may have different names. It sounds like a flaky idea to update a system catalog table but I have been assured that these four columns are ONLY seen by the optimizer and not the operational portions of the engine and so are safe to update. Art S. Kagel