You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by bp...@apache.org on 2006/10/16 23:43:50 UTC
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/
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.