Hi There:
I am seeing delete clob data from a table does not reclaim the disk space.
The same behavior does not happen to blob. For Blob, after delete, the database
size will decrease. However, for Clob, after delete, the database size remaind
the same. If we check the compression space after delete clob using
SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE, the size is smaller after delete clob
rows. And, this is the same with blob.
Is this a bug or expected behavior?
I am attaching the repro for you to review.
Note the output indicate the databaseSize() did not change after delete clob
data from TAB table:
===========================
The database size: 11494963
delete rows from TAB
The database size: 11494963
import java.sql.*;
import java.io.*;
import org.apache.derby.tools.JDBCDisplayUtil;
import org.apache.derbyTesting.functionTests.util.streams.CharAlphabet;
import org.apache.derbyTesting.functionTests.util.streams.LoopingAlphabetReader;
import org.apache.derbyTesting.functionTests.util.streams.LoopingAlphabetStream;
public class TestInPlaceCompressWithPKViolation {
public static void main(String[] args) throws Exception{
Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
Connection conn =
DriverManager.getConnection("jdbc:derby:testdb;create=true");
createTable(conn);
PreparedStatement ps = conn.prepareStatement("INSERT INTO TAB
VALUES(1,?)");
int clobLength = 3000000;
for (int i = 0; i < 5; i ++) {
Reader streamReader = new LoopingAlphabetReader(clobLength,
CharAlphabet.modernLatinLowercase());
ps.setCharacterStream(1,streamReader,clobLength);
try {
ps.executeUpdate();
} catch (SQLException se) {
System.out.println(se.getMessage());
}
}
//compressAndCheckSpaceTable(conn);
System.out.println("The database size: " + databaseSize(new
File("testdb")));
//System.out.println("Call compressAndCheckSpaceTable again");
//compressAndCheckSpaceTable(conn);
Statement s2 = conn.createStatement();
s2.executeUpdate("delete from TAB");
s2.close();
System.out.println("delete rows from TAB");
System.out.println("The database size: " + databaseSize(new
File("testdb")));
//compressAndCheckSpaceTable(conn);
}
private static void createTable(Connection conn) throws SQLException {
Statement s = conn.createStatement();
try {
s.executeUpdate("DROP TABLE TAB");
} catch (SQLException se) {
}
s.executeUpdate("CREATE TABLE TAB (I INT PRIMARY KEY, C CLOB)");
s.close();
}
private static void compressAndCheckSpaceTable(Connection conn) throws
SQLException {
Statement s = conn.createStatement();
showSpaceTable("*****spaceTable before
SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP','TAB',1,1,1)",conn);
s.executeUpdate("call
SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP','TAB',1,1,1)");
showSpaceTable("*****spaceTable after
SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP','TAB',1,1,1)",conn);
s.executeUpdate("CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP','TAB',1)");
showSpaceTable("*****spaceTable after
SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP','TAB',1)",conn);
s.close();
}
private static void showSpaceTable(String label, Connection conn) throws
SQLException{
Statement s = conn.createStatement();
System.out.println(label);
ResultSet rs = s.executeQuery("select * from new
org.apache.derby.diag.SpaceTable('APP','TAB') t WHERE CONGLOMERATENAME =
'TAB'");
PrintWriter out = new PrintWriter(System.out);
JDBCDisplayUtil.DisplayResults(out,s, conn);
out.flush();
}
private static long databaseSize(File dbname) {
long length = 0;
if (dbname.isDirectory()) {
String[] children = dbname.list();
for (int i=0; i<children.length; i++)
length = length + databaseSize(new File(dbname,
children[i]));
return length;
}
else
return dbname.length();
}
}
