Return-Path: X-Original-To: apmail-db-derby-commits-archive@www.apache.org Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id EB0709ED0 for ; Tue, 22 May 2012 14:02:06 +0000 (UTC) Received: (qmail 911 invoked by uid 500); 22 May 2012 14:02:06 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 886 invoked by uid 500); 22 May 2012 14:02:06 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 878 invoked by uid 99); 22 May 2012 14:02:06 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 22 May 2012 14:02:06 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=5.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 22 May 2012 14:01:57 +0000 Received: from eris.apache.org (localhost [127.0.0.1]) by eris.apache.org (Postfix) with ESMTP id 545692388860; Tue, 22 May 2012 14:01:35 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r1341481 - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/sql/dictionary/ engine/org/apache/derby/impl/services/daemon/ engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/impl/sql/execute/ testing/org/apache/derby... Date: Tue, 22 May 2012 14:01:34 -0000 To: derby-commits@db.apache.org From: kristwaa@apache.org X-Mailer: svnmailer-1.0.8-patched Message-Id: <20120522140135.545692388860@eris.apache.org> Author: kristwaa Date: Tue May 22 14:01:33 2012 New Revision: 1341481 URL: http://svn.apache.org/viewvc?rev=1341481&view=rev Log: DERBY-3790: Investigate if request for update statistics can be skipped for certain kind of indexes, one instance may be unique indexes based on one column. Make update statistics code skip generation of statistics for single-column unique indexes. This affects the istat daemon, the create index code, and the SYSCS_UTIL.SYSCS_UPDATE_STATISTICS system procedure. As a safe-guard a temporary debug property has been added to allow users to revert to the old behavior in case the changes mess up their query plans. Adjusted tests for the reduced number of statistics entries. Added new tests for the debug property, for upgrade, and for behavior verification. Patch file: derby-3790-1c-skip_stats_scui.diff Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/KeepDisposableStatsPropertyTest.java (with props) db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/helpers/ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/helpers/DisposableIndexStatistics.java (with props) Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TableDescriptor.java db/derby/code/trunk/java/engine/org/apache/derby/impl/services/daemon/IndexStatisticsDaemonImpl.java db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateIndexConstantAction.java db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SelectivityTest.java db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UpdateStatisticsTest.java db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/AutomaticIndexStatisticsTest.java db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/_Suite.java db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_9.java db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/IndexStatsUtil.java Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TableDescriptor.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TableDescriptor.java?rev=1341481&r1=1341480&r2=1341481&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TableDescriptor.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TableDescriptor.java Tue May 22 14:01:33 2012 @@ -642,22 +642,41 @@ public class TableDescriptor extends Tup * Gets the number of indexes on the table, including the backing indexes. * * @return the number of columns in the table. - * + * @see #getQualifiedNumberOfIndexes */ public int getTotalNumberOfIndexes() throws StandardException { - int totalNumberOfIndexes = 0; - ConglomerateDescriptor[] cds = getConglomerateDescriptors(); - - for (int index = 0; index < cds.length; index++) - { - if (cds[index].isIndex()) { totalNumberOfIndexes++; } - } - - return totalNumberOfIndexes; + return getQualifiedNumberOfIndexes(0, false); } + /** + * Returns the number of indexes matching the criteria. + * + * @param minColCount the minimum number of ordered columns in the indexes + * we want to count + * @param nonUniqeTrumpsColCount if {@code true} a non-unique index will be + * included in the count even if it has less than {@code minColCount} + * ordered columns + * @return Number of matching indexes. + * @see #getTotalNumberOfIndexes() + */ + public int getQualifiedNumberOfIndexes(int minColCount, + boolean nonUniqeTrumpsColCount) { + int matches = 0; + for (Iterator congIter = conglomerateDescriptorList.iterator(); + congIter.hasNext(); ) { + ConglomerateDescriptor cd = (ConglomerateDescriptor)congIter.next(); + if (cd.isIndex()) { + IndexRowGenerator irg = cd.getIndexDescriptor(); + if (irg.numberOfOrderedColumns() >= minColCount || + (nonUniqeTrumpsColCount && !irg.isUnique())) { + matches++; + } + } + } + return matches; + } /** * Builds a list of all triggers which are relevant to a Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/services/daemon/IndexStatisticsDaemonImpl.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/services/daemon/IndexStatisticsDaemonImpl.java?rev=1341481&r1=1341480&r2=1341481&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/impl/services/daemon/IndexStatisticsDaemonImpl.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/services/daemon/IndexStatisticsDaemonImpl.java Tue May 22 14:01:33 2012 @@ -45,6 +45,7 @@ import org.apache.derby.iapi.sql.conn.La import org.apache.derby.iapi.sql.depend.DependencyManager; import org.apache.derby.iapi.sql.dictionary.ConglomerateDescriptor; import org.apache.derby.iapi.sql.dictionary.DataDictionary; +import org.apache.derby.iapi.sql.dictionary.IndexRowGenerator; import org.apache.derby.iapi.sql.dictionary.StatisticsDescriptor; import org.apache.derby.iapi.sql.dictionary.TableDescriptor; import org.apache.derby.iapi.sql.execute.ExecIndexRow; @@ -119,16 +120,6 @@ public class IndexStatisticsDaemonImpl Property.STORAGE_AUTO_INDEX_STATS_DEBUG_QUEUE_SIZE_DEFAULT); } - /** - * Tells if the user want us to fall back to pre 10.9 behavior. - *

