db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kmars...@apache.org
Subject svn commit: r760497 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/execute/ testing/org/apache/derbyTesting/functionTests/tests/lang/ testing/org/apache/derbyTesting/junit/
Date Tue, 31 Mar 2009 15:55:27 GMT
Author: kmarsden
Date: Tue Mar 31 15:55:26 2009
New Revision: 760497

URL: http://svn.apache.org/viewvc?rev=760497&view=rev
Log:
DERBY-4116 SYSCS_UTIL.SYSCS_UPDATE_STATISTICS should update the store estimated row count
for the table


Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SelectivityTest.java
  (with props)
Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java?rev=760497&r1=760496&r2=760497&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
Tue Mar 31 15:55:26 2009
@@ -645,7 +645,9 @@
 		UUID[] objectUUID;
 		GroupFetchScanController gsc;
 		DependencyManager dm = dd.getDependencyManager();
-
+		//initialize numRows to -1 so we can tell if we scanned an index.	
+		long numRows = -1;		
+		
 		td = dd.getTableDescriptor(tableId);
 		if (updateStatisticsAll)
 		{
@@ -702,7 +704,7 @@
 
 			int numCols = indexRow[indexNumber].nColumns() - 1;
 			long[] cardinality = new long[numCols];
-			long numRows = 0;
+			numRows = 0;
 			initializeRowBuffers(indexRow[indexNumber]);
 
 			/* Read uncommited, with record locking. Actually CS store may
@@ -744,6 +746,7 @@
 					rowBufferArray[GROUP_FETCH_SIZE - 1] = lastUniqueKey;
 					lastUniqueKey = tmp;
 				} // while
+				gsc.setEstimatedRowCount(numRows);
 			} // try
 			finally
 			{
@@ -754,7 +757,7 @@
 			if (numRows == 0)
 			{
 				/* if there is no data in the table: no need to write anything
-				 * to sys.systatstics.
+				 * to sys.sysstatstics
 				 */
 				break;			
 			}
@@ -779,6 +782,33 @@
 			} // for each leading column (c1) (c1,c2)....
 
 		} // for each index.
+
+		// DERBY-4116 if there were indexes we scanned, we now know the row count.
+		// Update statistics should update the store estimated row count for the table.
+		// If we didn't scan an index and don't know, numRows will still be -1 and
+		// we skip the estimatedRowCount update.
+		
+		if (numRows == -1)
+			return;
+		
+		ScanController heapSC = tc.openScan(td.getHeapConglomerateId(),
+				false,  // hold
+				0,      // openMode: for read
+				TransactionController.MODE_RECORD, // locking
+				TransactionController.ISOLATION_READ_UNCOMMITTED, //isolation level
+				null,   // scancolumnlist-- want everything.
+				null,   // startkeyvalue-- start from the beginning.
+				0,
+				null,   // qualifiers, none!
+				null,   // stopkeyvalue,
+				0);
+		
+		try {	
+			heapSC.setEstimatedRowCount(numRows);
+		} finally {			
+			heapSC.close();
+		}
+
 	}
 
 	private void initializeRowBuffers(ExecIndexRow ir)

