[
https://issues.apache.org/jira/browse/DERBY-5487?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Dimuthu Wickramanayake updated DERBY-5487:
------------------------------------------
Priority: Major (was: Minor)
> Primary key disk pages not reclaimed when using
> SYSCS_UTIL.SYSCS_COMPRESS_TABLE with just the purge_rows option
> ---------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-5487
> URL: https://issues.apache.org/jira/browse/DERBY-5487
> Project: Derby
> Issue Type: Bug
> Components: Store
> Affects Versions: 10.8.1.2
> Environment: Windows 7, Embedded Derby mode
> Reporter: Sundar Narayanaswamy
> Labels: derby_triage10_8
> Attachments: ASF.LICENSE.NOT.GRANTED--screenshot-1.jpg,
> DerbyInPlaceCompress.java
>
>
> When I continuously insert data, delete the inserted data then compress with
> purge_rows option in a loop, space is not reclaimed from the primary key
> file. The inserts are committed every 10000 rows, deletes committed every
> 50000 rows. All the rows that were inserted are deleted. The primary key
> values continually increase (across the inserts) . All the activities occur
> on a single thread. Included below is the space table output after each
> iteration in the loop:
> As can be seen below and in the screenshot attached, the NumAllocatedpages
> for SQL111029001155930 is continuously increasing. This increase does not
> happen if the primary key values are reset after each iteration (ie, primary
> key values for new inserts are in the same range as deleted rows).
> Iteration: 0
> ConglomerateName IsIndex NumAllocatedPages NumFreePages
> NumUnFilledPages PageSize EstimSpaceSaving
> LOCATION 0 1 803 1
> 4096 3289088
> SQL111029003533400 1 238 31 179
> 4096 126976
> LOC_INDEX 1 211 185 119
> 4096 757760
> Database size: 12993 KB
> Iteration: 1
> ConglomerateName IsIndex NumAllocatedPages NumFreePages
> NumUnFilledPages PageSize EstimSpaceSaving
> LOCATION 0 1 816 1
> 4096 3342336
> SQL111029003533400 1 324 192 200
> 4096 786432
> LOC_INDEX 1 1 406 1
> 4096 1662976
> Database size: 17112 KB
> Iteration: 2
> ConglomerateName IsIndex NumAllocatedPages NumFreePages
> NumUnFilledPages PageSize EstimSpaceSaving
> LOCATION 0 7 810 1
> 4096 3317760
> SQL111029003533400 1 579 23 294
> 4096 94208
> LOC_INDEX 1 394 28 2
> 4096 114688
> Database size: 22821 KB
> Iteration: 3
> ConglomerateName IsIndex NumAllocatedPages NumFreePages
> NumUnFilledPages PageSize EstimSpaceSaving
> LOCATION 0 1 816 0
> 4096 3342336
> SQL111029003533400 1 631 227 451
> 4096 929792
> LOC_INDEX 1 5 437 3
> 4096 1789952
> Database size: 18054 KB
> Iteration: 4
> ConglomerateName IsIndex NumAllocatedPages NumFreePages
> NumUnFilledPages PageSize EstimSpaceSaving
> LOCATION 0 1 817 0
> 4096 3346432
> SQL111029003533400 1 735 174 460
> 4096 712704
> LOC_INDEX 1 1 441 1
> 4096 1806336
> Database size: 15632 KB
> Iteration: 5
> ConglomerateName IsIndex NumAllocatedPages NumFreePages
> NumUnFilledPages PageSize EstimSpaceSaving
> LOCATION 0 4 814 0
> 4096 3334144
> SQL111029003533400 1 992 21 690
> 4096 86016
> LOC_INDEX 1 378 64 127
> 4096 262144
> Database size: 25633 KB
> Iteration: 6
> ConglomerateName IsIndex NumAllocatedPages NumFreePages
> NumUnFilledPages PageSize EstimSpaceSaving
> LOCATION 0 1 817 0
> 4096 3346432
> SQL111029003533400 1 1071 199 744
> 4096 815104
> LOC_INDEX 1 5 437 3
> 4096 1789952
> Database size: 19763 KB
> Iteration: 7
> ConglomerateName IsIndex NumAllocatedPages NumFreePages
> NumUnFilledPages PageSize EstimSpaceSaving
> LOCATION 0 1 817 0
> 4096 3346432
> SQL111029003533400 1 1320 29 932
> 4096 118784
> LOC_INDEX 1 395 47 11
> 4096 192512
> Database size: 26911 KB
> Iteration: 8
> ConglomerateName IsIndex NumAllocatedPages NumFreePages
> NumUnFilledPages PageSize EstimSpaceSaving
> LOCATION 0 1 817 0
> 4096 3346432
> SQL111029003533400 1 1421 177 1014
> 4096 724992
> LOC_INDEX 1 1 441 1
> 4096 1806336
> Database size: 21003 KB
> Iteration: 9
> ConglomerateName IsIndex NumAllocatedPages NumFreePages
> NumUnFilledPages PageSize EstimSpaceSaving
> LOCATION 0 1 817 0
> 4096 3346432
> SQL111029003533400 1 1593 106 1063
> 4096 434176
> LOC_INDEX 1 1 441 1
> 4096 1806336
> Database size: 29662 KB
> Iteration: 10
> ConglomerateName IsIndex NumAllocatedPages NumFreePages
> NumUnFilledPages PageSize EstimSpaceSaving
> LOCATION 0 1 817 0
> 4096 3346432
> SQL111029003533400 1 1736 135 1083
> 4096 552960
> LOC_INDEX 1 3 439 2
> 4096 1798144
> Database size: 22035 KB
> ------------------ Java Information ------------------
> Java Version: 1.6.0_21
> Java Vendor: Sun Microsystems Inc.
> Java home: c:\development\
> Java classpath: .;derby-10.8.1.2.jar
> OS name: Windows 7
> OS architecture: x86
> OS version: 6.1
> Java user name: testuser
> Java user home: C:\Users\testuser
> Java user dir: C:\temp\Derby
> java.specification.name: Java Platform API Specification
> java.specification.version: 1.6
> java.runtime.version: 1.6.0_21-b06
> --------- Derby Information --------
> JRE - JDBC: Java SE 6 - JDBC 4.0
> [C:\temp\Derby\derby-10.8.1.2.jar] 10.8.1.2 - (1095077)
> ------------------------------------------------------
> ----------------- Locale Information -----------------
> ------------------------------------------------------
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
