You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Next »

Background

Fragment table includes dataspace_id and anchor_id columns. Anchor table also has dataspace_id column. Since an Anchor belongs to only one Dataspace, and a Fragment belongs to only one Anchor, the dataspace_id column in Fragment is redundant (since the fragment's dataspace_id is a duplicate of the anchor's dataspace_id).

It is at best redundant to have the same data duplicated in multiple database tables, and at worst could lead to inconsistencies.

This task is to remove dataspace_id column from the Fragment table.

In addition to wasting space on duplicate data, the Fragment table also includes an index for dataspace_id, a foreign key constraint, and dataspace_id is included in the uniqueness constraint for Fragment. Removing all these may improve write performance (albeit a small improvement).

There are no negative performance impacts expected, as the only functionality relying on dataspace_id is query-across-anchors (relevant SQL queries can be changed to use an inexpensive table JOIN on Anchor table).

Implementation Proposal

It is proposed to remove the dataspace_id column from the Fragment table in the Liquibase changelog. This also involves removing associated indexes and constraints.

Proposed Liquibase changes

databaseChangeLog:

  - changeSet:
      id: 19-1
      author: cps
      changes:
        - dropIndex:
            indexName: FKI_FRAGMENT_DATASPACE_ID_FK
            tableName: fragment
      rollback:
        - createIndex:
            columns:
              - column:
                  name: dataspace_id
            indexName: FKI_FRAGMENT_DATASPACE_ID_FK
            tableName: fragment

  - changeSet:
      id: 19-2
      author: cps
      changes:
        - dropUniqueConstraint:
            constraintName: fragment_dataspace_id_anchor_id_xpath_key
            tableName: fragment
        - addUniqueConstraint:
            columnNames: anchor_id, xpath
            constraintName: fragment_anchor_id_xpath_key
            tableName: fragment
      rollback:
        - dropUniqueConstraint:
            constraintName: fragment_anchor_id_xpath_key
            tableName: fragment
        - addUniqueConstraint:
            columnNames: dataspace_id, anchor_id, xpath
            constraintName: fragment_dataspace_id_anchor_id_xpath_key
            tableName: fragment

  - changeSet:
      id: 19-3
      author: cps
      changes:
        - dropForeignKeyConstraint:
            baseTableName: fragment
            constraintName: fragment_dataspace_id_fkey
      rollback:
        - addForeignKeyConstraint:
            baseColumnNames: dataspace_id
            baseTableName: fragment
            constraintName: fragment_dataspace_id_fkey
            deferrable: false
            initiallyDeferred: false
            onDelete: NO ACTION
            onUpdate: NO ACTION
            referencedColumnNames: id
            referencedTableName: dataspace
            validate: true

  - changeSet:
      id: 19-4
      author: cps
      changes:
        - dropColumn:
            columnName: dataspace_id
            tableName: fragment
      rollback:
        - addColumn:
            tableName: fragment
            columns:
              - column:
                  name: dataspace_id
                  type: INTEGER
        - sqlFile:
            path: changelog/db/changes/19-repopulate-dataspace-id-for-rollback.sql
        - addNotNullConstraint:
            tableName: fragment
            columnName: dataspace_id
            columnDataType: INTEGER


Results

Table description before update, after update, and after rollback


Fragment table before update, after update, and after rollback

Before update





