• 1 reply
  • Latest Post - ‏2012-12-14T21:29:23Z by Rphilo
36 Posts

Pinned topic Implication of Index on Target Tables in a subcription

‏2012-12-14T01:15:27Z | idr-cdc
I am using Oracle as my target table using LiveAuidt option . My understanding is having an index is double load on CDC because it has to do the insert and maintain the index also.
However, the below statement from documentation is contrary to that .Any help appreciated. Practically we do not have requirement for index for access purpose as its a staging area and not an access layer but if adding index helps performance then why not?
Information Management IBM InfoSphere Change Data Capture, Version 6.5.2

Indexes in your target database

A large number of indexes in your target database will put more demands on your target database and take more time to manage when an operation is applied. Tables that do not have a unique index and fragmentation of the table or index will slow down the apply process for the InfoSphere® CDC target replication engine.

You can use the Target Apply metrics in the Management Console Performance view to determine if InfoSphere CDC is extremely slow in applying database operations to your target database. If this is the case, you should determine if a different indexing strategy for the table in question will still meet your querying needs for that table. 
Updated on 2016-08-14T15:43:20Z at 2016-08-14T15:43:20Z by Ken Motilall
  • Rphilo
    565 Posts

    Re: Implication of Index on Target Tables in a subcription

    The documentation is trying to say two things, I think
    1) The more indexes you have on your target table, the more work the database has to do when a row is inserted
    2) On the other hand if you have updates or deletes to apply on the target it is beneficial to have a primary key constraint or unique index for the mirroring key, as otherwise a table scan would be required, which can be very costly for large tables,
    However in your case you are using audit mapping, so all the data applied on the target is in the form of an insert. Therefore an index is of no use to the apply, and the most efficient set-up from the point of CDC is to have no indexes at all. However this means that any process which reads or updates the data applied by CDC has to work harder, and you would normally seek to balance the requirement to optimise the audit apply with no indexes, and the requirement to optimise the data processing with the indexes this requires.if the number of indexes required is low (one or two, say)  then I would not expect CDC to be impacted by having them.