db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mi...@apache.org
Subject svn commit: r398761 - in /db/derby/code/branches/10.1/java: engine/org/apache/derby/iapi/db/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/tests/store/
Date Tue, 02 May 2006 01:27:24 GMT
Author: mikem
Date: Mon May  1 18:27:21 2006
New Revision: 398761

URL: http://svn.apache.org/viewcvs?rev=398761&view=rev
Log:
DERBY-1187

backported fix for DERBY-1187 from trunk to 10.1 branch, targeted for the
10.1.3 release.  Original comments from fix (392439):
Defragment pass was not being committed properly, so when rows at
front of table were being deleted and freeing pages the defragment
pass did not move the rows to the free pages.  Longer term the
defragment pass should commit more often, see DERBY-1188.
Added extra test to OnlineCompressTest.java for this case.


Modified:
    db/derby/code/branches/10.1/java/engine/org/apache/derby/iapi/db/OnlineCompress.java
    db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/OnlineCompressTest.out
    db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/store/BaseTest.java
    db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/store/OnlineCompressTest.java

Modified: db/derby/code/branches/10.1/java/engine/org/apache/derby/iapi/db/OnlineCompress.java
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/engine/org/apache/derby/iapi/db/OnlineCompress.java?rev=398761&r1=398760&r2=398761&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/engine/org/apache/derby/iapi/db/OnlineCompress.java (original)
+++ db/derby/code/branches/10.1/java/engine/org/apache/derby/iapi/db/OnlineCompress.java Mon
May  1 18:27:21 2006
@@ -74,16 +74,46 @@
     IN DEFRAGMENT_ROWS   SMALLINT,
     IN TRUNCATE_END      SMALLINT)
 <p>
-This system procedure can be used to force 3 levels of in place
-compression of a SQL table.  The table is specified using the
-SCHEMANAME and TABLENAME arguments.  
+Use the SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE system procedure to reclaim 
+unused, allocated space in a table and its indexes. Typically, unused allocated
+space exists when a large amount of data is deleted from a table, and there
+have not been subsequent inserts to use the space freed by the deletes.  
+By default, Derby does not return unused space to the operating system. For 
+example, once a page has been allocated to a table or index, it is not 
+automatically returned to the operating system until the table or index is 
+destroyed. SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE allows you to return unused 
+space to the operating system.
 <p>
+This system procedure can be used to force 3 levels of in place compression
+of a SQL table: PURGE_ROWS, DEFRAGMENT_ROWS, TRUNCATE_END.  Unlike 
+SYSCS_UTIL.SYSCS_COMPRESS_TABLE() all work is done in place in the existing
+table/index.
+<p>
+Syntax:
+SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE(
+    IN SCHEMANAME        VARCHAR(128),
+    IN TABLENAME         VARCHAR(128),
+    IN PURGE_ROWS        SMALLINT,
+    IN DEFRAGMENT_ROWS   SMALLINT,
+    IN TRUNCATE_END      SMALLINT)
+<p>
+SCHEMANAME: 
+An input argument of type VARCHAR(128) that specifies the schema of the table. Passing a
null will result in an error.
+<p>
+TABLENAME:
+An input argument of type VARCHAR(128) that specifies the table name of the 
+table. The string must exactly match the case of the table name, and the 
+argument of "Fred" will be passed to SQL as the delimited identifier 'Fred'. 
+Passing a null will result in an error.
+<p>
+PURGE_ROWS:
 If PURGE_ROWS is set to non-zero then a single pass is made through the table 
 which will purge committed deleted rows from the table.  This space is then
 available for future inserted rows, but remains allocated to the table.
 As this option scans every page of the table, it's performance is linearly 
 related to the size of the table.
 <p>
+DEFRAGMENT_ROWS:
 If DEFRAGMENT_ROWS is set to non-zero then a single defragment pass is made
 which will move existing rows from the end of the table towards the front
 of the table.  The goal of the defragment run is to empty a set of pages
