Menu
Is it possible to do direct-load INSERTs in Oracle through JDBC?
I currently use batched prepared statements (through Spring JDBC), is there any way to make these bypass the redo logs on a NOLOGGING table?
This is with Oracle 11g.
465
Direct-load INSERT enhances performance during insert operations by formatting and writing data directly into Oracle datafiles, without using the buffer cache. This functionality is similar to that of the Direct Loader utility (SQL*Loader).
A direct path load eliminates much of the Oracle database overhead by formatting Oracle data blocks and writing the data blocks directly to the database files. A direct load does not compete with other users for database resources, so it can usually load data at near disk speed.
The user executes a statement to insert a new row into a table. Oracle checks the user's privileges to make sure the user has the necessary rights to perform the insertion. If the user's privilege information isn't already in the library cache, it will have to be read from disk into that cache.
It's not simply a case of your tables being enabled for nologging, only some operations are going to not log. I would suggest you check UNRECOVERABLE_TIME for your v$datafile to see when these non recoverable operations were performed and then match that up to what it was that happened.
direct path inserts are only possible in a insert into x as select * from y scenario. This can be done using jdbc, no problem. This can not be done with insert and values. This also can not be done when the database in in force logging mode. Most of the times when a standby database in connected, the primary database will be in force logging mode.
As Gary Myers mentioned, since 11gR2 there is the APPEND_VALUES hint. As with the 'old' append hint, it should only be used for bulk inserts.
I hope this helps, Ronald.
120
There is an APPEND_VALUES hint introduced in 11gR2 for direct path inserts with INSERT...VALUES.
Don't have an 11gR2 instance available to test whether it works with JDBC batch inserts. It is worth a try though.
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
