0

My team is looking at getting live updates from our Oracle database from a frequently updated table. I have discovered CQN and configured a test environment to use UTL_HTTP to POST to a webhook when the table changes. However, in the 'Best Practices' section of the documentation for CQN:

Register few queries—preferably those that reference objects that rarely change.

And in other articles for instance:

if you know the table is subject to a lot of change, then your apps will be better off simply polling the table and avoiding any CQN overhead. Note that CQN was designed to be used for relatively infrequently updated tables.

CQN had seemed like a great solution until I read these disclaimers. The table we want to get live updates from has about 1000 inserts per second during busy time bursts.

The alternative to CQN appears to be polling to query this table every second. Is polling like that the best solution for getting live updates from a table, or is there something I am missing? I am working with Oracle 11g.

Thank you.

4
  • 1
    Oracke 20c Transaction Event Queuing (TEQ) might be the answer if asked next year Jul 29, 2020 at 20:20
  • what is the drawback of a polling solution?
    – miracle173
    Jul 29, 2020 at 22:06
  • @miracle173 I imagine the drawback of a polling solution is duplicate management. If some query job runs in a loop and the query takes 1-10 seconds to return data, we'd want to grab 10+ seconds of data each time to make sure we didn't miss anything. Running that every second, you would have a bunch of data to identify as duplicates to ignore. It's an easy problem to solve, but I suppose we were hoping for something Oracle can provide "out of the box" to obviate that need. With CQN you don't have to worry about duplicates.
    – Aaron B
    Jul 30, 2020 at 18:22
  • The Oracle Advanced Streams @pmdba referenced looks interesting, I will be reading up on that soon.
    – Aaron B
    Jul 30, 2020 at 18:23

1 Answer 1

1

Polling isn't uncommon; it will depend on what your application and data latency requirements are. Depending on your infrastructure, it may not be possible to poll more than a few times per minute.

If you must have real-time, or as near as possible, you might be able to use Oracle Advanced Queues to stream the updates.

It would be an extra cost, but one other alternative is Oracle GoldenGate:

1
  • Thanks @pmdba, Oracle Streams Advanced Queueing looks promising. I will be reading up on that soon.
    – Aaron B
    Jul 30, 2020 at 18:25

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.