| /* |
| * Class org.apache.derbyTesting.functionTests.tests.lang.UpdateStatisticsTest |
| * |
| * Licensed to the Apache Software Foundation (ASF) under one |
| * or more contributor license agreements. See the NOTICE file |
| * distributed with this work for additional information |
| * regarding copyright ownership. The ASF licenses this file |
| * to you under the Apache License, Version 2.0 (the |
| * "License"); you may not use this file except in compliance |
| * with the License. You may obtain a copy of the License at |
| * |
| * http://www.apache.org/licenses/LICENSE-2.0 |
| * |
| * Unless required by applicable law or agreed to in writing, |
| * software distributed under the License is distributed on an |
| * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| * KIND, either express or implied. See the License for the |
| * specific language governing permissions and limitations |
| * under the License. |
| */ |
| |
| package org.apache.derbyTesting.functionTests.tests.lang; |
| |
| import java.sql.CallableStatement; |
| import java.sql.Connection; |
| import java.sql.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| import java.sql.Types; |
| import junit.framework.Test; |
| import org.apache.derbyTesting.junit.BaseJDBCTestCase; |
| import org.apache.derbyTesting.junit.DatabasePropertyTestSetup; |
| import org.apache.derbyTesting.junit.IndexStatsUtil; |
| import org.apache.derbyTesting.junit.JDBC; |
| import org.apache.derbyTesting.junit.TestConfiguration; |
| import org.apache.derbyTesting.junit.RuntimeStatisticsParser; |
| import org.apache.derbyTesting.junit.SQLUtilities; |
| |
| /** |
| * Tests for updating the statistics of one index or all the indexes on a |
| * table DERBY-269, DERBY-3788. |
| * Tests for dropping the statistics of one index or all the indexes on a |
| * table DERBY-4115. |
| */ |
| public class UpdateStatisticsTest extends BaseJDBCTestCase { |
| |
| public UpdateStatisticsTest(String name) { |
| super(name); |
| } |
| |
| public static Test suite() { |
| // Disable automatic index statistics generation. The generation will be |
| // triggered when preparing a statement and this will interfere |
| // with some of the asserts in testUpdateStatistics. |
| // With automatic generation enabled, testUpdateStatistics may |
| // fail intermittently due to timing, mostly when run |
| // with the client driver. |
| Test test = TestConfiguration.defaultSuite(UpdateStatisticsTest.class); |
| Test statsDisabled = DatabasePropertyTestSetup.singleProperty |
| ( test, "derby.storage.indexStats.auto", "false", true ); |
| return statsDisabled; |
| } |
| |
| /** |
| * Test that parser can work with column and index named STATISTICS and |
| * does not get confused with non-reserved keyword STATISTICS used by |
| * UPDATE and DROP STATISTICS syntax generated internally for |
| * SYSCS_DROP_STATISTICS and SYSCS_UPDATE_STATISTICS |
| */ |
| public void testIndexAndColumnNamedStatistics() throws SQLException { |
| String tbl = "T1"; |
| // Helper object to obtain information about index statistics. |
| IndexStatsUtil stats = new IndexStatsUtil(openDefaultConnection()); |
| Statement s = createStatement(); |
| |
| // Get the initial count of statistics in the database. |
| int initialStatsCount = stats.getStats().length; |
| |
| //Notice the name of one of the columns is STATISTICS |
| s.executeUpdate("CREATE TABLE t1 (c11 int, statistics int not null)"); |
| //Notice that the name of the index is STATISTICS which is same as |
| // one of the column names |
| s.executeUpdate("CREATE INDEX statistIcs ON t1(c11)"); |
| s.executeUpdate("INSERT INTO t1 VALUES(1,1)"); |
| stats.assertNoStatsTable(tbl); |
| //Drop the column named STATISTICS and make sure parser doesn't |
| // throw an error |
| s.executeUpdate("ALTER TABLE t1 DROP statistics"); |
| //Should still be able to call update/drop statistics on index |
| // STATISTICS |
| s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','T1','STATISTICS')"); |
| stats.assertTableStats(tbl, 1); |
| s.executeUpdate("CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','T1','STATISTICS')"); |
| stats.assertNoStatsTable(tbl); |
| //Add the column named STATISTICS back |
| s.executeUpdate("ALTER TABLE t1 ADD COLUMN statistics int"); |
| stats.assertNoStatsTable(tbl); |
| //Update or drop statistics for index named STATISTICS. Note that there |
| // is also a column named STATISTICS in the table |
| s.executeUpdate("CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','T1','STATISTICS')"); |
| stats.assertNoStatsTable(tbl); |
| s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','T1','STATISTICS')"); |
| stats.assertTableStats(tbl, 1); |
| s.executeUpdate("DROP TABLE t1"); |
| |
| // Check that we haven't created some other statistics as a side-effect. |
| assertEquals(initialStatsCount, stats.getStats().length); |
| } |
| |
| //DERBY-5750(Sending an empty string as table name to compress table |
| // procedure or empty string as index name to update statistics procedure |
| // makes the parser throw an exception.) |
| // |
| //No table name will result in exception since Derby doesn't know table |
| // whose statistics it needs to be update/drop |
| public void testStatisticsProcsWithEmptyParamsDerby5750() throws SQLException { |
| Statement s = createStatement(); |
| s.execute("create table DERBY5750_t1 (c11 int)"); |
| s.executeUpdate("CREATE INDEX DERBY5750_I1 ON DERBY5750_t1(c11)"); |
| //Following statements will give exceptions since there is no schema |
| // named empty string |
| assertStatementError( |
| "42Y07", s, |
| "call syscs_util.SYSCS_UPDATE_STATISTICS("+ |
| "'','DERBY5750_T1','DERBY5750_I1')"); |
| assertStatementError( |
| "42Y07", s, |
| "call syscs_util.SYSCS_DROP_STATISTICS("+ |
| "'','DERBY5750_T1','DERBY5750_I1')"); |
| assertStatementError( |
| "42Y07", s, |
| "call syscs_util.SYSCS_UPDATE_STATISTICS("+ |
| "'','','DERBY5750_I1')"); |
| assertStatementError( |
| "42Y07", s, |
| "call syscs_util.SYSCS_DROP_STATISTICS("+ |
| "'','','DERBY5750_I1')"); |
| |
| //null schema name will translate to current schema |
| s.execute("call syscs_util.SYSCS_UPDATE_STATISTICS("+ |
| "null,'DERBY5750_T1','DERBY5750_I1')"); |
| s.execute("call syscs_util.SYSCS_DROP_STATISTICS(" + |
| "null,'DERBY5750_T1','DERBY5750_I1')"); |
| |
| //Following statements will give exceptions since there is no table |
| // named empty string |
| assertStatementError( |
| "42X05", s, |
| "call syscs_util.SYSCS_UPDATE_STATISTICS("+ |
| "null,'','DERBY5750_I1')"); |
| assertStatementError( |
| "42X05", s, |
| "call syscs_util.SYSCS_DROP_STATISTICS("+ |
| "null,'','DERBY5750_I1')"); |
| assertStatementError( |
| "42X05", s, |
| "call syscs_util.SYSCS_UPDATE_STATISTICS("+ |
| "'APP','','DERBY5750_I1')"); |
| assertStatementError( |
| "42X05", s, |
| "call syscs_util.SYSCS_DROP_STATISTICS("+ |
| "'APP','','DERBY5750_I1')"); |
| |
| //Following statements will give exceptions since table name can't |
| // be null |
| assertStatementError( |
| "42X05", s, |
| "call syscs_util.SYSCS_UPDATE_STATISTICS("+ |
| "null,null,'DERBY5750_I1')"); |
| assertStatementError( |
| "42X05", s, |
| "call syscs_util.SYSCS_DROP_STATISTICS("+ |
| "null,null,'DERBY5750_I1')"); |
| assertStatementError( |
| "42X05", s, |
| "call syscs_util.SYSCS_UPDATE_STATISTICS("+ |
| "'APP',null,'DERBY5750_I1')"); |
| assertStatementError( |
| "42X05", s, |
| "call syscs_util.SYSCS_DROP_STATISTICS("+ |
| "'APP',null,'DERBY5750_I1')"); |
| |
| //Provide all the 3 params, schema, table and index name |
| s.execute("call syscs_util.SYSCS_UPDATE_STATISTICS("+ |
| "'APP','DERBY5750_T1','DERBY5750_I1')"); |
| s.execute("call syscs_util.SYSCS_DROP_STATISTICS("+ |
| "'APP','DERBY5750_T1','DERBY5750_I1')"); |
| |
| |
| //Following statements will give exceptions since there is no index |
| // named empty string |
| assertStatementError( |
| "42X65", s, |
| "call syscs_util.SYSCS_UPDATE_STATISTICS("+ |
| "'APP','DERBY5750_T1','')"); |
| assertStatementError( |
| "42X65", s, |
| "call syscs_util.SYSCS_DROP_STATISTICS("+ |
| "'APP','DERBY5750_T1','')"); |
| |
| s.execute("drop table DERBY5750_t1"); |
| } |
| |
| /** |
| * 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(); |
| } |
| |
| /** |
| * Test that SYSCS_UPDATE_STATISTICS doesn't obtain exclusive locks on |
| * the table or rows in the table (DERBY-4274). |
| */ |
| public void testNoExclusiveLockOnTable() throws SQLException { |
| Statement s = createStatement(); |
| s.execute("create table t (x char(1))"); |
| s.execute("create index ti on t(x)"); |
| s.execute("insert into t values 'a','b','c','d'"); |
| |
| setAutoCommit(false); |
| s.execute("lock table t in share mode"); |
| |
| Connection c2 = openDefaultConnection(); |
| Statement s2 = c2.createStatement(); |
| // This call used to time out because SYSCS_UPDATE_STATISTICS tried |
| // to lock T exclusively. |
| s2.execute("call syscs_util.syscs_update_statistics('APP', 'T', null)"); |
| s2.close(); |
| c2.close(); |
| |
| s.execute("drop table t"); |
| commit(); |
| } |
| |
| /** |
| * Regression test case for DERBY-5153. Compilation in parallel with |
| * update of statistics sometimes failed on debug builds. |
| */ |
| public void testParallelCompilationAndUpdate() throws Exception { |
| setAutoCommit(false); |
| |
| // Create and populate a test table with a multi-column index. |
| Statement s = createStatement(); |
| s.execute("create table derby5153(a int, b int, c int, d int)"); |
| s.execute("create index idx on derby5153(a,b,c,d)"); |
| |
| PreparedStatement ins = |
| prepareStatement("insert into derby5153 values (1,2,3,4)"); |
| for (int i = 0; i < 100; i++) { |
| ins.execute(); |
| } |
| |
| commit(); |
| |
| // Start a thread that repeatedly updates the statistics for IDX. |
| Connection updateConn = openDefaultConnection(); |
| IndexUpdateThread t = |
| new IndexUpdateThread(updateConn, "APP", "DERBY5153", "IDX"); |
| t.start(); |
| |
| try { |
| |
| // Compile/execute the query a number of times while the index |
| // statistics are being updated. This often failed with an assert |
| // failure in debug builds before DERBY-5153. |
| for (int i = 0; i < 100; i++) { |
| ResultSet rs = s.executeQuery( |
| "select * from derby5153 t1, derby5153 t2 " + |
| "where t1.a = t2.a"); |
| rs.close(); |
| } |
| |
| } finally { |
| |
| // Let the update thread know we're done. |
| t.done = true; |
| |
| } |
| |
| t.join(); |
| |
| // Check if the update thread failed, and report if it did. |
| if (t.exception != null) { |
| throw t.exception; |
| } |
| |
| updateConn.close(); |
| |
| // Clean-up |
| dropTable("derby5153"); |
| commit(); |
| } |
| |
| /** |
| * Fixed DERBY-5681(When a foreign key constraint on a table is dropped, |
| * the associated statistics row for the conglomerate is not removed.) |
| * @throws Exception |
| */ |
| public void testDERBY5681() throws Exception { |
| // Helper object to obtain information about index statistics. |
| IndexStatsUtil stats = new IndexStatsUtil(openDefaultConnection()); |
| Statement s = createStatement(); |
| |
| //Test - primary key constraint |
| s.executeUpdate("CREATE TABLE TEST_TAB_1 (c11 int not null,"+ |
| "c12 int not null, c13 int)"); |
| stats.assertNoStatsTable("TEST_TAB_1"); |
| //Insert data into table with no constraint and there will be no stat |
| // for that table at this point |
| 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. 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.assertNoStatsTable("TEST_TAB_1"); |
| s.executeUpdate("ALTER TABLE TEST_TAB_1 "+ |
| "DROP CONSTRAINT TEST_TAB_1_PK_1"); |
| stats.assertNoStatsTable("TEST_TAB_1"); |
| s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEST_TAB_1', null)"); |
| stats.assertNoStatsTable("TEST_TAB_1"); |
| //Add the primary key constraint back since it will be used by the next |
| // test to create foreign key constraint |
| 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 |
| s.executeUpdate("ALTER TABLE TEST_TAB_1 "+ |
| "ADD CONSTRAINT TEST_TAB_1_UNQ_1 "+ |
| "UNIQUE (c12)"); |
| stats.assertNoStatsTable("TEST_TAB_1"); |
| s.executeUpdate("ALTER TABLE TEST_TAB_1 "+ |
| "DROP CONSTRAINT TEST_TAB_1_UNQ_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",2); |
| s.executeUpdate("ALTER TABLE TEST_TAB_1 "+ |
| "DROP CONSTRAINT TEST_TAB_1_UNQ_2"); |
| 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 - foreign key but no primary key constraint |
| s.executeUpdate("CREATE TABLE TEST_TAB_3 (c31 int not null)"); |
| s.executeUpdate("INSERT INTO TEST_TAB_3 VALUES(1),(2)"); |
| s.executeUpdate("ALTER TABLE TEST_TAB_3 "+ |
| "ADD CONSTRAINT TEST_TAB_3_FK_1 "+ |
| "FOREIGN KEY(c31) REFERENCES TEST_TAB_1(c11)"); |
| stats.assertTableStats("TEST_TAB_3",1); |
| s.executeUpdate("ALTER TABLE TEST_TAB_3 "+ |
| "DROP CONSTRAINT TEST_TAB_3_FK_1"); |
| stats.assertNoStatsTable("TEST_TAB_3"); |
| |
| //Test - foreign key and primary key constraint |
| s.executeUpdate("CREATE TABLE TEST_TAB_2 (c21 int not null)"); |
| s.executeUpdate("INSERT INTO TEST_TAB_2 VALUES(1),(2)"); |
| s.executeUpdate("ALTER TABLE TEST_TAB_2 "+ |
| "ADD CONSTRAINT TEST_TAB_2_PK_1 "+ |
| "PRIMARY KEY (c21)"); |
| stats.assertNoStatsTable("TEST_TAB_2"); |
| // DERBY-5702 Add a foreign key constraint and now we should find one |
| // row of statistics for TEST_TAB_2 (for the foreign key constraint). |
| s.executeUpdate("ALTER TABLE TEST_TAB_2 "+ |
| "ADD CONSTRAINT TEST_TAB_2_FK_1 "+ |
| "FOREIGN KEY(c21) REFERENCES TEST_TAB_1(c11)"); |
| //DERBY-5702 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.assertNoStatsTable("TEST_TAB_2"); |
| s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEST_TAB_2', null)"); |
| 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.assertNoStatsTable("TEST_TAB_1"); |
| s.executeUpdate("ALTER TABLE TEST_TAB_2 "+ |
| "DROP CONSTRAINT TEST_TAB_2_FK_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.assertNoStatsTable("TEST_TAB_2"); |
| s.execute("CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','TEST_TAB_2', null)"); |
| //After DERBY-4115 is implemented, we will see no statistics |
| // for TEST_TAB_2 after calling SYSCS_DROP_STATISTICS on it. |
| stats.assertNoStatsTable("TEST_TAB_2"); |
| s.execute("drop table TEST_TAB_2"); |
| s.execute("drop table TEST_TAB_1"); |
| stats.release(); |
| } |
| |
| /** |
| * Tests that the functionality that drops disposable statistics leaves |
| * useful statistics intact. |
| */ |
| public void testDisposableStatsEagerness() |
| throws SQLException { |
| setAutoCommit(false); |
| String tbl = "DISPOSABLE_STATS_EAGERNESS"; |
| String tbl_fk = tbl + "_FK"; |
| String nuIdx = "NU_" + tbl; |
| Statement stmt = createStatement(); |
| |
| // Create and populate the foreign key table. |
| stmt.executeUpdate("create table " + tbl_fk + "(" + |
| "pk1 int generated always as identity)"); |
| PreparedStatement ps = prepareStatement( |
| "insert into " + tbl_fk + " values (DEFAULT)"); |
| for (int i=1; i <= 1000; i++) { |
| ps.executeUpdate(); |
| } |
| |
| // Create and populate the main table. |
| stmt.executeUpdate("create table " + tbl + "(" + |
| "pk1 int generated always as identity," + |
| "pk2 int not null," + |
| "mynonunique int, " + |
| "fk int not null)"); |
| ps = prepareStatement("insert into " + tbl + |
| " values (DEFAULT, ?, ?, ?)"); |
| for (int i=1; i <= 1000; i++) { |
| ps.setInt(1, i); |
| ps.setInt(2, i % 35); |
| ps.setInt(3, i); |
| ps.executeUpdate(); |
| } |
| |
| // Create the various indexes. |
| stmt.executeUpdate("alter table " + tbl_fk + " add constraint PK_" + |
| tbl_fk + " primary key (pk1)"); |
| |
| stmt.executeUpdate("alter table " + tbl + " add constraint PK_" + tbl + |
| " primary key (pk1, pk2)"); |
| stmt.executeUpdate("alter table " + tbl + " add constraint FK_" + tbl + |
| " foreign key (fk) references " + tbl_fk + "(pk1)"); |
| stmt.executeUpdate("create index " + nuIdx + " on " + tbl + |
| "(mynonunique)"); |
| commit(); |
| setAutoCommit(true); |
| IndexStatsUtil stats = new IndexStatsUtil(getConnection()); |
| // Expected FK table: 0 |
| // Expected main table: 2xPK, 1 non-unique, 1 FK = 4 |
| stats.assertNoStatsTable(tbl_fk); |
| stats.assertTableStats(tbl, 4); |
| IndexStatsUtil.IdxStats[] tbl_stats_0 = stats.getStatsTable(tbl); |
| |
| // Run the update statistics procedure. |
| // Sleep at least one tick to ensure the timestamps differ. |
| sleepAtLeastOneTick(); |
| ps = prepareStatement( |
| "call syscs_util.syscs_update_statistics('APP', ?, ?)"); |
| ps.setNull(2, Types.VARCHAR); |
| ps.setString(1, tbl); |
| ps.execute(); |
| ps.setString(1, tbl_fk); |
| ps.execute(); |
| |
| // Check the counts. |
| stats.assertNoStatsTable(tbl_fk); |
| stats.assertTableStats(tbl, 4); |
| // Check the timestamps (i.e. were they actually updated?). |
| IndexStatsUtil.IdxStats[] tbl_stats_1 = stats.getStatsTable(tbl); |
| assertEquals(tbl_stats_0.length, tbl_stats_1.length); |
| for (int i=0; i < tbl_stats_1.length; i++) { |
| assertTrue(tbl_stats_1[i].after(tbl_stats_0[i])); |
| } |
| |
| // Now make sure updating one index doesn't modify the others' stats. |
| sleepAtLeastOneTick(); |
| ps.setString(1, tbl); |
| ps.setString(2, nuIdx); |
| ps.execute(); |
| // Just use any of the previous stats as a reference point. |
| IndexStatsUtil.IdxStats nonUniqueIdx = stats.getStatsIndex(nuIdx)[0]; |
| assertTrue(nonUniqueIdx.after(tbl_stats_1[0])); |
| // Check the counts again. |
| stats.assertNoStatsTable(tbl_fk); |
| stats.assertTableStats(tbl, 4); |
| |
| // Cleanup |
| dropTable(tbl); |
| dropTable(tbl_fk); |
| } |
| |
| /** |
| * A thread class that repeatedly calls SYSCS_UTIL.SYSCS_UPDATE_STATISTICS |
| * until the flag {@code done} is set to true. Any exception thrown during |
| * the lifetime of the thread can be found in the field {@code exception}. |
| */ |
| private static class IndexUpdateThread extends Thread { |
| private final CallableStatement updateStats; |
| private volatile boolean done; |
| private Exception exception; |
| |
| private IndexUpdateThread( |
| Connection c, String schema, String table, String index) |
| throws SQLException { |
| updateStats = c.prepareCall( |
| "call syscs_util.syscs_update_statistics(?,?,?)"); |
| updateStats.setString(1, schema); |
| updateStats.setString(2, table); |
| updateStats.setString(3, index); |
| } |
| |
| public void run() { |
| try { |
| while (!done) { |
| updateStats.execute(); |
| } |
| updateStats.close(); |
| } catch (Exception e) { |
| this.exception = e; |
| } |
| } |
| } |
| } |