idxpathattributesanchor_idparent_iddataspace_idschema_node_id
1/cps-ran-schema{}1
1
2/cps-ran-schema/NearRTRIC[@idNearRTRIC="11"]{"idNearRTRIC": "11"}111
137/cps-ran-schema/NearRTRIC[@idNearRTRIC="22"]/GNBDUFunction[@idGNBDUFunction="6"]/NRCellDU[@idNRCellDU="15549"]/attributes{"nRPCI": 12, "nRTAC": 310, "cellLocalId": 15549}11361
139/cps-ran-schema/NearRTRIC[@idNearRTRIC="22"]/GNBDUFunction[@idGNBDUFunction="6"]/NRCellDU[@idNRCellDU="15360"]/attributes{"nRPCI": 12, "nRTAC": 310, "cellLocalId": 15360}11381
141/cps-ran-schema/NearRTRIC[@idNearRTRIC="22"]/GNBDUFunction[@idGNBDUFunction="6"]/NRCellDU[@idNRCellDU="15361"]/attributes{"nRPCI": 12, "nRTAC": 310, "cellLocalId": 15361}11401
144/dmi-registry{}2
2
145/dmi-registry/cm-handles[@id='336a9e2779b64a1cac04fee3208a1c76']{"id": "336a9e2779b64a1cac04fee3208a1c76", "dmi-service-name": "http://ncmp-dmi-plugin-stub:8080", "dmi-data-service-name": "", "dmi-model-service-name": ""}21442
After update





idxpathattributesanchor_idparent_idschema_node_id
1/cps-ran-schema{}1


2/cps-ran-schema/NearRTRIC[@idNearRTRIC="11"]{"idNearRTRIC": "11"}11

137/cps-ran-schema/NearRTRIC[@idNearRTRIC="22"]/GNBDUFunction[@idGNBDUFunction="6"]/NRCellDU[@idNRCellDU="15549"]/attributes{"nRPCI": 12, "nRTAC": 310, "cellLocalId": 15549}1136

139/cps-ran-schema/NearRTRIC[@idNearRTRIC="22"]/GNBDUFunction[@idGNBDUFunction="6"]/NRCellDU[@idNRCellDU="15360"]/attributes{"nRPCI": 12, "nRTAC": 310, "cellLocalId": 15360}1138

141/cps-ran-schema/NearRTRIC[@idNearRTRIC="22"]/GNBDUFunction[@idGNBDUFunction="6"]/NRCellDU[@idNRCellDU="15361"]/attributes{"nRPCI": 12, "nRTAC": 310, "cellLocalId": 15361}1140

144/dmi-registry{}2


145/dmi-registry/cm-handles[@id='48d42e05942b457dafdfa24195fdb092']{"id": "48d42e05942b457dafdfa24195fdb092", "dmi-service-name": "http://ncmp-dmi-plugin-stub:8080", "dmi-data-service-name": "", "dmi-model-service-name": ""}2144

After rollback





idxpathattributesanchor_idparent_idschema_node_iddataspace_id
1/cps-ran-schema{}1

1
2/cps-ran-schema/NearRTRIC[@idNearRTRIC="11"]{"idNearRTRIC": "11"}11
1
137/cps-ran-schema/NearRTRIC[@idNearRTRIC="22"]/GNBDUFunction[@idGNBDUFunction="6"]/NRCellDU[@idNRCellDU="15549"]/attributes{"nRPCI": 12, "nRTAC": 310, "cellLocalId": 15549}1136
1
139/cps-ran-schema/NearRTRIC[@idNearRTRIC="22"]/GNBDUFunction[@idGNBDUFunction="6"]/NRCellDU[@idNRCellDU="15360"]/attributes{"nRPCI": 12, "nRTAC": 310, "cellLocalId": 15360}1138
1
141/cps-ran-schema/NearRTRIC[@idNearRTRIC="22"]/GNBDUFunction[@idGNBDUFunction="6"]/NRCellDU[@idNRCellDU="15361"]/attributes{"nRPCI": 12, "nRTAC": 310, "cellLocalId": 15361}1140
1
144/dmi-registry{}2

2
145/dmi-registry/cm-handles[@id='48d42e05942b457dafdfa24195fdb092']{"id": "48d42e05942b457dafdfa24195fdb092", "dmi-service-name": "http://ncmp-dmi-plugin-stub:8080", "dmi-data-service-name": "", "dmi-model-service-name": ""}2144
2
  • No labels