This chapter describes the Oracle performance extensions to the Java Database Connectivity (JDBC) standard.
This chapter covers the following topics:
You can reduce the number of round-trips to the database, thereby improving application performance, by grouping multipleUPDATE,DELETE, orINSERT statements into a single batch and having the whole batch sent to the database and processed in one trip. This is referred to as update batching.
Note:
The JDBC 2.0 specification refers to update batching as batch updates.This is especially useful with prepared statements, when you are repeating the same statement with different bind variables.
Oracle JDBC supports two distinct models for update batching:
The standard model, implementing the JDBC 2.0 specification, which is referred to as standard update batching
The Oracle-specific model, independent of the JDBC 2.0 specification, which is referred to as Oracle update batching
Note:
It is important to be aware that you cannot mix these models. In any single application, you can use one model or the other, but not both. Oracle JDBC driver will throw exceptions when you mix these.This section covers the following topics:
This section compares and contrasts the general models and types of statements supported for standard update batching and Oracle update batching.
Oracle Model Versus Standard Model
Oracle update batching uses a batch value that typically results in implicit processing of a batch. The batch value is the number of operations you want to add to a batch for each trip to the database. As soon as that many operations have been added to the batch, the batch is processed. Note the following:
You can set a default batch for the connection object, which applies to any prepared statement run in that connection.
For any individual prepared statement object, you can set a statement batch value that overrides the connection batch value.
You can choose to explicitly process a batch at any time, overriding both the connection batch value and the statement batch value.
Standard update batching is a manual, explicit model. There is no batch value. You manually add operations to the batch, and then, explicitly choose when to process the batch.
Oracle update batching is a more efficient model because the driver knows ahead of time how many operations will be batched. In this sense, the Oracle model is more static and predictable. With the standard model, the driver has no way of knowing in advance how many operations will be batched. In this sense, the standard model is more dynamic in nature.
If you want to use update batching, then you can choose between the two models on the basis of the following:
Use Oracle update batching if portability is not critical. This will probably result in the greatest performance improvement.
Use standard update batching if portability is a higher priority than performance.
As implemented by Oracle, update batching is intended for use with prepared statements, when you are repeating the same statement with different bind variables. Be aware of the following:
Oracle update batching supportsonly prepared statement objects. For a callable statement, both the connection default batch value and the statement batch value are overridden with a value of 1. In an Oracle generic statement, there is no statement batch value, and the connection default batch value is overridden with a value of 1.
To adhere to the JDBC 2.0 standard, Oracle implementation of standard update batching supports callable statements, withoutOUT parameters, and generic statements, as well as prepared statements. You can migrate standard update batching into an Oracle JDBC application without difficulty.
You can batch onlyUPDATE,INSERT, orDELETE operations. Processing a batch that includes an operation that attempts to return a result set will cause an exception.
Note:
The Oracle implementation of standard update batching does not implement true batching for generic statements and callable statements. Although Oracle JDBC supports the use of standard batching syntax forStatement andCallableStatement objects, you will see performance improvement for onlyPreparedStatement objects.The Oracle update batching feature associates a batch value with each prepared statement object. With Oracle update batching, instead of the JDBC driver running a prepared statement each time theexecuteUpdate method is called, the driver adds the statement to a batch of accumulated processing requests. The driver will pass all the operations to the database for processing once the batch value is reached. For example, if the batch value is 10, then each batch of 10 operations will be sent to the database and processed in one trip.
A method in theOracleConnection class enables you to set a default batch value for the Oracle connection as a whole, and this batch value applies to any Oracle prepared statement in the connection. For any particular Oracle prepared statement, a method in theOraclePreparedStatement class enables you to set a statement batch value that overrides the connection batch value. You can also override both batch values by choosing to manually process the pending batch.
Note:
Do not mix standard update batching with Oracle update batching in the same application. The JDBC driver will throw an exception when you mix these.
Disable auto-commit mode if you use either update batching model. In case an error occurs while you are processing a batch, this provides you the option of committing or rolling back the operations that ran successfully prior to the error.
Note the following limitations and implementation details regarding Oracle update batching:
By default, there is no statement batch value and the connection batch value is 1.
Batch values between 5 and 30 tend to be the most effective. Setting a very high value might even have a negative effect. It is worth trying different values to verify the effectiveness for your particular application.
Regardless of the batch value in effect, if any of the bind variables of an Oracle prepared statement is astream type, then Oracle JDBC driver sets the batch value to 1 and sends any queued requests to the database for processing.
Oracle JDBC driver automatically runs thesendBatch method of an Oracle prepared statement in any of the following circumstances:
The connection receives aCOMMIT request, either as a result of calling thecommit method or as a result of auto-commit mode.
The statement receives aclose request.
The connection receives aclose request.
Note:
A connectionCOMMIT request, statement close, or connection close has an effect on a pending batch only if you use Oracle update batching. However, if you use standard update batching, then it has no effect on a pending batch.If the connection receives aROLLBACK request beforesendBatch has been called, then the pending batched operations are not removed. You must explicitly callclearBatch to do this.
You can specify a default batch value for any Oracle prepared statement in your Oracle connection. To do this, use thesetDefaultExecuteBatch method of theOracleConnection object. For example, the following code sets the default batch value to 20 for all prepared statement objects associated with theconn connection object:
((OracleConnection)conn).setDefaultExecuteBatch(20);
Even though this sets the default batch value for all the prepared statements of the connection, you can override it by calling thesetExecuteBatch method of theoracle.jdbc.OraclePreparedStatement interface on individual Oracle prepared statements.
The connection batch value will apply to statement objects created after this batch value was set.
Note that instead of calling thesetDefaultExecuteBatch method, you can set thedefaultBatchValue Java property if you use a JavaProperties object in establishing the connection.
Use the following steps to set the statement batch value for a particular Oracle prepared statement. This will override any connection batch value set using thesetDefaultExecuteBatch method of theOracleConnection instance for the connection in which the statement is processed.
Write your prepared statement, and specify input values for the first row, as follows:
PreparedStatement ps = conn.prepareStatement ("INSERT INTO dept VALUES (?,?,?)");ps.setInt (1,12);ps.setString (2,"Oracle");ps.setString (3,"USA");Cast your prepared statement toOraclePreparedStatement, and apply thesetExecuteBatch method. In this example, the batch size of the statement is set to 2.
((OraclePreparedStatement)ps).setExecuteBatch(2);
If you wish, insert thegetExecuteBatch method at any point in the program to check the default batch value for the statement, as follows:
System.out.println (" Statement Execute Batch Value " + ((OraclePreparedStatement)ps).getExecuteBatch());If you send an execute-update call to the database at this point, then no data will be sent to the database, and the call will return 0.
// No data is sent to the database by this call to executeUpdateSystem.out.println ("Number of rows updated so far: " + ps.executeUpdate ());If you enter a set of input values for a second row and an execute-update, then the number of batch calls toexecuteUpdate will be equal to the batch value of 2. The data will be sent to the database, and both rows will be inserted in a single round-trip.
ps.setInt (1, 11);ps.setString (2, "Applications");ps.setString (3, "Indonesia");int rows = ps.executeUpdate ();System.out.println ("Number of rows updated now: " + rows);ps.close ();To check the overall connection batch value of an Oracle connection instance, use theOracleConnection classgetDefaultExecuteBatch method:
Integer batch_val = ((OracleConnection)conn).getDefaultExecuteBatch();
To check the particular statement batch value of an Oracle prepared statement, use theOraclePreparedStatement classgetExecuteBatch method:
Integer batch_val = ((OraclePreparedStatement)ps).getExecuteBatch();
Note:
If no statement batch value has been set, thengetExecuteBatch will return the connection batch value.If you want to process accumulated operations before the batch value in effect is reached, then use thesendBatch method of theOraclePreparedStatement object.
For this example, presume you set the connection batch value to 20. This sets the default batch value for all prepared statement objects associated with the connection to 20. You can accomplish this by casting your connection toOracleConnection and applying thesetDefaultExecuteBatch method for the connection, as follows:
((OracleConnection)conn).setDefaultExecuteBatch (20);
Override the batch value as follows:
Write your prepared statement, specify input values for the first row, and then process the statement, as follows:
PreparedStatement ps = conn.prepareStatement ("insert into dept values (?, ?, ?)"); ps.setInt (1, 32);ps.setString (2, "Oracle");ps.setString (3, "USA");System.out.println (ps.executeUpdate ());The batch is not processed at this point. Theps.executeUpdate method returns0.
If you enter a set of input values for a second operation and callexecuteUpdate again, then the data will still not be sent to the database, because the batch value in effect for the statement is the connection batch value, which is20.
ps.setInt (1, 33);ps.setString (2, "Applications");ps.setString (3, "Indonesia");// this batch is still not executed at this pointint rows = ps.executeUpdate (); System.out.println ("Number of rows updated before calling sendBatch: " + rows);Note that the value ofrows in theprintln statement is0.
If you apply thesendBatch method at this point, then the two previously batched operations will be sent to the database in a single round-trip. ThesendBatch method also returns the total number of updated rows. This property ofsendBatch is used byprintln to print the number of updated rows.
// Execution of both previously batched executes will happen// at this point. The number of rows updated will be// returned by sendBatch.rows = ((OraclePreparedStatement)ps).sendBatch ();System.out.println ("Number of rows updated by calling sendBatch: " + rows);ps.close ();After you process the batch, you must still commit the changes, presuming auto-commit is disabled as recommended.
Callingcommit on the connection object in Oracle batching not only commits operations in batches that have been processed, but also issues an implicitsendBatch call to process all pending batches. Socommit effectively commits changes for all operations that have been added to a batch.
In a nonbatching situation, theexecuteUpdate method of anOraclePreparedStatement object will return the number of database rows affected by the operation.
In an Oracle batching situation, this method returns the number of rows affected at the time the method is invoked, as follows:
If anexecuteUpdate call results in the operation being added to the batch, then the method returns a value of 0, because nothing was written to the database yet.
If anexecuteUpdate call results in the batch value being reached and the batch being processed, then the method will return the total number of rows affected by all operations in the batch.
Similarly, thesendBatch method of anOraclePreparedStatement object returns the total number of rows affected by all operations in the batch.
Example 23-1 illustrates the use of Oracle update batching.
Example 23-1 Oracle Update Batching
The following example illustrates how you use the Oracle update batching feature. It assumes you have imported theoracle.driver.* interfaces.
...OracleDataSource ods = new OracleDataSource();ods.setURL("jdbc:oracle:oci);ods.setUser("scott");ods.setPassword("tiger");Connection conn = ods.getConnection();conn.setAutoCommit(false);PreparedStatement ps = conn.prepareStatement("insert into dept values (?, ?, ?)"); //Change batch size for this statement to 3 ((OraclePreparedStatement)ps).setExecuteBatch (3); ps.setInt(1, 23); ps.setString(2, "Sales"); ps.setString(3, "USA"); ps.executeUpdate(); //JDBC queues this for later execution ps.setInt(1, 24); ps.setString(2, "Blue Sky"); ps.setString(3, "Montana"); ps.executeUpdate(); //JDBC queues this for later execution ps.setInt(1, 25); ps.setString(2, "Applications"); ps.setString(3, "India"); ps.executeUpdate(); //The queue size equals the batch value of 3 //JDBC sends the requests to the databaseps.setInt(1, 26); ps.setString(2, "HR"); ps.setString(3, "Mongolia"); ps.executeUpdate(); //JDBC queues this for later execution ((OraclePreparedStatement)ps).sendBatch(); // JDBC sends the queued requestconn.commit();ps.close();...Note:
Updates deferred through batching can affect the results of other queries. In the following example, if the first query is deferred due to batching, then the second will return unexpected results:UPDATE emp SET name = "Sue" WHERE name = "Bob";SELECT name FROM emp WHERE name = "Sue";
If any one of the batched operations fails to complete successfully or attempts to return a result set during anexecuteBatch call, then the processing stops and ajava.sql.BatchUpdateException is generated.
If the exception is raised, you can call thegetUpdateCounts method on theBatchUpdateException object to retrieve the update count. This method returns anint array of update counts, just as theexecuteBatch method does.
In Oracle Database 11g Release 1 (11.1), the integer array returned containsnStatement.EXECUTE_FAILED entries, wheren is the size of the batch. However, this does not indicate where in the batch the error occurred. The only option you have is to roll back the transaction.
In Oracle Database 11g Release 1 (11.1), the integer array returned containsnStatement.SUCCESS_NO_INFO entries, wheren is the number of elements in the batch that have been successfully executed.
Note:
The execution of the batch always stops with the first element of the batch that generates an error.Oracle implements the standard update batching model according to the JDBC 2.0 specification.
This model, unlike the Oracle update batching model, depends on explicitly adding statements to the batch using anaddBatch method and explicitly processing the batch using anexecuteBatch method. In the Oracle model, you callexecuteUpdate as in a nonbatching situation, but whether an operation is added to the batch or the whole batch is processed is typically determined implicitly, depending on whether or not a predetermined batch value is reached.
Note:
Do not mix standard update batching with Oracle update batching in the same application. Oracle JDBC driver will throw exceptions when these are mixed.
Disable auto-commit mode if you use either update batching model. In case an error occurs while you are processing a batch, this provides you the option of committing or rolling back the operations that ran successfully prior to the error.
This section discusses the limitations and implementation details regarding the Oracle implementation of standard update batching.
In Oracle JDBC applications, update batching is intended for use with prepared statements that are being processed repeatedly with different sets of bind values.
The Oracle implementation of standard update batching does not implement true batching for generic statements and callable statements. Even though Oracle JDBC supports the use of standard batching forStatement andCallableStatement objects, you are unlikely to see performance improvement.
When any statement object is first created, its statement batch is empty. Use the standardaddBatch method to add an operation to the statement batch. This method is specified in the standardjava.sql.Statement,PreparedStatement, andCallableStatement interfaces, which are implemented by theoracle.jdbc.OracleStatement,OraclePreparedStatement, andOracleCallableStatement interfaces, respectively.
For aStatement object, theaddBatch method takes a JavaString with a SQL operation as input. For example:
...Statement stmt = conn.createStatement();stmt.addBatch("INSERT INTO emp VALUES(1000, 'Joe Jones')");stmt.addBatch("INSERT INTO dept VALUES(260, 'Sales')");stmt.addBatch("INSERT INTO emp_dept VALUES(1000, 260)");...At this point, three operations are in the batch.
Note:
Remember, however, that in the Oracle implementation of standard update batching, you will probably see no performance improvement in batching generic statements.For prepared statements, update batching is used to batch multiple runs of the same statement with different sets of bind parameters. For aPreparedStatement orOraclePreparedStatement object, theaddBatch method takes no input. It simply adds the operation to the batch using the bind parameters last set by the appropriatesetXXX methods. This is also true forCallableStatement orOracleCallableStatement objects, but remember that in the Oracle implementation of standard update batching, you will probably see no performance improvement in batching callable statements.
For example:
...PreparedStatement pstmt = conn.prepareStatement("INSERT INTO employees VALUES(?, ?)");pstmt.setInt(1, 2000);pstmt.setString(2, "Milo Mumford");pstmt.addBatch();pstmt.setInt(1, 3000);pstmt.setString(2, "Sulu Simpson");pstmt.addBatch();...At this point, two operations are in the batch.
Because a batch is associated with a single prepared statement object, you can batch only repeated runs of a single prepared statement, as in this example.
To process the current batch of operations, use theexecuteBatch method of the statement object. This method is specified in the standardStatement interface, which is extended by the standardPreparedStatement andCallableStatement interfaces.
Following is an example that repeats the prepared statementaddBatch calls shown previously and then processes the batch:
...PreparedStatement pstmt = conn.prepareStatement("INSERT INTO employees VALUES(?, ?)");pstmt.setInt(1, 2000);pstmt.setString(2, "Milo Mumford");pstmt.addBatch();pstmt.setInt(1, 3000);pstmt.setString(2, "Sulu Simpson");pstmt.addBatch();int[] updateCounts = pstmt.executeBatch();...Starting from Oracle Database 11g Release 1 (11.1), theexecuteBatch method has been improved so that when an error occurs in the middle of the batch execution, theBatchUpdateExecution exception that is thrown contains the position of the error in the batch. TheBatchUpdateExecution.getUpdateCounts method returns an array ofint containing the update counts for the updates that were executed successfully before this error occurred. So if an error occurs in the 5th element of the batch, then the size of the array returned is 4 and each value isStatement.SUCCESS_NO_INFO.
After you process the batch, you must still commit the changes, presuming auto-commit is disabled as recommended.
Callingcommit, commits nonbatched operations and batched operations for statement batches that have been processed, but for the Oracle implementation of standard batching, has no effect on pending statement batches that havenot been processed.
To clear the current batch of operations instead of processing it, use theclearBatch method of the statement object. This method is specified in the standardStatement interface, which is extended by the standardPreparedStatement andCallableStatement interfaces.
Keep the following things in mind:
When a batch is processed, operations are performed in the order in which they were batched.
After callingaddBatch, you must call eitherexecuteBatch orclearBatch before a call toexecuteUpdate, otherwise there will be a SQL exception.
AclearBatch orexecuteBatch call resets the statement batch to empty.
The statement batch is not reset to empty if the connection receives aROLLBACK request. You must explicitly callclearBatch to reset it.
Note:
If you are using Oracle update batching in Oracle Database 11g, then you do not have to clear your batches explicitly in the code after a rollback. However, it is OK to invokeclearBatch method after a rollback.
If you are using Oracle update batching in an earlier release, then you have to invokeclearBatch method to clear your batches explicitly after a rollback.
InvokingclearBatch method after a rollback works for all releases.
AnexecuteBatch call closes the current result set of the statement object, if one exists.
Nothing is returned by theclearBatch method.
Following is an example that repeats the prepared statementaddBatch calls shown previously but then clears the batch under certain circumstances:
...PreparedStatement pstmt = conn.prepareStatement("INSERT INTO employees VALUES(?, ?)");pstmt.setInt(1, 2000);pstmt.setString(2, "Milo Mumford");pstmt.addBatch();pstmt.setInt(1, 3000);pstmt.setString(2, "Sulu Simpson");pstmt.addBatch();if (...condition...){ int[] updateCounts = pstmt.executeBatch(); ...}else{ pstmt.clearBatch(); ...}If a statement batch is processed successfully, then the integer array, or update counts array, returned by the statementexecuteBatch call will always have one element for each operation in the batch. In the Oracle implementation of standard update batching, the values of the array elements are as follows:
For a prepared statement batch, it is not possible to know the number of rows affected in the database by each individual statement in the batch. Therefore, all array elements have a value of-2. According to the JDBC 2.0 specification, a value of-2 indicates that the operation was successful but the number of rows affected is unknown.
For a generic statement batch, the array contains the actual update counts indicating the number of rows affected by each operation. The actual update counts can be provided only in the case of generic statements in the Oracle implementation of standard batching.
For a callable statement batch, the server always returns the value1 as the update count, irrespective of the number rows affected by each operation.
In your code, upon successful processing of a batch, you should be prepared to handle either-2,1, or true update counts in the array elements. For a successful batch processing, the array contains either all-2, 1, or all positive integers.
Example 23-2 illustrates the use of standard update batching.
Example 23-2 Standard Update Batching
This example combines the sample fragments in the previous sections, accomplishing the following steps:
Disabling auto-commit mode, which you should always do when using either update batching model
Creating a prepared statement object
Adding operations to the batch associated with the prepared statement object
Processing the batch
Committing the operations from the batch
conn.setAutoCommit(false);PreparedStatement pstmt = conn.prepareStatement("INSERT INTO employees VALUES(?, ?)");pstmt.setInt(1, 2000);pstmt.setString(2, "Milo Mumford");pstmt.addBatch();pstmt.setInt(1, 3000);pstmt.setString(2, "Sulu Simpson");pstmt.addBatch();int[] updateCounts = pstmt.executeBatch();conn.commit();pstmt.close();...You can process the update counts array to determine if the batch processed successfully.
If any one of the batched operations fails to complete successfully or attempts to return a result set during anexecuteBatch call, then the processing stops and ajava.sql.BatchUpdateException is generated.
After a batch exception, the update counts array can be retrieved using thegetUpdateCounts method of theBatchUpdateException object. This returns anint array of update counts, just as theexecuteBatch method does. In the Oracle implementation of standard update batching, contents of the update counts array are as follows, after a batch is processed:
For a prepared statement batch, it is not possible to know which operation failed. The array has one element for each operation in the batch, and each element has a value of-3. According to the JDBC 2.0 specification, a value of-3 indicates that an operation did not complete successfully. In this case, it was presumably just one operation that actually failed, but because the JDBC driver does not know which operation that was, it labels all the batched operations as failures.
You should always perform aROLLBACK operation in this situation.
For a generic statement batch or callable statement batch, the update counts array is only a partial array containing the actual update counts up to the point of the error. The actual update counts can be provided because Oracle JDBC cannot use true batching for generic and callable statements in the Oracle implementation of standard update batching.
For example, if there were 20 operations in the batch, the first 13 succeeded, and the 14th generated an exception, then the update counts array will have 13 elements, containing actual update counts of the successful operations.
You can either commit or roll back the successful operations in this situation, as you prefer.
In your code, upon failed processing of a batch, you should be prepared to handle either-3 or true update counts in the array elements when an exception occurs. For a failed batch processing, you will have either a full array of-3 or a partial array of positive integers.
You cannot callexecuteUpdate for regular, nonbatched processing of an operation if the statement object has a pending batch of operations.
However, you can intermix batched operations and nonbatched operations in a single statement object if you process nonbatched operations either prior to adding any operations to the statement batch or after processing the batch. Essentially, you can callexecuteUpdate for a statement object only when its update batch is empty. If the batch is non-empty, then an exception will be generated.
For example, it is valid to have a sequence, such as the following:
...PreparedStatement pstmt = conn.prepareStatement("INSERT INTO employees VALUES(?, ?)");pstmt.setInt(1, 2000);pstmt.setString(2, "Milo Mumford");int scount = pstmt.executeUpdate(); // OK; no operations in pstmt batchpstmt.setInt(1, 3000);pstmt.setString(2, "Sulu Simpson");pstmt.addBatch(); // Now start a batchpstmt.setInt(1, 4000);pstmt.setString(2, "Stan Leland");pstmt.addBatch();int[] bcounts = pstmt.executeBatch();pstmt.setInt(1, 5000);pstmt.setString(2, "Amy Feiner");int scount = pstmt.executeUpdate(); // OK; pstmt batch was executed...Intermixing nonbatched operations on one statement object and batched operations on another statement object within your code is permissible. Different statement objects are independent of each other with regard to update batching operations. ACOMMIT request will affect all nonbatched operations and all successful operations in processed batches, but will not affect any pending batches.
Premature batch flush happens due to a change in cached metadata. Cached metadata can be changed due to various reasons, such as the following:
The initial bind was null and the following bind is not null.
A scalar type is initially bound as string and then bound as scalar type or the reverse.
The premature batch flush count is summed to the return value of the nextexecuteUpdate orsendBatch method.
The old functionality lost all these batch flush values which can be obtained now. To switch back to the old functionality, you can set theAccumulateBatchResult property tofalse, as follows:
java.util.Properties info = new java.util.Properties(); info.setProperty("user", "SCOTT"); info.setProperty("passwd", "TIGER"); // other properties ... // property: batch flush type info.setProperty("AccumulateBatchResult", "false");OracleDataSource ods = new OracleDataSource();ods.setConnectionProperties(info);ods.setURL("jdbc:oracle:oci:@"");Connection conn = ods.getConnection();Note:
TheAccumulateBatchResult property is set totrue by default.Example 23-3 illustrates premature batch flushing.
Example 23-3 Premature Batch Flushing
((OraclePreparedStatement)pstmt).setExecuteBatch (2); pstmt.setNull (1, OracleTypes.NUMBER); pstmt.setString (2, "test11"); int count = pstmt.executeUpdate (); // returns 0 /* * Premature batch flush happens here. */ pstmt.setInt (1, 22); pstmt.setString (2, "test22"); int count = pstmt.executeUpdate (); // returns 0 pstmt.setInt (1, 33); pstmt.setString (2, "test33"); /* * returns 3 with the new batching scheme where as, * returns 2 with the old batching scheme. */ int count = pstmt.executeUpdate ();
In addition to update batching, Oracle JDBC drivers support the following extensions that improve performance by reducing round-trips to the database:
This reduces round-trips to the database by fetching multiple rows of data each time data is fetched. The extra data is stored in client-side buffers for later access by the client. The number of rows to prefetch can be set as desired.
This avoids an inefficiency in the standard JDBC protocol for performing and returning the results of queries.
Suppressing database metadataTABLE_REMARKS columns
This avoids an expensive outer join operation.
Oracle provides several extensions to connection properties objects to support these performance extensions. These extensions enable you to set theremarksReporting flag and default values for row prefetching and update batching.
This section covers the following topics:
There is no maximum prefetch setting.The default value is 10. Larger or smaller values may be appropriate depending on the number of rows and columns expected from the query. You can set the default connection row-prefetch value using aProperties object.
When a statement object is created, it receives the default row-prefetch setting from the associated connection. Subsequent changes to the default connection row-prefetch setting will have no effect on the statement row-prefetch setting.
If a column of a result set is of data typeLONG,LONG RAW orLOBs returned through the data interface, that is, the streaming types, then JDBC changes the statement row-prefetch setting to 1, even if you never actually read a value of either of these types.
Setting the prefetch size can affect the performance of an application. Increasing the prefetch size will reduce the number of round-trips required to get all the data, but will increase memory usage. This will depend on the number and size of the columns in the query and the number of rows expected to be returned. It will also depend on the memory and CPU loading of the JDBC client machine. The optimum for a standalone client application will be different from a heavily loaded application server. Please consider also the speed and latency of the network connection.
Note:
Starting from Oracle Database 11g Release 1 (11.1), the Thin driver can fetch the firstprefetch_size number of rows from the server in the very first roundtrip. This saves one round-trip in select statements.If you are migrating an application from earlier releases of Oracle JDBC drivers to 10g Release 1 (10.1) or later releases of Oracle JDBC drivers, then you should revisit the optimizations that you had done earlier, because the memory usage and performance characteristics may have changed substantially.
A common situation that you may encounter is, say, you have a query that selects a unique key. The query will return only zero or one row. Setting the prefetch size to 1 will decrease memory and CPU usage and cannot increase round-trips. However, you must be careful to avoid the error of requesting an extra fetch by writingwhile(rs.next()) instead ofif(rs.next()).
If you are using the JDBC Thin driver, then in the case where only zero or one row is expected, use theuseFetchSizeWithLongColumn connection property, because it will performPARSE,EXECUTE, andFETCH in a single round-trip.
Tuning of the prefetch size should be done along with tuning of memory management in your JVM under realistic loads of the actual application.
Note:
Do not mix the JDBC 2.0 fetch size application programming interface (API) and the Oracle row-prefetching API in your application. You can use one or the other, but not both.
Be aware that setting the Oracle fetch size value can affect not only queries, but also explicitly refetching rows in a result set through the result setrefreshRow method, which is relevant for scroll-sensitive/read-only, scroll-sensitive/updatable, and scroll-insensitive/updatable result sets, and the window size of a scroll-sensitive result set, affecting how often automatic refetches are performed. However, the Oracle fetch size value will be overridden by any setting of the fetch size.
See Also:
"Supported Connection Properties"The implementation ofdefineColumnType changed significantly in Oracle Database 10g. Previously,defineColumnType was used both as a performance optimization and to force data type conversion. In previous releases, all of the drivers benefited from calls todefineColumnType. Starting from Oracle Database 10g, the JDBC Thin driver no longer needs the information provided. The JDBC Thin driver achieves maximum performance without calls todefineColumnType. The JDBC Oracle Call Interface (OCI) and server-side internal drivers still get better performance when the application usesdefineColumnType.
If your code is used with both the JDBC Thin and OCI drivers, you can disable thedefineColumnType method when using the Thin driver by setting the connection propertydisableDefineColumnType totrue. Doing this makesdefineColumnType have no effect. Do not set this connection property totrue when using the JDBC OCI or server-side internal drivers.
You can also usedefineColumnType to control how much memory the client-side allocates or to limit the size of variable-length data.
Follow these general steps to define column types for a query:
If necessary, cast your statement object toOracleStatement,OraclePreparedStatement, orOracleCallableStatement, as applicable.
If necessary, use theclearDefines method of yourStatement object to clear any previous column definitions for thisStatement object.
On each column, call thedefineColumnType method of yourStatement object, passing it these parameters:
Column index (integer)
Type code (integer)
Use thestatic constants of thejava.sql.Types class ororacle.jdbc.OracleTypes class, such asTypes.INTEGER,Types.FLOAT,Types.VARCHAR,OracleTypes.VARCHAR, andOracleTypes.ROWID. Type codes for standard types are identical in these two classes.
Type name (string)
For structured objects, object references, and arrays, you must also specify the type name. For example,Employee,EmployeeRef, orEmployeeArray.
Maximum field size (integer)
Optionally specify a maximum data length for this column.
You cannot specify a maximum field size parameter if you are defining the column type for a structured object, object reference, or array. If you try to include this parameter, it will be ignored.
Form of use (short)
Optionally specify a form of use for the column. This can beOraclePreparedStatement.FORM_CHAR to use the database character set orOraclePreparedStatement.FORM_NCHAR to use the national character set. If this parameter is omitted, the default isFORM_CHAR.
For example, assumingstmt is an Oracle statement, use:
stmt.defineColumnType(column_index,typeCode);
If the column isVARCHAR or equivalent and you know the length limit:
stmt.defineColumnType(column_index,typeCode,max_size);
For anNVARCHAR column where the original maximum length is desired and conversion to the database character set is requested:
stmt.defineColumnType(column_index,typeCode, 0, OraclePreparedStatement.FORM_CHAR );
For structured object, object reference, and array columns:
stmt.defineColumnType(column_index,typeCode,typeName);
Set a maximum field size if you do not want to receive the full default length of the data. Calling thesetMaxFieldSize method of the standard JDBCStatement class sets a restriction on the amount of data returned. Specifically, the size of the data returned will be the minimum of the following:
The maximum field size set indefineColumnType
The maximum field size set insetMaxFieldSize
The natural maximum size of the data type
After you complete these steps, use theexecuteQuery method of the statement to perform the query.
Note:
It is no longer necessary to specify a data type for each column of the expected result set.Example 23-4 illustrates the use of this feature. It assumes you have imported theoracle.jdbc.* interfaces.
Example 23-4 Defining Column Types
OracleDataSource ods = new OracleDataSource();ods.setURL("jdbc:oracle:thin:@localhost:1502:orcl");ods.setUser("scott");ods.setPassword("tiger");Connection conn = ods.getConnection();Statement stmt = conn.createStatement();// Allocate only 2 chars for this column (truncation will happen)((OracleStatement)stmt).defineColumnType(1, Types.VARCHAR, 2);ResultSet rset = stmt.executeQuery("select ename from emp");while (rset.next() ) System.out.println(rset.getString(1));stmt.close();As this example shows, you must cast theStatement object,stmt, toOracleStatement in the invocation of thedefineColumnType method. ThecreateStatement method of the connection returns an object of typejava.sql.Statement, which does not have thedefineColumnType andclearDefines methods. These methods are provided only in theOracleStatement implementation.
The define-extensions use JDBC types to specify the desired types. The allowed define types for columns depend on the internal Oracle type of the column.
All columns can be defined to their natural JDBC types. In most cases, they can be defined to theTypes.CHAR orTypes.VARCHAR type code.
Table 23-1 lists the valid column definition arguments you can use in thedefineColumnType method.
Table 23-1 Valid Column Type Specifications
| If the column has Oracle SQL type: | You can use defineColumnType to define it as: |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
It is always valid to usedefineColumnType with the original data type of the column.
ThegetColumns,getProcedureColumns,getProcedures, andgetTables methods of the database metadata classes are slow if they must reportTABLE_REMARKS columns, because this necessitates an expensive outer join. For this reason, the JDBC driver doesnot reportTABLE_REMARKS columns by default.
You can enableTABLE_REMARKS reporting by passing atrue argument to thesetRemarksReporting method of anOracleConnection object.
Equivalently, instead of callingsetRemarksReporting, you can set theremarksReporting Java property if you use a JavaProperties object in establishing the connection.
If you are using a standardjava.sql.Connection object, you must cast it toOracleConnection to usesetRemarksReporting.
Example 23-5 illustrates how to enableTABLE_REMARKS reporting.
Example 23-5 TABLE_REMARKS Reporting
Assumingconn is the name of your standardConnection object, the following statement enablesTABLE_REMARKS reporting:
( (oracle.jdbc.OracleConnection)conn ).setRemarksReporting(true);
By default, thegetColumns method does not retrieve information about the columns if a synonym is specified. To enable the retrieval of information if a synonym is specified, you must call thesetIncludeSynonyms method on the connection as follows:
( (oracle.jdbc.driver.OracleConnection)conn ).setIncludeSynonyms(true)
This will cause all subsequentgetColumns method calls on the connection to include synonyms. This is similar tosetRemarksReporting. Alternatively, you can set theincludeSynonyms connection property. This is similar to theremarksReporting connection property.
However, bear in mind that ifincludeSynonyms istrue, then the name of the object returned in thetable_name column will be the synonym name, if a synonym exists. This is true even if you pass the table name togetColumns.
Considerations for getProcedures and getProcedureColumns Methods
According to JDBC versions 1.1 and 1.2, the methodsgetProcedures andgetProcedureColumns treat thecatalog,schemaPattern,columnNamePattern, andprocedureNamePattern parameters in the same way. In the Oracle definition of these methods, the parameters are treated differently:
catalog
Oracle does not have multiple catalogs, but it does have packages. Consequently, thecatalog parameter is treated as the package name. This applies both on input, which is thecatalog parameter, and the output, which is thecatalog column in the returnedResultSet. On input, the construct"", which is an empty string, retrieves procedures and arguments without a package, that is, standalone objects. Anull value means to drop from the selection criteria, that is, return information about both standalone and packaged objects. That is, it has the same effect as passing in the percent sign (%). Otherwise, thecatalog parameter should be a package name pattern, with SQL wild cards, if desired.
schemaPattern
All objects within Oracle database must have a schema, so it does not make sense to return information for those objects without one. Thus, the construct"", which is an empty string, is interpreted on input to mean the objects in the current schema, that is, the one to which you are currently connected. To be consistent with the behavior of thecatalog parameter,null is interpreted to drop the schema from the selection criteria. That is, it has the same effect as passing in%. It can also be used as a pattern with SQL wild cards.
procedureNamePattern andcolumnNamePattern
The empty string (" ") does not make sense for either parameter, because all procedures and arguments must have names. Thus, the construct"" will raise an exception. To be consistent with the behavior of other parameters,null has the same effect as passing in percent sign (%).