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

Hi Neil, the point in slowing down queries over day are the different sizes of those tables inherited in the queries. This happens when you're doing the statistics in old-fashioned-way, with the optimizer taken his decisions from row-size, row-count and value-count and not from value-distribution. If you have to join two tables and prepare the statement, when one of these is empty, the optimizer chooses to first use the empty table. If this table fills over day and gets much bigger than the other table involved, you will see response-times going worse. Generating a value-distribution on an empty table won't solve the problem and generating a value-distribution on the more "static" table can make things even worse. Doing an update statistics over day may be impossible, due to the whole-system impact of this and wouldn't affect the execution of the allready prepared queries. This is a very special case and i don't know whether the original-poster has a similar situation at all. Neil Truby schrieb: > > 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

Drop and recreate the table might be fastest. On Mon, 19 Apr 1999 17:06:21 +0200, Martin Berns <Martin.Berns@Materna.De> wrote: >Hi Neil, >the point in slowing down queries over day are the different sizes of >those >tables inherited in the queries. This happens when you're doing the >statistics >in old-fashioned-way, with the optimizer taken his decisions from >row-size, >row-count and value-count and not from value-distribution. If you have >to join >two tables and prepare the statement, when one of these is empty, the >optimizer >chooses to first use the empty table. If this table fills over day and >gets >much bigger than the other table involved, you will see response-times >going >worse. Generating a value-distribution on an empty table won't solve the >problem >and generating a value-distribution on the more "static" table can make >things >even worse. Doing an update statistics over day may be impossible, due >to >the whole-system impact of this and wouldn't affect the execution of the >allready prepared queries. >This is a very special case and i don't know whether the original-poster >has >a similar situation at all. > >Neil Truby schrieb: >> >> 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