[
https://issues.apache.org/jira/browse/DERBY-5487?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Sundar Narayanaswamy updated DERBY-5487:
----------------------------------------
Attachment: (was: DerbyInPlaceCompress.java)
> 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, patch
> Fix For: 10.8.1.2
>
>
> 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, 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
>
> SQL111029001155930 1 153 116
> 39 4096 475136
> LOC_INDEX 1 1 397
> 1 4096 1626112
> Database size: 13447 KB
> Iteration: 1
> ConglomerateName IsIndex NumAllocatedPages NumFreePages
> NumUnFilledPages PageSize EstimSpaceSaving
> LOCATION 0 1 816
> 1 4096 3342336
>
> SQL111029001155930 1 400 31
> 284 4096 126976
> LOC_INDEX 1 103 318
> 84 4096 1302528
> Database size: 23777 KB
> Iteration: 2
> ConglomerateName IsIndex NumAllocatedPages NumFreePages
> NumUnFilledPages PageSize EstimSpaceSaving
> LOCATION 0 1 816
> 0 4096 3342336
> SQL111029001155930 1 452 226
> 294 4096 925696
> LOC_INDEX 1 3 418
> 2 4096 1712128
> Database size: 17094 KB
>
> Iteration: 3
> ConglomerateName IsIndex NumAllocatedPages NumFreePages
> NumUnFilledPages PageSize EstimSpaceSaving
> LOCATION 0 1 817
> 0 4096 3346432
>
> SQL111029001155930 1 504 226
> 331 4096 925696
> LOC_INDEX 1 1 435
> 1 4096 1781760
>
> Database size: 25802 KB
> Iteration: 4
> ConglomerateName IsIndex NumAllocatedPages NumFreePages
> NumUnFilledPages PageSize EstimSpaceSaving
> LOCATION 0 1 817
> 0 4096 3346432
>
> SQL111029001155930 1 691 91
> 367 4096 372736
> LOC_INDEX 1 3 433
> 2 4096 1773568
>
> Database size: 17636 KB
> Iteration: 5
> ConglomerateName IsIndex NumAllocatedPages NumFreePages
> NumUnFilledPages PageSize EstimSpaceSaving
> LOCATION 0 2 816
> 0 4096 3342336
>
> SQL111029001155930 1 944 25
> 546 4096 102400
> LOC_INDEX 1 311 125
> 80 4096 512000
> Database size: 15316 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 is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
