Re: Delete vs Drop & Create

From: "Clifton M. Bean" <cmbean@email.msn.com> > >I don't see the delete statement included in this text. One of the things That's because there wasn't one. :-) >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. I've never benchmarked deleting by rowid, but I have (some limited) empirical evidence that suggests that bulk deletes should be done with all indexes dropped (bearing RI constraints in mind, etc, etc.) In other words, the implication that bulk deleting from a table using a single column index (or indeed *any* index) is A Good Thing, is, IMHO, misguided. >SAP/Informix Database Administrator >Informix Certified Database Specialist >Informix 4GL-Certified >Informix D4GL-Certified >Tekmetrics Certified Informix DBA >Tekmetrics Certified RDBMS Developer You have *way* too much spare time... :-) >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