Re: TRUNCATE statement for Informix exists?
Art S. Kagel wrote:
} 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 - <<EOF
} drop 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
After dropping and re-creating the table, this technique also involves re-creating the foreign
keys and views dependent on that table.
--
Rajiv Jhaveri (rajivp@qsun.ho.att.com)