- * This means do not drop any disposable statistics, and do not skip - * statistics for single-column primary key indexes. - */ - private static final boolean FORCE_OLD_BEHAVIOR = - PropertyUtil.getSystemBoolean( - Property.STORAGE_AUTO_INDEX_STATS_DEBUG_FORCE_OLD_BEHAVIOR); - private final HeaderPrintWriter logStream; /** Tells if logging is enabled. */ private final boolean doLog; @@ -144,8 +135,15 @@ public class IndexStatisticsDaemonImpl private boolean daemonDisabled; /** The context manager for the worker thread. */ private final ContextManager ctxMgr; - /** Tells if the database is older than 10.9 (for soft upgrade). */ - private final boolean dbIsPre10_9; + /** + * Tells if disposable stats should be generated, which will happen in + * soft-upgrade mode or when the user asks us to revert to the old behavior. + *

+ * Made public to allow access for CreateIndexConstantAction and + * FromBaseTable, but this is no longer necessary when the debug property + * to keep disposable statistics is removed. + */ + public final boolean skipDisposableStats; /** The language connection context for the worker thread. */ private LanguageConnectionContext daemonLCC; /** @@ -219,7 +217,12 @@ public class IndexStatisticsDaemonImpl this.traceToStdOut = (traceLevel.equalsIgnoreCase("both") || traceLevel.equalsIgnoreCase("stdout")); this.doTrace = traceToDerbyLog || traceToStdOut; - this.dbIsPre10_9 = checkIfDbIsPre10_9(db); + + // For now allow users to override the new behavior through a debug + // property. Will be removed or renamed in a future release. + boolean keepDisposableStats = PropertyUtil.getSystemBoolean( + Property.STORAGE_AUTO_INDEX_STATS_DEBUG_FORCE_OLD_BEHAVIOR); + this.skipDisposableStats = dbAtLeast10_9(db) && !keepDisposableStats; this.db = db; this.dbOwner = userName; @@ -238,17 +241,17 @@ public class IndexStatisticsDaemonImpl "}) -> " + databaseName); } - /** Tells if the database is older than 10.9. */ - private boolean checkIfDbIsPre10_9(Database db) { + /** Tells if the database is 10.9 or newer. */ + private boolean dbAtLeast10_9(Database db) { try { - // Note the negation. - return !db.getDataDictionary().checkVersion( + return db.getDataDictionary().checkVersion( DataDictionary.DD_VERSION_DERBY_10_9, null); } catch (StandardException se) { if (SanityManager.DEBUG) { SanityManager.THROWASSERT("dd version check failed", se); } - return true; + // Not expected to happen, but if it does err on the safe-side. + return false; } } @@ -412,7 +415,7 @@ public class IndexStatisticsDaemonImpl boolean asBackgroundTask) throws StandardException { final boolean identifyDisposableStats = - (cds == null && !FORCE_OLD_BEHAVIOR && !dbIsPre10_9); + (cds == null && skipDisposableStats); // Fetch descriptors if we're updating statistics for all indexes. if (cds == null) { cds = td.getConglomerateDescriptors(); @@ -435,18 +438,27 @@ public class IndexStatisticsDaemonImpl { for (int i = 0; i < cds.length; i++) { + // Skip non-index conglomerates if (!cds[i].isIndex()) { conglomerateNumber[i] = -1; continue; } - + IndexRowGenerator irg = cds[i].getIndexDescriptor(); + // Skip single-column unique indexes unless we're told not to, + // or we are running in soft-upgrade-mode on a pre 10.9 db. + if (skipDisposableStats) { + if (irg.isUnique() && irg.numberOfOrderedColumns() == 1) { + conglomerateNumber[i] = -1; + continue; + } + } + conglomerateNumber[i] = cds[i].getConglomerateNumber(); objectUUID[i] = cds[i].getUUID(); - indexRow[i] = - cds[i].getIndexDescriptor().getNullIndexRow( + indexRow[i] = irg.getNullIndexRow( td.getColumnDescriptorList(), heapCC.newRowLocationTemplate()); } Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java?rev=1341481&r1=1341480&r2=1341481&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java Tue May 22 14:01:33 2012 @@ -77,6 +77,8 @@ import org.apache.derby.iapi.store.acces import org.apache.derby.iapi.types.DataValueDescriptor; +// Temporary until user override for disposable stats has been removed. +import org.apache.derby.impl.services.daemon.IndexStatisticsDaemonImpl; import org.apache.derby.impl.sql.catalog.SYSUSERSRowFactory; /** @@ -928,9 +930,10 @@ public class FromBaseTable extends FromT hasCheckedIndexStats = true; // Only mark if a base table and there are indexes. Skip VTIs, // system tables, subqueries etc. - if (tableDescriptor.getTableType() == - TableDescriptor.BASE_TABLE_TYPE && - tableDescriptor.getTotalNumberOfIndexes() > 0) { + // The case where we have a table with a single-column unique + // index is pretty common, so avoid engaging the istat + // daemon if that's the only index on the table. + if (qualifiesForStatisticsUpdateCheck(tableDescriptor)) { tableDescriptor.markForIndexStatsUpdate(baseRowCount()); } } @@ -4752,4 +4755,32 @@ public class FromBaseTable extends FromT } } + /** + * Tells if the given table qualifies for a statistics update check in the + * current configuration. + * + * @param td the table to check + * @return {@code true} if qualified, {@code false} if not + */ + private boolean qualifiesForStatisticsUpdateCheck(TableDescriptor td) + throws StandardException { + int qualifiedIndexes = 0; + // Only base tables qualifies. + if (td.getTableType() == TableDescriptor.BASE_TABLE_TYPE) { + IndexStatisticsDaemonImpl istatDaemon = (IndexStatisticsDaemonImpl) + getDataDictionary().getIndexStatsRefresher(false); + // Usually only tables with at least one non-unique index or + // multi-column unique indexes qualify, but soft-upgrade mode is a + // special case (as is the temporary user override available). + // TODO: Rewrite if-logic when the temporary override is removed. + if (istatDaemon == null) { // Read-only database + qualifiedIndexes = 0; + } else if (istatDaemon.skipDisposableStats) { + qualifiedIndexes = td.getQualifiedNumberOfIndexes(2, true); + } else { + qualifiedIndexes = td.getTotalNumberOfIndexes(); + } + } + return (qualifiedIndexes > 0); + } } Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateIndexConstantAction.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateIndexConstantAction.java?rev=1341481&r1=1341480&r2=1341481&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateIndexConstantAction.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateIndexConstantAction.java Tue May 22 14:01:33 2012 @@ -61,6 +61,9 @@ import org.apache.derby.iapi.types.DataV import org.apache.derby.iapi.types.RowLocation; import org.apache.derby.iapi.types.TypeId; +// Used only to access a debug flag, will be removed or replaced. +import org.apache.derby.impl.services.daemon.IndexStatisticsDaemonImpl; + /** * ConstantAction to create an index either through * a CREATE INDEX statement or as a backing index to @@ -577,8 +580,7 @@ class CreateIndexConstantAction extends if (uniqueWithDuplicateNulls) { - if (lcc.getDataDictionary().checkVersion( - DataDictionary.DD_VERSION_DERBY_10_4, null)) + if (dd.checkVersion(DataDictionary.DD_VERSION_DERBY_10_4, null)) { indexProperties.put( "uniqueWithDuplicateNulls", Boolean.toString(true)); @@ -612,8 +614,7 @@ class CreateIndexConstantAction extends // For now, assume that all index columns are ordered columns if (! shareExisting) { - if (lcc.getDataDictionary().checkVersion( - DataDictionary.DD_VERSION_DERBY_10_4, null)) + if (dd.checkVersion(DataDictionary.DD_VERSION_DERBY_10_4, null)) { indexRowGenerator = new IndexRowGenerator( indexType, @@ -918,8 +919,9 @@ class CreateIndexConstantAction extends } CardinalityCounter cCount = (CardinalityCounter)rowSource; - long numRows; - if ((numRows = cCount.getRowCount()) > 0) + + long numRows = cCount.getRowCount(); + if (addStatistics(dd, indexRowGenerator, numRows)) { long[] c = cCount.getCardinality(); for (int i = 0; i < c.length; i++) @@ -937,6 +939,38 @@ class CreateIndexConstantAction extends } } + /** + * Determines if a statistics entry is to be added for the index. + *

+ * As an optimization, it may be better to not write a statistics entry to + * SYS.SYSSTATISTICS. If it isn't needed by Derby as part of query + * optimization there is no reason to spend resources keeping the + * statistics up to date. + * + * @param dd the data dictionary + * @param irg the index row generator + * @param numRows the number of rows in the index + * @return {@code true} if statistics should be written to + * SYS.SYSSTATISTICS, {@code false} otherwise. + * @throws StandardException if accessing the data dictionary fails + */ + private boolean addStatistics(DataDictionary dd, + IndexRowGenerator irg, + long numRows) + throws StandardException { + boolean add = (numRows > 0); + if (dd.checkVersion(DataDictionary.DD_VERSION_DERBY_10_9, null) && + // This horrible piece of code will hopefully go away soon! + ((IndexStatisticsDaemonImpl)dd.getIndexStatsRefresher(false)). + skipDisposableStats) { + if (add && irg.isUnique() && irg.numberOfOrderedColumns() == 1) { + // Do not add statistics for single-column unique indexes. + add = false; + } + } + return add; + } + // CLASS METHODS /////////////////////////////////////////////////////////////////////// @@ -995,28 +1029,6 @@ class CreateIndexConstantAction extends } /** - * Do necessary clean up (close down controllers, etc.) before throwing - * a statement exception. - * - * @param scan ScanController for the heap - * @param indexController ConglomerateController for the index - */ - private void statementExceptionCleanup( - ScanController scan, - ConglomerateController indexController) - throws StandardException - { - if (indexController != null) - { - indexController.close(); - } - if (scan != null) - { - scan.close(); - } - } - - /** * Scan the base conglomerate and insert the keys into a sorter, * returning a rowSource on the sorter. * Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SelectivityTest.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SelectivityTest.java?rev=1341481&r1=1341480&r2=1341481&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SelectivityTest.java (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SelectivityTest.java Tue May 22 14:01:33 2012 @@ -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/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UpdateStatisticsTest.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UpdateStatisticsTest.java?rev=1341481&r1=1341480&r2=1341481&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UpdateStatisticsTest.java (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UpdateStatisticsTest.java Tue May 22 14:01:33 2012 @@ -394,14 +394,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"); @@ -412,40 +410,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)"); @@ -464,29 +474,28 @@ public class UpdateStatisticsTest extend s.executeUpdate("ALTER TABLE TEST_TAB_2 "+ "ADD CONSTRAINT TEST_TAB_2_PK_1 "+ "PRIMARY KEY (c21)"); - stats.assertTableStats("TEST_TAB_2",1); - //DERBY-5702 Add a foreign key constraint and now we should find 2 rows - // of statistics for TEST_TAB_2 - 1 for primary key and other for - // foreign key constraint + 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.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("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. @@ -545,10 +554,9 @@ public class UpdateStatisticsTest extend commit(); setAutoCommit(true); IndexStatsUtil stats = new IndexStatsUtil(getConnection()); - // Expected FK table: 1 (PK only) + // Expected FK table: 0 // Expected main table: 2xPK, 1 non-unique, 1 FK = 4 - stats.assertTableStats(tbl_fk, 1); - IndexStatsUtil.IdxStats tbl_fk_pk_0 = stats.getStatsTable(tbl_fk)[0]; + stats.assertNoStatsTable(tbl_fk); stats.assertTableStats(tbl, 4); IndexStatsUtil.IdxStats[] tbl_stats_0 = stats.getStatsTable(tbl); // Avoid timestamp comparison problems on super-fast machines... @@ -568,11 +576,9 @@ public class UpdateStatisticsTest extend ps.execute(); // Check the counts. - stats.assertTableStats(tbl_fk, 1); + stats.assertNoStatsTable(tbl_fk); stats.assertTableStats(tbl, 4); // Check the timestamps (i.e. were they actually updated?). - IndexStatsUtil.IdxStats tbl_fk_pk_1 = stats.getStatsTable(tbl_fk)[0]; - assertTrue(tbl_fk_pk_1.after(tbl_fk_pk_0)); 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++) { @@ -587,7 +593,7 @@ public class UpdateStatisticsTest extend IndexStatsUtil.IdxStats nonUniqueIdx = stats.getStatsIndex(nuIdx)[0]; assertTrue(nonUniqueIdx.after(tbl_stats_1[0])); // Check the counts again. - stats.assertTableStats(tbl_fk, 1); + stats.assertNoStatsTable(tbl_fk); stats.assertTableStats(tbl, 4); // Cleanup Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/AutomaticIndexStatisticsTest.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/AutomaticIndexStatisticsTest.java?rev=1341481&r1=1341480&r2=1341481&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/AutomaticIndexStatisticsTest.java (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/AutomaticIndexStatisticsTest.java Tue May 22 14:01:33 2012 @@ -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. *

- * 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. *

- * 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(); Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/KeepDisposableStatsPropertyTest.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/KeepDisposableStatsPropertyTest.java?rev=1341481&view=auto ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/KeepDisposableStatsPropertyTest.java (added) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/KeepDisposableStatsPropertyTest.java Tue May 22 14:01:33 2012 @@ -0,0 +1,174 @@ +/* + + Derby - Class org.apache.derbyTesting.functionTests.tests.store.KeepDisposableStatsPropertyTest + + 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.store; + +import java.sql.PreparedStatement; +import java.sql.SQLException; +import java.sql.Statement; +import java.sql.Types; +import java.util.Properties; + +import junit.framework.Test; +import junit.framework.TestSuite; + +import org.apache.derbyTesting.junit.BaseJDBCTestCase; +import org.apache.derbyTesting.junit.IndexStatsUtil; +import org.apache.derbyTesting.junit.JDBC; +import org.apache.derbyTesting.junit.SystemPropertyTestSetup; +import org.apache.derbyTesting.junit.Utilities; + +/** + * Tests that the debug property used to revert to the previous behavior for + * dealing with disposable index cardinality statistics works. + */ +public class KeepDisposableStatsPropertyTest + extends BaseJDBCTestCase { + + public KeepDisposableStatsPropertyTest(String name) { + super(name); + } + + /** Verifies the behavior when the property is set to {@code true}. */ + public void testPropertyFalse() + throws SQLException { + assertOnSCUI(false); + } + + /** Verifies the behavior when the property is set to {@code true}. */ + public void testPropertyTrue() + throws SQLException { + assertOnSCUI(true); + } + + /** Verifies that the default for the property is {@code false}. */ + public void testPropertyDefault() + throws SQLException { + assertOnSCUI(false); + } + + /** Runs the real test case. */ + private void assertOnSCUI(boolean keepDisposable) + throws SQLException { + IndexStatsUtil stats = new IndexStatsUtil(openDefaultConnection()); + // Create table. + String TAB = "STAT_SCUI"; + dropTable(TAB); + Statement stmt = createStatement(); + stmt.executeUpdate("create table " + TAB + + " (id int not null, val int)"); + stats.assertNoStatsTable(TAB); + PreparedStatement psIns = prepareStatement( + "insert into " + TAB + " values (?,?)"); + setAutoCommit(false); + for (int i=0; i < 20; i++) { + psIns.setInt(1, i); + psIns.setInt(2, i); + psIns.executeUpdate(); + } + commit(); + setAutoCommit(true); + stmt.executeUpdate("alter table " + TAB + " add constraint PK_" + TAB + + " primary key(id)"); + stats.assertTableStats(TAB, keepDisposable ? 1 : 0); + stmt.executeUpdate( + "create unique index UNIQ_IDX_" + TAB + " ON " + TAB + "(val)"); + stats.assertTableStats(TAB, keepDisposable ? 2 : 0); + PreparedStatement ps = prepareStatement( + "call SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', ?, ?)"); + // Update stats for all indexes. + ps.setString(1, TAB); + ps.setNull(2, Types.VARCHAR); + ps.execute(); + stats.assertTableStats(TAB, keepDisposable ? 2 : 0); + + // Update stats for one specific index. + ps.setString(2, "UNIQ_IDX_" + TAB); + ps.execute(); + stats.assertTableStats(TAB, keepDisposable ? 2 : 0); + + // Drop statistics. + stmt.execute("call SYSCS_UTIL.SYSCS_DROP_STATISTICS(" + + "'APP', '" + TAB + "', null)"); + stats.assertNoStatsTable(TAB); + + // Update and assert again, this time in the reverse order. + ps.execute(); + stats.assertTableStats(TAB, keepDisposable ? 1 : 0); + ps.setNull(2, Types.VARCHAR); + ps.execute(); + stats.assertTableStats(TAB, keepDisposable ? 2 : 0); + IndexStatsUtil.IdxStats[] oldStats = stats.getStatsTable(TAB); + + // Insert more data to trigger an automatic update + setAutoCommit(false); + for (int i=20; i < 2000; i++) { + psIns.setInt(1, i); + psIns.setInt(2, i); + psIns.executeUpdate(); + } + commit(); + setAutoCommit(true); + JDBC.assertDrainResultsHasData( + stmt.executeQuery("select count(*) from " + TAB)); + prepareStatement("select * from " + TAB + " where id = ?"); + Utilities.sleep(200); + IndexStatsUtil.IdxStats[] newStats = stats.getStatsTable(TAB); + assertEquals(oldStats.length, newStats.length); + for (int i=0; i < oldStats.length; i++) { + assertEquals(keepDisposable, newStats[i].after(oldStats[i])); + } + + // Cleanup + dropTable(TAB); + stats.release(); + } + + /** + * Returns a suite where the test is run without specifying the property + * (use the default value), explicitly setting it to {@code true}, and + * explicitly setting it to {@code false}. + */ + public static Test suite() { + String property = "derby.storage.indexStats.debug.forceOldBehavior"; + TestSuite suite = new TestSuite("KeepDisposableStatsPropertyTestSuite"); + // Test the default (expected to be false). + suite.addTest( + new KeepDisposableStatsPropertyTest("testPropertyDefault")); + + // Test setting the property explicitly to true. + Properties propsOn = new Properties(); + propsOn.setProperty(property, "true"); + TestSuite suiteOn = new TestSuite("Do KeepDisposableStats"); + suiteOn.addTest( + new KeepDisposableStatsPropertyTest("testPropertyTrue")); + suite.addTest(new SystemPropertyTestSetup(suiteOn, propsOn, true)); + + // Test setting the property explicitly to false. + Properties propsOff = new Properties(); + propsOff.setProperty(property, "false"); + TestSuite suiteOff = new TestSuite("Don't KeepDisposableStats"); + suiteOff.addTest( + new KeepDisposableStatsPropertyTest("testPropertyFalse")); + suite.addTest(new SystemPropertyTestSetup(suiteOff, propsOff, true)); + + return suite; + } +} Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/KeepDisposableStatsPropertyTest.java ------------------------------------------------------------------------------ svn:eol-style = native Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/_Suite.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/_Suite.java?rev=1341481&r1=1341480&r2=1341481&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/_Suite.java (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/_Suite.java Tue May 22 14:01:33 2012 @@ -82,6 +82,7 @@ public class _Suite extends BaseTestCase suite.addTest(TableLockBasicTest.suite()); suite.addTest(ServicePropertiesFileTest.suite()); suite.addTest(Derby5234Test.suite()); + suite.addTest(KeepDisposableStatsPropertyTest.suite()); /* Tests that only run in sane builds */ if (SanityManager.DEBUG) { Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_9.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_9.java?rev=1341481&r1=1341480&r2=1341481&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_9.java (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_9.java Tue May 22 14:01:33 2012 @@ -37,6 +37,7 @@ import javax.sql.DataSource; import junit.framework.Test; import junit.framework.TestSuite; +import org.apache.derbyTesting.functionTests.tests.upgradeTests.helpers.DisposableIndexStatistics; import org.apache.derbyTesting.junit.IndexStatsUtil; import org.apache.derbyTesting.junit.JDBC; import org.apache.derbyTesting.junit.JDBCDataSource; @@ -941,7 +942,7 @@ public class Changes10_9 extends Upgrade s.execute("CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','TEST_TAB_2', null)"); stats.assertNoStatsTable("TEST_TAB_2"); s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEST_TAB_2', null)"); - stats.assertTableStats("TEST_TAB_2", 1); // TODO: Adjust for DERBY-3790 + stats.assertNoStatsTable("TEST_TAB_2"); break; case PH_POST_HARD_UPGRADE: @@ -952,4 +953,102 @@ public class Changes10_9 extends Upgrade break; } } + + /** + * Verifies the behavior of the update statistics code when faced with + * "disposable statistics entries". + *

+ * A disposable statistics entry is a row in SYS.SYSSTATISTICS that has + * been orphaned (see DERBY-5681) or it is on longer needed by the + * Derby optimizer (due to internal changes/improvements). + *

+ * This test expects different things based on the phase: + *

create
+ *
- run statements that will cause disposable statistics + * entries to be created
+ *
soft upgrade
+ *
- run the new update statistics code, expecting it to leave the + * disposable statistics intact
+ *
downgrade
+ *
- verify that the relevant statistics are present
+ *
hard upgrade
+ *
- run the new update statistics code, expecting it to get rid + * of the disposable statistics
+ *
+ */ + public void testDisposableStatisticsExplicit() + throws SQLException { + // Don't run this test with versions prior to 10.5, since the + // required SYSCS_UPDATE_STATISTICS don't exist in older versions. + if (!oldAtLeast(10, 5)) { + return; + } + + final String TBL = "ISTAT_DISPOSABLE_STATS"; + String updateStatsSQL = "call syscs_util.syscs_update_statistics(" + + "'APP', ?, null)"; + DisposableIndexStatistics dis = + new DisposableIndexStatistics(getConnection(), TBL); + + switch (getPhase()) { + // create with old version + case PH_CREATE: + { + dis.createAndPopulateTables(); + // We expect that the maximum number of statistics have been + // created here, since we're using an older version of Derby + // that contained a bug and lacked the latest optimizations. + dis.assertStatsCount( + DisposableIndexStatistics.getNumTotalPossibleStats()); + break; + } + // boot with new version and soft-upgrade + case PH_SOFT_UPGRADE: + { + PreparedStatement ps = prepareStatement(updateStatsSQL); + String[] tables = dis.getTableNames(); + // Update statistics on all relevant tables. + for (int i=0; i < tables.length; i++) { + ps.setString(1, tables[i]); + ps.executeUpdate(); + } + dis.assertStatsCount( + DisposableIndexStatistics.getNumTotalPossibleStats()); + break; + } + // soft-downgrade: boot with old version after soft-upgrade + case PH_POST_SOFT_UPGRADE: + { + dis.assertStatsCount( + DisposableIndexStatistics.getNumTotalPossibleStats()); + break; + } + // boot with new version and hard-upgrade + case PH_HARD_UPGRADE: + { + dis.assertStatsCount( + DisposableIndexStatistics.getNumTotalPossibleStats()); + PreparedStatement ps = prepareStatement(updateStatsSQL); + String[] tables = dis.getTableNames(); + for (int i=0; i < tables.length; i++) { + ps.setString(1, tables[i]); + ps.executeUpdate(); + } + // Confirm that we disposed of the statistics that were added + // due to a bug or simply not needed by Derby. + try { + dis.assertStatsCount( + DisposableIndexStatistics.getNumTotalPossibleStats() - + DisposableIndexStatistics.getNumDisposableStats()); + } finally { + for (int i=0; i < tables.length; i++) { + dropTable(tables[i]); + } + } + commit(); + break; + } + } + } + } Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/helpers/DisposableIndexStatistics.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/helpers/DisposableIndexStatistics.java?rev=1341481&view=auto ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/helpers/DisposableIndexStatistics.java (added) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/helpers/DisposableIndexStatistics.java Tue May 22 14:01:33 2012 @@ -0,0 +1,247 @@ +/* + + Derby - Class org.apache.derbyTesting.functionTests.tests.upgradeTests.helpers.DisposableIndexStatistics + + 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.upgradeTests.helpers; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.SQLException; +import java.sql.Statement; +import java.util.ArrayList; +import java.util.Arrays; +import java.util.List; + +import junit.framework.Assert; + +import org.apache.derbyTesting.junit.IndexStatsUtil; + +/** + * Helper class encapsulating logic used in the upgrade test for testing + * functionality dropping, and skipping generation of, disposable statistics + * entries. + */ +public class DisposableIndexStatistics { + + private static final int ROW_COUNT = 2000; + + private final Connection con; + private final String tbl; + private final String fktbl; + private final String pktbl; + + /** + * Creates a new helper instance using the given connection and table. + * + * @param con connection + * @param tableName base table name + */ + public DisposableIndexStatistics(Connection con, String tableName) { + this.con = con; + this.tbl = tableName; + this.fktbl = tableName + "_FK"; + this.pktbl = tableName + "_PK_2COL"; + } + + /** Creates and populates the test tables. */ + public void createAndPopulateTables() + throws SQLException { + con.setAutoCommit(true); + Statement stmt = con.createStatement(); + // Populate the tables first, then add the indexes/constraints. + // This ensure the statistics are actually created. + // Statistics: two entries due to two columns in the index. + stmt.executeUpdate("create table " + pktbl + "( " + + "id1 int generated always as identity, " + + "id2 int not null" + + ")"); + // Statistics: zero entries (single-column primary key), one without + // optimization logic. + stmt.executeUpdate("create table " + fktbl + "( " + + "id int not null generated always as identity" + + ")"); + // Statistics: three with DERBY-5681 fixed and optimization, + // otherwise five. + stmt.executeUpdate("create table " + tbl + "( " + + "id int not null generated always as identity, " + + "fk_dropped int not null, " + + "fk_self int, " + + "fk_self_notnull int not null, " + + "nonunique int" + + ")"); + + // Insert data + insertData(con); + + IndexStatsUtil stats = new IndexStatsUtil(con); + // Add constraints + stmt.executeUpdate("alter table " + pktbl + " add constraint " + + "PK_TWOCOL_PKTAB primary key (id1, id2)"); + stats.getStatsTable(pktbl, 2); + stmt.executeUpdate("alter table " + fktbl + " add constraint " + + "PK_FKTAB primary key (id)"); + stats.getStatsTable(fktbl, 1); + stmt.executeUpdate("alter table " + tbl + " add constraint " + + "PK_MAIN primary key (id)"); + stats.getStatsTable(tbl, 1); + stmt.executeUpdate("create index DUPS_MAIN on " + tbl + "(nonunique)"); + stats.getStatsTable(tbl, 2); + stmt.executeUpdate("alter table " + tbl + " add constraint " + + "FKS_MAIN foreign key (fk_self) references " + tbl + "(id)"); + stats.getStatsTable(tbl, 3); + stmt.executeUpdate("alter table " + tbl + " add constraint " + + "FKSNN_MAIN foreign key (fk_self_notnull) references " + + tbl + "(id)"); + stats.getStatsTable(tbl, 4); + + int preFkAddition = stats.getStatsTable(tbl).length; + // This doesn't trigger DERBY-5681. + stmt.executeUpdate("alter table " + tbl + " add constraint " + + "fk_to_be_dropped foreign key (fk_dropped) " + + "references " + fktbl + "(id)"); + Assert.assertTrue(stats.getStatsTable(tbl).length == preFkAddition +1); + stmt.executeUpdate("alter table " + tbl + " drop constraint " + + "fk_to_be_dropped"); + Assert.assertTrue(stats.getStatsTable(tbl).length == preFkAddition); + + // Trigger DERBY-5681. + stmt.executeUpdate("alter table " + tbl + " add constraint " + + "fk_on_pk foreign key (id) " + + "references " + fktbl + "(id)"); + stmt.executeUpdate("call syscs_util.syscs_update_statistics(" + + "'APP', '" + tbl + "', null)"); + Assert.assertTrue(stats.getStatsTable(tbl).length == preFkAddition +1); + stmt.executeUpdate("alter table " + tbl + " drop constraint " + + "fk_on_pk"); + // Derby failed to drop the statistics when the constraint got dropped. + Assert.assertTrue(stats.getStatsTable(tbl).length == preFkAddition +1); + + // Do an assert, but since we may be run with both old and new + // releases allow for two cases. + Assert.assertEquals( + getNumTotalPossibleStats(), getAllRelevantStats(null)); + } + + private void insertData(Connection con) + throws SQLException { + // Populate the foreign key table. + boolean oldAutoCommitValue = con.getAutoCommit(); + con.setAutoCommit(false); + PreparedStatement ps = con.prepareStatement( + "insert into " + fktbl + " values (DEFAULT)"); + for (int row = 0; row < ROW_COUNT; row++) { + ps.executeUpdate(); + } + ps.close(); + con.commit(); + + ps = con.prepareStatement( + "insert into " + pktbl + " values (DEFAULT, ?)"); + int reducedRowNumber = ROW_COUNT / 3; + for (int row = 0; row < reducedRowNumber; row++) { + ps.setInt(1, row); + ps.executeUpdate(); + } + ps.close(); + con.commit(); + + // Populate the main table. + ps = con.prepareStatement( + "insert into " + tbl + " values (DEFAULT,?,?,?,?)"); + for (int row = 0; row < ROW_COUNT; row++) { + ps.setInt(1, (row % reducedRowNumber) +1); + ps.setInt(2, (row % 2000) +1); + ps.setInt(3, (row % 19) +1); + ps.setInt(4, row % 10); + ps.executeUpdate(); + } + ps.close(); + con.commit(); + con.setAutoCommit(oldAutoCommitValue); + } + + /** Returns the names of the tables used by this test. */ + public String[] getTableNames() { + return new String[] {tbl, fktbl, pktbl}; + } + + /** Asserts the number of statistics entries for all relevant tables. */ + public void assertStatsCount(int expected) + throws SQLException { + ArrayList entries = new ArrayList(); + int found = getAllRelevantStats(entries); + if (found != expected) { + Assert.assertEquals( + IndexStatsUtil.buildStatString( + getStatArray(entries), + "DisposableIndexStatistics tables"), + expected, found); + } + } + + /** Converts the list of statistics to an array. */ + private IndexStatsUtil.IdxStats[] getStatArray(List list) { + int size = list.size(); + IndexStatsUtil.IdxStats[] ret = new IndexStatsUtil.IdxStats[size]; + list.toArray(ret); + return ret; + } + + /** + * Fetches all relevant statistics. + * + * @param list list to append statistics to (may be {@code null}) + * @return The number of relevant statistics entries found. + * @throws SQLException if something goes wrong + */ + private int getAllRelevantStats(List list) + throws SQLException { + boolean oldAutoCommitValue = con.getAutoCommit(); + con.setAutoCommit(true); + IndexStatsUtil stats = new IndexStatsUtil(con); + String[] tables = getTableNames(); + int count = 0; + for (int i=0; i < tables.length; i++) { + IndexStatsUtil.IdxStats[] entries = stats.getStatsTable(tables[i]); + if (list != null) { + list.addAll(Arrays.asList(entries)); + } + count += entries.length; + } + stats.release(false); + con.setAutoCommit(oldAutoCommitValue); + return count; + } + + /** + * Total number of possible statistics entries. + *

+ * This number includes orphaned and unnecessary statistics, and these + * entries are expected to be purged out when running with the current/ + * newest version of Derby. + */ + public static int getNumTotalPossibleStats() { + return 8; + } + + /** Number of disposable statistics entries. */ + public static int getNumDisposableStats() { + return 3; + } +} Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/helpers/DisposableIndexStatistics.java ------------------------------------------------------------------------------ svn:eol-style = native Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/IndexStatsUtil.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/IndexStatsUtil.java?rev=1341481&r1=1341480&r2=1341481&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/IndexStatsUtil.java (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/IndexStatsUtil.java Tue May 22 14:01:33 2012 @@ -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++) {