Re: Deleting all rows from standard (with loggin) table on IDS 9.40
> Lukas > > Quickest and cleanest way is to drop and recreate the table. > That's what I don't like to do (I could also drop all foreing keys, indexes and change table type to raw....delete data... insert new data ... recreate indexes and foreign keys). I also have a use case, where I need to delete only aprox 50% of rows according index (part of primary key). Lukas
Lukas Barton wrote: > >> Lukas >> >> Quickest and cleanest way is to drop and recreate the table. >> > > That's what I don't like to do (I could also drop all foreing keys, > indexes and change table type to raw....delete data... insert new data > ... recreate indexes and foreign keys). > I also have a use case, where I need to delete only aprox 50% of rows > according index (part of primary key). Get my package, utils2_ak, from the IIUG Software Repository. One of the utilities in there is dbdelete. It is written to delete rows VERY quickly without using a large number of resources. It basically deletes then commits 8192 rows at a time. It deletes by ROWID be first fetching 8192 rowids based on a WHERE clause you supply (in your case you don't need to supply one). If the table is fragmented it must be fragmented WITH ROWID otherwise you have to use dbdelete in it's 'by key' mode by supplying one or more -u<keycol> args which runs far slower than the default 'by ROWID' mode. In your case if you have ROWIDS, the basics args are all that's needed: dbdelete -d mydatabase -t bigtable -p 50 Art S. Kagel
Lukas Barton wrote: > >> Lukas >> >> Quickest and cleanest way is to drop and recreate the table. >> > > That's what I don't like to do (I could also drop all foreing keys, > indexes and change table type to raw....delete data... insert new data > ... recreate indexes and foreign keys). > I also have a use case, where I need to delete only aprox 50% of rows > according index (part of primary key). Get my package, utils2_ak, from the IIUG Software Repository. One of the utilities in there is dbdelete. It is written to delete rows VERY quickly without using a large number of resources. It basically deletes then commits 8192 rows at a time. It deletes by ROWID be first fetching 8192 rowids based on a WHERE clause you supply (in your case you don't need to supply one). If the table is fragmented it must be fragmented WITH ROWID otherwise you have to use dbdelete in it's 'by key' mode by supplying one or more -u<keycol> args which runs far slower than the default 'by ROWID' mode. In your case if you have ROWIDS, the basics args are all that's needed: dbdelete -d mydatabase -t bigtable -p 50 Art S. Kagel