Using Oracle Database Features

Oracle Hints

Oracle Hints is an Oracle Database feature through which a developer makes decisions usually reserved for the optimizer. Developers 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.

To add hints to a read query:

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):

// This line sets up the query
ReadObjectQuery query = new ReadObjectQuery(Employee.class);
query.setSelectionCritera(new ExpressionBuilder().get("id").equal(new Integer(1));
// This line adds 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.

Hierarchical Queries

Hierarchical Queries is an Oracle Database mechanism that lets you select database rows based on hierarchical order. For example, you can design a query that reads the row of a given employee, followed by the rows of people the employee manages, followed by their managed employees, and so on.

To create a hierarchical query, use the setHierarchicalQueryClause method. This method takes three parameters, as follows:

setHierarchicalQueryClause(StartWith, ConnectBy, OrderSibling)

This expression requires all three parameters, as described in the subsequent text.

ReadAllQuery raq = new ReadAllQuery(Employee.class);
// Specifies a START WITH expression
Expression startExpr = expressionBuilder.get("id").equal(new Integer(1));
// Specifies a CONNECT BY expression
Expression connectBy = expressionBuilder.get("managedEmployees");
// Specifies an ORDER SIBLINGS BY vector
Vector order = new Vector();
order.addElement(expressionBuilder.get("lastName"));
order.addElement(expressionBuilder.get("firstName"));
raq.setHierarchicalQueryClause(startExpr, connectBy, order);
Vector employees = uow.executeQuery(raq);

SELECT * FROM EMPLOYEE START WITH ID=1 CONNECT BY PRIOR ID=MANAGER_ID ORDER SIBLINGS BY LAST_NAME, FIRST_NAME

Stored Procedure Cursor Output Parameters

Oracle databases use output parameters rather than result sets to return data from stored procedures. Cursored output parameters let you retrieve the result set in a cursored stream rather than as a single result set. When you use the Oracle JDBC drivers, configure a StoredProcedureCall object to pass a cursor to TopLink as a standard result set.

StoredProcedureCall call = new StoredProcedureCall();
call.setProcedureName("READ_ALL_EMPLOYEES");
call.useNamedCursorOutputAsResultSet("RESULT_CURSOR");
ReadAllQuery query = new ReadAllQuery();
query.setReferenceClass(Employee.class);
query.setCall(call);
Vector employees = (Vector) Session.executequery(Query);