8 Working with Data in a TimesTen Database

Column overview

When you create the columns in the table, the column names are case-insensitive.

Each column has the following:

• A data type

• Optional nullability, primary key and foreign key properties

• An optional default value

Unless you explicitly declare a column NOT NULL, columns are nullable. If a column in a table is nullable, it can contain a NULL value. Otherwise, each row in the table must have a non-NULL value in that column.

The format of TimesTen columns cannot be altered. It is possible to add or remove columns but not to change column definitions. To add or remove columns, use the ALTER TABLE statement. To change column definitions, an application must first drop the table and then recreate it with the new definitions.

Inline and out-of-line columns

The in-memory layout of the rows of a table is designed to provide fast access to rows while minimizing wasted space. TimesTen designates each VARBINARY, NVARCHAR2 and VARCHAR2 column of a table as either inline or out-of-line.

• An inline column has a fixed length. All values of fixed-length columns of a table are stored row wise.

• A not inline column (also referred to as an out-of-line column) has a varying length. Some VARCHAR2, NVARCHAR2 or VARBINARY data type columns are stored out-of-line. Out-of-line columns are not stored contiguously with the row but are allocated. By default, TimesTen stores VARCHAR2, NVARCHAR2 and VARBINARY columns whose declared column length is > 128 bytes as out-of-line. In addition, all LOB data types are stored out-of-line. By default, TimesTen stores variable-length columns whose declared column length is <= 128 bytes as inline.

Most operations are slightly slower when performed on an out-of-line column instead of an inline column. There are several performance considerations when you use out-of-line columns instead of inline columns:

• Accessing data is slower because TimesTen does not store data from out-of-line columns contiguously with the row.

• Populating data is slower because TimesTen generates more logging operations.

• Deleting data is slower because TimesTen performs more reclaim and logging operations. If you are deleting a large number of rows (100,000 or more) consider using multiple smaller DELETE FROM statements, a TRUNCATE TABLE statement, or the DELETE FIRST clause. For more information, see "Avoid large DELETE statements".

• Storing a column requires less overhead.

The maximum sizes of inline and out-of-line portions of a row are listed in "Using the ttIsql tablesize command".

Default column values

When you create a table, you can specify default values for the columns. The default value you specify must be compatible with the data type of the column. You can specify one of the following default values for a column:

• NULL for any column type

• A constant value

• SYSDATE for DATE and TIMESTAMP columns

• USER for CHAR columns

• CURRENT_USER for CHAR columns

• SYSTEM_USER for CHAR columns

If you use the DEFAULT clause of the CREATE TABLE statement but do not specify the default value, the default value is NULL. See "CREATE TABLE" in the Oracle TimesTen In-Memory Database SQL Reference.

Table names

A TimesTen table is identified uniquely by its owner name and table name. Every table has an owner. By default, TimesTen defines the owner as the user who created the table. Tables created by TimesTen, such as system tables, have the owner name SYS.

To uniquely refer to a table, specify both its owner and name separated by a period ("."), such as MARY.PAYROLL. If an application does not specify an owner, TimesTen looks for the table under the user name of the caller, then under the user name SYS.

A name is an alphanumeric value that begins with a letter. A name can include underscores. The maximum length of a table name is 30 characters. The maximum length of an owner name is also 30 characters. TimesTen displays all table, column and owner names to upper case characters. See "Names, Namespace and Parameters" in the Oracle TimesTen In-Memory Database SQL Reference for additional information.

Table access

Applications access tables through SQL statements. The TimesTen query optimizer automatically chooses a fast way to access tables. It uses existing indexes or, if necessary, creates temporary indexes to speed up access. For improved performance, applications should explicitly create indexes for frequently searched columns because the automatic creation and destruction of temporary indexes incurs a performance overhead. For more details, see "Tune statements and use indexes". You can use optimizer hints (statement or transaction level) to tune the TimesTen execution plan for a specific application. For more information on optimizer hints, see "Use optimizer hints to modify the execution plan".

Primary keys, foreign keys and unique indexes

You can create a primary key on one or more columns to indicate that duplicate values for that set of columns should be rejected. Primary key columns cannot be nullable. A table can have at most one primary key. TimesTen automatically creates a range index on the primary key to enforce uniqueness on the primary key and to improve access speeds through the primary key. Once a row is inserted, its primary key columns cannot be modified, except to change a range index to a hash index.

