Derby
  1. Derby
  2. 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

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.8.2.2
    • Fix Version/s: None
    • Component/s: SQL
    • Urgency:
      Low
    • Issue & fix info:
      Repro attached

      Description

      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;

        Issue Links

          Activity

          Mamta A. Satoor created issue -
          Hide
          Mike Matrigali added a comment -

          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.

          Show
          Mike Matrigali added a comment - 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.
          Hide
          Mamta A. Satoor added a comment - - edited

          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.

          Show
          Mamta A. Satoor added a comment - - edited 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.
          Mamta A. Satoor made changes -
          Field Original Value New Value
          Link This issue is related to DERBY-5681 [ DERBY-5681 ]
          Hide
          Knut Anders Hatlen added a comment -

          Derby 10.10 triage: Adjusting priority and urgency per Mike's comments.

          Show
          Knut Anders Hatlen added a comment - Derby 10.10 triage: Adjusting priority and urgency per Mike's comments.
          Knut Anders Hatlen made changes -
          Labels derby_triage10_10
          Urgency Low [ 10053 ]
          Priority Major [ 3 ] Minor [ 4 ]
          Gavin made changes -
          Workflow jira [ 12663446 ] Default workflow, editable Closed status [ 12801959 ]

            People

            • Assignee:
              Unassigned
              Reporter:
              Mamta A. Satoor
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:

                Development