Automatic Passthrough of Dynamic Load to the Oracle Database

Set the TT_DynamicPassthrough optimizer hint to notify TimesTen Classic to pass through qualified SELECT statements to the Oracle database for cache groups created without a WHERE clause.

When an application issues statements on a TimesTen connection, the statement can be executed in the TimesTen database or passed through to the Oracle database for resolution. If passed through to the Oracle database, the results are returned but the cache instance is not loaded. Whether the statement is executed on the TimesTen or Oracle databases depends on the composition of the statement and the setting of the PassThrough connection attribute.

In TimesTen Classic, for cache groups that are created without a WHERE clause, you can limit the number of rows that are dynamically loaded from the Oracle database into the cache instance. You can set the TT_DynamicPassthrough(N) optimizer hint, where N is the limit to the number of rows allowed to load into the cache instance. If any SELECT statement to the Oracle database would return a result with > N number of rows, then the statement is passed through to the Oracle database and the results are not loaded into the cache instance.

By default, the SELECT statement for a dynamic load of a cache group that qualifies for dynamic load is executed on the TimesTen Classic database and all rows of the cache instances are loaded. In addition, if you provide the optimizer hint and set N=0, then all rows are loaded into the cache instance on the TimesTen Classic database.

This optimizer hint is supported as connection and statement level hints.

Statement level hint:

/*+TT_DynamicPassThrough (1)*/

Connection level hint:

OptimizerHint = TT_DynamicPassThrough (1)

The following example is a statement level optimizer hint requesting a dynamic passthrough of a SELECT statement to the Oracle database if a dynamic load returns 1000 rows or more for the SELECT statement.

SELECT /*+ TT_DynamicPassThrough(1000)*/ ...

See Setting a Passthrough Level.

See Optimizer Hints in the Oracle TimesTen In-Memory Database SQL Reference and Use Optimizer Hints to Modify the Execution Plan in the Oracle TimesTen In-Memory Database Operations Guide.