Although a table may have only one primary key, additional uniqueness properties may be added to the table using unique indexes. See "CREATE INDEX" in the Oracle TimesTen In-Memory Database SQL Reference for more information.

A table may also have one or more foreign keys through which rows correspond to rows in another table. Foreign keys relate to a primary key or uniquely indexed columns in the other table. Foreign keys use a range index on the referencing columns. See "CREATE TABLE" in the Oracle TimesTen In-Memory Database SQL Reference for more information.

System tables

In addition to tables created by applications, a TimesTen database contains system tables. System tables contain TimesTen metadata such as descriptions of all tables and indexes in the database, as well as other information such as optimizer plans. Applications may query system tables just as they query user tables. Applications may not update system tables. TimesTen system tables are described in the "System Tables" chapter in the Oracle TimesTen In-Memory Database System Tables and Views Reference.

Creating a table

To create a table, use the SQL statement CREATE TABLE. The syntax for all SQL statements is provided in the Oracle TimesTen In-Memory Database SQL Reference. TimesTen converts table names to upper case characters.

CREATE TABLE NameID (CustId TT_INTEGER, CustName VARCHAR2(50));

CREATE TABLE Customer
(custId NUMBER NOT NULL PRIMARY KEY,
custName CHAR(100) NOT NULL,
Addr CHAR(100),
Zip NUMBER,
Region CHAR(10))
UNIQUE HASH ON (custId) PAGES = 30;

Dropping a table

To drop a TimesTen table, call the SQL statement DROP TABLE.

DROP TABLE NameID;

Estimating table size

Increasing the size of a TimesTen database can be done on first connect. To avoid having to increase the size of a database, it is important not to underestimate the eventual database size. Use the ttSize utility to estimate table size.

The following example shows that the ttSize utility estimates the rows, inline row bytes, size of any indexes on the table, and the total size of the table:

ttSize -tbl Pat.tab1 MyDb

Rows = 2

Total in-line row bytes = 17524
Indexes:

 Bitmap index PAT.BITMAP_ID adds 6282 bytes
  Total index bytes = 6282

Total = 23806

You can also calculate the size of an existing table with the ttIsql tablesize command. For more information, see "Using the ttIsql tablesize command".

Usage-based aging

Usage-based aging enables you to maintain the amount of memory used in a database within a specified threshold by removing the least recently used (LRU) data.

Define LRU aging for a new table by using the AGING LRU clause of the CREATE TABLE statement. Aging begins automatically if the aging state is ON.

Call the ttAgingLRUConfig built-in procedure to specify the LRU aging attributes. The attribute values apply to all tables in the database that have an LRU aging policy. If you do not call the ttAgingLRUConfig built-in procedure, then the default values for the attributes are used.

The following table summarizes the LRU aging attributes:

If you set a new value for AgingCycle after an LRU aging policy has already been defined, aging occurs based on the current time and the new cycle time. For example, if the original aging cycle is 15 minutes and LRU aging occurred 10 minutes ago, aging is expected to occur again in 5 minutes. However, if you change the AgingCycle parameter to 30 minutes, then aging occurs 30 minutes from the time you call the ttAgingLRUConfig procedure with the new value for AgingCycle.

If a row has been accessed or referenced since the last aging cycle, it is not eligible for LRU aging. A row is considered to be accessed or referenced if one of the following is true:

• The row is used to build the result set of a SELECT statement.

• The row has been flagged to be updated or deleted.

• The row is used to build the result set of an INSERT SELECT statement.

You can use the ALTER TABLE statement to perform the following tasks:

• Enable or disable the aging state on a table that has an aging policy defined by using the ALTER TABLE statement with the SET AGING {ON|OFF} clause.

• Add an LRU aging policy to an existing table by using the ALTER TABLE statement with the ADD AGING LRU [ON|OFF] clause.

• Drop aging on a table by using the ALTER TABLE statement with the DROP AGING clause.

Call the ttAgingScheduleNow built-in procedure to schedule when aging starts. For more information, see "Scheduling when aging starts".

To change aging from LRU to time-based on a table, first drop aging on the table by using the ALTER TABLE statement with the DROP AGING clause. Then add time-based aging by using the ALTER TABLE statement with the ADD AGING USE clause.

Time-based aging

Time-based aging removes data from a table based on the specified data lifetime and frequency of the aging process. Specify a time-based aging policy for a new table with the AGING USE clause of the CREATE TABLE statement. Add a time-based aging policy to an existing table with the ADD AGING USE clause of the ALTER TABLE statement.

The AGING USE clause has a ColumnName argument. ColumnName is the name of the column that is used for time-based aging, also called the timestamp column. The timestamp column must be defined as follows:

• ORA_TIMESTAMP, TT_TIMESTAMP, ORA_DATE or TT_DATE data type

• NOT NULL

Your application updates the values of the timestamp column. If the value of this column is unknown for some rows and you do not want the rows to be aged, then define the column with a large default value. You can create an index on the timestamp column for better performance of the aging process.

You cannot drop the timestamp column from a table that has a time-based aging policy.

If the data type of the timestamp column is ORA_TIMESTAMP, TT_TIMESTAMP, or ORA_DATE, you can specify the lifetime in days, hours, or minutes in the LIFETIME clause of the CREATE TABLE statement. If the data type of the timestamp column is TT_DATE, specify the lifetime in days.

The value in the timestamp column is subtracted from SYSDATE. The result is truncated the result using the specified unit (minute, hour, day) and compared with the specified LIFETIME value. If the result is greater than the LIFETIME value, then the row is a candidate for aging.

Use the CYCLE clause to indicate how often the system should examine the rows to remove data that has exceeded the specified lifetime. If you do not specify CYCLE, aging occurs every five minutes. If you specify 0 for the cycle, then aging is continuous. Aging begins automatically if the state is ON.

Use the ALTER TABLE statement to perform the following tasks:

• Enable or disable the aging state on a table with a time-based aging policy by using the SET AGING {ON|OFF} clause.

• Change the aging cycle on a table with a time-based aging policy by using the SET AGING CYCLE clause.

• Change the lifetime by using the SET AGING LIFETIME clause.

• Add time-based aging to an existing table with no aging policy by using the ADD AGING USE clause.

• Drop aging on a table by using the DROP AGING clause.

Call the ttAgingScheduleNow built-in procedure to schedule when aging starts. For more information, see "Scheduling when aging starts".

To change the aging policy from time-based aging to LRU aging on a table, first drop time-based aging on the table. Then add LRU aging by using the ALTER TABLE statement with the ADD AGING LRU clause.

Aging and foreign keys

Tables that are related by foreign keys must have the same aging policy.

• If LRU aging is in effect and a row in a child table is recently accessed, then neither the parent row nor the child row is deleted.

• If time-based aging is in effect and a row in a parent table is a candidate for aging out, then the parent row and all of its children are deleted.

• If a table has ON DELETE CASCADE enabled, the setting is ignored.

Scheduling when aging starts

Call the ttAgingScheduleNow built-in procedure to schedule the aging process. The aging process starts as soon as you call the procedure unless there is already an aging process in progress, in which case it begins when that aging process has completed.

When you call ttAgingScheduleNow, the aging process starts regardless of whether the state is ON or OFF.

The aging process starts only once as a result of calling ttAgingScheduleNow does not change the aging state. If the aging state is OFF when you call ttAgingScheduleNow, then the aging process starts, but it does not continue after the process is complete. To continue aging, you must call ttAgingScheduleNow again or change the aging state to ON.

If the aging state is already set to ON, then ttAgingScheduleNow resets the aging cycle based on the time ttAgingScheduleNow was called.

You can control aging externally by disabling aging by using the ALTER TABLE statement with the SET AGING OFF clause. Then use ttAgingScheduleNow to start aging at the desired time.

Use ttAgingScheduleNow to start or reset aging for an individual table by specifying its name when you call the procedure. If you do not specify a table name, then ttAgingScheduleNow starts or resets aging on all of the tables in the database that have aging defined.

Aging and replication

For active standby pairs, implement aging on the active master database. Deletes that occur as a result of aging are replicated to the standby master database and the read-only subscribers. If a failover to the standby master database occurs, aging is enabled on the database after its role changes to ACTIVE.

For all other types of replication schemes, implement aging separately on each node. The aging policy must be the same on all nodes.

If you implement LRU aging on a multimaster replication scheme used as a hot standby, LRU aging may provide unintended results. After a failover, you may not have all of the desired data because aging occurs locally.

The SELECT query in the CREATE VIEW statement

The SELECT query used to define the contents of a materialized view is similar to the top-level SQL SELECT statement described in "SQL Statements" in the Oracle TimesTen In-Memory Database SQL Reference, with the following restrictions:

• A SELECT * query in a view definition is expanded at view creation time. Any columns added after a view is created do not affect the view.

• The following cannot be used in a SELECT statement that is creating a view:

• DISTINCT

• FIRST

• ORDER BY

• Arguments

• Temporary tables

• Each expression in the select list must have a unique name. A name of a simple column expression would be that column's name unless a column alias is defined. RowId is considered an expression and needs an alias.

• No SELECT FOR UPDATE or SELECT FOR INSERT statements can be used on a view.

• Certain TimesTen query restrictions are not checked when a non-materialized view is created. Views that violate those restrictions may be allowed to be created, but an error is returned when the view is referenced later in an executed statement.

Synchronous materialized view

The synchronous materialized view, by default, updates the result set data from the detail tables at the time of the detail table transaction. Every time data is updated in the detail tables, the result set is updated. Thus, the synchronous materialized view is never out of sync with the detail tables. However, this can affect your performance. A single transaction, the user transaction, executes the updates for both the detail table and any synchronous materialized views.

Asynchronous materialized view

The materialized view is populated and it is in sync with the detail tables at creation. When the detail tables are updated, the asynchronous materialized views are not updated immediately. At any moment, they can be out of sync with the corresponding detail tables. The asynchronous materialized view defers updates to the result set as a trade-off for performance. You decide when and how the result set is refreshed either manually by the user or automatically within a pre-configured interval. The asynchronous materialized view is always refreshed in its own transaction, not within the user transaction that updates the detail tables. Thus, the user transaction is not blocked by any updates for the asynchronous materialized view.

The asynchronous refresh may use either of the following refresh method configurations:

• FAST, which updates only the incremental changes since the last update.

• COMPLETE, which provides a full refresh.

To facilitate a FAST refresh, you must create a materialized view log to manage the deferred incremental transactions for each detail table used by the asynchronous materialized view. Each detail table requires only one materialized view log for managing all deferred transactions, even if it is included in more than one FAST asynchronous materialized view.

The detail table cannot be dropped if there is an associated materialized view or materialized view log.

When to use synchronous or asynchronous materialized views

The following sections provide guidelines on when to use synchronous or asynchronous materialized views:

• Joins and aggregate functions turn into super locks

• Freshness of the materialized view

• Overhead cost

Creating a materialized view

To create a materialized view, use the SQL statement CREATE MATERIALIZED VIEW.

When creating a materialized view, you can establish primary keys and the size of the hash table in the same manner as described for tables in "Primary keys, foreign keys and unique indexes".

The materialized view examples are based on the following two tables:

CREATE TABLE customer(custId int not null,
  custName char(100) not null,
  Addr char(100),
  Zip int,
  Region char(10),
  PRIMARY KEY (custId));

CREATE TABLE bookOrder(orderId int not null,
  custId int not null,
  book char(100),
  PRIMARY KEY (orderId),
  FOREIGN KEY (custId) REFERENCES Customer(custId));

The following sections provide details and examples for creating materialized views:

• Creating a synchronous materialized view

• Creating an asynchronous materialized view

• The SELECT query in the CREATE MATERIALIZED VIEW statement

CREATE MATERIALIZED VIEW SampleMV AS
 SELECT customer.custId, custName, orderId, book
 FROM customer, bookOrder
 WHERE customer.custId=bookOrder.custId;

CREATE MATERIALIZED VIEW LOG ON customer WITH PRIMARY KEY (custName);

CREATE MATERIALIZED VIEW LOG ON bookOrder WITH (custId, book);

CREATE MATERIALIZED VIEW SampleAMV 
 REFRESH 
     FAST 
     NEXT SYSDATE + NUMTODSINTERVAL(1, 'HOUR')
 AS SELECT customer.custId, custName, orderId, book
 FROM customer, bookOrder
 WHERE customer.custId=bookOrder.custId;

REFRESH MATERIALIZED VIEW SampleAMV;

Dropping a materialized view or a materialized view log

To drop any materialized view, execute the DROP VIEW statement.

The following statement drops the sampleMV materialized view.

DROP VIEW sampleMV;

When there are no asynchronous materialized views referencing a table, the materialized view log on that table can be dropped. For example, if you have dropped the materialized view sampleAMV, then the following statements drop the associated materialized view logs.

DROP MATERIALIZED VIEW LOG ON customer;
DROP MATERIALIZED VIEW LOG ON bookOrder;

The syntax for all SQL statements is provided in the "SQL Statements" chapter in the Oracle TimesTen In-Memory Database SQL Reference.

select tblname from sys.tables where tblid = 507244;
< T1 >
1 row found.

Restrictions on materialized views and detail tables

