Our IDR replicate a oracle source which has billions in volume to the target, in doing so it also captures the changed records into the log table. Since, there are lot of activity going on to the source, the log table is growing in millions per day.
The issue being here, we use the log table to identify the delta records and currently there're no index nor partition on the log tables which results in full table scan each time if in need of identifying delta records.
Can someone suggests how to improve this? i.e. Is it fine to have index on one of column that IDR create/uses? Example op_time column which records the date & time of modification made to the particular record. or creation of partition on op_time? Any of these things will make IDR replication process tedious or make them fail?