Re: TRUNCATE statement for Informix exists?

Glen Bayliss wrote: > > Jean-Francois DENIS wrote: > > > > Hello, > > > > With Oracle, you can use the statement TRUNCATE that deletes logically > > all records of a table (while the table definition and indexes remain). > > This is much more performant than DROP/CREATE statements since it is a > > logical delete. > > Does anyone know if a similar statement exists in Informix? > Why don't you try > > DELETE FROM tablename > WHERE 1=1 This is MUCH MUCH slower for all but the smallest tables. The answer is that Informix does not supply this useful command so you have to go through: dbschema -d mydatabase -t mytable mytable.sql dbaccess mydatabase - <<EOFdrop table mytable;EOF dbaccess mydatabase - <mytable.sql Which is fast enough. The only problem is that any prepared statements in running applications will begin to get -710 and/or other optimizer related errors since their prepared query plan is no longer valid. A TRUNCATE statement would indeed be convenient. Submit a "FEATURE REQUEST" Menlo seems inclined to listen to requests for Oracle source compatibility lately (don't ask I cannot tell) so you may get this one. Art S. Kagel