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 "Mamta A. Satoor (JIRA)" <ji...@apache.org> on 2012/04/20 22:57:33 UTC
[jira] [Created] (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
Mamta A. Satoor created DERBY-5702:
--------------------------------------
Summary: 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
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: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (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
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5702?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13258636#comment-13258636 ]
Mike Matrigali commented on DERBY-5702:
---------------------------------------
Is there any user level problem with this situation. In this situation I think the optimizer in the worst case sees 2 indexes that are on exactly the same keys in same order. One index is unique and one is not. The optimizer should always choose the unique one, when it chooses unique indexes it should never look at the statistics.
If anyone chooses to fix this issue, they should look at optimizes creation of the statistic row - at least for single column indexes. There is no need to scan the whole table when you know the underlying index is unique.
I would rate this issue as minor, unless a user level issue is known.
> 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
>
> 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: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Issue Comment Edited] (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
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5702?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13271690#comment-13271690 ]
Mamta A. Satoor edited comment on DERBY-5702 at 5/9/12 6:53 PM:
----------------------------------------------------------------
While working on an upgrade test for DERBY-4115, I found that a missing statistics like this can be created with update statistics procedure but that procedure was added in 10.5 and hence is only available in 10.5 and higher. Additionally, A table compress is supposed to update the statistics as well which was added in 10.1 but I found that running table compress did not create this missing statistics. So, whenever we decide to fix this issue, we probably want to also see why table compress would not add missing statistics for a constraint which is sharing a backing index.
was (Author: mamtas):
While working on an upgrade test for DERBY-4115, I found that a missing statistics like this can be created with update statistics procedure but that procedure was added in 10.5 and hence is only available in 10.5 and higher. Additionally, A table compress is supposed to update the statistics as well which was added in 10.3 but I found that running table compress did not create this missing statistics. So, whenever we decide to fix this issue, we probably want to also see why table compress would not add missing statistics for a constraint which is sharing a backing index.
> 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
>
> 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: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (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
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5702?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13271690#comment-13271690 ]
Mamta A. Satoor commented on DERBY-5702:
----------------------------------------
While working on an upgrade test for DERBY-4115, I found that a missing statistics like this can be created with update statistics procedure but that procedure was added in 10.5 and hence is only available in 10.5 and higher. Additionally, A table compress is supposed to update the statistics as well which was added in 10.3 but I found that running table compress did not create this missing statistics. So, whenever we decide to fix this issue, we probably want to also see why table compress would not add missing statistics for a constraint which is sharing a backing index.
> 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
>
> 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: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[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
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ 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