Update the Table and Column Statistics Used By the TimesTen Query Optimizer

The query optimizer uses statistics stored in TimesTen database system tables to determine the optimal execution plan for a statement. Table-level statistics such as the number of rows in a table are stored in the SYS.TBL_STATS table. Column-level statistics such as the minimum and maximum value, and the number of unique values in a column are stored in the SYS.COL_STATS table.

Table and column statistics are not computed as updates occur on regular and cache tables. Instead statistics are updated when an explicit request is made.

You can update statistics for all tables owned by an user or for a specific table owned by an user.

To update statistics for all tables, right-click the Tables node and choose Statistics > Update.

To update statistics for a specific table, click the + to the left of the Tables node. Right-click the name of the table and choose Statistics > Update.

In the Prompts tab of the Update statistics dialog, choose Invalidate referenced commands to invalidate the execution plans of statements that reference the table on which statistics are updated. Invalidating a statement's execution plan causes that statement to be recompiled or reprepared upon its next execution.

For Interval type for table statistics, choose Complete interval to divide the rows of the table into two or more intervals and compute statistics on each interval, or Single interval to compute statistics on the entire set of rows as a single interval. You must define a range index on the table to compute complete interval statistics.

Click Apply.

To view the optimizer statistics of a table, click the name of the table.

In the Statistics tab within the table's tab, the top section shows the table statistics such as:

The bottom section shows the column statistics. For each column, the following is displayed:

For each interval of each column, the following is displayed:

For more information about query optimizer statistics, see "Statistics" in the Oracle In-Memory Database Cache Introduction, "When optimization occurs" in the Oracle TimesTen In-Memory Database Operations Guide or "ttOptUpdateStats" in the Oracle TimesTen In-Memory Database Reference.

Related Topics

SQL Developer: TimesTen Tasks

SQL Developer Concepts and Usage