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.

Related Views

This procedure has this related view.

SYS.V$INDEX_ADVICE_OUTPUT

Syntax

ttIndexAdviceCaptureOutput([captureLevel])

Parameters

ttIndexAdviceCaptureOutput has this optional parameter:

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.

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.

See Also