A materialized view is a read-only table that cannot be updated directly. This means a materialized view cannot be updated by an INSERT, DELETE, or UPDATE statement by replication, XLA, or the cache agent.

For example, any attempt to update a row in a materialized view generates the following error:

805: Update view table directly has not been implemented

Readers familiar with other implementations of materialized views should note the following characteristics of TimesTen views:

• Detail tables can be replicated, but materialized views cannot.

• Neither a materialized view nor its detail tables can be part of a cache group.

• No referential indexes can be defined on the materialized view.

• To drop a materialized view must use the DROP VIEW statement.

• You cannot alter a materialized view. You must use the DROP VIEW statement and then create a new materialized view with a CREATE MATERIALIZED VIEW statement.

• Materialized views must be explicitly created by the application. The TimesTen query optimizer has no facility to automatically create materialized views.

• The TimesTen query optimizer does not rewrite queries on the detail tables to reference materialized views. Application queries must directly reference views, if they are to be used.

• There are some restrictions to the SQL used to create materialized views. See "CREATE MATERIALIZED VIEW" in the Oracle TimesTen In-Memory Database SQL Reference for details.

Performance implications of materialized views

The following sections describes performance implications for each type of materialized view:

• Managing performance for asynchronous materialized views

• Managing performance for synchronous materialized views

Prepare for executing the Index Advisor

Before you execute the Index Advisor, you should optionally perform the following:

1. Since the Index Advisor relies on the query plan, set any relevant optimizer hints that you would use for the SQL workload before enabling the Index Advisor and running the workload. For more details on optimizer hints, see "Use optimizer hints to modify the execution plan".

2. Update statistics for tables included in the SQL workload and force statements to be re-prepared during the capture. This provides the most up-to-date statistics for the data collection and causes the statements to be re-prepared based on the latest statistics.

Update statistics for tables included in the SQL workload with one of the following built-in procedures: ttOptUpdateStats, ttOptEstimateStats, or ttOptSetTblStats. In the built-in procedures, set the invalidate parameter to 1 to invalidate all commands that reference the indicated tables and force these commands to be automatically prepared again when re-executed. This ensures that statistics are up to date.

• The ttOptUpdateStats built-in procedure provides a full update of all statistics for the tables. However, it can be time consuming.

• The ttOptEstimateStats evaluates statistics based upon a small percentage of rows in the indicated tables.

• The ttOptSetTblStats sets the statistics to known values provided by you.

The following example estimates statistics for all tables for the current user by evaluating a random sample of ten percent of the rows in these tables. It also invalidates all commands already prepared that reference these tables.

CALL ttOptEstimateStats ( '', 1, '10 PERCENT' );

Capture the data used for generating index recommendations

Call the ttIndexAdviceCaptureStart and ttIndexAdviceCaptureEnd built-in procedures to capture the information needed by the Index Advisor to generate index recommendations, as follows:

1. Call the ttIndexAdviceCaptureStart built-in procedure to start the process to collect index information.

2. Run the SQL workload.

3. Call the ttIndexAdviceCaptureEnd built-in procedure to end the index information collection process.

When you call the ttIndexAdviceCaptureStart built-in procedure to initiate the data collection process, provide the following:

• In the captureLevel parameter, specify whether the index information is to be collected for the current connection or for the entire database. You can execute multiple connection-level captures concurrently for independent connections without conflict. A database-level capture can take place in parallel with a connection-level capture. Since there is no conflict between a database-level and a connection-level capture, any outstanding connection-level captures that are already in progress when a database-level capture is initiated completes as intended. However, an error is returned if you initiate a second request for a database-level capture while the first is still active; an error is also returned if a second request for a connection-level capture from the same connection is initiated while the first connection-level capture is still active.

  If you invoke ttIndexAdviceCaptureStart for a database-level capture, any outstanding connection-level captures that are already in progress complete.

• The captureMode parameter designates that you want the data collection performed on one of the following scenarios:

  • Perform the collection of index information using the current execution of the SQL workload.

  • Base the collection of index information not on a current execution of the SQL workload, but on existing computed statistics and query plan analysis. In this scenario, the SQL statements have been prepared, but not executed. This mode can only be executed with a connection-level capture.

To complete the capture, call the ttIndexAdviceCaptureEnd built-in procedure that ends either an active connection-level capture from the same connection or an active database-level capture. Completing a database-level capture requires the ADMIN privilege.

If a connection fails during a capture, the following occurs:

