Oracle8i JDBC Developer's Guide and Reference Release 3 (8.1.7) Part Number A83724-01 |
Library | Service | Contents | Index

## Additional Oracle Performance Extensions

In addition to update batching, discussed previously, Oracle JDBC drivers support the following extensions that improve performance by reducing round trips to the database:

- prefetching rows

  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.

- specifying column types

  This avoids an inefficiency in the normal JDBC protocol for performing and returning the results of queries.

- suppressing database metadata `TABLE_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 the `remarksReporting` flag and default values for row prefetching and update batching. For more information, see "Specifying a Database URL and Properties Object".

### Oracle Row Prefetching

Oracle JDBC drivers include extensions that allow you to set the number of rows to prefetch into the client while a result set is being populated during a query. This feature reduces the number of round trips to the server.

Note: With JDBC 2.0, the ability to preset the fetch size has become standard functionality. For information about the standard implementation of this feature, see "Fetch Size".

#### Setting the Oracle Prefetch Value

Standard JDBC receives the result set one row at a time, and each row requires a round trip to the database. The row-prefetching feature associates an integer row-prefetch setting with a given statement object. JDBC fetches that number of rows at a time from the database during the query. That is, JDBC will fetch N rows that match the query criteria and bring them all back to the client at once, where N is the prefetch setting. Then, once your `next()` calls have run through those N rows, JDBC will go back to fetch the next N rows that match the criteria.

You can set the number of rows to prefetch for a particular Oracle statement (any type of statement). You can also reset the default number of rows that will be prefetched for all statements in your connection. The default number of rows to prefetch to the client is 10.

Set the number of rows to prefetch for a particular statement as follows:

1. Cast your statement object to an `OracleStatement`, `OraclePreparedStatement`, or `OracleCallableStatement` object, as applicable, if it is not already one of these.

2. Use the `setRowPrefetch()` method of the statement object to specify the number of rows to prefetch, passing in the number as an integer. If you want to check the current prefetch number, use the `getRowPrefetch()` method of the Statement object, which returns an integer.

Set the default number of rows to prefetch for all statements in a connection, as follows:

1. Cast your `Connection` object to an `OracleConnection` object.

2. Use the `setDefaultRowPrefetch()` method of your `OracleConnection` object to set the default number of rows to prefetch, passing in an integer that specifies the desired default. If you want to check the current setting of the default, then use the `getDefaultRowPrefetch()` method of the `OracleConnection` object. This method returns an integer.

   Equivalently, instead of calling `setDefaultRowPrefetch()`, you can set the `defaultRowPrefetch` Java property if you use a Java `Properties` object in establishing the connection. See "Specifying a Database URL and Properties Object".

   Notes: Do not mix the JDBC 2.0 fetch size 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 row-prefetch value can affect not only queries, but also: 1) explicitly refetching rows in a result set through the result set `refreshRow()` method available with JDBC 2.0 (relevant for scroll-sensitive/read-only, scroll-sensitive/updatable, and scroll-insensitive/updatable result sets); and 2) the "window" size of a scroll-sensitive result set, affecting how often automatic refetches are performed. The Oracle row-prefetch value will be overridden, however, by any setting of the fetch size. See "Fetch Size" for more information.

##### Example: Row Prefetching

The following example illustrates the row-prefetching feature. It assumes you have imported the `oracle.jdbc.driver.*` classes.

    Connection conn =
           DriverManager.getConnection("jdbc:oracle:oci8:","scott","tiger");

    //Set the default row-prefetch setting for this connection
    ((OracleConnection)conn).setDefaultRowPrefetch(7);

    /* The following statement gets the default row-prefetch value for
       the connection, that is, 7.
     */
    Statement stmt = conn.createStatement();

    /* Subsequent statements look the same, regardless of the row
       prefetch value. Only execution time changes.
     */
    ResultSet rset = stmt.executeQuery("SELECT ename FROM emp");
    System.out.println( rset.next () );

    while( rset.next () )
        System.out.println( rset.getString (1) );

    //Override the default row-prefetch setting for this statement
    ( (OracleStatement)stmt ).setRowPrefetch (2);

    ResultSet rset = stmt.executeQuery("SELECT ename FROM emp");
    System.out.println( rset.next () );

    while( rset.next() )
       System.out.println( rset.getString (1) );

    stmt.close();

For complete sample applications, including how to set the connection default row-prefetch value and the statement row-prefetch value, see "Oracle Row Prefetching Specified in Connection--RowPrefetch_connection.java" and "Oracle Row Prefetching Specified in Statement--RowPrefetch_statement.java".

#### Oracle Row-Prefetching Limitations

There is no maximum prefetch setting, but empirical evidence suggests that 10 is effective. Oracle does not recommend exceeding this value in most situations. If you do not set the default row-prefetch value for a connection, 10 is the default.

A statement object receives the default row-prefetch setting from the associated connection at the time the statement object is created. Subsequent changes to the connection's default row-prefetch setting have no effect on the statement's row-prefetch setting.

If a column of a result set is of datatype `LONG` or `LONG RAW` (that is, the streaming types), JDBC changes the statement's row-prefetch setting to 1, even if you never actually read a value of either of those types.

If you use the form of the `DriverManager` class `getConnection()` method that takes a `Properties` object as an argument, then you can set the connection's default row-prefetch value that way. See "Specifying a Database URL and Properties Object".

### Defining Column Types

Oracle JDBC drivers enable you to inform the driver of the types of the columns in an upcoming query, saving a round trip to the database that would otherwise be necessary to describe the table.

When standard JDBC performs a query, it first uses a round trip to the database to determine the types that it should use for the columns of the result set. Then, when JDBC receives data from the query, it converts the data, as necessary, as it populates the result set.

When you specify column types for a query, you avoid the first round trip to the database. The server, which is optimized to do so, performs any necessary type conversions.

For a complete sample application, see "Oracle Column Type Definitions--DefineColumnType.java".

Following these general steps to define column types for a query:

1. Cast your statement object to an `OracleStatement`, `OraclePreparedStatement`, or `OracleCallableStatement` object, as applicable, if it is not already one of these.

2. If necessary, use the `clearDefines()` method of your `Statement` object to clear any previous column definitions for this `Statement` object.

3. For each column of the expected result set, invoke the `defineColumnType()` method of your `Statement` object, passing it these parameters:

   - column index (integer)

   - typecode (integer)

     Use the static constants of the `java.sql.Types` class or `oracle.jdbc.driver.OracleTypes` class (such as `Types.INTEGER`, `Types.FLOAT`, `Types.VARCHAR`, `OracleTypes.VARCHAR`, and `OracleTypes.ROWID`). Typecodes for standard types are identical in these two classes.

   - type name (string) (structured objects, object references, and arrays only)

     For structured objects, object references, and arrays, you must also specify the type name (for example, `Employee`, `EmployeeRef`, or `EmployeeArray`).

   - (optionally) 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.

   For example, assuming `stmt` is an Oracle statement, use this syntax:

       stmt.defineColumnType(column_index, typeCode);

   or (recommended if the column is `VARCHAR` or equivalent and you know the length limit):

       stmt.defineColumnType(column_index, typeCode, max_size);

   or (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 the `setMaxFieldSize()` method of the standard JDBC `Statement` class sets a restriction on the amount of data returned. Specifically, the size of the data returned will be the minimum of:

   - the maximum field size set in `defineColumnType()`

   or:

   - the maximum field size set in `setMaxFieldSize()`

   or:

   - the natural maximum size of the datatype

Once you complete these steps, use the statement's `executeQuery()` method to perform the query.

Note: You must define the datatype for every column of the expected result set. If the number of columns for which you specify types does not match the number of columns in the result set, the process fails with a SQL exception.

##### Example: Defining Column Types

The following example illustrates the use of this feature. It assumes you have imported the `oracle.jdbc.driver.*` classes.

    Connection conn =
         DriverManager.getConnection("jdbc:oracle:oci8:","scott","tiger");

    Statement stmt = conn.createStatement();

    /*Ask for the column as a string:
     *Avoid a round trip to get the column type.
     *Convert from number to string on the server.
     */
    ((OracleStatement)stmt).defineColumnType(1, Types.VARCHAR);

    ResultSet rset = stmt.executeQuery("select empno from emp");

    while (rset.next() )
        System.out.println(rset.getString(1));

    stmt.close();

As this example shows, you must cast the statement (`stmt`) to type `OracleStatement` in the invocation of the `defineColumnType()` method. The connection's `createStatement()` method returns an object of type `java.sql.Statement`, which does not have the `defineColumnType()` and `clearDefines()` methods. These methods are provided only in the `OracleStatement` 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 the `Types.CHAR` or `Types.VARCHAR` typecode.

Table 13-1 lists the valid column definition arguments you can use in the `defineColumnType()` method.

##### Table 13-1 Valid Column Type Specifications

If the column has Oracle SQL type: | You can use defineColumnType() to define it as:
--- | ---
NUMBER, VARNUM | BIGINT, TINYINT, SMALLINT, INTEGER, FLOAT, REAL, DOUBLE, NUMERIC, DECIMAL, CHAR, VARCHAR
CHAR, VARCHAR2 | CHAR, VARCHAR
LONG | CHAR, VARCHAR, LONGVARCHAR
LONGRAW | LONGVARBINARY, VARBINARY, BINARY
RAW | VARBINARY, BINARY
DATE | DATE, TIME, TIMESTAMP, CHAR, VARCHAR
ROWID | ROWID

### DatabaseMetaData TABLE_REMARKS Reporting

The `getColumns()`, `getProcedureColumns()`, `getProcedures()`, and `getTables()` methods of the database metadata classes are slow if they must report `TABLE_REMARKS` columns, because this necessitates an expensive outer join. For this reason, the JDBC driver does not report `TABLE_REMARKS` columns by default.

You can enable `TABLE_REMARKS` reporting by passing a `true` argument to the `setRemarksReporting()` method of an `OracleConnection` object.

Equivalently, instead of calling `setRemarksReporting()`, you can set the `remarksReporting` Java property if you use a Java `Properties` object in establishing the connection. See "Specifying a Database URL and Properties Object".

If you are using a standard `java.sql.Connection` object, you must cast it to `OracleConnection` to use `setRemarksReporting()`.

#### Example: TABLE_REMARKS Reporting

Assuming `conn` is the name of your standard `Connection` object, the following statement enables `TABLE_REMARKS` reporting.

    ( (oracle.jdbc.driver.OracleConnection)conn ).setRemarksReporting(true);

#### Considerations for getProcedures() and getProcedureColumns() Methods

According to JDBC versions 1.1 and 1.2, the methods `getProcedures()` and `getProcedureColumns()` treat the `catalog`, `schemaPattern`, `columnNamePattern`, and `procedureNamePattern` 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, the `catalog` parameter is treated as the package name. This applies both on input (the `catalog` parameter) and output (the `catalog` column in the returned `ResultSet`). On input, the construct `"` `"` (the empty string) retrieves procedures and arguments without a package, that is, standalone objects. A `null` value means to drop from the selection criteria, that is, return information about both stand-alone and packaged objects (same as passing in "`%`"). Otherwise the `catalog` parameter should be a package name pattern (with SQL wild cards, if desired).

- `schemaPattern`: All objects within Oracle must have a schema, so it does not make sense to return information for those objects without one. Thus, the construct `"` `"` (the 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 the `catalog` parameter, `null` is interpreted to drop the schema from the selection criteria (same as passing in "`%`"). It can also be used as a pattern with SQL wild cards.

- `procedureNamePattern` and `columnNamePattern`: 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 "`%`".

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index