I am trying to run a couple of reconciliation queries to identify missed deletes from our change data capture tool, but I need it to run relatively fast...
My source and target tables are identical but constantly in flux as CDC is applying UPDATES, INSERTS, and DELETES from source to target constantly. The issue we have is to identify those situations when the CDC tool does not replicate some database log activity from Oracle to Synapse.
The way we are doing this
for INSERTS and UPDATES is to grab all transactions where the UPDATETIME is greater than or equal to the previous UPDATETIME of the same table for the last reconciliation batch job.
DELETES are a bit more problematic because a DELETE can occur for ANY record, recent or old. The way I solved this was to do a COUNT(*) of all rows on both sides but GROUP them on CREATETIME formatted as a BIGINT, such as 'YYYYHHMMHR24'.
All I need, then, is to compare the two sides and only examine the YYYYMMDDHH24 buckets where counts differ. In those cases, I grab all the IDS of those respective buckets and wait for the CDC latency period to elapse before comparing the individual ID rows again. Hopefully, the pending deletes will CATCH UP and we match up.
My Azure Synapse target can manage these queries quite well on a table with two billion rows... But Oracle never completes... it runs forever...
So my question is, how can I manage to get the query below to run faster on the source side so that I can perform my reconciliation with my target side?
SELECT to_number(to_char(CREATETIME,'yyyymmddhh24')) AS KEY,
COUNT(*) AS VALUE
GROUP BY to_number(to_char(CREATETIME,'yyyymmddhh24'))
ORDER BY to_number(to_char(CREATETIME,'yyyymmddhh24'))
I was thinking to create an index but I am converting the datetime column to 'yyyymmddhh24' so not sure how to do this in Oracle.
Thanks for any suggestions.