Added: 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=760497&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SelectivityTest.java
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SelectivityTest.java
Tue Mar 31 15:55:26 2009
@@ -0,0 +1,130 @@
+/*
+ * Class org.apache.derbyTesting.functionTests.tests.lang.SelectivityTest
+ *
+ * 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.Connection;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+
+import junit.framework.Test;
+import junit.framework.TestSuite;
+
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
+import org.apache.derbyTesting.junit.JDBC;
+
+public class SelectivityTest extends BaseJDBCTestCase {
+
+    public SelectivityTest(String name) {
+        super(name);
+    }
+    
+    public void testSingleColumnSelectivity() throws SQLException {
+        // choose whatever plan you want but the row estimate should be.
+        //(n * n) * 0.5
+        Connection conn = getConnection();
+        Statement s = createStatement();      
+        s.executeUpdate("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
+        s.executeQuery("select template.id from --DERBY-PROPERTIES joinOrder=fixed\n" 
+                + "test, template where test.two = template.two").close();         
+        checkEstimatedRowCount(conn,8020012.5);
+        
+
+            
+    }
+    
+    public static Test suite() {
+        return new CleanDatabaseTestSetup(new TestSuite(SelectivityTest.class,
+                "SelectivityTest")) {
+            protected void decorateSQL(Statement s) throws SQLException {
+                s.executeUpdate("create table two (x int)");
+                s.executeUpdate("insert into two values (1),(2)");
+                s.executeUpdate("create table ten (x int)");
+                s
+                        .executeUpdate("insert into ten values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)");
+                s.executeUpdate("create table twenty (x int)");
+                s
+                        .executeUpdate("insert into twenty values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20)");
+                s
+                        .executeUpdate("create table hundred (x int generated always as identity,
dc int)");
+                s
+                        .executeUpdate("insert into hundred (dc) select t1.x from ten t1,
ten t2");
+                s
+                        .executeUpdate("create table template (id int not null generated
always as identity, two int, twenty int, hundred int)");
+                // 4000 rows
+                s
+                        .executeUpdate("insert into template (two, twenty, hundred) select
two.x, twenty.x, hundred.x from two, twenty, hundred");
+                s.executeUpdate("create index template_two on template(two)");
+                s
+                        .executeUpdate("create index template_twenty on template(twenty)");
+                // 20 distinct values
+                s
+                        .executeUpdate("create index template_22 on template(twenty,two)");
+                s
+                        .executeUpdate("create unique index template_id on template(id)");
+                s
+                        .executeUpdate("create index template_102 on template(hundred,two)");
+                s
+                        .executeUpdate("create table test (id int, two int, twenty int, hundred
int)");
+                s.executeUpdate("create index test_id on test(id)");
+                s.executeUpdate("insert into test select * from template");
+
+                s
+                        .executeUpdate("create view showstats as "
+                                + "select cast (conglomeratename as varchar(20)) indexname,
"
+                                + "cast (statistics as varchar(40)) stats, "
+                                + "creationtimestamp createtime, "
+                                + "colcount ncols "
+                                + "from sys.sysstatistics, sys.sysconglomerates "
+                                + "where conglomerateid = referenceid");
+                ResultSet statsrs = s
+                        .executeQuery("select indexname, stats, ncols from showstats order
by indexname, stats, createtime, ncols");
+                JDBC.assertFullResultSet(statsrs, new String[][] {
+                        {"TEMPLATE_102","numunique= 100 numrows= 4000","1"},
+                        {"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
+                        .executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEMPLATE',null)");
+                s
+                        .executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEST',null)");
+                statsrs = s
+                        .executeQuery("select  indexname, stats, ncols from showstats order
by indexname, stats, createtime, ncols");
+                JDBC.assertFullResultSet(statsrs, new String[][] {
+                        {"TEMPLATE_102","numunique= 100 numrows= 4000","1"},
+                        {"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"}}                
                                              
+                );
+                
+            }
+        };
+    }
+}

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SelectivityTest.java
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java?rev=760497&r1=760496&r2=760497&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
Tue Mar 31 15:55:26 2009
@@ -137,6 +137,7 @@
         suite.addTest(AnsiSignaturesTest.suite());
         suite.addTest(PredicatePushdownTest.suite());
         suite.addTest(UngroupedAggregatesNegativeTest.suite());
+        suite.addTest(SelectivityTest.suite());
         // Add the XML tests, which exist as a separate suite
         // so that users can "run all XML tests" easily.
         suite.addTest(XMLSuite.suite());

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java?rev=760497&r1=760496&r2=760497&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java Tue
Mar 31 15:55:26 2009
@@ -38,6 +38,8 @@
 import junit.framework.AssertionFailedError;
 
 import org.apache.derby.iapi.services.info.JVMInfo;
+import org.apache.derby.iapi.sql.execute.RunTimeStatistics;
+import org.apache.derby.impl.jdbc.EmbedConnection;
 import org.apache.derby.tools.ij;
 
 
@@ -1298,6 +1300,25 @@
   
 
     /**
+     * Return estimated row count for runtime statistics.  
+     * Requires caller first turned on RuntimeStatistics, executed a query and closed the
ResultSet.
+     * 
+     * For client calls we just return as we can't find out this information.
+     * @param conn
+     * @param expectedCount
+     * @throws SQLException
+     */
+    public static void checkEstimatedRowCount(Connection conn, double expectedCount) throws
SQLException {
+	if (! (conn instanceof EmbedConnection))
+	    return;
+	
+	EmbedConnection econn = (EmbedConnection) conn;
+	RunTimeStatistics rts = econn.getLanguageConnection().getRunTimeStatisticsObject();
+	assertNotNull(" RuntimeStatistics is null. Did you call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)?",rts);
+	assertEquals((long) expectedCount, (long) rts.getEstimatedRowCount());
+	}
+
+    /**
      * Check consistency of all tables
      * 
      * @param conn



Mime
View raw message