You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Knut Anders Hatlen (JIRA)" <ji...@apache.org> on 2012/09/18 17:31:07 UTC
[jira] [Updated] (DERBY-5702) Creating a foreign key constraint
does not automatically create a statistics row if foreign key constraint
will share a backing index created for a primary key
[ https://issues.apache.org/jira/browse/DERBY-5702?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-5702:
--------------------------------------
Priority: Minor (was: Major)
Urgency: Low
Labels: derby_triage10_10 (was: )
Derby 10.10 triage: Adjusting priority and urgency per Mike's comments.
> Creating a foreign key constraint does not automatically create a statistics row if foreign key constraint will share a backing index created for a primary key
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-5702
> URL: https://issues.apache.org/jira/browse/DERBY-5702
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.8.2.2
> Reporter: Mamta A. Satoor
> Priority: Minor
> Labels: derby_triage10_10
>
> When a foreign key constraints is created on a column which already has a primary key constraint, the statistics for foreign key constraint do not get created automatically. Have to run update statistics by hand to add statistics for foreign key constraint. I hope my understanding of statistics creation is correct in this regards. Following script shows the issue
> java -Dderby.storage.indexStats.auto=false -Dij.exceptionTrace=true org.apache.derby.tools.ij
> connect 'jdbc:derby:db1;create=true';
> CREATE TABLE TEST_TAB_1
> (
> ID INTEGER NOT NULL primary key
> );
> CREATE TABLE TEST_TAB_2
> (
> ID INTEGER not null, ID1 INTEGER not null
> );
> insert into TEST_TAB_1 values (1);
> insert into test_tab_2 values(1,1);
> CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEST_TAB_1', null);
> CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEST_TAB_2', null);
> --At this point, we will find statistics row for primary key constraint on TEST_TAB_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_1' and c.ISINDEX = false;
> --Now create primary key constraint on TEST_TAB_2
> ALTER TABLE TEST_TAB_2
> ADD CONSTRAINT TEST_TAB_2_PK_1
> PRIMARY KEY (id);
> --At this point, we will find statistics row for primary key constraint on TEST_TAB_2
> 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 if we create a foreign key constraint on TEST_TAB_2(ID), there will be no statistics created for it.
> ALTER TABLE TEST_TAB_2
> ADD CONSTRAINT TEST_TAB_2_FK_1
> FOREIGN KEY(id) REFERENCES TEST_TAB_1(id);
> --still only one statistics row for TEST_TAB_2
> 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;
> --Running statistics creation by hand will create 2nd statistics row for TEST_TAB_2
> CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEST_TAB_2', null);
> -- now will have 2 statistics rows for TEST_TAB_2
> 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;
> --If I create foreign key on a column that does not share backing index with primary key, stats will get created automatically
> ALTER TABLE TEST_TAB_2
> ADD CONSTRAINT TEST_TAB_2_FK_2
> FOREIGN KEY(id1) REFERENCES TEST_TAB_1(id);
> --will have additional constraint row for new foreign key constraint on TEST_TAB_2
> 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;
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira