ttOptSetOrder

This procedure specifies the order in which tables should be joined by the optimizer. The character string is a list of table names or table correlation names referenced in the query or a subquery, separated by spaces (not commas). The table listed first is scanned first by the plan. (It is outermost in a nested loop join, for example.) A correlation name is a shortcut or alias for a qualified table name. AutoCommit must be set to OFF when running this built-in procedure.

Required Privilege

This procedure requires no privilege.

Usage in TimesTen Scaleout and TimesTen Classic

This procedure is supported in TimesTen Classic.

TimesTen Scaleout applications can call this built-in procedure.

In TimesTen Scaleout, this procedure runs locally on the element from which it is called.

Related Views

This procedure has no related views.

Syntax

ttOptSetOrder('joinOrder')

Parameters

ttOptSetOrder has the required parameter:

Parameter	Type	Description
`joinOrder`	`TT_VARCHAR(1024)`	List of space-separated table or table correlation names. If an owner is required to distinguish the table name, use a table correlation name. If the `joinOrder` is not specified the query optimizer reverts to its default behavior.

Result Set

ttOptSetOrder returns no results.

Examples

CALL ttOptSetOrder ('EMPS DEPTS ACCTS');

If an application makes the call:

CALL ttOptSetOrder('ORDERS CUSTOMERS');

The optimizer scans the ORDERS table before scanning the CUSTOMERS when evaluating the following query that lists all the customers who have at least one unshipped order:

SELECT CUSTOMERS.NAME
FROM CUSTOMERS
WHERE EXISTS (SELECT 1
        FROM  ORDERS
        WHERE CUSTOMERS.ID = ORDERS.CUSTID
        AND  ORDER.STATUS ='UN-SHIPPED');

Consider an application that makes the following call.

ttOptSetOrder('DEPTS EMPS ACCTS');

The optimizer is prevented from performing a join between DEPTS and ACCTS when evaluating the number of employees working on a specific account:

SELECT COUNT(DISTINCT EMPS.ID)
FROM  ACCTS, DEPTS, EMPS
WHERE ACCTS.DEPTS = DEPTS.ID
AND  EMPS.DEPTS = DEPTS.ID
AND  ACCTS.NUM = :AcctNum

If the application does not reset the join order and tries to prepare a command that does not reference each of the three tables (and no others), the optimizer issues warning number 965. The specified join order is not applicable. TimesTen considers valid join orders and ignores the specified join order when preparing the command.

Notes

A table alias name for a derived table is not supported in the join order. If you specify a table alias name, TimesTen returns the warning message 965 that indicates the order cannot be honored.
The string length is limited to 1,024 bytes. If a string exceeds this length, it is truncated and a warning is issued.
When correlation names referenced in subqueries are in included in the order, TimesTen may internally change the isolation mode.
When a command is prepared, the current optimizer flags, index hints, and join order are maintained in the structure of the compiled form of the command and are used if the command is ever reprepared by the system. See The TimesTen Query Optimizer in Oracle TimesTen In-Memory Database Operations Guide for an example of reprepared statements.
The changes made by this call take effect immediately and affect all subsequent calls to the ODBC function SQLPrepare or the JDBC method Connection.prepareCall in the current transaction. The query optimizer reverts to its default behavior for subsequent transactions.
The tables referenced by a query must exactly match the names given if the join order is to be used (the comparisons are not case sensitive). A complete ordering must be specified; there is no mechanism for specifying partial orders. If the query has a subquery then the join order should also reference the correlation names in the subquery. In essence, the join order should reference all the correlation names referenced in the query. The TimesTen optimizer internally implements a subquery as a special kind of join query with a GROUP BY. For the join order to be applicable it should reference all the correlation names. If there is a discrepancy, Times issues a warning and ignores the specified join order completely.
You can also set the join order using statement level optimizer hints in certain SQL statements. For details, see Statement Level Optimizer Hints in the Oracle TimesTen In-Memory Database SQL Reference. Specifically, see the section Summary of Statement, Transaction, and Connection Level Optimizer Hints to understand the behavior of each style of hint.