Understanding Tables

A TimesTen table consists of rows that have a common format or structure. This format is described by the table's columns.

The following sections describes tables, its columns and how to manage them:

Overview of Tables

This section includes the following topics:

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, or the DELETE FIRST clause. For more information, see Avoid Large DELETE Statements.

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 for one or more columns, those default values for those columns are 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 or TTREP.

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.

Table Access

Applications access tables through SQL statements. The TimesTen query optimizer automatically chooses the optimal 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 must be declared NOT NULL. 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.

Note:

Indexes are discussed in Understanding Indexes.

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.

Note:

Columns of a primary key must be NOT NULL; however, a unique index can be built on columns declared as NOT NULL.

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.

Tables in TimesTen Scaleout

In TimesTen Scaleout, tables are the objects used to define how to distribute data in your database.

TimesTen Scaleout manages the distribution of data according to the defined distribution scheme of the table. When you create the table, it exists on every element of the database. Rows of data in the table exist on different elements of the database. See Working With Tables in the Oracle TimesTen In-Memory Database Scaleout User's Guide.

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 directly update system tables. TimesTen system tables are described in the System Tables and Views chapter in the Oracle TimesTen In-Memory Database System Tables and Views Reference.

Note:

TimesTen system table formats may change between releases of TimesTen.

Working With Tables

To perform any operation that creates, drops or manages a table, the user must have the appropriate privileges.

See SQL Statements chapter in the Oracle TimesTen In-Memory Database SQL Reference.

This section includes the following topics:

Creating a Table

To create a table, use the SQL statement CREATE TABLE.

The syntax for all SQL statements is provided in SQL Statements in the Oracle TimesTen In-Memory Database SQL Reference. TimesTen converts table names to upper case characters.

The following SQL statement example creates a table, called customer, with two columns: cust_id and cust_name of two different data types.

Command> CREATE TABLE customer (cust_id TT_INTEGER, cust_name VARCHAR2(50));

This example creates a table, called customer, with a hash index. The customer table has columns: cust_id, cust_name, addr, zip, and region. The cust_id column is designated as the primary key, so that the CustId value in a row uniquely identifies that row in the table, as described in Primary Keys, Foreign Keys and Unique Indexes.

The UNIQUE HASH ON custId PAGES value indicates that there are 30 pages in the hash index. This means that the expected number of rows in the table is 30 * 256 = 7680. If the table ends up with significantly more rows than this, performance can be degraded, and the hash index should be resized.

See SET PAGES in the ALTER TABLE section in the Oracle TimesTen In-Memory Database SQL Reference. See Size Hash Indexes Appropriately.

Command> CREATE TABLE customer
(cust_id NUMBER NOT NULL PRIMARY KEY, 
cust_name CHAR(100) NOT NULL, 
addr CHAR(100), 
zip NUMBER, 
region CHAR(10)) 
UNIQUE HASH ON (cust_id) PAGES = 30;

Dropping a Table

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

The following example drops the table customer.

Command> DROP TABLE customer;

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:

 Index PAT adds 6282 bytes
  Total index bytes = 6282

Total = 23806

You can also calculate the size of an existing table with the ttIsql tablesize command. See Using the ttIsql tablesize Command.

Implementing an Aging Policy in Your Tables

You can define an aging policy for one or more tables in your database.

An aging policy refers to the type of aging and the aging attributes, as well as the aging state (ON or OFF). You can specify one of the following types of aging policies: usage-based or time-based. Usage-based aging removes least recently used (LRU) data within a specified database usage range. Time-based aging removes data based on the specified data lifetime and frequency of the aging process. You can define only one type of aging for a specified table.

You can define an aging policy for a new table with the CREATE TABLE statement. You can add an aging policy to an existing table with the ALTER TABLE statement if the table does not already have an aging policy defined. You can change the aging policy by dropping aging and adding a new aging policy.

You cannot specify aging on the following types of tables:

