Data Integration

 View Only

  • 1.  target table without unique index/constraint

    Posted Tue February 07, 2023 04:56 AM
    we discovered a situation where
    a target table for cdc has no unique index nor unique constraint
    but for the moment we do not have any duplicates
    in cdc subscription the key was specified as automatic - but there is none
    will cdc use the entire row as index ?
    we can change the subs to specify the key and select all columns. but there is anyhow a potential problem if nullable columns are allowed
    we tried to create an unique index on table, but this table is very large and we get out of swap space.
    we need to ask to change the machine to get more.
    question : will cdc use the entire row as index ?  and fail if dupl row is encountered I presume
    is cdc not checking this when you setup the subs that nothing is unique on table ? is it allowed ?



    ------------------------------
    Thanks for all answers
    Best Regards,
    Guy Przytula
    ------------------------------

    #DataIntegration
    #DataReplication


  • 2.  RE: target table without unique index/constraint

    Posted Tue February 07, 2023 05:09 AM
    Guy

    Yes. most target databases will allow you to map a table without a unique key. Ultimately if there is no key then CDC would do a search by all searchable columns. Some databases will allow a search on null columns, but that would be implicitly taken care of.

    So yes you can have duplicates in the target it there are duplicates on the source. This is a problem if the target database returns multiple rows in the result set in relation to the CDC apply SQL update ... where.. statement as then multiple rows are updated or deleted on the target in response to a single transaction entry (relating to an operation on one row only. Oracle as an example will allow the where to be qualified with a row ID so only one row is returned out of the duplicates on the target: other databases like Netezza do not seem to support this and all matching rows are deleted or updated.

    But there is a potentially a wider issue. Without a unique index, probably a full table scan is performed. for every update and delete operation. For a large active table this is a big impact on the replication throughput - and I have had customers with massive latency issues caused by full table scans. On the other hand if the source table only has inserts, then there is no issue either of performance or integrity and the lack of a unique index is not an issue.

    Hope this helps

    Robert

    ------------------------------
    Robert Philo
    ------------------------------

    Original Message


  • 3.  RE: target table without unique index/constraint

    Posted Tue February 07, 2023 05:55 AM
    many thanks


    ------------------------------
    Thanks for all answers
    Best Regards,
    Guy Przytula
    ------------------------------

    Original Message