Performance of the 'alter table shrink space' statement
HI,
My customer has a table in his database into which a variable number of rows is inserted each day (possibly many thousands), and daily housekeeping routines delete a similar number of rows each day. The rules for determining which rows are to be deleted are complex, so the simple solution of partitioning the table such that a whole partition could be dropped is not feasible. Consequently, the table grows and becomes 'untidy' (or 'sparse') quickly, and the customer reclaims space by means of 'alter table...shrink space'.
So far so good. He is now facing the prospect of a large increase in volumes of data to be loaded into this table, and subsequently deleted, and his use of the 'shrink' facility will scan a larger number of table extents. He is keen to know whether the time taken by this 'shrink' operation will increase directly in proportion to the number of extents in the table (i.e. in an approximately linear manner), whether its performance will degrade exponentially as the number of extents increases, or whether there will be 'step changes' in its duration as the number
My customer has a table in his database into which a variable number of rows is inserted each day (possibly many thousands), and daily housekeeping routines delete a similar number of rows each day. The rules for determining which rows are to be deleted are complex, so the simple solution of partitioning the table such that a whole partition could be dropped is not feasible. Consequently, the table grows and becomes 'untidy' (or 'sparse') quickly, and the customer reclaims space by means of 'alter table...shrink space'.
So far so good. He is now facing the prospect of a large increase in volumes of data to be loaded into this table, and subsequently deleted, and his use of the 'shrink' facility will scan a larger number of table extents. He is keen to know whether the time taken by this 'shrink' operation will increase directly in proportion to the number of extents in the table (i.e. in an approximately linear manner), whether its performance will degrade exponentially as the number of extents increases, or whether there will be 'step changes' in its duration as the number
0