Global temporary tables
Detail tables of materialized views

You can also implement aging in cache groups. See Implementing Aging in a Cache Group for TimesTen Classic in the Oracle TimesTen In-Memory Database Cache Guide.

Note:

There is no support to specify aging policies for tables within TimesTen Scaleout. See Comparison between TimesTen Scaleout and TimesTen Classic in the Oracle TimesTen In-Memory Database Scaleout User's Guide.

This section includes the following topics:

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.

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. See Scheduling When Aging Starts.

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 inserted.
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.

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.

Note:

When you drop LRU aging or add LRU aging to tables that are referenced in commands, TimesTen marks the compiled commands invalid. The commands need to be recompiled.

There are two LRU aging policies:

LRU aging based on set thresholds for the amount of permanent memory in use. This is the default. Once you create (or alter) a table to use LRU aging, the LRU aging policy initially uses the default thresholds for permanent memory in use. See Defining LRU Aging Based on Thresholds for Permanent Memory in Use.
LRU aging based on row thresholds for a set of tables. See Defining LRU Aging Based on Row Thresholds for Tables.

Both types of LRU aging can co-exist. Row threshold based aging takes precedence over permanent memory in use based aging. For each cycle after row based aging is processed, the current permanent space usage is used as the threshold for LRU aging of tables that use permanent memory in use threshold aging.

Defining LRU Aging Based on Thresholds for Permanent Memory in Use

You can define that LRU aging occurs based on set thresholds for the amount of permanent memory in use.

This method uses the PERM_IN_USE_SIZE metric to determine the thresholds for LRU aging across all tables. If the high threshold is reached, TimesTen deletes rows from all tables in order to reduce the permanent memory in use.

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:

LRU Aging Attribute	Description
`LowUsageThreshhold`	The percent of the database `PermSize` at which LRU aging is deactivated.
`HighUsageThreshhold`	The percent of the database `PermSize` at which LRU aging is activated.
`AgingCycle`	The number of minutes between aging cycles.

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.

Defining LRU Aging Based on Row Thresholds for Tables

After a table or cache group is enabled for LRU aging, you can use the ttAgingTableLRUConfig built-in procedure to configure that LRU aging occurs based on row thresholds for a set of tables.

In some situations, the permanent memory in use based aging may result in excessive purging of data from specific tables. You may find that using row based aging for such tables enables a better space management to be achieved. When you set LRU aging using the ttAgingTableLRUConfig built-in procedure, it sets thresholds based on rows for specified tables and on cache instances when applied to specified cache groups. You can limit the number of rows deleted from a specified table by setting row thresholds for the table.

Both types of LRU aging can co-exist. Table-based aging takes precedence over permanent memory in use based aging. Thus, a table configured with the attributes specified with the ttAgingTableLRUConfig built-in procedure is aged solely based on the specified row thresholds. For each cycle after row based aging is processed, the current permanent space usage is used as the threshold for LRU aging of tables that use permanent memory in use threshold aging.

The following table summarizes the LRU aging attributes:

If you have tables or cache group tables in a parent/child relationship, provide the parent table to ensure that cascade delete operations perform correctly.

LRU Aging Attribute	Description
`tblOwner`	TimesTen table or cache group owner.
`tblName`	Name of a table. Provide the cache root table for a cache group. LRU aging automatically deletes rows from child tables.
`LowRowsThreshhold`	The number of the rows at which LRU aging is deactivated. LRU aging stops when the number of rows reaches `LowRowsThreshold` rows in the table.
`HighRowsThreshhold`	The number of the rows at which LRU aging is activated. LRU aging starts when the number of rows reaches `HighRowsThreshold` rows in the table.
`AgingCycle`	The number of minutes between aging cycles. The defaults and behavior is the same as the `AgingCycle` in the `ttAgingLRUConfig` built-in procedure.

