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 18:32:52 UTC
svn commit: r464551 - in /db/derby/code/trunk/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 09:32:50 2006
New Revision: 464551
URL: http://svn.apache.org/viewvc?view=rev&rev=464551
Log:
DERBY-737: SYSCS_UTIL.SYSCS_COMPRESS_TABLE should create statistics
This patch was contributed by Mamta A. Satoor (msatoor@gmail.com)
The changes have been very localized in
AlterTableConstantAction.java!updateIndex(). Currently, this method checks
if statistics already exist for an index. If yes, then it sets a flag
updateStatistics to true. Later, the code checks for this flag and drops
the existing statistics and creates new statistics for that index provided
the user table at this point is not empty. So, as we can see, if there is
an index with no preexisting statistics, the flag updateStatistics will be
set to false and hence no statistics related code is executed and hence
even though the user table is not empty at the time of compress, no
statistics get generated for such an index.
I am proposing to fix the problem by still using the flag to see if
an index has pre-existing statistics. If yes, then we should drop
those statistics. Next, whether the index has pre-existing statistics or
not, go ahead and create new statistics for the index provided the
user table is not currently empty.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/iapi/db/OnlineCompress.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/compressTable.sql
Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/db/OnlineCompress.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/db/OnlineCompress.java?view=diff&rev=464551&r1=464550&r2=464551
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/db/OnlineCompress.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/db/OnlineCompress.java Mon Oct 16 09:32:50 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/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?view=diff&rev=464551&r1=464550&r2=464551
==============================================================================
--- 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 Mon Oct 16 09:32:50 2006
@@ -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
*/
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out?view=diff&rev=464551&r1=464550&r2=464551
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out Mon Oct 16 09:32:50 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/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/compressTable.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/compressTable.sql?view=diff&rev=464551&r1=464550&r2=464551
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/compressTable.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/compressTable.sql Mon Oct 16 09:32:50 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.