@@ -93,10 +123,53 @@
 needs to update index entries for every base table row move, and thus execution
 time is linearly related to the size of the table.
 <p>
+TRUNCATE_END:
 If TRUNCATE_END is set to non-zero then all contiguous pages at the end of
-the table will be returned to the OS.  Running the DEFRAGMENT_ROWS option may
-increase the number of pages affected.  This option itself does no scans of
-the table, so performs on the order of a few system calls.
+the table will be returned to the OS.  Running the PURGE_ROWS and/or 
+DEFRAGMENT_ROWS passes options may increase the number of pages affected.  
+This option itself does no scans of the table, so performs on the order of a 
+few system calls.
+<p>
+SQL example:
+To compress a table called CUSTOMER in a schema called US, using all 
+available compress options:
+call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('US', 'CUSTOMER', 1, 1, 1);
+
+To quickly just return the empty free space at the end of the same table, 
+this option will run much quicker than running all phases but will likely
+return much less space:
+call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('US', 'CUSTOMER', 0, 0, 1);
+
+Java example:
+To compress a table called CUSTOMER in a schema called US, using all 
+available compress options:
+
+CallableStatement cs = conn.prepareCall
+("CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE(?, ?, ?, ?, ?)");
+cs.setString(1, "US");
+cs.setString(2, "CUSTOMER");
+cs.setShort(3, (short) 1);
+cs.setShort(4, (short) 1);
+cs.setShort(5, (short) 1);
+cs.execute();
+
+To quickly just return the empty free space at the end of the same table, 
+this option will run much quicker than running all phases but will likely
+return much less space:
+
+CallableStatement cs = conn.prepareCall
+("CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE(?, ?, ?, ?, ?)");
+cs.setString(1, "US");
+cs.setString(2, "CUSTOMER");
+cs.setShort(3, (short) 0);
+cs.setShort(4, (short) 0);
+cs.setShort(5, (short) 1);
+cs.execute();
+
+<p>
+It is recommended that the SYSCS_UTIL.SYSCS_COMPRESS_TABLE procedure is 
+issued in auto-commit mode.
+Note: This procedure acquires an exclusive table lock on the table being compressed. All
statement plans dependent on the table or its indexes are invalidated. For information on
identifying unused space, see the Derby Server and Administration Guide.
 
 TODO LIST:
 o defragment requires table level lock in nested user transaction, which
@@ -218,10 +291,15 @@
                     schemaName + "." + tableName);
             }
 
-            /* Skip views */
-            if (td.getTableType() == TableDescriptor.VIEW_TYPE)
+            switch (td.getTableType())
             {
-                return;
+            /* Skip views and vti tables */
+            case TableDescriptor.VIEW_TYPE:
+            	return;
+            // other types give various errors here
+            // DERBY-719,DERBY-720
+            default:
+            	break;
             }
 
 
@@ -321,6 +399,15 @@
                     }
                 }
             }
+
+            // TODO - It would be better if commits happened more frequently
+            // in the nested transaction, but to do that there has to be more
+            // logic to catch a ddl that might jump in the middle of the 
+            // above loop and invalidate the various table control structures
+            // which are needed to properly update the indexes.  For example
+            // the above loop would corrupt an index added midway through
+            // the loop if not properly handled.  See DERBY-1188.  
+            nested_tc.commit();
 			
 		}
 		catch (StandardException se)
@@ -403,9 +490,15 @@
                 schemaName + "." + tableName);
         }
 
-        /* Skip views */
-        if (td.getTableType() != TableDescriptor.VIEW_TYPE)
+        switch (td.getTableType())
         {
+        /* Skip views and vti tables */
+        case TableDescriptor.VIEW_TYPE:
+        	break;
+        // other types give various errors here
+        // DERBY-719,DERBY-720
+        default:
+          {
 
             ConglomerateDescriptor[] conglom_descriptors = 
                 td.getConglomerateDescriptors();
@@ -416,6 +509,7 @@
 
                 tc.purgeConglomerate(cd.getConglomerateNumber());
             }
+          }
         }
 
         return;
