* Test for update statistics
*/
public void testUpdateAndDropStatistics() throws SQLException {
String tbl1 = "T1";
// Helper object to obtain information about index statistics.
IndexStatsUtil stats = new IndexStatsUtil(openDefaultConnection());
Statement s = createStatement();
//Calls to update and drop statistics below should fail because
// table APP.T1 does not exist
dropTable("T1");
assertStatementError("42Y55", s,
"CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','T1',null)");
assertStatementError("42Y55", s,
"CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','T1',null)");
s.executeUpdate("CREATE TABLE t1 (c11 int, c12 varchar(128))");
//following will pass now because we have created APP.T1
s.execute("CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','T1',null)");
s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','T1',null)");
//following should fail because index I1 does not exist on table APP.T1
assertStatementError("42X65", s,
"CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','T1','I1')");
assertStatementError("42X65", s,
"CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','T1','I1')");
s.executeUpdate("CREATE INDEX i1 on t1(c12)");
//following will pass now because we have created index I1 on APP.T1
s.execute("CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','T1','I1')");
s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','T1','I1')");
//The following set of subtest will ensure that when an index is
//created on a table when there is no data in the table, then Derby
//will not generate a row for it in sysstatistics table. If the index
//is created after the table has data on it, there will be a row for
//it in sysstatistics table. In order to generate statistics for the
//first index, users can run the stored procedure
//SYSCS_UPDATE_STATISTICS
//So far the table t1 is empty and we have already created index I1 on
//it. Since three was no data in the table when index I1 was created,
//there will be no row in sysstatistics table
stats.assertNoStatsTable(tbl1);
//Now insert some data into t1 and then create a new index on the
//table. This will cause sysstatistics table to have one row for this
//new index. Old index will still not have a row for it in
//sysstatistics table
s.executeUpdate("INSERT INTO T1 VALUES(1,'a'),(2,'b'),(3,'c'),(4,'d')");
s.executeUpdate("CREATE INDEX i2 ON t1(c11)");
stats.assertTableStats(tbl1, 1);
//Drop the statistics on index I2 and then add it back by calling
// update statistics
s.execute("CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','T1','I2')");
//Since we dropped the only statistics that existed for table T1, there
// will no stats found at this point
stats.assertNoStatsTable(tbl1);
s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','T1','I2')");
//The statistics for index I2 has been added back
stats.assertTableStats(tbl1, 1);
//Now update the statistics for the old index I1 using the new stored
//procedure. Doing this should add a row for it in sysstatistics table
s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','T1','I1')");
stats.assertTableStats(tbl1, 2);
//Drop the statistics on index I1 and then add it back by calling
// update statistics
s.execute("CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','T1','I1')");
stats.assertTableStats(tbl1, 1);
s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','T1','I1')");
stats.assertTableStats(tbl1, 2);
//Drop all the statistics on table T1 and then recreate all the
// statisitcs back again
s.execute("CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','T1',null)");
stats.assertNoStatsTable(tbl1);
s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','T1',null)");
stats.assertTableStats(tbl1, 2);
//Dropping the index should get rid of it's statistics
s.executeUpdate("DROP INDEX I1");
stats.assertTableStats(tbl1, 1);
//calls to system procedure for update and drop statistics are
// internally converted into ALTER TABLE ... sql but that generated
// sql format is not available to end user to issue directly. Write a
// test case for these internal sql syntaxes
assertStatementError("42X01", s,
"ALTER TABLE APP.T1 ALL UPDATE STATISTICS");
assertStatementError("42X01", s,
"ALTER TABLE APP.T1 UPDATE STATISTICS I1");
assertStatementError("42X01", s,
"ALTER TABLE APP.T1 ALL DROP STATISTICS");
assertStatementError("42X01", s,
"ALTER TABLE APP.T1 STATISTICS DROP I1");
//cleanup
s.executeUpdate("DROP TABLE t1");
//Try update and drop statistics on global temporary table
s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit delete rows not logged");
s.executeUpdate("insert into session.t1 values(11, 1)");
//following should fail because update/drop statistics can't be issued
// on global temporary tables
assertStatementError("42995", s,
"CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('SESSION','T1',null)");
assertStatementError("42995", s,
"CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('SESSION','T1',null)");
//Following test will show that updating the statistics will make a
//query pickup better index compare to prior to statistics availability.
//
//Check statistics update causes most efficient index usage
//Create a table with 2 non-unique indexes on 2 different columns.
//The indexes are created when the table is still empty and hence
//there are no statistics available for them in sys.sysstatistics.
//The table looks as follows
// create table t2(c21 int, c22 char(14), c23 char(200))
// create index t2i1 on t2(c21)
// create index t2i2 on t2(c22)
//Load the data into the table and running following query will
//pickup index t2i1 on column c21
// select * from t2 where c21=? and c22=?
//But once you make the statistics available for t2i2, the query
//will pickup index t2i2 on column c22 for the query above
//
//Start of test case for better index selection after statistics
//availability
s.executeUpdate("CREATE TABLE t2(c21 int, c22 char(14), c23 char(200))");
//No statistics will be created for the 2 indexes because the table is
//empty
s.executeUpdate("CREATE INDEX t2i1 ON t2(c21)");
s.executeUpdate("CREATE INDEX t2i2 ON t2(c22)");
stats.assertNoStatsTable("T2");
PreparedStatement ps = prepareStatement("INSERT INTO T2 VALUES(?,?,?)");
for (int i=0; i<1000; i++) {
ps.setInt(1, i%2);
ps.setString(2, "Tuple " +i);
ps.setString(3, "any value");
ps.addBatch();
}
ps.executeBatch();
s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
//Executing the query below and looking at it's plan will show that
//we picked index T2I1 rather than T2I2 because there are no
//statistics available for T2I2 to show that it is a better index
ps = prepareStatement("SELECT * FROM t2 WHERE c21=? AND c22=?");
ps.setInt(1, 0);
ps.setString(2, "Tuple 4");
JDBC.assertDrainResults(ps.executeQuery());
RuntimeStatisticsParser rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
assertTrue(rtsp.usedSpecificIndexForIndexScan("T2","T2I1"));
//Running the update statistics below will create statistics for T2I2
s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','T2','T2I2')");
stats.assertIndexStats("T2I2", 1);
//Rerunning the query "SELECT * FROM t2 WHERE c21=? AND c22=?" and
//looking at it's plan will show that this time it picked up more
//efficient index which is T2I2.
JDBC.assertDrainResults(ps.executeQuery());
rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
assertTrue(rtsp.usedSpecificIndexForIndexScan("T2","T2I2"));
//Drop statistics for T2I2 and we should see that we go back to using
// T2I1 rather than T2I2
s.execute("CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','T2','T2I2')");
stats.assertIndexStats("T2I2", 0);
//Rerunning the query "SELECT * FROM t2 WHERE c21=? AND c22=?" and
// looking at it's plan will show that this time it picked up T2I1
// rather than more efficient index T2I2 because no stats exists
// for T2I2
JDBC.assertDrainResults(ps.executeQuery());
rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
assertTrue(rtsp.usedSpecificIndexForIndexScan("T2","T2I1"));
//cleanup
s.executeUpdate("DROP TABLE t2");
//End of test case for better index selection after statistics
//availability
stats.release();
}