Handling application changes of state is a capability that Oracle Database outshines SQL at, contends Bruce Hopkins in his Sun Developer Network article "Creating High-Performance Database Applications with Java Triggers and the Oracle Database." What Hopkins offers here is a means for developers to completely eliminate the necessity for polling databases for state changes by using Java triggers in the Oracle database that handle state changes by themselves.
So, Hopkins writes, since there is a JVM inside the Oracle Database, why not make use of it and the resulting potential 600% increase in performance improvement over an external JVM. He points out that, in a typical 3-tier architecture that has Java in tiers 2 and 3 much of the performance improvement comes in not having to maintain a TCP connection between the OracleJVM and the Oracle database as both reside on the same machine.
Furthermore, given that the OracleJVM exists in the same address space of the SQL engine, it does not need to perform IPC (inter-process communication) or RPC (remote process communication) to connect the Java code to the database. In addition, the JDBC code within the OracleJVM reuses the existing connection to the database that's already present, so the overhead of creating a new connection to the database is eliminated.
Since at least one machine needs to be able to handle a large number of HTTP requests from web clients. This machine should have a pool of connections to the database, and should be able to reuse those connections to handle various operations on behalf of the end-users. What this means, then, is that, if you have any internal tasks -- such as internal state changes -- contained within the database itself, then it's a likely candidate to be encapsulated within the database's JVM.
As a preliminary to actually creating a pure-Java trigger in the Oracle database, Hopkins walks his readers through the process of creating and loading a simple Java class in the database and execute a method from the command line. This gives users the proof that it is possible to load Java classes in the OracleJVM, create a PL/SQL wrapper around Java methods, and call the wrapper from within the database.
That done, the next step is to write a pure Java trigger that actually solves a real business problem. The problem Hopkins presents for this step is creating a book reorder trigger similar to the operation routinely performed by online booksellers who need to maintain stock in titles. As he did in the prior step, Hopkins includes all the code samples necessary to complete the process.
The article shows how to create an order-point threshold and how to use batch processing to place multiple orders at the same time. Hopkins points out that when a user's Java code executes from within the OracleJVM it is still possible to use all the JDBC classes and paradigms that one would normally use if the code were executed outside of the database.
"Creating High-Performance Database Applications with Java Triggers and the Oracle Database" -- Hopkins' article
Oracle Database 11g Release 2
Read More ...