@@ -454,10 +548,16 @@
                 schemaName + "." + tableName);
         }
 
-        /* Skip views */
-        if (td.getTableType() != TableDescriptor.VIEW_TYPE)
+        switch (td.getTableType())
         {
-            ConglomerateDescriptor[] conglom_descriptors = 
+        /* Skip views and vti tables */
+        case TableDescriptor.VIEW_TYPE:
+        	break;
+        // other types give various errors here
+        // DERBY-719,DERBY-720
+        default:
+          {
+          ConglomerateDescriptor[] conglom_descriptors = 
                 td.getConglomerateDescriptors();
 
             for (int cd_idx = 0; cd_idx < conglom_descriptors.length; cd_idx++)
@@ -466,6 +566,7 @@
 
                 tc.compressConglomerate(cd.getConglomerateNumber());
             }
+          }
         }
 
         return;

Modified: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/OnlineCompressTest.out
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/OnlineCompressTest.out?rev=398761&r1=398760&r2=398761&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/OnlineCompressTest.out
(original)
+++ db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/OnlineCompressTest.out
Mon May  1 18:27:21 2006
@@ -72,3 +72,9 @@
 Executing test: begin checkPurgePhase50 row test, create = false.
 Executing test: end checkPurgePhase50 row test.
 Ending test: test3
+Beginning test: test5
+Executing test: begin test5: 2000 row test.
+Executing test: end test5: 2000 row test.
+Executing test: begin test5: 10000 row test.
+Executing test: end test5: 10000 row test.
+Ending test: test5

