Re: Delete vs Drop & Create

From: Claudia Sarmiento <csarmientom@yahoo.com> > >I have to delete rows of a table very quickly, so Is there a way to >improve performance to the Delete statement? >Or is most quickly drop the table and recreate (There is no problem if >I can do that) If you want to delete _all_ the rows from a table, DROP TABLE and CREATE TABLE will always be quicker. If you don't want to delete all the records from a table, you may find drop and recreate to be a sub-optimal solution... :-) ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com

I don't see the delete statement included in this text. One of the things you might want to consider is to use the rowid of the item to drop your rows if: 1. the primary key is multicolumn, or 2. there is NO unique index for the fields in the where clause. To get the rowid, you will have to specifically query for it in your proceeding select statement using the following syntax: select rowid, etc etc etc etc into rowid_id, etc etc etc etc from tablename .... IF the table is fragmented by EXPRESSION or ROUND ROBIN, you need to look at the fragment expression used to see if the fragment using the keywords WITH ROWID. Hope this helps. =============================================== Clifton M. Bean cmbean@msn.com SAP/Informix Database Administrator Informix Certified Database Specialist Informix 4GL-Certified Informix D4GL-Certified Tekmetrics Certified Informix DBA Tekmetrics Certified RDBMS Developer =============================================== Obnoxio The Clown <obnoxio@hotmail.com> wrote in message news:7pbcnn$p5t$1@news.xmission.com... > > From: Claudia Sarmiento <csarmientom@yahoo.com> > > > >I have to delete rows of a table very quickly, so Is there a way to > >improve performance to the Delete statement? > >Or is most quickly drop the table and recreate (There is no problem if > >I can do that) > > If you want to delete _all_ the rows from a table, DROP TABLE and CREATE > TABLE will always be quicker. If you don't want to delete all the records > from a table, you may find drop and recreate to be a sub-optimal solution... > :-) > > > ______________________________________________________ > Get Your Private, Free Email at http://www.hotmail.com