• If the capture is a connection-level capture, the capture ends and all associated resources are freed.

• If the capture is a database-level capture, the capture continues until another user with ADMIN privileges connects and invokes the ttIndexAdviceCaptureEnd built-in procedure to end a database-level capture.

If temporary space becomes full during a capture, an active capture ends and the data collected during the capture is saved.

The following example starts a collection for the Index Advisor at the connection-level for the current execution of a SQL workload:

Call ttIndexAdviceCaptureStart(0,0);

The following example ends the collection for the connection-level capture:

Call ttIndexAdviceCaptureEnd(0)

Retrieve index recommendations and data collection information

1. Call the ttIndexAdviceCaptureInfoGet built-in procedure to retrieve data collection overview information for the Index Advisor.

2. Call the ttIndexAdviceCaptureOutput built-in procedure to retrieve the recommended indexes.

   Note:

   These built-in procedures retrieve the data collection overview and Index Advisor recommendations. Execute either or both for the data you want.

3. After a DBA has evaluated the recommended index creation statements, apply the desired index creation recommendations.

Command> CALL ttIndexAdviceCaptureInfoGet();
< 0, 1, 0, 0, 363, 369, 2012-07-27 11:44:08.136833, 2012-07-27 12:07:35.410993 >
1 row found.

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

Drop data collected for the index advisor and finalize results

After you have applied the CREATE statements for the new indexes that have been approved by the DBA, you can drop the captured data collected for the Index Advisor. The ttIndexAdviceCaptureDrop built-in procedure drops the existing data collected for the specified captureLevel, which can either be a connection or database-level capture.

Call ttIndexAdviceCaptureDrop(0);

You must call this built-in procedure twice to drop both a connection-level and database-level capture. You may not invoke this built-in procedure while a capture at the same level is in progress.

You can repeat the steps in "Prepare for executing the Index Advisor" and "Retrieve index recommendations and data collection information" until a SQL workload is executed with no more index recommendations. You can keep updating the statistics for the tables on which the new indexes were applied and re-execute the Index Advisor to see if any new indexes are now recommended.

Example using Index Advisor built-in procedures

The following shows the flow of a data collection for a SQL workload and the resulting index advice provided by the Index Advisor built-in procedures.

Command> CALL ttOptUpdateStats();

Command> CALL ttIndexAdviceCaptureStart();

Command> SELECT employee_id, first_name, last_name FROM employees;
< 100, Steven, King >
< 101, Neena, Kochhar >
< 102, Lex, De Haan >
< 103, Alexander, Hunold >
< 104, Bruce, Ernst >
...
< 204, Hermann, Baer >
< 205, Shelley, Higgins >
< 206, William, Gietz >
107 rows found.

Command> SELECT MAX(salary) AS MAX_SALARY 
FROM employees 
WHERE employees.hire_date > '2000-01-01 00:00:00';
< 10500 >
1 row found.

Command> SELECT employee_id, job_id FROM job_history
       > WHERE (employee_id, job_id) NOT IN (SELECT employee_id, job_id
       > FROM employees);
< 101, AC_ACCOUNT >
< 101, AC_MGR >
< 102, IT_PROG >
< 114, ST_CLERK >
< 122, ST_CLERK >
< 176, SA_MAN >
< 200, AC_ACCOUNT >
< 201, MK_REP >
8 rows found.

Command> WITH dept_costs AS (
       > SELECT department_name, SUM(salary) dept_total
       > FROM employees e, departments d
       > WHERE e.department_id = d.department_id
       > GROUP BY department_name),
       > avg_cost AS (
       > SELECT SUM(dept_total)/COUNT(*) avg
       > FROM dept_costs)
       > SELECT * FROM dept_costs
       > WHERE dept_total >
       > (SELECT avg FROM avg_cost)
       > ORDER BY department_name;
< Sales, 304500 >
< Shipping, 156400 >
2 rows found.

Command> call ttIndexAdviceCaptureEnd();

Command> call ttIndexAdviceCaptureInfoGet();
< 0, 1, 0, 0, 9, 6, 2012-07-27 11:44:08.136833, 2012-07-27 12:07:35.410993 >
1 row found.

Command> call ttIndexAdviceCaptureOutput();
< 1, create index EMPLOYEES_i1 on HR.EMPLOYEES(SALARY); >
< 1, create index EMPLOYEES_i2 on HR.EMPLOYEES(HIRE_DATE); >
2 rows found.

Command> call ttIndexAdviceCaptureDrop();