Modified: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/store/BaseTest.java
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/store/BaseTest.java?rev=398761&r1=398760&r2=398761&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/store/BaseTest.java
(original)
+++ db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/store/BaseTest.java
Mon May  1 18:27:21 2006
@@ -43,7 +43,8 @@
 **/
 public abstract class BaseTest
 {
-    private static boolean debug_system_procedures_created = false;
+    private   static boolean debug_system_procedures_created = false;
+    protected static boolean verbose = false;
 
     abstract public void testList(Connection conn) throws SQLException;
 
@@ -309,14 +310,14 @@
         //     state,
         //     status
         // from
-        //     new org.apache.derby.diag.LockTable() l  
-        // right outer join new org.apache.derby.diag.TransactionTable() t
+        //     SYSCS_DIAG.LOCK_TABLE l  
+        // right outer join SYSCS_DIAG.TRANSACTION_TABLE t
         //     on l.xid = t.xid where l.tableType <> 'S' and 
         //        t.type='UserTransaction'
         // order by
         //     tabname, type desc, mode, cnt, lockname;
         String lock_query = 
-            "select cast(l.xid as char(8)) as xid, cast(username as char(8)) as username,
cast(t.type as char(8)) as trantype, cast(l.type as char(8)) as type, cast(lockcount as char(3))
as cnt, cast(mode as char(4)) as mode, cast(tablename as char(12)) as tabname, cast(lockname
as char(10)) as lockname, state, status from new org.apache.derby.diag.LockTable() l right
outer join new org.apache.derby.diag.TransactionTable() t on l.xid = t.xid where l.tableType
<> 'S' ";
+            "select cast(l.xid as char(8)) as xid, cast(username as char(8)) as username,
cast(t.type as char(8)) as trantype, cast(l.type as char(8)) as type, cast(lockcount as char(3))
as cnt, cast(mode as char(4)) as mode, cast(tablename as char(12)) as tabname, cast(lockname
as char(10)) as lockname, state, status from SYSCS_DIAG.LOCK_TABLE l right outer join SYSCS_DIAG.LOCK_TABLE
t on l.xid = t.xid where l.tableType <> 'S' ";
         if (!include_system_locks)
             lock_query += "and t.type='UserTransaction' ";
         
@@ -356,5 +357,139 @@
         rs.close();
 
         return(lock_output);
+    }
+
+    /**
+     * create given table on the input connection.
+     * <p>
+     * Takes care of dropping the table if it exists already.
+     * <p>
+     *
+	 * @exception  StandardException  Standard exception policy.
+     **/
+    public void createTable(
+    Connection  conn,
+    String      tbl_name,
+    String      create_str)
+		throws SQLException
+    {
+        Statement  stmt = conn.createStatement();
+
+        // drop table, ignore table does not exist error.
+
+        try
+        {
+            stmt.executeUpdate("drop table " + tbl_name);
+        }
+        catch (Exception e)
+        {
+            // ignore drop table errors.
+        }
+
+        stmt.executeUpdate(create_str);
+    }
+
+    /**
+     * call the space table vti.
+     * <p>
+     * Utility test function to call the space table vti to get information
+     * about allocated and free pages.  Information is passed back in an
+     * int array as follows:
+     *   is_index                 = ret_info[0];
+     *   num_alloc                = ret_info[1];
+     *   num_free                 = ret_info[2];
+     *   page_size                = ret_info[3];
+     *   estimate_space_savings   = ret_info[4];
+     * <p>
+     *
+	 * @return the space information about the table.
+     *
+	 * @exception  StandardException  Standard exception policy.
+     **/
+    protected static final int SPACE_INFO_IS_INDEX          = 0;
+    protected static final int SPACE_INFO_NUM_ALLOC         = 1;
+    protected static final int SPACE_INFO_NUM_FREE          = 2;
+    protected static final int SPACE_INFO_NUM_UNFILLED      = 3;
+    protected static final int SPACE_INFO_PAGE_SIZE         = 4;
+    protected static final int SPACE_INFO_ESTIMSPACESAVING  = 5;
+
+    protected static final int SPACE_INFO_NUMCOLS           = 6;
+
+    protected int[] getSpaceInfo(
+    Connection  conn,
+    String      schemaName,
+    String      tableName,
+    boolean     commit_xact)
+		throws SQLException
+    {
+        String stmt_str = 
+            "select conglomeratename, isindex, numallocatedpages, numfreepages, numunfilledpages,
pagesize, estimspacesaving from new org.apache.derby.diag.SpaceTable('" +
+            tableName + "') t where isindex = 0";
+        PreparedStatement space_stmt = conn.prepareStatement(stmt_str);
+        ResultSet rs = space_stmt.executeQuery();
+
+        if (!rs.next())
+        {
+            if (SanityManager.DEBUG)
+            {
+                SanityManager.THROWASSERT(
+                    "No rows returned from space table query on table: " +
+                    schemaName + "." + tableName);
+            }
+        }
+
+        int[] ret_info = new int[SPACE_INFO_NUMCOLS];
+        String conglomerate_name        = rs.getString(1);
+        for (int i = 0; i < SPACE_INFO_NUMCOLS; i++)
+        {
+            ret_info[i] = rs.getInt(i + 2);
+        }
+
+        if (rs.next())
+        {
+            if (SanityManager.DEBUG)
+            {
+                SanityManager.THROWASSERT(
+                    "More than one row returned from space query on table: " +
+                    schemaName + "." + tableName);
+            }
+        }
+
+        if (verbose)
+        {
+            System.out.println(
+                "Space information for " + schemaName + "." + tableName + ":");
+            System.out.println(
+                "isindex = " + ret_info[SPACE_INFO_IS_INDEX]);
+            System.out.println(
+                "num_alloc = " + ret_info[SPACE_INFO_NUM_ALLOC]);
+            System.out.println(
+                "num_free = " + ret_info[SPACE_INFO_NUM_FREE]);
+            System.out.println(
+                "num_unfilled = " + ret_info[SPACE_INFO_NUM_UNFILLED]);
+            System.out.println(
+                "page_size = " + ret_info[SPACE_INFO_PAGE_SIZE]);
+            System.out.println(
+                "estimspacesaving = " + ret_info[SPACE_INFO_ESTIMSPACESAVING]);
+        }
+
+        rs.close();
+
+        if (commit_xact)
+            conn.commit();
+
+        return(ret_info);
+    }
+
+    /**
+     * Given output from getSpaceInfo(), return total pages in file.
+     * <p>
+     * simply the sum of allocated and free pages.
+     *
+     **/
+    protected int total_pages(int[] space_info)
+    {
+        return(space_info[SPACE_INFO_NUM_FREE] + 
+               space_info[SPACE_INFO_NUM_ALLOC]);
     }
 }

Modified: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/store/OnlineCompressTest.java
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/store/OnlineCompressTest.java?rev=398761&r1=398760&r2=398761&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/store/OnlineCompressTest.java
(original)
+++ db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/store/OnlineCompressTest.java
Mon May  1 18:27:21 2006
@@ -74,89 +74,6 @@
     }
 
     /**
-     * call the space table vti.
-     * <p>
-     * Utility test function to call the space table vti to get information
-     * about allocated and free pages.  Information is passed back in an
-     * int array as follows:
-     *   is_index                 = ret_info[0];
-     *   num_alloc                = ret_info[1];
-     *   num_free                 = ret_info[2];
-     *   page_size                = ret_info[3];
-     *   estimate_space_savings   = ret_info[4];
-     * <p>
-     *
-	 * @return the space information about the table.
-     *
-	 * @exception  StandardException  Standard exception policy.
-     **/
-    private static final int SPACE_INFO_IS_INDEX        = 0;
-    private static final int SPACE_INFO_NUM_ALLOC       = 1;
-    private static final int SPACE_INFO_NUM_FREE        = 2;
-    private static final int SPACE_INFO_PAGE_SIZE       = 3;
-    private static final int SPACE_INFO_ESTIMSPACESAVING = 4;
-    private int[] getSpaceInfo(
-    Connection  conn,
-    String      schemaName,
-    String      tableName,
-    boolean     commit_xact)
-		throws SQLException
-    {
-        String stmt_str = 
-            "select conglomeratename, isindex, numallocatedpages, numfreepages, pagesize,
estimspacesaving from new org.apache.derby.diag.SpaceTable('" +
-            tableName + "') t where isindex = 0";
-        PreparedStatement space_stmt = conn.prepareStatement(stmt_str);
-        ResultSet rs = space_stmt.executeQuery();
-
-        if (!rs.next())
-        {
-            if (SanityManager.DEBUG)
-            {
-                SanityManager.THROWASSERT(
-                    "No rows returned from space table query on table: " +
-                    schemaName + "." + tableName);
-            }
-        }
-
-        int[] ret_info = new int[5];
-        String conglomerate_name        = rs.getString(1);
-        for (int i = 0; i < 5; i++)
-        {
-            ret_info[i] = rs.getInt(i + 2);
-        }
-
-        if (rs.next())
-        {
-            if (SanityManager.DEBUG)
-            {
-                SanityManager.THROWASSERT(
-                    "More than one row returned from space query on table: " +
-                    schemaName + "." + tableName);
-            }
-        }
-
-        if (verbose)
-        {
-            System.out.println(
-                "Space information for " + schemaName + "." + tableName + ":");
-            System.out.println("isindex = " + ret_info[SPACE_INFO_IS_INDEX]);
-            System.out.println("num_alloc = " + ret_info[SPACE_INFO_NUM_ALLOC]);
-            System.out.println("num_free = " + ret_info[SPACE_INFO_NUM_FREE]);
-            System.out.println("page_size = " + ret_info[SPACE_INFO_PAGE_SIZE]);
-            System.out.println(
-                "estimspacesaving = " + ret_info[SPACE_INFO_ESTIMSPACESAVING]);
-        }
-
-        rs.close();
-
-        if (commit_xact)
-            conn.commit();
-
-        return(ret_info);
-    }
-
-
-    /**
      * Create and load a table.
      * <p>
      * If create_table is set creates a test data table with indexes.
@@ -401,13 +318,15 @@
         "    IS_INDEX         =" + before_info[SPACE_INFO_IS_INDEX]     + 
         "\n    NUM_ALLOC        =" + before_info[SPACE_INFO_NUM_ALLOC]    +
         "\n    NUM_FREE         =" + before_info[SPACE_INFO_NUM_FREE]     +
+        "\n    NUM_UNFILLED     =" + before_info[SPACE_INFO_NUM_UNFILLED] +
         "\n    PAGE_SIZE        =" + before_info[SPACE_INFO_PAGE_SIZE]    +
         "\n    ESTIMSPACESAVING =" + before_info[SPACE_INFO_ESTIMSPACESAVING]);
         System.out.println("after_info:");
         System.out.println(
-        "    IS_INDEX         =" + after_info[SPACE_INFO_IS_INDEX]     + 
+        "    IS_INDEX         =" + after_info[SPACE_INFO_IS_INDEX]       + 
         "\n    NUM_ALLOC        =" + after_info[SPACE_INFO_NUM_ALLOC]    +
         "\n    NUM_FREE         =" + after_info[SPACE_INFO_NUM_FREE]     +
+        "\n    NUM_UNFILLED     =" + after_info[SPACE_INFO_NUM_UNFILLED] +
         "\n    PAGE_SIZE        =" + after_info[SPACE_INFO_PAGE_SIZE]    +
         "\n    ESTIMSPACESAVING =" + after_info[SPACE_INFO_ESTIMSPACESAVING]);
     }
@@ -994,6 +913,311 @@
         endTest(conn, test_name);
     }
 
+    /**
+     * Create and load table for test5.
+     * <p>
+     * schema of table:
+     *     keycol   int, 
+     *     onehalf  int, 
+     *     onethird int, 
+     *     c        varchar(300)
+     *
+     * @param conn          Connection to use for sql execution.
+     * @param create_table  If true, create new table - otherwise load into
+     *                      existing table.
+     * @param tblname       table to use.
+     * @param num_rows      number of rows to add to the table.
+     *
+	 * @exception  StandardException  Standard exception policy.
+     **/
+    private void test5_load(
+    Connection  conn,
+    String      schemaName,
+    String      table_name,
+    int         num_rows)
+        throws SQLException
+    {
+        Statement s = conn.createStatement();
+
+        s.execute(
+            "create table " + table_name + 
+            " (keycol integer primary key, onehalf integer, onethird integer, c varchar(300))");
+        s.close();
+
+        PreparedStatement insert_stmt = 
+            conn.prepareStatement(
+                "insert into " + table_name + " values(?, ?, ?, ?)");
+
+        char[]  data1_data = new char[200];
+
+        for (int i = 0; i < data1_data.length; i++)
+        {
+            data1_data[i] = 'b';
+        }
+        String  data1_str = new String(data1_data);
+
+        for (int i = 0; i < num_rows; i++)
+        {
+            insert_stmt.setInt(1, i);               // keycol
+            insert_stmt.setInt(2, i % 2);           // onehalf:  0 or 1 
+            insert_stmt.setInt(3, i % 3);           // onethird: 0, 1, or 3
+            insert_stmt.setString(4, data1_str);    // c
+            insert_stmt.execute();
+        }
+
+        conn.commit();
+    }
+    
+    /**
+     * Execute test5, simple defragement test. 
+     * <p>
+     * o delete every other row, defragment
+     * o delete every third row, defragment
+     * o delete last 1000 rows, defragment
+     * o delete first 512 rows, defragment.
+     * <p>
+     * run test with at least 2000 rows.
+     **/
+    private void test5_run(
+    Connection  conn,
+    String      schemaName,
+    String      table_name,
+    int         num_rows)
+        throws SQLException
+    {
+        testProgress("begin test5: " + num_rows + " row test.");
+
+        if (verbose)
+            testProgress("Calling compress.");
+
+        // compress with no deletes should not affect size
+        int[] ret_before = getSpaceInfo(conn, "APP", table_name, true);
+        callCompress(conn, "APP", table_name, true, true, true, true);
+        int[] ret_after  = getSpaceInfo(conn, "APP", table_name, true);
+
+        if (ret_after[SPACE_INFO_NUM_ALLOC] != ret_before[SPACE_INFO_NUM_ALLOC])
+        {
+            log_wrong_count(
+                "Expected no alloc page change.", 
+                table_name, num_rows, 
+                ret_before[SPACE_INFO_NUM_ALLOC], 
+                ret_after[SPACE_INFO_NUM_ALLOC],
+                ret_before, ret_after);
+        }
+
+        if (verbose)
+            testProgress("calling consistency checker.");
+
+        if (!checkConsistency(conn, schemaName, table_name))
+        {
+            logError("conistency check failed.");
+        }
+
+        // DELETE EVERY OTHER ROW, COMPRESS, CHECK
+        //
+        //
+
+        // delete all the rows every other row.
+        ret_before = getSpaceInfo(conn, "APP", table_name, true);
+        executeQuery(
+            conn, "delete from " + table_name + " where onehalf = 0", true);
+
+        if (verbose)
+            testProgress("deleted every other row, now calling compress.");
+
+        callCompress(conn, "APP", table_name, true, true, true, true);
+        ret_after  = getSpaceInfo(conn, "APP", table_name, true);
+
+        if (total_pages(ret_after) != total_pages(ret_before))
+        {
+            // currently deleting every other row does not add free or unfilled
+            // pages to the container so defragment has nowhere to put the rows.
+
+            log_wrong_count(
+                "Expected no truncation.",
+                table_name, num_rows, 1, ret_after[SPACE_INFO_NUM_ALLOC],
+                ret_before, ret_after);
+        }
+
+        if (verbose)
+            testProgress("calling consistency checker.");
+
+        if (!checkConsistency(conn, schemaName, table_name))
+        {
+            logError("conistency check failed.");
+        }
+
+        // DELETE EVERY THIRD ROW in original dataset, COMPRESS, CHECK
+        //
+        //
+
+        // delete every third row
+        ret_before = getSpaceInfo(conn, "APP", table_name, true);
+        executeQuery(
+            conn, "delete from " + table_name + " where onethird = 0", true);
+
+        if (verbose)
+            testProgress("deleted every third row, now calling compress.");
+
+        callCompress(conn, "APP", table_name, true, true, true, true);
+        ret_after  = getSpaceInfo(conn, "APP", table_name, true);
+
+        if (total_pages(ret_after) != total_pages(ret_before))
+        {
+            // currently deleting every third row does not create any free 
+            // or unfilled pages so defragment has no place to move rows.
+            log_wrong_count(
+                "Expected no truncation.",
+                table_name, num_rows, 1, ret_after[SPACE_INFO_NUM_ALLOC],
+                ret_before, ret_after);
+        }
+
+        if (verbose)
+            testProgress("calling consistency checker.");
+
+        if (!checkConsistency(conn, schemaName, table_name))
+        {
+            logError("conistency check failed.");
+        }
+
+        // DELETE top "half" of rows in original dataset, COMPRESS, CHECK
+        //
+        //
+
+        // delete top "half" of the rows in the original dataset.
+        ret_before = getSpaceInfo(conn, "APP", table_name, true);
+        executeQuery(
+            conn, "delete from " + table_name + " where keycol > " + 
+            (num_rows / 2), true);
+
+        if (verbose)
+            testProgress("deleted top half of the rows, now calling compress.");
+
+        callCompress(conn, "APP", table_name, true, true, true, true);
+        ret_after  = getSpaceInfo(conn, "APP", table_name, true);
+
+        // compress should be able to clean up about 1/2 of the pages.
+        if (verbose)
+        {
+            log_wrong_count(
+                "deleted top half keys, spaceinfo:",
+                table_name, num_rows, 
+                ((total_pages(ret_before) / 2) + 2),
+                ret_after[SPACE_INFO_NUM_ALLOC],
+                ret_before, ret_after);
+        }
+
+        if (total_pages(ret_after) > ((total_pages(ret_before) / 2) + 2))
+        {
+            log_wrong_count(
+                "Expected at least " + 
+                (ret_before[SPACE_INFO_NUM_ALLOC] / 2 + 2) +
+                " pages to be truncated.",
+                table_name, num_rows, 1, ret_after[SPACE_INFO_NUM_ALLOC],
+                ret_before, ret_after);
+        }
+
+        if (verbose)
+            testProgress("calling consistency checker.");
+
+        if (!checkConsistency(conn, schemaName, table_name))
+        {
+            logError("conistency check failed.");
+        }
+
+        // DELETE 1st 500 rows in original dataset, COMPRESS, CHECK
+        //
+        //
+
+        // delete keys less than 500
+        ret_before = getSpaceInfo(conn, "APP", table_name, true);
+        executeQuery(
+            conn, "delete from " + table_name + " where keycol < 500 ", true);
+
+        if (verbose)
+            testProgress("deleted keys < 500, now calling compress.");
+
+        callCompress(conn, "APP", table_name, true, true, true, true);
+        ret_after  = getSpaceInfo(conn, "APP", table_name, true);
+
+        if (verbose)
+        {
+            log_wrong_count(
+                "deleted bottom 500 keys, spaceinfo:",
+                table_name, num_rows, 
+                (total_pages(ret_before) - 33),
+                ret_after[SPACE_INFO_NUM_ALLOC],
+                ret_before, ret_after);
+        }
+
+        // The bottom 500 keys, assuming 4k pages, takes about 33 pages
+        if (total_pages(ret_after) > (total_pages(ret_before) - 33))
+        {
+            log_wrong_count(
+                "Expected at least 33 pages reclaimed.",
+                table_name, num_rows, 1, ret_after[SPACE_INFO_NUM_ALLOC],
+                ret_before, ret_after);
+        }
+
+        if (verbose)
+            testProgress("calling consistency checker.");
+
+        if (!checkConsistency(conn, schemaName, table_name))
+        {
+            logError("conistency check failed.");
+        }
+
+
+        conn.commit();
+
+        testProgress("end test5: " + num_rows + " row test.");
+    }
+
+    /**
+     * Cleanup after test5_run
+     **/
+    private void test5_cleanup(
+    Connection  conn,
+    String      schemaName,
+    String      table_name,
+    int         num_rows)
+        throws SQLException
+    {
+        executeQuery(conn, "drop table " + table_name, true);
+    }
+
+    /**
+     * Test 5 - simple defragment test.
+     * <p>
+     * Create dataset and then:
+     * o delete every other row, defragment
+     * o delete every third row, defragment
+     * o delete last 1000 rows, defragment
+     * o delete first 512 rows, defragment.
+     * <p>
+     * run test with at least 2000 rows.
+     *
+     **/
+    private void test5(
+    Connection  conn,
+    String      test_name,
+    String      table_name)
+        throws SQLException 
+    {
+        beginTest(conn, test_name);
+
+        int[] test_cases = {2000, 10000};
+
+        for (int i = 0; i < test_cases.length; i++)
+        {
+            test5_load(conn, "APP", table_name, test_cases[i]);
+            test5_run(conn, "APP", table_name, test_cases[i]);
+            test5_cleanup(conn, "APP", table_name, test_cases[i]);
+        }
+
+        endTest(conn, test_name);
+    }
+
 
 
     public void testList(Connection conn)
@@ -1002,7 +1226,8 @@
         test1(conn, "test1", "TEST1");
         // test2(conn, "test2", "TEST2");
         test3(conn, "test3", "TEST3");
-        // test4(conn, "test2", "TEST2");
+        // test4(conn, "test4", "TEST4");
+        test5(conn, "test5", "TEST5");
     }
 
     public static void main(String[] argv) 



Mime
View raw message