user8995776 wrote:The client should poll for updates.
I have a Java Swing client ... Does anyone know how to do that?
rp0428 wrote:Provide supporting documentation for that.
The client should poll for updates.
Pushing from the database, which your original suggestion and the others provides no benefit in terms of the enterprise and might cause unforseen and difficult to diagnose problems.
I don't agree with either of these comments. Please explain them and provide any supporting documentation or other reliabe sources that you are basing these comments on.
In particular clarify whether your comments are about the technology of notification in general or whether you feel that the notification solution is not appropriate for this particular use case.
Polling is generally a terribly wasteful, inefficient and non-productive method of trying to identify changes that happen in a database.
Naturally the actual metrics for this depend on the number, types and frequency of the DB changes in general and with their relation to the polling frequency in particular.Yep. And most of the time the specifics for that is such that one or both the following is true
Oracle itself has gone in the opposite direction with the technology that it develops as can be inferred by the various methods provided in recent releases to support replication and the technology underlying the support of fast refresh materialized views.Oracle has all sorts of stuff in it. That doesn't mean that it ideal for all situations or even most situations.
These technologies include Advanced Queuing, Change Data Capture, Advanced Replication, Standby Databases and the like.And yet for 20 years people have managed to implement large solutions without that.
The only major Oracle technology that I am aware of that does not tend towards publish/subscribe is LogMiner and it does not use polling but rather uses a batch query process.
Detection of DB changes is difficult for even small single tables without using functionality provided by Oracle specifically for that purpose. There are many, many references in these forums and on the web in general where developers have attempted to 'roll-their-own' procedures for detecting changes after the fact. Most of these attempts are flawed both in their design and execution.And yet you think that this new technology will provide immediate error free solutions both on Oracles sides and the implementors side?
Polling is generally wasteful and inefficient for several reasons. First, poll queries can execute repeatedly when there haven't been any changes at all. This effort is entirely wasted and non-productive.I have a large volume database server whose cpu utilization is less than 3% with a projected volume this year of 3/4 billion dollars. In the mid 1980s I was more concerned with utilization. Not so much any more.
The poll queries cannot easily determine exactly when the change occured; change meaning it was COMMITTED to the database. The common approach of updating columns such as MODIFIED_DATE within a trigger are flawed because any such date/time assigned in the trigger is virtually guaranteed to occur BEFORE the data is actually committed.I agree that the design you specified is flawed.
The classic flaw manifests itself when the trigger assigns a value before midnight but the commit actually occurs after midnight. Thus two INSERT/UPDATES can have their data COMMITTED at exactly the same SCN and timestamp value but the MODIFIED_DATE values can be for two different days.
There is no way to query this data on a nightly basis to get 'changes that happened today' with the risk of missing data the next night when the extract occurs, or worse, duplicating some data in the extracts for two different nights.
And 'pushing from the database'? The change notification process is similar to publish/subscribe mechanisms and also similar to the way that listeners are used in Java.I agree your analogy is not apt for this discussion.
Certainly you wouldn't suggest that Java applications should 'poll' GUI objects to see if a user has taken any action would you?
EJP wrote:The fact the CPU usage occurs has nothing to do with +"trying to identify changes that happen in a database."+
The fact that polling is wasteful hardly needs supporting documentation. It is obvious by inspection, and rp0428 has already given several supporting reasons.
Computers switched from polling I/O to interrupt-driven I/O in about 1957 for exactly the same reason.Which of course has nothing to do with the generality of that statement when applied to +"of trying to identify changes that happen in a database."+