Skip Headers

# Oracle Hints

Previous

Next

Oracle Hints is an Oracle Database feature through which you can make decisions usually reserved for the optimizer. You use hints to specify things such as join order for a join statement, or the optimization approach of an SQL call.

The TopLink query framework supports Oracle Hints with the following API:

    setHintString("/*[hints or comments]*/");

TopLink adds the hint to the SQL string as a comment immediately following a `SELECT`, `UPDATE`, `INSERT`, or `DELETE` statement.

Add hints to a read query as follows:

1. Create a `ReadObjectQuery` or a `ReadAllQuery`

2. Set the selection criteria.

3. Add hints as needed.

For example, the following code uses the `FULL` hint (which explicitly chooses a full table scan for the specified table):

    // Create the query and set Employee as its reference class
    ReadObjectQuery query = new ReadObjectQuery(Employee.class);
    // Retrieve ExpressionBuilder from the query
    ExpressionBuilder builder = query.getExpressionBuilder();
    query.setSelectionCritera(builder.get("id").equal(new Integer(1));
    // Add the hint
    query.setHintString("/*+ FULL */" );

This code generates the following SQL:

    SELECT /*+ FULL */ FROM EMPLOYEE WHERE ID=1

To add hints to `WRITE`, `INSERT`, `UPDATE`, and `DELETE`, create custom queries for these operations in the TopLink query framework, then specify hints as required. For more information, see the following:

- "Configuring Custom SQL Queries for Basic Persistence Operations"

- "Configuring Custom EIS Interactions for Basic Persistence Operations"

For more information about the available hints, see the Oracle Database documentation.