ttIndexAdviceCaptureOutput

This built-in returns a list of index recommendations from the last recorded capture at the specified level. It also returns an executable CREATE INDEX SQL statement for creating the recommended index.

This procedure and the procedures related to it are referred to as the Index Advisor. For details on using these procedures, see Using the Index Advisor to Recommend Indexes in the Oracle TimesTen In-Memory Database Operations Guide.

For a connection level capture, run this procedure in the same connection that initiated the capture. For a database level capture, run this procedure in a connection with ADMIN privileges.

Required Privilege

This procedure requires no privilege to get output on a connection level capture.

This procedure requires ADMIN privileges to get output on a database level capture.

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.

Related Views

This procedure has these related views.

SYS.GV$INDEX_ADVICE_OUTPUT

SYS.V$INDEX_ADVICE_OUTPUT

Syntax

ttIndexAdviceCaptureOutput([captureLevel])

Parameters

ttIndexAdviceCaptureOutput has this optional parameter:

Parameter Type Description

Parameter	Type	Description
`captureLevel`	`TT_INTEGER`	Supported values for the capture level are: `0` - Outputs index advice at the connection level for the current connection. This is the default value. `1` - Outputs index advice at the database level.

captureLevel

TT_INTEGER

Supported values for the capture level are:

0 - Outputs index advice at the connection level for the current connection. This is the default value.

1 - Outputs index advice at the database level.

Result Set

ttIndexAdviceCaptureOutput returns the result set:

Column	Type	Description
`stmtCount`	`TT_INTEGER`	The number of statements in the captured workload that would have benefited from this index if it were present.
`createStmt`	`TT_VARCHAR (8300) NOT NULL`	The executable statement that can create the recommended index.

Examples

The following example provides the CREATE INDEX statement for an index called PURCHASE_i1 on the HR.PURCHASE table. There are four distinct statements that would benefit from the index in this SQL workload.

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

Note:

All names returned are fully schema qualified.