db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
Subject svn commit: r464683 - in /db/derby/code/branches/10.2/java: engine/org/apache/derby/iapi/db/ engine/org/apache/derby/impl/sql/execute/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Mon, 16 Oct 2006 21:43:50 GMT
Author: bpendleton
Date: Mon Oct 16 14:43:49 2006
New Revision: 464683

URL: http://svn.apache.org/viewvc?view=rev&rev=464683
Log:
DERBY-737: SYSCS_UTIL.SYSCS_COMPRESS_TABLE should create statistics

This patch was contributed by Mamta A. Satoor (msatoor@gmail.com)

This change merged from the trunk using the command:
 svn merge -r 464550:464551 ../trunk/

Modified:
    db/derby/code/branches/10.2/java/engine/org/apache/derby/iapi/db/OnlineCompress.java
    db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
    db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out
    db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/compressTable.sql

Modified: db/derby/code/branches/10.2/java/engine/org/apache/derby/iapi/db/OnlineCompress.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/engine/org/apache/derby/iapi/db/OnlineCompress.java?view=diff&rev=464683&r1=464682&r2=464683
==============================================================================
--- db/derby/code/branches/10.2/java/engine/org/apache/derby/iapi/db/OnlineCompress.java (original)
+++ db/derby/code/branches/10.2/java/engine/org/apache/derby/iapi/db/OnlineCompress.java Mon
Oct 16 14:43:49 2006
@@ -334,7 +334,7 @@
             // the compress:
             //     index_col_map - map location of index cols in the base row, 
             //                     ie. index_col_map[0] is column offset of 1st
-            //                     key collumn in base row.  All offsets are 0 
+            //                     key column in base row.  All offsets are 0 
             //                     based.
             //     index_scan - open ScanController used to delete old index row
             //     index_cc   - open ConglomerateController used to insert new 
@@ -591,7 +591,7 @@
         // the compress:
         //     index_col_map - map location of index cols in the base row, ie.
         //                     index_col_map[0] is column offset of 1st key
-        //                     collumn in base row.  All offsets are 0 based.
+        //                     column in base row.  All offsets are 0 based.
         //     index_scan - open ScanController used to delete old index row
         //     index_cc   - open ConglomerateController used to insert new row
 

Modified: db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java?view=diff&rev=464683&r1=464682&r2=464683
==============================================================================
--- db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
(original)
+++ db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
Mon Oct 16 14:43:49 2006
@@ -1498,7 +1498,7 @@
 				updateStatistics = true;
 			}
 			else
-				cCount = tc.openSortRowSource(sortIds[index]);
+				cCount = new CardinalityCounter(tc.openSortRowSource(sortIds[index]));
 
 			newIndexCongloms[index] = tc.createAndLoadConglomerate(
 								   "BTREE",
@@ -1509,6 +1509,32 @@
 								   cCount,
 								   (long[]) null);
 