If both LowRowsThreshold and HighRowsThreshold are set to zero, table based LRU aging is disabled for this table. LRU aging on this table is switched to be based on the permanent memory in use threshold. See Defining LRU Aging Based on Thresholds for Permanent Memory in Use.

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

The following example sets the aging threshold for rows in the user1.table1 table to a low threshold of 10K rows and a high threshold of 100K rows. The aging cycle is set to run at the default of once every minute.

Command> Call ttAgingTableLRUConfig('user1', 'table1', 10000, 100000);
< USER1, TABLE1, 10000, 100000, 1 >
1 row found.

The following example sets the aging threshold for rows in the user1.table1 table to a low threshold of 5K rows and a high threshold of 12K rows. The aging cycle is set to run once every two minutes.

Command> Call ttAgingTableLRUConfig('user1', 'table1', 5000, 12000, 2);
< USER1, TABLE1, 10000, 100000, 0 >
1 row found.

The following example turns off aging by setting the low and high thresholds to zero.

Command> Call ttAgingTableLRUConfig('user1', 'table1', 0, 0);
< USER1, TABLE1, 0, 0, 1 >
1 row found.

You can retrieve the threshold settings by running the built-in procedure with just the schema owner and table name.

Command> Call ttAgingTableLRUConfig('user1', 'table1');
< USER1, TABLE1, 10000, 100000, 1 >
1 row found.

Time-Based Aging

Time-based aging removes data from a table based on the specified data lifetime and frequency of the aging process.

You can 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.

Note:

You cannot add or modify a column in an existing table and then use that column as a timestamp column because you cannot add or modify a column and define it to be NOT NULL.

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. 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.

There are two ways to define an LRU aging policy for your table:

Define the LRU aging policy at the database level based on the percentage of the permanent memory in use. See Defining LRU Aging Based on Thresholds for Permanent Memory in Use.
Define the LRU aging policy at the table level based on table rows or, in the case of cache groups, cache instances. See Defining LRU Aging Based on Row Thresholds for Tables.

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 multi-master 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.

Determining the Effectiveness of Aging

As aging deletes rows, TimesTen frees empty pages and reuses empty slots on non-full pages. The ttPageLevelTableInfo built-in procedure shows the page allocation for each table to determine when TimesTen is reusing empty slots and freeing empty pages or if new pages are allocated to store new rows.

The following demonstrates the output received for user1.table1 and user2.table2 using the ttPageLevelTableInfo built-in procedure:

Command> vertical 1; 
Command> call ttPageLevelTableInfo;

  TBLOWNER:                 USER1
  TBLNAME:                  TABLE1
  CARD:                     6
  LOGICAL_PGCNT:            1
  LOGICAL_NONFULLPAGES:     1
  LOGICAL_FREESLOTS:        250
  PHYSICAL_PGCNT:           2
  PHYSICAL_NONFULLPAGES0:   0
  PHYSICAL_NONFULLPAGES1:   0
  PHYSICAL_NONFULLPAGES2:   1
  PHYSICAL_NONFULLPAGES3:   1
  PHYSICAL_FREESLOTS0:      0
  PHYSICAL_FREESLOTS1:      0
  PHYSICAL_FREESLOTS2:      253
  PHYSICAL_FREESLOTS3:      253


  TBLOWNER:                 USER2
  TBLNAME:                  TABLE2
  CARD:                     3
  LOGICAL_PGCNT:            1
  LOGICAL_NONFULLPAGES:     1
  LOGICAL_FREESLOTS:        253
  PHYSICAL_PGCNT:           1
  PHYSICAL_NONFULLPAGES0:   0
  PHYSICAL_NONFULLPAGES1:   1
  PHYSICAL_NONFULLPAGES2:   0
  PHYSICAL_NONFULLPAGES3:   0
  PHYSICAL_FREESLOTS0:      0
  PHYSICAL_FREESLOTS1:      253
  PHYSICAL_FREESLOTS2:      0
  PHYSICAL_FREESLOTS3:      0

2 rows found.

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