[
https://issues.apache.org/jira/browse/DERBY-5681?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13257955#comment-13257955
]
Mamta A. Satoor commented on DERBY-5681:
----------------------------------------
While going through the code, I found following comment in
CreateIndexConstantAction about what will determine if a backing index will be
shared by multiple constraints.
/* The conditions which allow an index to share an
existing
* conglomerate are as follows:
*
* 1. the set of columns (both key and include columns)
and their
* order in the index is the same as that of an
existing index AND
*
* 2. the ordering attributes are the same AND
*
* 3. one of the following is true:
* a) the existing index is unique, OR
* b) the existing index is non-unique with
uniqueWhenNotNulls
* set to TRUE and the index being created is
non-unique, OR
* c) both the existing index and the one being
created are
* non-unique and have uniqueWithDuplicateNulls
set to FALSE.
*/
> When a foreign key constraint on a table is dropped, the associated
> statistics row for the conglomerate is not removed
> ----------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-5681
> URL: https://issues.apache.org/jira/browse/DERBY-5681
> Project: Derby
> Issue Type: Bug
> Components: SQL, Store
> Affects Versions: 10.8.2.2
> Reporter: Brett Bergquist
> Assignee: Mamta A. Satoor
> Attachments: DERBY5681_patch1_diff.txt
>
>
> If you drop the foreign key constraint for a table, the statistics row does
> not get removed. This affects the indexStat daemon because it now finds
> these statistics row which always appear as out of date, causing an update to
> be scheduled.
> Here is how to get it to happen:
> set schema app;
> CREATE TABLE TEST_TAB_1
> (
> ID INTEGER PRIMARY KEY NOT NULL
> );
> CREATE TABLE TEST_TAB_2
> (
> ID INTEGER PRIMARY KEY NOT NULL
> );
> ALTER TABLE TEST_TAB_2
> ADD CONSTRAINT TEST_TAB_2_FK_1
> FOREIGN KEY (ID) REFERENCES TEST_TAB_1(ID);
> insert into app.TEST_TAB_1 values (1);
> insert into test_tab_2 values(1);
> call syscs_util.syscs_update_statistics('APP', 'TEST_TAB_2', null);
> select
> c.TABLEID,
> c.CONGLOMERATENUMBER,
> c.CONGLOMERATENAME,
> c.ISINDEX,
> c.ISCONSTRAINT,
> c.CONGLOMERATEID,
> t.TABLEID,
> t.TABLENAME,
> t.TABLETYPE,
> s.STATID,
> s.REFERENCEID,
> s.TABLEID,
> s.CREATIONTIMESTAMP,
> s.TYPE,
> s.VALID,
> s.COLCOUNT,
> CAST(STATISTICS AS VARCHAR(40)) as STATISTICS
> from sys.SYSCONGLOMERATES c join sys.SYSTABLES t on c.TABLEID = t.TABLEID
> join sys.SYSSTATISTICS s on s.TABLEID = t.TABLEID
> where t.TABLENAME = 'TEST_TAB_2' and c.ISINDEX = false;
> -- At this point there are two statistic rows
> TABLEID CONGLOMERATENUMBER CONGLOMERATENAME ISINDEX ISCONSTRAINT
> CONGLOMERATEID TABLEID TABLENAME TABLETYPE STATID REFERENCEID
> TABLEID CREATIONTIMESTAMP TYPE VALID COLCOUNT STATISTICS
> 84490209-0136-6999-c1b4-000065089f97 348432
> 84490209-0136-6999-c1b4-000065089f97 false false
> cccb420a-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97
> TEST_TAB_2 T edbc8255-0136-6999-c1b4-000065089f97
> 55410238-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97
> 2012-03-31 17:36:49.629 I true 1 numunique= 1 numrows= 1
> 84490209-0136-6999-c1b4-000065089f97 348432
> 84490209-0136-6999-c1b4-000065089f97 false false
> cccb420a-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97
> TEST_TAB_2 T 05278254-0136-6999-c1b4-000065089f97
> 63454207-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97
> 2012-03-31 17:36:49.628 I true 1 numunique= 1 numrows= 1
> -- Now drop the constraint
> alter table TEST_TAB_2
> drop constraint TEST_TAB_2_FK_1;
> select
> c.TABLEID,
> c.CONGLOMERATENUMBER,
> c.CONGLOMERATENAME,
> c.ISINDEX,
> c.ISCONSTRAINT,
> c.CONGLOMERATEID,
> t.TABLEID,
> t.TABLENAME,
> t.TABLETYPE,
> s.STATID,
> s.REFERENCEID,
> s.TABLEID,
> s.CREATIONTIMESTAMP,
> s.TYPE,
> s.VALID,
> s.COLCOUNT,
> CAST(STATISTICS AS VARCHAR(40)) as STATISTICS
> from sys.SYSCONGLOMERATES c join sys.SYSTABLES t on c.TABLEID = t.TABLEID
> join sys.SYSSTATISTICS s on s.TABLEID = t.TABLEID
> where t.TABLENAME = 'TEST_TAB_2' and c.ISINDEX = false;
> -- There are still two statistic rows
> TABLEID CONGLOMERATENUMBER CONGLOMERATENAME ISINDEX ISCONSTRAINT
> CONGLOMERATEID TABLEID TABLENAME TABLETYPE STATID REFERENCEID
> TABLEID CREATIONTIMESTAMP TYPE VALID COLCOUNT STATISTICS
> 84490209-0136-6999-c1b4-000065089f97 348432
> 84490209-0136-6999-c1b4-000065089f97 false false
> cccb420a-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97
> TEST_TAB_2 T edbc8255-0136-6999-c1b4-000065089f97
> 55410238-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97
> 2012-03-31 17:36:49.629 I true 1 numunique= 1 numrows= 1
> 84490209-0136-6999-c1b4-000065089f97 348432
> 84490209-0136-6999-c1b4-000065089f97 false false
> cccb420a-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97
> TEST_TAB_2 T 05278254-0136-6999-c1b4-000065089f97
> 63454207-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97
> 2012-03-31 17:36:49.628 I true 1 numunique= 1 numrows= 1
> -- Add another row
> insert into app.TEST_TAB_1 values (2);
> insert into test_tab_2 values(2);
> -- Update the statistics
> call syscs_util.syscs_update_statistics('APP', 'TEST_TAB_2', null);
> select
> c.TABLEID,
> c.CONGLOMERATENUMBER,
> c.CONGLOMERATENAME,
> c.ISINDEX,
> c.ISCONSTRAINT,
> c.CONGLOMERATEID,
> t.TABLEID,
> t.TABLENAME,
> t.TABLETYPE,
> s.STATID,
> s.REFERENCEID,
> s.TABLEID,
> s.CREATIONTIMESTAMP,
> s.TYPE,
> s.VALID,
> s.COLCOUNT,
> CAST(STATISTICS AS VARCHAR(40)) as STATISTICS
> from sys.SYSCONGLOMERATES c join sys.SYSTABLES t on c.TABLEID = t.TABLEID
> join sys.SYSSTATISTICS s on s.TABLEID = t.TABLEID
> where t.TABLENAME = 'TEST_TAB_2' and c.ISINDEX = false;
> -- There are still two rows but now one show 1 row and one shows 2 rows
> TABLEID CONGLOMERATENUMBER CONGLOMERATENAME ISINDEX ISCONSTRAINT
> CONGLOMERATEID TABLEID TABLENAME TABLETYPE STATID REFERENCEID
> TABLEID CREATIONTIMESTAMP TYPE VALID COLCOUNT STATISTICS
> 84490209-0136-6999-c1b4-000065089f97 348432
> 84490209-0136-6999-c1b4-000065089f97 false false
> cccb420a-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97
> TEST_TAB_2 T edbc8255-0136-6999-c1b4-000065089f97
> 55410238-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97
> 2012-03-31 17:36:49.629 I true 1 numunique= 1 numrows= 1
> 84490209-0136-6999-c1b4-000065089f97 348432
> 84490209-0136-6999-c1b4-000065089f97 false false
> cccb420a-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97
> TEST_TAB_2 T 18438274-0136-6999-c1b4-000065089f97
> 63454207-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97
> 2012-03-31 17:41:19.164 I true 1 numunique= 2 numrows= 2
> -- Add the constraint back on
> ALTER TABLE TEST_TAB_2
> ADD CONSTRAINT TEST_TAB_2_FK_1
> FOREIGN KEY (ID) REFERENCES TEST_TAB_1(ID);
> -- Insert another row
> insert into app.TEST_TAB_1 values (3);
> insert into test_tab_2 values(3);
> -- Update the statistics
> call syscs_util.syscs_update_statistics('APP', 'TEST_TAB_2', null);
> select
> c.TABLEID,
> c.CONGLOMERATENUMBER,
> c.CONGLOMERATENAME,
> c.ISINDEX,
> c.ISCONSTRAINT,
> c.CONGLOMERATEID,
> t.TABLEID,
> t.TABLENAME,
> t.TABLETYPE,
> s.STATID,
> s.REFERENCEID,
> s.TABLEID,
> s.CREATIONTIMESTAMP,
> s.TYPE,
> s.VALID,
> s.COLCOUNT,
> CAST(STATISTICS AS VARCHAR(40)) as STATISTICS
> from sys.SYSCONGLOMERATES c join sys.SYSTABLES t on c.TABLEID = t.TABLEID
> join sys.SYSSTATISTICS s on s.TABLEID = t.TABLEID
> where t.TABLENAME = 'TEST_TAB_2' and c.ISINDEX = false;
> -- Now there are 3 rows
> TABLEID CONGLOMERATENUMBER CONGLOMERATENAME ISINDEX ISCONSTRAINT
> CONGLOMERATEID TABLEID TABLENAME TABLETYPE STATID REFERENCEID
> TABLEID CREATIONTIMESTAMP TYPE VALID COLCOUNT STATISTICS
> 84490209-0136-6999-c1b4-000065089f97 348432
> 84490209-0136-6999-c1b4-000065089f97 false false
> cccb420a-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97
> TEST_TAB_2 T edbc8255-0136-6999-c1b4-000065089f97
> 55410238-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97
> 2012-03-31 17:36:49.629 I true 1 numunique= 1 numrows= 1
> 84490209-0136-6999-c1b4-000065089f97 348432
> 84490209-0136-6999-c1b4-000065089f97 false false
> cccb420a-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97
> TEST_TAB_2 T 45eb02e8-0136-6999-c1b4-000065089f97
> 63454207-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97
> 2012-03-31 17:46:00.211 I true 1 numunique= 3 numrows= 3
> 84490209-0136-6999-c1b4-000065089f97 348432
> 84490209-0136-6999-c1b4-000065089f97 false false
> cccb420a-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97
> TEST_TAB_2 T 0ea502e9-0136-6999-c1b4-000065089f97
> 7ab90278-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97
> 2012-03-31 17:46:00.212 I true 1 numunique= 3 numrows= 3
> Note that dropping that recreating the constraint or compressing the table
> does not fix the problem.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