+			//For an index, if the statistics already exist, then drop them.
+			//The statistics might not exist for an index if the index was
+			//created when the table was empty.
+			//At ALTER TABLE COMPRESS time, for both kinds of indexes 
+			//(ie one with preexisting statistics and with no statistics), 
+			//create statistics for them if the table is not empty. 
+			//DERBY-737 "SYSCS_UTIL.SYSCS_COMPRESS_TABLE should create
+			//statistics if they do not exist"
+			if (updateStatistics)
+				dd.dropStatisticsDescriptors(td.getUUID(), cd.getUUID(), tc);
+			
+			long numRows;
+			if ((numRows = ((CardinalityCounter)cCount).getRowCount()) > 0)
+			{
+				long[] c = ((CardinalityCounter)cCount).getCardinality();
+				for (int i = 0; i < c.length; i++)
+				{
+					StatisticsDescriptor statDesc =
+						new StatisticsDescriptor(dd, dd.getUUIDFactory().createUUID(),
+								cd.getUUID(), td.getUUID(), "I", new StatisticsImpl(numRows, c[i]),
+								i + 1);
+					dd.addDescriptor(statDesc, null, // no parent descriptor
+							DataDictionary.SYSSTATISTICS_CATALOG_NUM,
+							true, tc);	// no error on duplicate.
+				}
+			}
 		}else
 		{
 			newIndexCongloms[index] = tc.createConglomerate(
@@ -1525,26 +1551,6 @@
 				dd.dropStatisticsDescriptors(td.getUUID(), cd.getUUID(), tc);
 		}
 
-		if (updateStatistics)
-		{
-			dd.dropStatisticsDescriptors(td.getUUID(), cd.getUUID(), tc);
-			long numRows;
-			if ((numRows = ((CardinalityCounter)cCount).getRowCount()) > 0)
-			{
-				long[] c = ((CardinalityCounter)cCount).getCardinality();
-				for (int i = 0; i < c.length; i++)
-				{
-					StatisticsDescriptor statDesc = 
-						new StatisticsDescriptor(dd, dd.getUUIDFactory().createUUID(),
-													cd.getUUID(), td.getUUID(), "I", new StatisticsImpl(numRows, c[i]),
-													i + 1);
-					dd.addDescriptor(statDesc, null, // no parent descriptor
-									 DataDictionary.SYSSTATISTICS_CATALOG_NUM,
-									 true, tc);	// no error on duplicate.
-				}
-			}
-		}
-
 		/* Update the DataDictionary
 		 * RESOLVE - this will change in 1.4 because we will get
 		 * back the same conglomerate number
@@ -1563,7 +1569,7 @@
 
 
 	/**
-	 * Get info on the indexes on the table being compress. 
+	 * Get info on the indexes on the table being compressed. 
 	 *
 	 * @exception StandardException		Thrown on error
 	 */

Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out?view=diff&rev=464683&r1=464682&r2=464683
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out
(original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out
Mon Oct 16 14:43:49 2006
@@ -1188,5 +1188,97 @@
 ij> drop table users;
 0 rows inserted/updated/deleted
 ij> -- end derby-1854 test case. 
+-- test case for derby-737 
+-- perform compress on a table that has some indexes with no statistics
+create table derby737table1 (c1 int, c2 int);
+0 rows inserted/updated/deleted
+ij> select * from sys.sysstatistics;
+STATID                              |REFERENCEID                         |TABLEID       
                     |CREATIONTIMESTAMP         |&|VALID|COLCOUNT   |STATISTICS  
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ij> -- create index on the table when the table is empty. No statistics will be
+--  generated for that index
+create index t1i1 on derby737table1(c1);
+0 rows inserted/updated/deleted
+ij> select * from sys.sysstatistics;
+STATID                              |REFERENCEID                         |TABLEID       
                     |CREATIONTIMESTAMP         |&|VALID|COLCOUNT   |STATISTICS  
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ij> -- the insert above will not add a row into sys.sysstatistics for index t1i1
+insert into derby737table1 values(1,1);
+1 row inserted/updated/deleted
+ij> select * from sys.sysstatistics;
+STATID                              |REFERENCEID                         |TABLEID       
                     |CREATIONTIMESTAMP         |&|VALID|COLCOUNT   |STATISTICS  
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ij> -- now compress the table and as part of the compress, Derby should generate
+--  statistics for all the indexes provided the table is not empty
+call syscs_util.syscs_compress_table('APP','DERBY737TABLE1',1);
+0 rows inserted/updated/deleted
+ij> -- Will find statistics for index t1i1 on derby737table1 because compress
+--  table created it.
+select * from sys.sysstatistics;
+STATID                              |REFERENCEID                         |TABLEID       
                     |CREATIONTIMESTAMP         |&|VALID|COLCOUNT   |STATISTICS  
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|xxxxFILTERED-UUIDxxxx|xxxxFILTERED-UUIDxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx|I|true
|1          |numunique= &
+ij> drop table derby737table1;
+0 rows inserted/updated/deleted
+ij> -- Next Test : Make sure that drop index will drop the existing statistics 
+create table derby737table2 (c1 int, c2 int);
+0 rows inserted/updated/deleted
+ij> insert into derby737table2 values(1,1),(2,2);
+2 rows inserted/updated/deleted
+ij> select * from sys.sysstatistics;
+STATID                              |REFERENCEID                         |TABLEID       
                     |CREATIONTIMESTAMP         |&|VALID|COLCOUNT   |STATISTICS  
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ij> -- since there is data in derby737table2 when index is getting created, 
+--   statistics will be created for that index 
+create index t2i1 on derby737table2(c1);
+0 rows inserted/updated/deleted
+ij> select * from sys.sysstatistics;
+STATID                              |REFERENCEID                         |TABLEID       
                     |CREATIONTIMESTAMP         |&|VALID|COLCOUNT   |STATISTICS  
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|xxxxFILTERED-UUIDxxxx|xxxxFILTERED-UUIDxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx|I|true
|1          |numunique= &
+ij> -- deleting all the rows in table will not drop the index statistics
+delete from derby737table2;
+2 rows inserted/updated/deleted
+ij> select * from sys.sysstatistics;
+STATID                              |REFERENCEID                         |TABLEID       
                     |CREATIONTIMESTAMP         |&|VALID|COLCOUNT   |STATISTICS  
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|xxxxFILTERED-UUIDxxxx|xxxxFILTERED-UUIDxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx|I|true
|1          |numunique= &
+ij> -- dropping index will drop the index statistics, if they exist
+drop index t2i1;
+0 rows inserted/updated/deleted
+ij> select * from sys.sysstatistics;
+STATID                              |REFERENCEID                         |TABLEID       
                     |CREATIONTIMESTAMP         |&|VALID|COLCOUNT   |STATISTICS  
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ij> -- Next Test : Male sure that compress table will drop the existing statistics
+--  and will not recreate them if the table is empty
+insert into derby737table2 values(1,1),(2,2);
+2 rows inserted/updated/deleted
+ij> select * from sys.sysstatistics;
+STATID                              |REFERENCEID                         |TABLEID       
                     |CREATIONTIMESTAMP         |&|VALID|COLCOUNT   |STATISTICS  
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ij> -- since there is data in derby737table2 when index is getting created, 
+--   statistics will be created for that index 
+create index t2i1 on derby737table2(c1);
+0 rows inserted/updated/deleted
+ij> select * from sys.sysstatistics;
+STATID                              |REFERENCEID                         |TABLEID       
                     |CREATIONTIMESTAMP         |&|VALID|COLCOUNT   |STATISTICS  
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|xxxxFILTERED-UUIDxxxx|xxxxFILTERED-UUIDxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx|I|true
|1          |numunique= &
+ij> -- deleting all the rows in table will not drop the index statistics
+delete from derby737table2;
+2 rows inserted/updated/deleted
+ij> select * from sys.sysstatistics;
+STATID                              |REFERENCEID                         |TABLEID       
                     |CREATIONTIMESTAMP         |&|VALID|COLCOUNT   |STATISTICS  
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|xxxxFILTERED-UUIDxxxx|xxxxFILTERED-UUIDxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx|I|true
|1          |numunique= &
+ij> -- now compress the table and as part of the compress, Derby should drop
+--  statistics for all the indexes and should not recreate them if the
+--  user table is empty
+call syscs_util.syscs_compress_table('APP','DERBY737TABLE2',1);
+0 rows inserted/updated/deleted
+ij> select * from sys.sysstatistics;
+STATID                              |REFERENCEID                         |TABLEID       
                     |CREATIONTIMESTAMP         |&|VALID|COLCOUNT   |STATISTICS  
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ij> --end derby-737 related test cases.
 ;
 ij> 

Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/compressTable.sql
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/compressTable.sql?view=diff&rev=464683&r1=464682&r2=464683
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/compressTable.sql
(original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/compressTable.sql
Mon Oct 16 14:43:49 2006
@@ -518,3 +518,54 @@
 drop table admins;
 drop table users;
 -- end derby-1854 test case. 
+
+-- test case for derby-737 
+-- perform compress on a table that has some indexes with no statistics
+create table derby737table1 (c1 int, c2 int); 
+select * from sys.sysstatistics;
+-- create index on the table when the table is empty. No statistics will be
+--  generated for that index
+create index t1i1 on derby737table1(c1);
+select * from sys.sysstatistics;
+-- the insert above will not add a row into sys.sysstatistics for index t1i1
+insert into derby737table1 values(1,1);
+select * from sys.sysstatistics;
+-- now compress the table and as part of the compress, Derby should generate
+--  statistics for all the indexes provided the table is not empty
+call syscs_util.syscs_compress_table('APP','DERBY737TABLE1',1);
+-- Will find statistics for index t1i1 on derby737table1 because compress
+--  table created it.
+select * from sys.sysstatistics;
+drop table derby737table1;
+-- Next Test : Make sure that drop index will drop the existing statistics 
+create table derby737table2 (c1 int, c2 int); 
+insert into derby737table2 values(1,1),(2,2);
+select * from sys.sysstatistics;
+-- since there is data in derby737table2 when index is getting created, 
+--   statistics will be created for that index 
+create index t2i1 on derby737table2(c1);
+select * from sys.sysstatistics;
+-- deleting all the rows in table will not drop the index statistics
+delete from derby737table2;
+select * from sys.sysstatistics;
+-- dropping index will drop the index statistics, if they exist
+drop index t2i1;
+select * from sys.sysstatistics;
+-- Next Test : Male sure that compress table will drop the existing statistics
+--  and will not recreate them if the table is empty
+insert into derby737table2 values(1,1),(2,2);
+select * from sys.sysstatistics;
+-- since there is data in derby737table2 when index is getting created, 
+--   statistics will be created for that index 
+create index t2i1 on derby737table2(c1);
+select * from sys.sysstatistics;
+-- deleting all the rows in table will not drop the index statistics
+delete from derby737table2;
+select * from sys.sysstatistics;
+-- now compress the table and as part of the compress, Derby should drop
+--  statistics for all the indexes and should not recreate them if the
+--  user table is empty
+call syscs_util.syscs_compress_table('APP','DERBY737TABLE2',1);
+select * from sys.sysstatistics;
+
+--end derby-737 related test cases.



Mime
View raw message