Re: The Fastest Way To Delete All Rows In A Table

Sorry, I wasn't clear enough. I want to retain the "200,000 row" information. The reason is that the next day, the table will fill up again and so I want the query optimiser to behave as though the table has many rows, not zero rows. I could run UPDATE STATISTICS the following day but timing it is tricky. If its run too early then the information gathered is not significant, if its run too late, the application accessing the database will have been performing badly. Neil Truby wrote in message <7f72vi$2d3$1@taliesin.netcom.net.uk>... >Which statistics? The ones relating to the state of the table when it had >200,000 rows that it no longer has? >I'm not sure what your requirement is here - if you're deleting all the rows >what point is there in retaining out-of-date statistics about it? > >Neil Truby >Londis Stores >Hampton Hill, UK > >> >>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. >> > > >

I still don't understand why you want to preserve the statistics. Assuming that the rows that you are going to insert differ from those to be deleted, the stats related to value distributions, no.of rows etc. will be inaccurate. If you're putting the same rows back, why? Perhaps to remove fragmentation or reduce the number of extents? In which case, ALTER FRAGMENT....INIT would be a better way. Neil John Goodbody wrote in message <7f8avf$s53@romeo.logica.co.uk>... >Sorry, I wasn't clear enough. I want to retain the "200,000 row" >information. > >The reason is that the next day, the table will fill up again and so I want >the query optimiser to behave as though the table has many rows, not zero >rows. > >I could run UPDATE STATISTICS the following day but timing it is tricky. If >its run too early then the information gathered is not significant, if its >run too late, the application accessing the database will have been >performing badly. > > > > >Neil Truby wrote in message <7f72vi$2d3$1@taliesin.netcom.net.uk>... >>Which statistics? The ones relating to the state of the table when it had >>200,000 rows that it no longer has? >>I'm not sure what your requirement is here - if you're deleting all the >rows >>what point is there in retaining out-of-date statistics about it? >> >>Neil Truby >>Londis Stores >>Hampton Hill, UK >> >>> >>>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. >>> >> >> >> > >