AutoCommitmust be set to
OFFwhen running this built-in procedure.
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.
This procedure has no related views.
ttOptSetOrder has the required parameter:
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
ttOptSetOrder returns no results.
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
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.
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
SQLPrepareor the JDBC method
Connection.prepareCallin 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.