ttOptSetOrder

Description

Specifies the order in which tables should be joined by the optimizer. The character string is a list of 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.

Required privilege

This procedure requires no privilege.

Syntax

ttOptSetOrder('joinOrder')

Parameters

ttOptSetOrder has the required parameter:

Result set

ttOptSetOrder returns no results.

Examples

CALL ttOptSetOrder ('EMPS DEPTS ACCTS');

Use the correlation name instead of the actual table name when specifying the join order.

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');

If an application makes the call:

ttOptSetOrder('DEPTS EMPS ACCTS');

the optimizer is prevented from executing 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

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, a warning is issued and the specified join order is ignored completely. Here are some examples:

See also

ttOptEstimateStats
ttOptGetFlag
ttOptGetOrder
ttOptSetColIntvlStats
ttOptSetFlag
ttOptSetTblStats
ttOptUpdateStats
ttPLSQLMemoryStats