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
    FROM dbo.my_huge_table
    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.

  • You could try creating an index on CREATETIME and hopefully reduce the number of data blocks that must be scanned to account for all rows. Also, not sure what purpose the to_number conversion serves in your query, since it's adding compute overhead and not really changing the output in any meaningful way (still displays and sorts essentially the same). Getting rid of it should substantially reduce your compute overhead.
    – pmdba
    Feb 11, 2023 at 4:44
  • i need the buckets at the level of YYYYMMDDHH... The GROUP BY must produce no more than 100,000 buckets
    – Lauren_G
    Feb 11, 2023 at 4:45
  • 1
    Adding to_number doesn't change the level of grouping or limit the total number of buckets, at least not as you've presented your query.
    – pmdba
    Feb 11, 2023 at 12:03

1 Answer 1


For efficiency (same resource, less time)

  1. You would have an index and a not null constraint on a column so the count(*) is faster;

  2. And if possible create_time would have a create_day column;

  3. If 2 not possible, partition table by create_time column; and the index becomes local.

For faster just throw more parallels at it.

SELECT /*+ parallel(src,12) */ 
  to_number(to_char(CREATETIME,'yyyymmddhh24')) AS KEY,
FROM dbo.my_huge_table src
GROUP BY to_number(to_char(CREATETIME,'yyyymmddhh24'))
ORDER BY to_number(to_char(CREATETIME,'yyyymmddhh24'))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.