db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Matrigali (JIRA)" <derby-...@db.apache.org>
Subject [jira] Updated: (DERBY-1187) defragment of inplace compress pass for described dataset is not freeing up empty pages.
Date Thu, 06 Apr 2006 22:46:59 GMT
     [ http://issues.apache.org/jira/browse/DERBY-1187?page=all ]

Mike Matrigali updated DERBY-1187:
----------------------------------

    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>



  was:
For the following script defragement 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>




> defragment of inplace compress pass for described dataset is not freeing up empty pages.
> ----------------------------------------------------------------------------------------
>
>          Key: DERBY-1187
>          URL: http://issues.apache.org/jira/browse/DERBY-1187
>      Project: Derby
>         Type: Bug

>     Versions: 10.1.1.0, 10.1.1.1, 10.1.1.2, 10.1.2.0, 10.1.2.1, 10.1.2.2, 10.1.2.3, 10.2.0.0
>     Reporter: Mike Matrigali
>     Assignee: Mike Matrigali
>     Priority: Minor
>      Fix For: 10.2.0.0

>
> 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>

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


Mime
View raw message