db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kmars...@apache.org
Subject svn commit: r1469436 - in /db/derby/code/branches/10.8.3.1_testcompat: ./ java/testing/org/apache/derbyTesting/functionTests/tests/lang/ java/testing/org/apache/derbyTesting/functionTests/tests/store/ java/testing/org/apache/derbyTesting/junit/
Date Thu, 18 Apr 2013 16:59:17 GMT
Author: kmarsden
Date: Thu Apr 18 16:59:17 2013
New Revision: 1469436

URL: http://svn.apache.org/r1469436
Log:
DERBY-3790 Change some cases to skip statistics update.
Merge 1341481 from trunk
Non-functional change.


Modified:
    db/derby/code/branches/10.8.3.1_testcompat/   (props changed)
    db/derby/code/branches/10.8.3.1_testcompat/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SelectivityTest.java
    db/derby/code/branches/10.8.3.1_testcompat/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UpdateStatisticsTest.java
    db/derby/code/branches/10.8.3.1_testcompat/java/testing/org/apache/derbyTesting/functionTests/tests/store/AutomaticIndexStatisticsTest.java
    db/derby/code/branches/10.8.3.1_testcompat/java/testing/org/apache/derbyTesting/junit/IndexStatsUtil.java

Propchange: db/derby/code/branches/10.8.3.1_testcompat/
------------------------------------------------------------------------------
  Merged /db/derby/code/trunk:r1341481

Modified: db/derby/code/branches/10.8.3.1_testcompat/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SelectivityTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8.3.1_testcompat/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SelectivityTest.java?rev=1469436&r1=1469435&r2=1469436&view=diff
==============================================================================
--- db/derby/code/branches/10.8.3.1_testcompat/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SelectivityTest.java
(original)
+++ db/derby/code/branches/10.8.3.1_testcompat/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SelectivityTest.java
Thu Apr 18 16:59:17 2013
@@ -104,7 +104,6 @@ public class SelectivityTest extends Bas
                         {"TEMPLATE_102","numunique= 200 numrows= 4000","2"},
                         {"TEMPLATE_22","numunique= 20 numrows= 4000","1"},
                         {"TEMPLATE_22","numunique= 40 numrows= 4000","2"},
-                        {"TEMPLATE_ID","numunique= 4000 numrows= 4000","1"},
                         {"TEMPLATE_TWENTY","numunique= 20 numrows= 4000","1"},
                         {"TEMPLATE_TWO","numunique= 2 numrows= 4000","1"}});            
  
                 s
@@ -118,7 +117,6 @@ public class SelectivityTest extends Bas
                         {"TEMPLATE_102","numunique= 200 numrows= 4000","2"},
                         {"TEMPLATE_22","numunique= 20 numrows= 4000","1"},
                         {"TEMPLATE_22","numunique= 40 numrows= 4000","2"},
-                        {"TEMPLATE_ID","numunique= 4000 numrows= 4000","1"},
                         {"TEMPLATE_TWENTY","numunique= 20 numrows= 4000","1"},
                         {"TEMPLATE_TWO","numunique= 2 numrows= 4000","1"},
                         {"TEST_ID","numunique= 4000 numrows= 4000","1"}}                
                                              

Modified: db/derby/code/branches/10.8.3.1_testcompat/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UpdateStatisticsTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8.3.1_testcompat/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UpdateStatisticsTest.java?rev=1469436&r1=1469435&r2=1469436&view=diff
==============================================================================
--- db/derby/code/branches/10.8.3.1_testcompat/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UpdateStatisticsTest.java
(original)
+++ db/derby/code/branches/10.8.3.1_testcompat/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UpdateStatisticsTest.java
Thu Apr 18 16:59:17 2013
@@ -289,14 +289,12 @@ public class UpdateStatisticsTest extend
         s.executeUpdate("INSERT INTO TEST_TAB_1 VALUES(1,1,1),(2,2,2)");
         s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEST_TAB_1', null)");
         stats.assertNoStatsTable("TEST_TAB_1");
-        //Add primary key constraint to the table and now we should find a 
-        // statistics row for it
+        // Add primary key constraint to the table. With DERBY-3790 this won't
+        // create a statistics entry, since the key consist of single column.
         s.executeUpdate("ALTER TABLE TEST_TAB_1 "+
                 "ADD CONSTRAINT TEST_TAB_1_PK_1 "+
         		"PRIMARY KEY (c11)");
-        stats.assertTableStats("TEST_TAB_1",1);
-        //Dropping primary key constraint will drop the corresponding
-        // statistics
+        stats.assertNoStatsTable("TEST_TAB_1");
         s.executeUpdate("ALTER TABLE TEST_TAB_1 "+
                 "DROP CONSTRAINT TEST_TAB_1_PK_1");
         stats.assertNoStatsTable("TEST_TAB_1");
@@ -307,40 +305,52 @@ public class UpdateStatisticsTest extend
         s.executeUpdate("ALTER TABLE TEST_TAB_1 "+
                 "ADD CONSTRAINT TEST_TAB_1_PK_1 "+
         		"PRIMARY KEY (c11)");
-        //The statistics for primary key constraint has been added
-        stats.assertTableStats("TEST_TAB_1",1);
+        stats.assertNoStatsTable("TEST_TAB_1");
 
         //Test - unique key constraint
         s.executeUpdate("ALTER TABLE TEST_TAB_1 "+
                 "ADD CONSTRAINT TEST_TAB_1_UNQ_1 "+
         		"UNIQUE (c12)");
-        stats.assertTableStats("TEST_TAB_1",2);
+        stats.assertNoStatsTable("TEST_TAB_1");
         s.executeUpdate("ALTER TABLE TEST_TAB_1 "+
                 "DROP CONSTRAINT TEST_TAB_1_UNQ_1");
-        stats.assertTableStats("TEST_TAB_1",1);
+        stats.assertNoStatsTable("TEST_TAB_1");
         s.executeUpdate("ALTER TABLE TEST_TAB_1 "+
                 "DROP CONSTRAINT TEST_TAB_1_PK_1");
         stats.assertNoStatsTable("TEST_TAB_1");
         s.executeUpdate("ALTER TABLE TEST_TAB_1 "+
                 "ADD CONSTRAINT TEST_TAB_1_PK_1 "+
         		"PRIMARY KEY (c11)");
+        stats.assertNoStatsTable("TEST_TAB_1");
+
+        //Test - non-unique index
+        s.executeUpdate("CREATE INDEX TEST_TAB_1_NUNQ_1 ON TEST_TAB_1(c12)");
         stats.assertTableStats("TEST_TAB_1",1);
+        s.executeUpdate("DROP INDEX TEST_TAB_1_NUNQ_1");
+        stats.assertNoStatsTable("TEST_TAB_1");
+        s.executeUpdate("ALTER TABLE TEST_TAB_1 "+
+                "DROP CONSTRAINT TEST_TAB_1_PK_1");
+        stats.assertNoStatsTable("TEST_TAB_1");
+        s.executeUpdate("ALTER TABLE TEST_TAB_1 "+
+                "ADD CONSTRAINT TEST_TAB_1_PK_1 "+
+        		"PRIMARY KEY (c11)");
+        stats.assertNoStatsTable("TEST_TAB_1");
 
         //Test - unique key constraint on nullable column & non-nullable column
         s.executeUpdate("ALTER TABLE TEST_TAB_1 "+
                 "ADD CONSTRAINT TEST_TAB_1_UNQ_2 "+
         		"UNIQUE (c12, c13)");
-        stats.assertTableStats("TEST_TAB_1",3);
+        stats.assertTableStats("TEST_TAB_1",2);
         s.executeUpdate("ALTER TABLE TEST_TAB_1 "+
                 "DROP CONSTRAINT TEST_TAB_1_UNQ_2");
-        stats.assertTableStats("TEST_TAB_1",1);
+        stats.assertNoStatsTable("TEST_TAB_1");
         s.executeUpdate("ALTER TABLE TEST_TAB_1 "+
                 "DROP CONSTRAINT TEST_TAB_1_PK_1");
         stats.assertNoStatsTable("TEST_TAB_1");
         s.executeUpdate("ALTER TABLE TEST_TAB_1 "+
                 "ADD CONSTRAINT TEST_TAB_1_PK_1 "+
         		"PRIMARY KEY (c11)");
-        stats.assertTableStats("TEST_TAB_1",1);
+        stats.assertNoStatsTable("TEST_TAB_1");
         
         //Test - foreign key but no primary key constraint
         s.executeUpdate("CREATE TABLE TEST_TAB_3 (c31 int not null)");
@@ -369,19 +379,19 @@ public class UpdateStatisticsTest extend
         //Like primary key earlier, adding foreign key constraint didn't
         // automatically add a statistics row for it. Have to run update
         // statistics manually to get a row added for it's stat
-        stats.assertTableStats("TEST_TAB_2",1);
+        stats.assertNoStatsTable("TEST_TAB_2");
         s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEST_TAB_2', null)");
-        stats.assertTableStats("TEST_TAB_2",2);
+        stats.assertTableStats("TEST_TAB_2",1);
         //Number of statistics row for TEST_TAB_1 will remain unchanged since
         // it has only primary key defined on it
-        stats.assertTableStats("TEST_TAB_1",1);
+        stats.assertNoStatsTable("TEST_TAB_1");
         s.executeUpdate("ALTER TABLE TEST_TAB_2 "+
                 "DROP CONSTRAINT TEST_TAB_2_FK_1");
-        //Dropping the foreign key constraint should remove one of the 
-        // statistics row for TEST_TAB_2. 
-        stats.assertTableStats("TEST_TAB_2",1);
+        //Dropping the foreign key constraint should cause the statistics row
+        // for TEST_TAB_2 to be dropped as well.
+        stats.assertNoStatsTable("TEST_TAB_2");
         s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEST_TAB_2', null)");
-        stats.assertTableStats("TEST_TAB_2",1);
+        stats.assertNoStatsTable("TEST_TAB_2");
         s.execute("drop table TEST_TAB_2");
         s.execute("drop table TEST_TAB_1");
         stats.release();

Modified: db/derby/code/branches/10.8.3.1_testcompat/java/testing/org/apache/derbyTesting/functionTests/tests/store/AutomaticIndexStatisticsTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8.3.1_testcompat/java/testing/org/apache/derbyTesting/functionTests/tests/store/AutomaticIndexStatisticsTest.java?rev=1469436&r1=1469435&r2=1469436&view=diff
==============================================================================
--- db/derby/code/branches/10.8.3.1_testcompat/java/testing/org/apache/derbyTesting/functionTests/tests/store/AutomaticIndexStatisticsTest.java
(original)
+++ db/derby/code/branches/10.8.3.1_testcompat/java/testing/org/apache/derbyTesting/functionTests/tests/store/AutomaticIndexStatisticsTest.java
Thu Apr 18 16:59:17 2013
@@ -117,12 +117,11 @@ public class AutomaticIndexStatisticsTes
                 "select * from " + TAB + " where id = ?");
         ps.close();
 
-        // Get statistics
+        // Get statistics for the non-unique index.
         IdxStats[] myStats = new IndexStatsUtil(
                 ds.getConnection(), DEFAULT_TIMEOUT).getStatsTable(TAB, 1);
         assertEquals(1, myStats.length);
         assertTrue(myStats[0].rows == 300);
-        assertTrue(myStats[0].card == 300);
 
         // Shutdown database and try to delete it.
         JDBCDataSource.shutdownDatabase(ds);
@@ -222,7 +221,7 @@ public class AutomaticIndexStatisticsTes
         IndexStatsUtil myStats =
                 new IndexStatsUtil(ds.getConnection(), DEFAULT_TIMEOUT);
         myStats.assertNoStatsTable(TAB2);
-        ps = con.prepareStatement("select * from " + TAB2 + " where id = ?");
+        con.prepareStatement("select * from " + TAB2 + " where id = ?");
         myStats.assertTableStats(TAB2, 1);
         myStats.release();
 
@@ -267,7 +266,7 @@ public class AutomaticIndexStatisticsTes
         // Trigger stats update on secondary table, make sure the daemon can
         // still process work.
         myStats.assertNoStatsTable(TAB2);
-        ps = con.prepareStatement("select * from " + TAB2 + " where id = ?");
+        con.prepareStatement("select * from " + TAB2 + " where id = ?");
         myStats.assertTableStats(TAB2, 1);
         myStats.release();
     }
@@ -367,7 +366,7 @@ public class AutomaticIndexStatisticsTes
             }
         }
 
-        // Finally, create a unique index on the val column.
+        // Finally, create a non-unique index on the val column.
         stmt.executeUpdate("create index IDXVAL on " + TAB + "(val)");
         ResultSet rs = stmt.executeQuery(
                 "select val from " + TAB + " order by val");
@@ -424,11 +423,68 @@ public class AutomaticIndexStatisticsTes
 
         // Select from the view, using index.
         stats.assertNoStatsTable(table);
-        ps = prepareStatement("select * from " + view + " where vcol2 = 7");
+        prepareStatement("select * from " + view + " where vcol2 = 7");
         stats.assertNoStatsTable(table);
         // Trigger update of the base table.
-        ps = prepareStatement("select * from " + table + " where col2 = 7");
-        stats.assertTableStats(table, 2);
+        prepareStatement("select * from " + table + " where col2 = 7");
+        stats.assertTableStats(table, 1);
+    }
+
+    /**
+     * Verifies that queries on a table with single-column unique indexes only
+     * don't trigger a statistics update with the istat daemon.
+     */
+    public void testNoUpdateTriggeredBySingleColumnUniqueIndex()
+            throws SQLException {
+        // Create table.
+        String TAB = "STAT_SCUI";
+        dropTable(TAB);
+        Statement stmt = createStatement();
+        stmt.executeUpdate("create table " + TAB +
+                " (id int primary key, val int unique not null)");
+        stats.assertNoStatsTable(TAB);
+        PreparedStatement ps = prepareStatement(
+                "insert into " + TAB + " values (?,?)");
+        setAutoCommit(false);
+        for (int i=0; i < 2000; i++) {
+            ps.setInt(1, i);
+            ps.setInt(2, i);
+            ps.executeUpdate();
+        }
+        commit();
+        // The queries below would trigger a stats update in earlier releases.
+        PreparedStatement psSel1 = prepareStatement(
+                "select id from " + TAB + " where id = ?");
+        psSel1.setInt(1, 98);
+        JDBC.assertSingleValueResultSet(psSel1.executeQuery(), "98");
+        PreparedStatement psSel2 = prepareStatement(
+                "select val from " + TAB + " where val = ?");
+        psSel2.setInt(1, 1573);
+        JDBC.assertSingleValueResultSet(psSel2.executeQuery(), "1573");
+        Utilities.sleep(100); 
+        stats.assertNoStatsTable(TAB);
+
+        // Try again after inserting more data.
+        for (int i=2000; i < 4000; i++) {
+            ps.setInt(1, i);
+            ps.setInt(2, i);
+            ps.executeUpdate();
+        }
+        commit();
+        forceRowCountEstimateUpdate(TAB);
+        psSel1 = prepareStatement(
+                "select id from " + TAB + " where id = ?");
+        psSel1.setInt(1, 117);
+        JDBC.assertSingleValueResultSet(psSel1.executeQuery(), "117");
+        psSel2 = prepareStatement(
+                "select val from " + TAB + " where val = ?");
+        psSel2.setInt(1, 1);
+        JDBC.assertSingleValueResultSet(psSel2.executeQuery(), "1");
+        Utilities.sleep(100); 
+        stats.assertNoStatsTable(TAB);
+
+        // Cleanup
+        dropTable(TAB);
     }
 
     // Utility methods
@@ -545,8 +601,9 @@ public class AutomaticIndexStatisticsTes
     /**
      * Default method to create and populate a simple test table.
      * <p>
-     * The table consists of a single integer column, which is also the primary
-     * key of the table.
+     * The table consists of a two integer columns, where the first is the
+     * primary key of the table and the second is a value with a non-unique
+     * index on it.
      *
      * @param table target table
      * @param rows number of rows to insert
@@ -560,8 +617,9 @@ public class AutomaticIndexStatisticsTes
     /**
      * Default method to create and populate a simple test table.
      * <p>
-     * The table consists of a single integer column, which is also the primary
-     * key of the table.
+     * The table consists of a two integer columns, where the first is the
+     * primary key of the table and the second is a value with a non-unique
+     * index on it.
      *
      * @param con the connection to use (may be {@code null}, in which case
      *      the default connection will be used)
@@ -584,7 +642,10 @@ public class AutomaticIndexStatisticsTes
         // See if the table exists, and if so, drop it.
         dropIfExists(con, table);
         // Create table.
-        s.executeUpdate("create table " + table + "(id int primary key)");
+        s.executeUpdate(
+                "create table " + table + "(id int primary key, val int)");
+        s.executeUpdate("create index NON_UNIQUE_INDEX_" + table + " on " +
+                table + "(val)");
 
         myStats.assertNoStatsTable(table);
 
@@ -593,6 +654,7 @@ public class AutomaticIndexStatisticsTes
         println("created " + table + ", inserting " + rows + " rows");
         insertSimple(con, table, rows, 0);
         println("completed in " + (System.currentTimeMillis() - start) + " ms");
+        myStats.assertNoStatsTable(table);
     }
 
     /**
@@ -624,11 +686,12 @@ public class AutomaticIndexStatisticsTes
     private void insertSimple(Connection con, String table, int rows, int start)
             throws SQLException {
         PreparedStatement ps = con.prepareStatement(
-                                    "insert into " + table + " values ?");
+                                    "insert into " + table + " values (?,?)");
         boolean autoCommit = con.getAutoCommit();
         con.setAutoCommit(false);
         for (int i=start; i < start+rows; i++) {
             ps.setInt(1, i);
+            ps.setInt(2, i % 20);
             ps.addBatch();
             if (i % 5000 == 0) {
                 ps.executeBatch();

Modified: db/derby/code/branches/10.8.3.1_testcompat/java/testing/org/apache/derbyTesting/junit/IndexStatsUtil.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8.3.1_testcompat/java/testing/org/apache/derbyTesting/junit/IndexStatsUtil.java?rev=1469436&r1=1469435&r2=1469436&view=diff
==============================================================================
--- db/derby/code/branches/10.8.3.1_testcompat/java/testing/org/apache/derbyTesting/junit/IndexStatsUtil.java
(original)
+++ db/derby/code/branches/10.8.3.1_testcompat/java/testing/org/apache/derbyTesting/junit/IndexStatsUtil.java
Thu Apr 18 16:59:17 2013
@@ -167,7 +167,7 @@ public class IndexStatsUtil {
      * @param name the name of the table(s)/index(es) associated with the stats
      * @return A string representation of the statistics.
      */
-    private String buildStatString(IdxStats[] stats, String name) {
+    public static String buildStatString(IdxStats[] stats, String name) {
         StringBuffer sb = new StringBuffer(
                 "Index statistics for " + name + SEP);
         for (int i=0; i < stats.length; i++) {



Mime
View raw message