[#DERBY-737] SYSCS_UTIL.SYSCS_COMPRESS_TABLE should create statistics if they do not exist

Derby

SYSCS_UTIL.SYSCS_COMPRESS_TABLE should create statistics if they do not exist

Created: 01/Dec/05 09:52 AM Updated: 28/Feb/08 05:21 PM

Return to search

Component/s:

SQL

Affects Version/s:

10.0.2.0, 10.0.2.1, 10.0.2.2, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6

Fix Version/s:

10.2.2.0, 10.3.1.4

Time Tracking:

Not Specified

File Attachments:

File Name	Date Attached	Attached By	Size
DERBY737_v1_diff_SYSCS_COMPRESS_TABLE.txt	2006-10-12 03:11 AM	Mamta A. Satoor	15 kB
DERBY737_v1_stat_SYSCS_COMPRESS_TABLE.txt	2006-10-12 03:11 AM	Mamta A. Satoor	0.4 kB

Issue Links:

Reference

This issue is related to:

~~DERBY-1964~~ Update the documentation of SYSCS_UTIL.SYSCS_COMPRESS_TABLE for the changes that went in as part of DERBY-737

Resolution Date:

16/Oct/06 09:44 PM

Description

« Hide

There must be an entry in the SYSSTATISTICS table in order for the cardinality statistics in SYSSTATISTICS to be created with SYSCS_UTIL.SYSCS_COMPRESS_TABLE

SYSCS_UTIL.SYSCS_COMPRESS_TABLE should create statistics if they don't exist. The only way to create them if the index was created on an empty table, seems to be to drop and recreate the index after the table has been loaded.

The documentation will also need updating if this change is made.
http://db.apache.org/derby/docs/10.1/tuning/ctunstats57373.html

All

Comments

Work Log

Change History

Subversion Commits

Sort Order:

Repository	Revision	Date	User	Message
ASF	#464551	Mon Oct 16 16:32:50 UTC 2006	bpendleton	~~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.
				Files Changed
				MODIFY /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/compressTable.sql MODIFY /db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java MODIFY /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out MODIFY /db/derby/code/trunk/java/engine/org/apache/derby/iapi/db/OnlineCompress.java

Repository	Revision	Date	User	Message
ASF	#464683	Mon Oct 16 21:43:49 UTC 2006	bpendleton	~~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/
				Files Changed
				MODIFY /db/derby/code/branches/10.2/java/engine/org/apache/derby/iapi/db/OnlineCompress.java MODIFY /db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/compressTable.sql MODIFY /db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java MODIFY /db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out

Repository	Revision	Date	User	Message
ASF	#632065	Thu Feb 28 17:21:33 UTC 2008	mamta	Merging changes for ~~DERBY-737~~ into 10.1 codeline. The revision being merged is 464551 and the commit comments for 464551 are as follows 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.
				Files Changed
				MODIFY /db/derby/code/branches/10.1/java/engine/org/apache/derby/iapi/db/OnlineCompress.java MODIFY /db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/compressTable.sql MODIFY /db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java MODIFY /db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out