deleting rows physical
I have following problem: in my Informix database the tables growing every day. If I delete some rows, the pysical space of the table will not be smaller. How can I delete this rows physical from the table/database (Informix 4.1, Sinix) goermet
In article <76oegi$pci$1@news.online.de>, goermet@online.de (online) wrote: > I have following problem: > > in my Informix database the tables growing every day. > If I delete some rows, the pysical space of the table > will not be smaller. How can I delete this rows physical > from the table/database > > (Informix 4.1, Sinix) > > goermet > > > > You don't need to worry about this, generally. The space used by the deleted row will be re-used on the next insertion. Paul Watson # I don't suffer from WF Software Ltd. # stress, I'm just Tel. (+44) 1436 674729 # a carrier Fax. (+44) 1436 678693 # www.wfsoftware.co.uk
I had think so, but it isn't so in the reality goermet WF Software schrieb in Nachricht ... >In article <76oegi$pci$1@news.online.de>, goermet@online.de (online) >wrote: > >> I have following problem: >> >> in my Informix database the tables growing every day. >> If I delete some rows, the pysical space of the table >> will not be smaller. How can I delete this rows physical >> from the table/database >> >> (Informix 4.1, Sinix) >> >> goermet >> >> >> >> >You don't need to worry about this, generally. The space used by the >deleted row will be re-used on the next insertion. > >Paul Watson # I don't suffer from >WF Software Ltd. # stress, I'm just >Tel. (+44) 1436 674729 # a carrier >Fax. (+44) 1436 678693 # >www.wfsoftware.co.uk
goermet wrote: > > I had think so, but it isn't so in the reality > > goermet > > WF Software schrieb in Nachricht ... > >In article <76oegi$pci$1@news.online.de>, goermet@online.de (online) > >wrote: > > > >> I have following problem: > >> > >> in my Informix database the tables growing every day. > >> If I delete some rows, the pysical space of the table > >> will not be smaller. How can I delete this rows physical > >> from the table/database Yes it is indeed true that space used by deleted rows will be reused. However, there are some caviats: If your table contains variable length columns (ie TEXT, BYTE, varchar, etc) and the inserted rows will not fit in space vacated on the existing pages and there is not enough unused space on that page so the existing rows can be compressed, and shuffled around to make contiguous space for the larger row, and there are no free pages in the existing extents, then additional extents will be allocated to your table. In addition, if you have blobs (TEXT or BYTE) the deleted rows' BLOB pages will not be released for reuse until the logical logfile that contains the original INSERT or UPDATE log record that created that version of the BLOB has been backed up. This means that if you are testing this and have BLOBs and have not backup up your logical logs since the test row was created or last updated then the space will not be available for reuse by new BLOBs or data pages. Art S. Kagel
Art S. Kagel wrote: > > goermet wrote: > > > > I had think so, but it isn't so in the reality > > > > goermet > > > > WF Software schrieb in Nachricht ... > > >In article <76oegi$pci$1@news.online.de>, goermet@online.de (online) > > >wrote: > > > > > >> I have following problem: > > >> > > >> in my Informix database the tables growing every day. > > >> If I delete some rows, the pysical space of the table > > >> will not be smaller. How can I delete this rows physical > > >> from the table/database > > Yes it is indeed true that space used by deleted rows will be reused. > However, there are some caviats: If your table contains variable > length columns (ie TEXT, BYTE, varchar, etc) and the inserted rows will > not fit in space vacated on the existing pages and there is not enough > unused space on that page so the existing rows can be compressed, and > shuffled around to make contiguous space for the larger row, and there > are no free pages in the existing extents, then additional extents will > be allocated to your table. In addition, if you have blobs (TEXT or > BYTE) the deleted rows' BLOB pages will not be released for reuse until > the logical logfile that contains the original INSERT or UPDATE log > record that created that version of the BLOB has been backed up. This > means that if you are testing this and have BLOBs and have not backup > up your logical logs since the test row was created or last updated > then the space will not be available for reuse by new BLOBs or data > pages. > > Art S. Kagel And if you want to 'compact' your table immediately (say, after huge delete), you have to force table rewrite, by iether altering it or (better) adding a cluster index somewhere. Take care that whole 'rewrite' transaction fits within available logical logs (taking into account long transaction high watermark, too). Cheers! Dragi "Bonzi" Raos