Retrieve Index Recommendations with ttIndexAdviceCaptureOutput

The ttIndexAdviceCaptureOutput built-in procedure retrieves the list of index recommendations from the last recorded capture at the specified level (connection or database-level).

The list contains the CREATE statement for each recommended index.

To request index recommendations for a connection-level capture, run ttIndexAdviceCaptureOutput with captureLevel set to 0 in the same connection that initiated the capture. For a database-level capture, run ttIndexAdviceCaptureOutput with captureLevel set to 1 in a connection where the user has ADMIN privilege.

The returned row contains:

• stmtCount - The number of times the index would be useful to speed up the SQL workload.

• createStmt - The statement that can be used to create the recommended index. All database object names in these statements are fully qualified.

The following example provides the CREATE statement for an index called PURCHASE_i1 on the HR.PURCHASE table, which would be useful 4 times for this SQL workload.

CALL ttIndexAdviceCaptureOutput();
< 4, create index PURCHASE_i1 on HR.PURCHASE(AMOUNT); >
1 row found. 

Note:

See ttIndexAdviceCaptureOutput in the Oracle TimesTen In-Memory Database Reference.