Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-1187

defragment of inplace compress pass for described dataset is not freeing up empty pages.

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6
    • Fix Version/s: 10.1.3.1, 10.2.1.6
    • Component/s: Store
    • Labels:
      None

      Description

      For the following script defragment pass is not freeing up the free pages:

      drop table t1;
      create table t1 (i integer primary key, j integer, c char(200));
      insert into t1 values (1, 1, 'a');
      insert into t1 (select t1.i + 1, t1.j + 1, t1.c from t1);
      insert into t1 (select t1.i + 2, t1.j + 2, t1.c from t1);
      insert into t1 (select t1.i + 4, t1.j + 4, t1.c from t1);
      insert into t1 (select t1.i + 8, t1.j + 8, t1.c from t1);
      insert into t1 (select t1.i + 16, t1.j + 16, t1.c from t1);
      insert into t1 (select t1.i + 32, t1.j + 32, t1.c from t1);
      insert into t1 (select t1.i + 64, t1.j + 64, t1.c from t1);
      insert into t1 (select t1.i + 128, t1.j + 128, t1.c from t1);
      insert into t1 (select t1.i + 256, t1.j + 256, t1.c from t1);
      insert into t1 (select t1.i + 512, t1.j + 512, t1.c from t1);

      delete from t1 where i < 512;

      select
      cast(conglomeratename as char(12)) as tabname,
      isindex,
      cast(numallocatedpages as int) as alloc,
      numfreepages,
      cast(numunfilledpages as int) as unfilled,
      pagesize,
      estimspacesaving
      from new org.apache.derby.diag.SpaceTable('T1') t order by conglomeratename;

      CALL SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP', 'T1', 1, 1, 1);

      select
      cast(conglomeratename as char(12)) as tabname,
      isindex,
      cast(numallocatedpages as int) as alloc,
      numfreepages,
      cast(numunfilledpages as int) as unfilled,
      pagesize,
      estimspacesaving
      from new org.apache.derby.diag.SpaceTable('T1') t order by conglomeratename;

      select
      cast(conglomeratename as char(12)) as tabname,
      isindex,
      cast(numallocatedpages as int) as alloc,
      numfreepages,
      cast(numunfilledpages as int) as unfilled,
      pagesize,
      estimspacesaving
      from new org.apache.derby.diag.SpaceTable('T1') t order by conglomeratename;

      Here is example output from a run on the trunk:
      ij> drop table t1;
      0 rows inserted/updated/deleted
      ij> create table t1 (i integer primary key, j integer, c char(200));
      0 rows inserted/updated/deleted
      ij> insert into t1 values (1, 1, 'a');
      1 row inserted/updated/deleted
      ij> insert into t1 (select t1.i + 1, t1.j + 1, t1.c from t1);
      1 row inserted/updated/deleted
      ij> insert into t1 (select t1.i + 2, t1.j + 2, t1.c from t1);
      2 rows inserted/updated/deleted
      ij> insert into t1 (select t1.i + 4, t1.j + 4, t1.c from t1);
      4 rows inserted/updated/deleted
      ij> insert into t1 (select t1.i + 8, t1.j + 8, t1.c from t1);
      8 rows inserted/updated/deleted
      ij> insert into t1 (select t1.i + 16, t1.j + 16, t1.c from t1);
      16 rows inserted/updated/deleted
      ij> insert into t1 (select t1.i + 32, t1.j + 32, t1.c from t1);
      32 rows inserted/updated/deleted
      ij> insert into t1 (select t1.i + 64, t1.j + 64, t1.c from t1);
      64 rows inserted/updated/deleted
      ij> insert into t1 (select t1.i + 128, t1.j + 128, t1.c from t1);
      128 rows inserted/updated/deleted
      ij> insert into t1 (select t1.i + 256, t1.j + 256, t1.c from t1);
      256 rows inserted/updated/deleted
      ij> insert into t1 (select t1.i + 512, t1.j + 512, t1.c from t1);
      512 rows inserted/updated/deleted
      ij> delete from t1 where i < 512;
      511 rows inserted/updated/deleted
      ij> select
      cast(conglomeratename as char(12)) as tabname,
      isindex,
      cast(numallocatedpages as int) as alloc,
      numfreepages,
      cast(numunfilledpages as int) as unfilled,
      pagesize,
      estimspacesaving
      from new org.apache.derby.diag.SpaceTable('T1') t order by conglomeratename;
      TABNAME |ISIND&|ALLOC |NUMFREEPAGES |UNFILLED |PAGESIZE |EST
      IMSPACESAVING
      --------------------------------------------------------------------------------
      -----------------
      SQL060406034|1 |7 |0 |0 |4096 |0

      T1 |0 |60 |9 |0 |4096 |368
      64

      2 rows selected
      ij> CALL SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP', 'T1', 1, 1, 1);
      0 rows inserted/updated/deleted
      ij> select
      cast(conglomeratename as char(12)) as tabname,
      isindex,
      cast(numallocatedpages as int) as alloc,
      numfreepages,
      cast(numunfilledpages as int) as unfilled,
      pagesize,
      estimspacesaving
      from new org.apache.derby.diag.SpaceTable('T1') t order by conglomeratename;
      TABNAME |ISIND&|ALLOC |NUMFREEPAGES |UNFILLED |PAGESIZE |EST
      IMSPACESAVING
      --------------------------------------------------------------------------------
      -----------------
      SQL060406034|1 |7 |0 |1 |4096 |0

      T1 |0 |69 |0 |0 |4096 |0

      2 rows selected
      ij>

        Attachments

          Activity

            People

            • Assignee:
              mikem Mike Matrigali
              Reporter:
              mikem Mike Matrigali
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: