You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by km...@apache.org on 2013/04/18 18:59:17 UTC
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/
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++) {