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.
Best Answer
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: