Index: java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java =================================================================== --- java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java (revision 454614) +++ java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java (working copy) @@ -1574,7 +1574,7 @@ updateStatistics = true; } else - cCount = tc.openSortRowSource(sortIds[index]); + cCount = new CardinalityCounter(tc.openSortRowSource(sortIds[index])); newIndexCongloms[index] = tc.createAndLoadConglomerate( "BTREE", @@ -1585,6 +1585,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( @@ -1601,26 +1627,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 @@ -1639,7 +1645,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 */ Index: java/engine/org/apache/derby/iapi/db/OnlineCompress.java =================================================================== --- java/engine/org/apache/derby/iapi/db/OnlineCompress.java (revision 454614) +++ java/engine/org/apache/derby/iapi/db/OnlineCompress.java (working copy) @@ -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 Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/compressTable.sql =================================================================== --- java/testing/org/apache/derbyTesting/functionTests/tests/lang/compressTable.sql (revision 454614) +++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/compressTable.sql (working copy) @@ -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. Index: java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out =================================================================== --- java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out (revision 454614) +++ java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out (working copy) @@ -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>