CREATE TABLE

The CREATE TABLE statement defines a table.

The CREATE TABLE statement is supported in TimesTen Scaleout and in TimesTen Classic. However, there are differences in syntax and semantics. For simplicity, the supported syntax, parameters, description (semantics), and examples for TimesTen Scaleout and for TimesTen Classic are separated into the usage with TimesTen Scaleout and the usage with TimesTen Classic. While there is repetition in the usages, it is presented this way in order to allow you to progress from syntax to parameters to semantics to examples for each usage.

Review the required privilege section and then see:

• CREATE TABLE: Usage with TimesTen Scaleout

• CREATE TABLE: Usage with TimesTen Classic

Required Privilege

CREATE TABLE (if owner) or CREATE ANY TABLE (if not owner).

The owner of the created table must have the REFERENCES privilege on tables referenced by the REFERENCE clause.

In TimesTen Classic:

• ADMIN privilege is required if replicating a new table across an active standby pair when DDL_REPLICATION_LEVEL=2 or greater and DDL_REPLICATION_ACTION=INCLUDE.

• These attributes cause the CREATE TABLE to implicitly execute an ALTER ACTIVE STANDBY PAIR... INCLUDE TABLE statement. See ALTER SESSION for more details.

After reviewing this section, see:

• CREATE TABLE: Usage with TimesTen Scaleout

• CREATE TABLE: Usage with TimesTen Classic

CREATE TABLE: Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout. Column-based compression and aging are not supported. The using index clause and the distribution clause is not supported for global temporary tables.

See:

• SQL Syntax for CREATE TABLE: TimesTen Scaleout

• Parameters for CREATE TABLE: TimesTen Scaleout

• Column Definition: TimesTen Scaleout

• Description for USING INDEX Clauses in CREATE TABLE: TimesTen Scaleout

• Additional Information for CREATE TABLE: TimesTen Scaleout

• Examples: Global and Local Indexes in TimesTen Scaleout

• Additional Examples: TimesTen Scaleout

CREATE TABLE: Usage with TimesTen Classic

See:

• SQL Syntax for CREATE TABLE: TimesTen Classic

• Parameters for CREATE TABLE: TimesTen Classic

• Column Definition: TimesTen Classic

• Description for CREATE TABLE: TimesTen Classic

• Examples: TimesTen Classic

SQL Syntax for CREATE TABLE: TimesTen Scaleout

The syntax for a persistent table:

CREATE TABLE [Owner.]TableName
(
    ColumnDefinition [,...]
     [PRIMARY KEY (ColumnName [,...]) [UsingIndexClause1]]|
     [[CONSTRAINT ForeignKeyName]
        FOREIGN KEY ([ColumnName] [,...])
        REFERENCES RefTableName
            [(ColumnName [,...])] [ON DELETE CASCADE]] [...]
    
)
[UNIQUE HASH ON (HashColumnName [,...])
    PAGES = PrimaryPages]
[DistributionClause]
[AS SelectQuery]

The syntax for the UsingIndexClause1 is shown below. Note: The CreateIndexStmt is the TimesTen CREATE INDEX statement. See CREATE INDEX for details. You must create a unique index as this is a requirement for a primary key.

UsingIndexClause1::= USING INDEX {GLOBAL | LOCAL}| USING INDEX (CreateIndexStmt)

The syntax for the DistributionClause:

DistributionClause::= DISTRIBUTE BY HASH [(ColumnName [,...])] | 
DISTRIBUTE BY REFERENCE [(ForeignKeyConstraint)] | DUPLICATE

Note:

You cannot specify a PRIMARY KEY in both the ColumnDefinition clause and the PRIMARY KEY clause. If you are specifying the UsingIndexClause1 clause, you must specify PRIMARY KEY and PRIMARY KEY must be specified after the ColumnDefinition clause. The UsingIndexClause1 clause cannot be specified as part of the ColumnDefinition clause.

Syntax for global temporary table:

The UsingIndexClause1 and the DistributionClause are not supported for global temporary tables. The syntax is:

CREATE GLOBAL TEMPORARY TABLE [Owner.]TableName
(
    {{ColumnDefinition} [,...]
     [PRIMARY KEY (ColumnName [,...])] |
     [[CONSTRAINT ForeignKeyName]
        FOREIGN KEY ([ColumnName] [,...])
        REFERENCES RefTableName
             [(ColumnName [,...])] [ON DELETE CASCADE]] [...]
      }
)
[UNIQUE HASH ON (HashColumnName [,...])
    PAGES = PrimaryPages]
[ON COMMIT { DELETE | PRESERVE } ROWS ]

Parameters for CREATE TABLE: TimesTen Scaleout

Parameter	Description
CREATE TABLE [Owner.]TableName	CREATE TABLE indicates you want to create a table. You must specify a name for the table and optionally the owner of the table.
(ColumnDefinition)	ColumnDefinition indicates the column name, data type, and so on. ColumnDefinition is described in Column Definition: TimesTen Scaleout. If you specify the AS SelectQuery clause, ColumnDefinition is optional.
PRIMARY KEY (ColumnName [,...])	The placement of the PRIMARY KEY keyword after the ColumnDefinition indicates the PRIMARY KEY is specified after the columns are defined. This enables you to specify more than one column for the primary key.
[UsingIndexClause1]	UsingIndexClause1 is optional and is described in the next two rows of this table. You cannot specify two USING INDEX clauses in the CREATE TABLE definition. This clause enables you to define a global or local index for the PRIMARY KEY.
USING INDEX {GLOBAL|LOCAL}	Part of [UsingIndexClause1]. If specified, indicates if a global or local index is to be created for the primary key.
USING INDEX (CreateIndexStmt)	Part of the [UsingIndexClause1] clause. When this USING INDEX clause is specified, the (CreateIndexStmt) clause indicates that you want to define the index according to the TimesTen CREATE INDEX statement. The parentheses ( ) are required. You must create a unique index as that is the requirement for a primary key.
CONSTRAINT ForeignKeyName	Specifies an optional user-defined name for a foreign key. If not provided by the user, the system provides a default name.
FOREIGN KEY	This specifies a foreign key constraint between the new table and the referenced table identified by RefTableName. There are two lists of columns specified in the foreign key constraint. Columns in the first list are columns of the new table and are called the referencing columns. Columns in the second list are columns of the referenced table and are called referenced columns. These two lists must match in data type, including length, precision and scale. The referenced table must already have a primary key or unique index on the referenced column. The column name list of referenced columns is optional. If omitted, the primary index of RefTableName is used. The declaration of a foreign key creates a range index on the referencing columns. The user cannot drop the referenced table or its referenced index until the referencing table is dropped. The foreign key constraint asserts that each row in the new table must match a row in the referenced table such that the contents of the referencing columns are equal to the contents of the referenced columns. Any INSERT, DELETE or UPDATE statements that violate the constraint return TimesTen error 3001. TimesTen supports SQL-92 "NO ACTION" update and delete rules and ON DELETE CASCADE. Foreign key constraints are not deferrable. A foreign key can be defined on a global temporary table, but it can only reference a global temporary table. If a parent table is defined with COMMIT DELETE, the child table must also have the COMMIT DELETE attribute. A foreign key cannot reference an active parent table. An active parent table is one that has some instance materialized for a connection. If you specify the AS SelectQuery clause, you cannot define a foreign key on the table you are creating.
[ON DELETE CASCADE]	Enables the ON DELETE CASCADE referential action. If specified, when rows containing referenced key values are deleted from a parent table, rows in child tables with dependent foreign key values are also deleted.
UNIQUE HASH ON	Hash index for the table. UNIQUE HASH ON requires that a primary key be defined.
HashColumnName	Column defined in the table that is to participate in the hash key of this table. The columns specified in the hash index must be identical to the columns in the primary key. If you specify the AS SelectQuery clause, you must define HashColumnName on the table you are creating.
PAGES = PrimaryPages	Sizes the hash index to reflect the expected number of pages in your table. To determine the value for PrimaryPages, divide the number of expected rows in your table by 256. For example, if your table has 256,000 rows, specify 1000 for PrimaryPages (256000/256=1000). The value for PrimaryPages must be a positive constant and must be greater than 0. If your estimate for PrimaryPages is too small, performance may be degraded.
[ON COMMIT {DELETE|PRESERVE} ROWS]	The optional statement specifies whether to delete or preserve rows when a transaction that touches a global temporary table is committed. If not specified, the rows of the temporary table are deleted.
AS SelectQuery	If specified, creates a new table from the contents of the result set of the SelectQuery. The rows returned by SelectQuery are inserted into the table. Data types and data type lengths are derived from SelectQuery. SelectQuery is a valid SELECT statement that may or may not contain a subquery. You can specify a statement level optimizer hint after the SELECT verb. See Statement Level Optimizer Hints for information on statement level optimizer hints.
DistributionClause	Supported in TimesTen Scaleout only. There are three options: DISTRIBUTE BY HASH [(ColumnName [,...])] DUPLICATE DISTRIBUTE BY REFERENCE [(ForeignKeyConstraint)] The DISTRIBUTE BY HASH clause specifies a hash distribution scheme which distributes data based on the hash of the primary key or the hash of the user-defined distribution column(s). Rows are distributed across the replica sets and each row exists in a replica set. The distribution key is optional. If specified, it consists of one or more columns and these columns are used to distribute the data. The DUPLICATE clause specifies a duplicate distribution scheme which distributes identical copies of data in a table to all elements of the database. All rows of a table exist in each element. The DISTRIBUTE BY REFERENCE clause specifies a reference distribution scheme which distributes the data of a child table based on the location of the parent row defined by the foreign key constraint. A child table row exists in the same replica set as its parent table. The foreign key constraint is optional in the DISTRIBUTE BY REFERENCE clause. However, if you define more than one foreign key constraint, you must specify one of the foreign key constraints in the DISTRIBUTE BY REFERENCE clause. If you do not specify a clause, the default is DISTRIBUTE BY HASH. You must specify the DistributionClause before the AS SelectQuery clause. You cannot update the distribution key columns.
GLOBAL TEMPORARY	Specifies that the table being created is a global temporary table. A temporary table is similar to a persistent table but it is effectively materialized only when referenced in a connection. A global temporary table definition is persistent and is visible to all connections, but the table instance is local to each connection. It is created when a command referencing the table is compiled for a connection and dropped when the connection is disconnected. All instances of the same temporary table have the same name but they are identified by an additional connection ID together with the table name. Global temporary tables are allocated in temp space. The contents of a global temporary table cannot be shared between connections. Each connection sees only its own content of the table and compiled commands that reference temporary tables are not shared among connections. Operations on temporary tables do generate log records. The amount of log they generate is less than for permanent tables. The DistributionClause is not supported. TRUNCATE TABLE is not supported with global temporary tables. Local temporary tables are not supported. No object privileges are needed to access global temporary tables. Do not specify the AS SelectQuery clause with global temporary tables.
ColumnName	Name of the column in a table. If the name is used in the primary key definition, it forms the primary key for the table to be created. Up to 16 columns can be specified for the primary key. For a foreign key, the ColumnName is optional. If not specified for a foreign key, the reference is to the parent table's primary key. If you specify the AS SelectQuery clause, you do not have to specify the ColumnName. Do not specify the data type with the AS SelectQuery clause.

Column Definition: TimesTen Scaleout

SQL Syntax

You can only use the keyword, ENABLE, when defining columns in the CREATE TABLE statement.

The syntax is as follows:

ColumnName ColumnDataType
 [DEFAULT DefaultVal]
 [[NOT] INLINE]
 [PRIMARY KEY | UNIQUE | 
 NULL [UNIQUE] | 
 NOT NULL [ENABLE] [PRIMARY KEY | UNIQUE] 
]

Column Definition Parameters

The column definition has the following parameters:

Parameter	Description
ColumnName	Name to be assigned to one of the columns in the new table. No two columns in the table can be given the same name. A table can have a maximum of 1000 columns. If you specify the AS SelectQuery clause, ColumnName is optional. The number of column names must match the number of columns in SelectQuery.
ColumnDataType	Type of data the column can contain. Some data types require that you indicate a length. See Data Types for the data types that can be specified. If you specify the AS SelectQuery clause, do not specify ColumnDataType.
DEFAULT DefaultVal	Indicates that if a value is not specified for the column in an INSERT statement, the default value DefaultVal is inserted into the column. The default value specified must have a type that is compatible with the data type of the column. A default value can be as long as the data type of the associated column allows. You cannot assign a default value for the ROWID data type or for columns in read-only cache groups. In addition, you cannot use a function within the DEFAULT clause. The following are the supported data types for DefaultVal: NULL Constant expression (an expression that is evaluated to a constant value) SYSDATE and GETDATE (SYSDATE and GETDATE) SYSTEM_USER (SYSTEM_USER) If the default value is one of the users, the data type of the column must be either CHAR or VARCHAR2 and the width of the column must be at least 30 characters. If you specify the AS SelectQuery clause, optionally, you can specify the DEFAULT clause on the table you are creating.
INLINE|NOT INLINE	By default, variable-length columns whose declared column length is greater than 128 bytes are stored out of line. Variable-length columns whose declared column length is less than or equal to 128 bytes are stored inline. The default behavior can be overridden during table creation through the use of the INLINE and NOT INLINE keywords. If you specify the AS SelectQuery clause, optionally, you can specify the INLINE | NOT INLINE clause on the table you are creating.
NULL	Indicates that the column can contain NULL values. If you specify the AS SelectQuery clause, optionally, you can specify NULL on the table you are creating. If you specify NULL, you cannot specify ENABLE.
NOT NULL [ENABLE]	Indicates that the column cannot contain NULL values. If NOT NULL is specified, any statement that attempts to place a NULL value in the column is rejected. If you specify the AS SelectQuery clause, optionally, you can specify NOT NULL [ENABLE] on the table you are creating. If you specify NOT NULL, you can optionally specify ENABLE. Because NOT NULL constraints are always enabled, you are not required to specify ENABLE. You can only use the keyword, ENABLE, when defining columns in the CREATE TABLE statement.
UNIQUE	A unique constraint placed on the column. No two rows in the table may have the same value for this column. TimesTen creates a unique range index to enforce uniqueness. So a column with a unique constraint can use more memory and time during execution than a column without the constraint. Cannot be used with PRIMARY KEY. If you specify the AS SelectQuery clause, optionally, you can specify UNIQUE on the table you are creating.
PRIMARY KEY	A unique NOT NULL constraint placed on the column. No two rows in the table may have the same value for this column. Cannot be used with UNIQUE. If you specify the AS SelectQuery clause, optionally, you can specify PRIMARY KEY on the table you are creating.

Description for USING INDEX Clauses in CREATE TABLE: TimesTen Scaleout

You have the option of specifying an additional clause after the PRIMARY KEY clause in your CREATE TABLE definition. This clause enables you to specify a global or local index for the primary key constraint.

• The USING INDEX {GLOBAL | LOCAL} clause is one option that enables you to specify a global or local index for the primary key constraint. You must specify the GLOBAL or the LOCAL keyword. You can optionally specify the USE HASH INDEX clause after the USING INDEX {GLOBAL | LOCAL} clause if you want to define a hash index.

• The USING INDEX (CreateIndexStmt) clause is your other option for specifying a global or local index. The(CreateIndexStmt) clause indicates that you want to define the index according to the TimesTen CREATE INDEX statement. The parentheses ( ) are required. You must create a unique index as that is the requirement for a primary key constraint. If you use the CREATE INDEX statement to create a hash index, see CREATE INDEX for information on the CREATE INDEX statement.

Note:

You cannot use both the USING INDEX {GLOBAL | LOCAL} and the USING INDEX (CreateIndexStmt) in the CREATE TABLE definition. Specify one clause or the other or specify neither.

When you create a hash index, the hash index must be sized. TimesTen provides the PAGES= clause for this purpose. Consider these options:

• If you specify the USING INDEX...CreateIndexStmt clause to create a HASH index, you have the option of specifying the PAGES= clause. If you do not specify the PAGES= clause, TimesTen uses PAGES=CURRENT as the default to size the hash index. (See CREATE INDEX for details on the CREATE INDEX statement.)

• If you specify the UNIQUE HASH ON clause (part of the CREATE TABLE definition), you must specify the PAGES= clause to size the hash index.

• If you specify both the USING INDEX...CreateIndexStmt and the UNIQUE HASH ON clause (part of the CREATE TABLE definition), TimesTen uses the value specified in the UNIQUE HASH ON...PAGES= clause to size the hash index. TimesTen also issues a warning that there was a different number of pages specified for the hash index and it is using the value specified in the UNIQUE HASH ON...PAGES= clause.

In this example, the PAGES= clause is specified in both the CreateIndexStmt clause (PAGES=200) and the UNIQUE HASH ON clause (PAGES=400). TimesTen issues a warning and uses PAGES=400 to size the hash index:

Command> CREATE TABLE mytab (col1 TT_INTEGER, col2 TT_INTEGER, PRIMARY KEY (col1, col2)
           USING INDEX (CREATE GLOBAL UNIQUE HASH INDEX myindex on mytab (col1,col2) PAGES=200)) 
           UNIQUE HASH ON (col1,col2) PAGES=400 DISTRIBUTE BY HASH (col1);
Warning  2252: Different number of pages specified for hash index MYINDEX in table and index definition. 
Index created with pages = 400

Restrictions:

• The USING INDEX clause cannot be used for foreign key constraints on a table.

• The USING INDEX clause cannot be used with views.

See CREATE INDEX for information on global and local indexes and their use in TimesTen Scaleout.

Additional Information for CREATE TABLE: TimesTen Scaleout

• TimesTen Scaleout distributes data by one of three distribution schemes:

  • Hash: TimesTen Scaleout distributes data based on the hash of the primary key column(s) or one or more columns you specify in the DISTRIBUTED BY HASH clause. A given row is stored in a replica set. Rows are evenly distributed across the replica sets. Hash is the default distribution scheme as it is appropriate for most tables.

  • Reference: TimesTen Scaleout distributes data of a child table based on the location of the parent table that is identified by the foreign key. A given row of a child table is present in the same replica set as its parent table. This distribution scheme optimizes joins by distributing related data within a single replica set. You can distribute the parent table by hash or reference. The parent is called the root table if it is distributed by hash. You must define the child (foreign) key columns as NOT NULL.

  • Duplicate: TimesTen Scaleout distributes full identical copies of data to all elements of the database. All rows are present in all elements. This distribution scheme optimizes the performance of reads by storing identical data in every data instance. This distribution scheme is appropriate for tables that are relatively small, frequently read, and infrequently modified.

  See Defining the Distribution Scheme for Tables and Defining Table Distribution Schemes in the Oracle TimesTen In-Memory Database Scaleout User's Guide for more information.

• For tables with a hash distribution scheme:

  • The distribution key is used if specified.

  • The primary key is used if the distribution key is not specified.

  • A hidden column is used if there is no primary key or distribution key. Data is distributed randomly and evenly.

  You should specify a distribution key if there is a primary key defined on the table, but the primary key is not the best way to distribute the data. If there is no primary key, but there is a unique column, then you may want to distribute the data on this unique column. If there is no primary key and no unique column, then do not specify a distribution key. TimesTen Scaleout distributes the data on the hidden column.

• If the distribution scheme is by reference:

  • Only a single foreign key constraint can be referenced in the DISTRIBUTE BY REFERENCE clause. There may be multiple foreign key constraints in the child table, but only one can be used to determine the reference distribution.

  • A referenced foreign key constraint must be named in the constraint clause if there is more than one.

  • The foreign key constraint in the reference distribution clause must reference the primary key or a unique key of the parent table. If the parent table is the root, the referenced key must be the distribution key.

  • You can create a foreign key relationship to a non distribution key column of the parent table, but you cannot then distribute by reference based on this foreign key relationship.

  • You cannot update the foreign key column that is used in the DISTRIBUTE BY REFERENCE clause.

• If you are planning to load your tables with data, consider creating your tables without indexes. After the data is loaded, you can then create your indexes. This reduces the time it take to load the data into the tables. The exception is if you are using foreign keys and reference tables.

• You can use the CREATE TABLE...AS SELECT statement to create a new table based on the definition of the original table. Note that primary key constraints are not carried over to the new table so how the data is distributed changes if you do not define a primary key constraint on the new table.

  See Use CREATE TABLE...AS SELECT for more information.

• You cannot update the distribution key column(s) unless you update the column(s) to the same value.

• All columns participating in the primary key are NOT NULL.

• A PRIMARY KEY that is specified in the ColumnDefinition can only be specified for one column.

• You cannot specify a PRIMARY KEY in both the ColumnDefinition clause and the PRIMARY KEY clause.

• For both primary key and foreign key constraints, duplicate column names are not allowed in the constraint column list.

• You cannot update primary key column(s) unless you update the column(s) to the same value.

• There are performance considerations when you define out of line columns instead of inline columns:

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

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

  • Deleting data is slower because TimesTen performs more reclaim and logging operations.

  • Storing a column requires less overhead.

• If ON DELETE CASCADE is specified on a foreign key constraint for a child table, a user can delete rows from a parent table for which the user has the DELETE privilege without requiring explicit DELETE privilege on the child table.

• To change the ON DELETE CASCADE triggered action, drop then redefine the foreign key constraint.

• You cannot create a table that has a foreign key referencing a cached table.

• UNIQUE column constraint and default column values are not supported with materialized views.

• Use the ALTER TABLE statement to change the representation of the primary key index for a table.

• If you specify the AS SelectQuery clause:

  • Data types and data type lengths are derived from the SelectQuery. Do not specify data types on the columns of the table you are creating.

  • TimesTen defines on columns in the new table NOT NULL constraints that were explicitly created on the corresponding columns of the selected table if SelectQuery selects the column rather than an expression containing the column.

  • NOT NULL constraints that were implicitly created by TimesTen on columns of the selected table (for example, primary keys) are carried over to the new table. You can override the NOT NULL constraint on the selected table by defining the new column as NULL. For example:

    CREATE TABLE newtable (newcol NULL) AS SELECT (col) FROM tab;
    

  • NOT INLINE/INLINE attributes are carried over to the new table.

  • Unique keys, foreign keys, indexes and column default values are not carried over to the new table.

  • If all expressions in SelectQuery are columns, rather than expressions, then you can omit the columns from the table you are creating. In this case, the name of the columns are the same as the columns in SelectQuery. If the SelectQuery contains an expression rather than a simple column reference, either specify a column alias or name the column in the CREATE TABLE statement.

  • Do not specify foreign keys on the table you are creating.

  • Do not specify the SELECT FOR UPDATE clause in SelectQuery.

  • The ORDER BY clause is not supported when you use the AS SelectQuery clause.

  • SelectQuery cannot contain set operators UNION, MINUS, INTERSECT.

• By default, a range index is created to enforce the primary key. Use the UNIQUE HASH clause to specify a hash index for the primary key.

  • If your application performs range queries using a table's primary key, then choose a range index for that table by omitting the UNIQUE HASH clause.

  • If your application performs only exact match lookups on the primary key, then a hash index may offer better response time and throughput. In such a case, specify the UNIQUE HASH clause.

• A hash index is created with a fixed size that remains constant for the life of the table or until the hash index is resized with the ALTER TABLE statement or when the index is dropped and recreated. A smaller hash index results in more hash collisions. A larger hash index reduces collisions but can waste memory. Hash key comparison is a fast operation, so a small number of hash collisions should not cause a performance problem for TimesTen.

  To ensure that your hash index is sized correctly, your application must indicate the expected size of your table with the value of the RowPages parameter of the SET PAGES clause. Compute this value by dividing the number of expected rows in your table by 256. For example, if your table has 256,000 rows, specify 1000 for the value of RowPages (256000/256=1000).

• At most 16 columns are allowed in a hash key.

• ON DELETE CASCADE is supported on detail tables of a materialized view. If you have a materialized view defined over a child table, a deletion from the parent table causes cascaded deletes in the child table. This, in turn, triggers changes in the materialized view.

• The total number of rows reported by the DELETE statement does not include rows deleted from child tables as a result of the ON DELETE CASCADE action.

• For ON DELETE CASCADE: Since different paths may lead from a parent table to a child table, the following rule is enforced:

  • Either all paths from a parent table to a child table are "delete" paths or all paths from a parent table to a child table are "do not delete" paths. Specify ON DELETE CASCADE on all child tables on the "delete" path.

  • This rule does not apply to paths from one parent to different children or from different parents to the same child.

• For ON DELETE CASCADE, the following rule is also enforced.

  • If a table is reached by a "delete" path, then all its children are also reached by a "delete" path.

• The data in a global temporary table is private to the current connection and does not need to be secured between users. Thus, global temporary tables do not require object privileges.

Examples: Global and Local Indexes in TimesTen Scaleout

These examples show various uses of the syntax for using global indexes with CREATE TABLE...PRIMARY KEY.

Create a table specifying a primary key. Use the USING INDEX GLOBAL clause to create a global range index. The index must be unique as is a requirement for a primary key.

Command> CREATE TABLE mytab (c TT_INTEGER, b TT_INTEGER, a TT_INTEGER, 
           PRIMARY KEY (c,b) USING INDEX GLOBAL) DISTRIBUTE BY HASH (a,b);
Command> indexes mytab;

Indexes on table SAMPLEUSER.MYTAB:
  MYTAB: global unique range index on columns:
    C
    B
  1 index found.

1 index found on 1 table
Command> DROP TABLE mytab;

Create a table specifying a primary key. Use the USING INDEX LOCAL clause to create a local range index. The index must be unique as is a requirement for a primary key.

Command> CREATE TABLE mytab (c TT_INTEGER, b TT_INTEGER, a TT_INTEGER, 
  PRIMARY KEY (c,b) USING INDEX LOCAL DISTRIBUTE BY HASH (a,b);
Command> indexes mytab;

Indexes on table SAMPLEUSER.MYTAB:
  MYTAB: unique range index on columns:
    C
    B
  1 index found.

1 index found on 1 table.
Command> DROP TABLE mytab;

Create a table specifying a primary key. Use the USING INDEX (CreateIndexStmt) clause to create a global range index. The (CreateIndexStmt) clause indicates that you want to define the index according to the TimesTen CREATE INDEX statement. The parentheses ( ) are required. You must specify the UNIQUE keyword when creating the index. This is a requirement for a primary key. See CREATE INDEX for information on this statement.

Command> CREATE TABLE mytab (c TT_INTEGER, b TT_INTEGER, a TT_INTEGER, 
           PRIMARY KEY (c,b) USING INDEX (CREATE GLOBAL UNIQUE INDEX GlobalUniqueIdx ON mytab (c,b))) DISTRIBUTE  BY HASH (a,b);
Command> indexes mytab;

Indexes on table SAMPLEUSER.MYTAB:
  GLOBALUNIQUEIDX: global unique range index on columns:
    C
    B
  1 index found.

1 index found on 1 table.
Command> DROP TABLE mytab;

Create a table specifying a primary key. Use the USING INDEX (CreateIndexStmt) clause to create a global range index. The (CreateIndexStmt) clause indicates that you want to define the index according to the TimesTen CREATE INDEX statement. The parentheses ( ) are required. The CREATE INDEX definition specifies the INCLUDE clause to include additional column(s) in the index definition. You must specify the UNIQUE keyword when creating the index. This is a requirement for a primary key. See CREATE INDEX for information on this statement.

Command> CREATE TABLE mytab (c TT_INTEGER, b TT_INTEGER, a TT_INTEGER, 
           PRIMARY KEY (c,b) USING INDEX (CREATE GLOBAL UNIQUE INDEX GlobalUniqueIdx 
           ON mytab (c,b) INCLUDE (a))) DISTRIBUTE  BY HASH (a,b);
Command> indexes mytab;

Indexes on table SAMPLEUSER.MYTAB:
  GLOBALUNIQUEIDX: global unique range index on columns:
    C
    B
    Included columns:
      A
  1 index found.

1 index found on 1 table.
Command> DROP TABLE mytab;

Create a table specifying a primary key. Use the USING INDEX (CreateIndexStmt) clause to create a global unique hash index. The (CreateIndexStmt) clause indicates that you want to define the index according to the TimesTen CREATE INDEX statement. The parentheses ( ) are required. You must specify the UNIQUE keyword when creating the index. This is a requirement for a primary key. See CREATE INDEX for information on this statement.

Command> CREATE TABLE mytab (c TT_INTEGER, b TT_INTEGER, a TT_INTEGER, 
           PRIMARY KEY (c,b) USING INDEX (CREATE GLOBAL UNIQUE HASH INDEX GlobalUniqueIdx 
           ON mytab (c,b))) DISTRIBUTE  BY HASH (a,b);
Command> indexes mytab;

Indexes on table SAMPLEUSER.MYTAB:
  GLOBALUNIQUEIDX: global unique hash index on columns:
    C
    B
  1 index found.

1 index found on 1 table.
Command> DROP TABLE mytab;

Additional Examples: TimesTen Scaleout

These examples illustrate how to create tables with the duplicate, hash, and reference distribution schemes.

• Create the account_type Table

• Create the account_status Table

• Create the customers Table

• Create the accounts Table

• Create the transactions Table

• View the Tables

• View the Definition of the Accounts Table

These examples illustrate how to create tables with the DISTRIBUTE BY REFERENCE distribution scheme:

• DISTRIBUTE BY REFERENCE with One Foreign Key

• Table with More Than One Foreign Key

• Foreign Key Relationship Not On Distribution Key of the Parent Table

• Using First and Second Level Child Foreign Key Relationship

"Use CREATE TABLE...AS SELECT" shows how to use the CREATE TABLE...AS SELECT clause in TimesTen Scaleout.

Create the account_type Table

This example runs ttIsql to create the account_type table and use a duplicate distribution scheme to distribute the data. This table contains few rows and uses a duplicate distribution scheme to optimize reads. Copies of the data in the table are distributed to all elements of the database.

Command> CREATE TABLE account_type ( type CHAR(1) NOT NULL PRIMARY KEY,
           description VARCHAR2(100) NOT NULL) DUPLICATE;

Create the account_status Table

This example runs ttIsql to create the account_status table and use a duplicate distribution scheme. The table size is small and uses a distribution scheme to optimize reads. Copies of the data in the table are distributed to all elements of the database.

Command> CREATE TABLE account_status(status NUMBER(2) NOT NULL PRIMARY KEY,
           description VARCHAR2(100) NOT NULL) DUPLICATE;

Create the customers Table

This example runs ttIsql to create the customers table and distributes the table by hash. The data in the table is distributed to each element based on the hash of the cust_id column (the primary key).

Command> CREATE TABLE customers(cust_id NUMBER(10,0) NOT NULL PRIMARY KEY,
           first_name VARCHAR2(30) NOT NULL,last_name VARCHAR2(30) NOT NULL,
           addr1 VARCHAR2(64),addr2 VARCHAR2(64), zipcode VARCHAR2(5),
           member_since DATE NOT NULL)
         DISTRIBUTE BY HASH;

Create the accounts Table

This example runs ttIsql to create the accounts table and defines three primary/foreign key relationships. The accounts table is distributed by reference and the data is distributed based on the fk_customer foreign key constraint. This scheme optimizes the performance of joins by distributing the data in the accounts table based on the location of the corresponding value of the customers.cust_id parent column (of the fk_customer foreign key constraint). The row of a child table exists in the same replica set as the parent table. If the join is performed on the primary or foreign key, the data is stored on one element, so TimesTen Scaleout does not have to access different elements.

Command> CREATE TABLE accounts(account_id NUMBER(10,0) NOT NULL PRIMARY KEY,
           phone VARCHAR2(15) NOT NULL,account_type CHAR(1) NOT NULL,
           status NUMBER(2) NOT NULL,current_balance NUMBER(10,2) NOT NULL,
           prev_balance NUMBER(10,2) NOT NULL,date_created DATE NOT NULL,
           cust_id NUMBER(10,0) NOT NULL,
         CONSTRAINT fk_customer FOREIGN KEY (cust_id)
         REFERENCES customers(cust_id),CONSTRAINT fk_acct_type
         FOREIGN KEY (account_type)
         REFERENCES account_type(type),
         CONSTRAINT fk_acct_status
         FOREIGN KEY (status)
         REFERENCES account_status(status)
         )
         DISTRIBUTE BY REFERENCE (fk_customer);

Create the transactions Table

This example runs ttIsql to create the transactions table. The transactions table is distributed by reference and the data is distributed based on the fk_accounts foreign key constraint. This scheme optimizes the performance of joins by distributing the data in the transaction table based on the location of the corresponding value of the accounts.account_id parent column (of the fk_accounts foreign key constraint). The row of a child table exists in the same replica set as the parent table. If the join is performed on the primary or foreign key, the data is stored on one element, so TimesTen Scaleout does not have to access different elements.

The accounts parent table is also distributed by reference. This defines a two level distribute by reference distribution hierarchy.

Command> CREATE TABLE transactions(transaction_id NUMBER(10,0) NOT NULL,
           account_id NUMBER(10,0) NOT NULL ,
           transaction_ts TIMESTAMP NOT NULL,
           description VARCHAR2(60),
           optype CHAR(1) NOT NULL,
           amount NUMBER(6,2) NOT NULL,
         PRIMARY KEY (account_id, transaction_id, transaction_ts),
         CONSTRAINT fk_accounts FOREIGN KEY (account_id)
         REFERENCES accounts(account_id)
         )
         DISTRIBUTE BY REFERENCE (fk_accounts);

View the Tables

This example runs the ttIsql tables command to view the tables in the database.

Command> tables;
  SAMPLEUSER.ACCOUNTS
  SAMPLEUSER.ACCOUNT_STATUS
  SAMPLEUSER.ACCOUNT_TYPE
  SAMPLEUSER.CUSTOMERS
  SAMPLEUSER.TRANSACTIONS
5 tables found.

View the Definition of the Accounts Table

This example runs the ttIsql describe command to view the definition of the accounts table.

Command> describe accounts;
 
Table SAMPLEUSER.ACCOUNTS:
  Columns:
   *ACCOUNT_ID                      NUMBER (10) NOT NULL
    PHONE                           VARCHAR2 (15) INLINE NOT NULL
    ACCOUNT_TYPE                    CHAR (1) NOT NULL
    STATUS                          NUMBER (2) NOT NULL
    CURRENT_BALANCE                 NUMBER (10,2) NOT NULL
    PREV_BALANCE                    NUMBER (10,2) NOT NULL
    DATE_CREATED                    DATE NOT NULL
    CUST_ID                         NUMBER (10) NOT NULL
  DISTRIBUTE BY REFERENCE (FK_CUSTOMER)
 
1 table found.
(primary key columns are indicated with *)

DISTRIBUTE BY REFERENCE with One Foreign Key

This example illustrates that you do not have to specify the foreign key constraint in the DISTRIBUTE BY REFERENCE clause. There is only one foreign key.

First create the Orders table and distribute by hash.

Command> CREATE TABLE Orders
           (OrderId TT_INTEGER NOT NULL PRIMARY KEY,
            OrderDate DATE NOT NULL,
            discount BINARY_FLOAT)
         DISTRIBUTE BY HASH;

Create the OrderDetails table with one foreign key constraint. There is no need to name the constraint in the distribution clause.

Command> CREATE TABLE OrderDetails
           (OrderId TT_INTEGER NOT NULL,
            PartId TT_INTEGER NOT NULL,
            Quantity TT_INTEGER NOT NULL,
         FOREIGN KEY (OrderId)
         REFERENCES Orders (OrderId))
         DISTRIBUTE BY REFERENCE;

Run the ttIsql describe command to view the tables.

Command> describe Orders;
 
Table SAMPLEUSER.ORDERS:
  Columns:
   *ORDERID                         TT_INTEGER NOT NULL
    ORDERDATE                       DATE NOT NULL
    DISCOUNT                        BINARY_FLOAT
  DISTRIBUTE BY HASH (ORDERID)
 
1 table found.
(primary key columns are indicated with *)

Command> describe OrderDetails;
 
Table SAMPLEUSER.ORDERDETAILS:
  Columns:
    ORDERID                         TT_INTEGER NOT NULL
    PARTID                          TT_INTEGER NOT NULL
    QUANTITY                        TT_INTEGER NOT NULL
  DISTRIBUTE BY REFERENCE
 
1 table found.
(primary key columns are indicated with *)

Table with More Than One Foreign Key

This example illustrates that if a table contains more than one foreign key constraint, the DISTRIBUTE BY REFERENCE clause must name the foreign key constraint that will be used as the reference. The customers2 table is the parent and is distributed by hash. The OrderDetails2 table contains two foreign key constraints and this table is distributed by reference on the c1_1 constraint. This constraint must be included in the DISTRIBUTED BY REFERENCE clause.

Command> CREATE TABLE customers2 (CustomerId TT_INTEGER NOT NULL PRIMARY KEY,
           LastOrderDate DATE NOT NULL,PromotionDiscount BINARY_FLOAT)
         DISTRIBUTE BY HASH;

Command> CREATE TABLE OrderDetails2 (OrderId TT_INTEGER NOT NULL,
           CustomerId TT_INTEGER NOT NULL, Quantity TT_INTEGER NOT NULL,
         CONSTRAINT c1_1 FOREIGN KEY (OrderId) 
         REFERENCES Orders (OrderId),
         CONSTRAINT c2_2 FOREIGN KEY (CustomerId) 
         REFERENCES Customers2 (CustomerId))
         DISTRIBUTE BY REFERENCE (c1_1);

Foreign Key Relationship Not On Distribution Key of the Parent Table

This example creates the orders2 parent table with the OrderId primary key and the CouponId unique key. The table is distributed by hash. Since no distribution key is specified, the data is distributed by hash on the OrderId primary key. The coupons child table establishes a foreign key relationship on the CouponId unique key. Since this key is not the distribution key of the orders2 parent table, TimesTen Scaleout throws an error.

Command> CREATE TABLE Orders2 (OrderId TT_INTEGER NOT NULL PRIMARY KEY,
           CouponId TT_INTEGER NOT NULL UNIQUE, OrderDate DATE NOT NULL,
           discount BINARY_FLOAT)
         DISTRIBUTE BY HASH;

Command> CREATE TABLE Coupons (CouponId TT_INTEGER NOT NULL, 
           discount BINARY_FLOAT,
         CONSTRAINT CouponC1 FOREIGN KEY (CouponId) 
         REFERENCES Orders2 (CouponId) )
         DISTRIBUTE BY REFERENCE (CouponC1);
 1067: The Parent keys for a distribute by reference table with hash distributed
parent must include the distribution keys of the parent.
The command failed.

Using First and Second Level Child Foreign Key Relationship

This example creates the Coupons2 parent table and distributes the data by hash. The Orders3 child table is created as a first level foreign key relationship and the parent table (Coupons2) is the root table. The OrderDetails3 child table is created as a second level foreign key relationship and the parent table (Orders3) is a reference table.

Command> CREATE TABLE Coupons2 (CouponId TT_INTEGER NOT NULL PRIMARY KEY, 
           discount BINARY_FLOAT)
         DISTRIBUTE BY HASH;

Command> CREATE TABLE Orders3 (OrderId TT_INTEGER NOT NULL PRIMARY KEY,
           CouponId TT_INTEGER NOT NULL, OrderDate DATE NOT NULL,
           discount BINARY_FLOAT, CONSTRAINT c1_coupons FOREIGN KEY (CouponId)
         REFERENCES Coupons2 (CouponId))
         DISTRIBUTE BY REFERENCE (c1_coupons);

Command> CREATE TABLE OrderDetails3 (OrderId TT_INTEGER NOT NULL,
           PartId TT_INTEGER NOT NULL, quantity TT_INTEGER NOT NULL,
         CONSTRAINT c1_orders FOREIGN KEY (OrderId)
         REFERENCES Orders3 (OrderId))
         DISTRIBUTE BY REFERENCE (C1_orders);

Use CREATE TABLE...AS SELECT

This example creates the NewCustomers table based on the customers table. It defines a primary key constraint to maintain the same distribution scheme and ensure the data is distributed on the primary key.

Command> CREATE TABLE NewCustomers(cust_id PRIMARY KEY, first_name, last_name, 
           addr1, addr2, zipcode, member_since) AS SELECT * FROM customers;
0 rows inserted.
Command> describe NewCustomers;
 
Table SAMPLEUSER.NEWCUSTOMERS:
  Columns:
   *CUST_ID                         NUMBER (10) NOT NULL
    FIRST_NAME                      VARCHAR2 (30) INLINE NOT NULL
    LAST_NAME                       VARCHAR2 (30) INLINE NOT NULL
    ADDR1                           VARCHAR2 (64) INLINE
    ADDR2                           VARCHAR2 (64) INLINE
    ZIPCODE                         VARCHAR2 (5) INLINE
    MEMBER_SINCE                    DATE NOT NULL
  DISTRIBUTE BY HASH (CUST_ID)
 
1 table found.
(primary key columns are indicated with *)

Run ttIsql describe to view the original customers table:

Command> describe Customers;

Table SAMPLEUSER.CUSTOMERS:
  Columns:
   *CUST_ID                         NUMBER (10) NOT NULL
    FIRST_NAME                      VARCHAR2 (30) INLINE NOT NULL
    LAST_NAME                       VARCHAR2 (30) INLINE NOT NULL
    ADDR1                           VARCHAR2 (64) INLINE
    ADDR2                           VARCHAR2 (64) INLINE
    ZIPCODE                         VARCHAR2 (5) INLINE
    MEMBER_SINCE                    DATE NOT NULL
  DISTRIBUTE BY HASH (CUST_ID)
 
1 table found.
(primary key columns are indicated with *)

SQL Syntax for CREATE TABLE: TimesTen Classic

You cannot specify a PRIMARY KEY in both the ColumnDefinition clause and the PRIMARY KEY clause.

The syntax for a persistent table:

CREATE TABLE [Owner.]TableName
(
    {{ColumnDefinition} [,...]
     [PRIMARY KEY (ColumnName [,...]) |
     [[CONSTRAINT ForeignKeyName]
        FOREIGN KEY ([ColumnName] [,...])
        REFERENCES RefTableName
            [(ColumnName [,...])] [ON DELETE CASCADE]] [...]
    }
)
[ColumnBasedCompression]
[UNIQUE HASH ON (HashColumnName [,...])
    PAGES = PrimaryPages]
[AGING {LRU|
         USE ColumnName
             LIFETIME Num1 {SECOND[S] | MINUTE[S] | HOUR[S] |DAY[S]}
               [CYCLE Num2 {SECOND[S] | MINUTE[S] |HOUR[S] |DAY[S]}]
       }[ON|OFF]
] 
[AS SelectQuery]

The syntax for a global temporary table is:

CREATE GLOBAL TEMPORARY TABLE [Owner.]TableName
(
    {{ColumnDefinition} [,...]
     [PRIMARY KEY (ColumnName [,...]) |
     [[CONSTRAINT ForeignKeyName]
        FOREIGN KEY ([ColumnName] [,...])
        REFERENCES RefTableName
             [(ColumnName [,...])] [ON DELETE CASCADE]] [...]
      }
)
[UNIQUE HASH ON (HashColumnName [,...])
    PAGES = PrimaryPages]
[ON COMMIT { DELETE | PRESERVE } ROWS]

Parameters for CREATE TABLE: TimesTen Classic

Parameter	Description
[Owner.]TableName	Name to be assigned to the new table. Two tables cannot have the same owner name and table name. If you do not specify the owner name, your login name becomes the owner name for the new table. Owners of tables in TimesTen are determined by the user ID settings or login names. Oracle Database table owner names must always match TimesTen table owner names. See Basic Names for rules on defining names.
GLOBAL TEMPORARY	Specifies that the table being created is a global temporary table. A temporary table is similar to a persistent table but it is effectively materialized only when referenced in a connection. A global temporary table definition is persistent and is visible to all connections, but the table instance is local to each connection. It is created when a command referencing the table is compiled for a connection and dropped when the connection is disconnected. All instances of the same temporary table have the same name but they are identified by an additional connection ID together with the table name. Global temporary tables are allocated in temp space. The contents of a global temporary table cannot be shared between connections. Each connection sees only its own content of the table and compiled commands that reference temporary tables are not shared among connections. When DDL_REPLICATION_LEVEL is 2 or greater, the creation of a global temporary table is replicated in an active standby pair, but the global temporary table is not included in the replication scheme. Temporary tables are automatically excluded from active standby pairs or when the DATASTORE element has been specified. A cache group table cannot be defined as a temporary table. Changes to temporary tables cannot be tracked with XLA. Operations on temporary tables do generate log records. The amount of log they generate is less than for permanent tables. Truncate table is not supported with global temporary tables. Local temporary tables are not supported. No object privileges are needed to access global temporary tables. Do not specify the AS SelectQuery clause with global temporary tables.
ColumnDefinition	An individual column in a table. Each table must have at least one column. If you specify the AS SelectQuery clause, ColumnDefinition is optional.
ColumnName	Name of the column in a table. Is used in various clauses of the CREATE TABLE statement. If the name is used in the primary key definition, it forms the primary key for the table to be created. Up to 16 columns can be specified for the primary key. For a foreign key, the ColumnName is optional. If not specified for a foreign key, the reference is to the parent table's primary key. If you specify the AS SelectQuery clause, you do not have to specify the ColumnName. Do not specify the data type with the AS SelectQuery clause.
PRIMARY KEY	PRIMARY KEY may only be specified once in a table definition. It provides a way of identifying one or more columns that, together, form the primary key of the table. The contents of the primary key have to be unique and NOT NULL. You cannot specify a column as both UNIQUE and a single column PRIMARY KEY.
CONSTRAINT ForeignKeyName	Specifies an optional user-defined name for a foreign key. If not provided by the user, the system provides a default name.
FOREIGN KEY	This specifies a foreign key constraint between the new table and the referenced table identified by RefTableName. There are two lists of columns specified in the foreign key constraint. Columns in the first list are columns of the new table and are called the referencing columns. Columns in the second list are columns of the referenced table and are called referenced columns. These two lists must match in data type, including length, precision and scale. The referenced table must already have a primary key or unique index on the referenced column. The column name list of referenced columns is optional. If omitted, the primary index of RefTableName is used. The declaration of a foreign key creates a range index on the referencing columns. The user cannot drop the referenced table or its referenced index until the referencing table is dropped. The foreign key constraint asserts that each row in the new table must match a row in the referenced table such that the contents of the referencing columns are equal to the contents of the referenced columns. Any INSERT, DELETE or UPDATE statements that violate the constraint return TimesTen error 3001. TimesTen supports SQL-92 "NO ACTION" update and delete rules and ON DELETE CASCADE. Foreign key constraints are not deferrable. A foreign key can be defined on a global temporary table, but it can only reference a global temporary table. If a parent table is defined with COMMIT DELETE, the child table must also have the COMMIT DELETE attribute. A foreign key cannot reference an active parent table. An active parent table is one that has some instance materialized for a connection. If you specify the AS SelectQuery clause, you cannot define a foreign key on the table you are creating.
[ON DELETE CASCADE]	Enables the ON DELETE CASCADE referential action. If specified, when rows containing referenced key values are deleted from a parent table, rows in child tables with dependent foreign key values are also deleted.
ColumnBasedCompression	Defines compression at the column level, which stores data more efficiently. Eliminates redundant storage of duplicate values within columns and improves the performance of SQL queries that perform full table scans. See Column-Based Compression of Tables (TimesTen Classic) for details.
UNIQUE	UNIQUE provides a way of identifying a column where each row must contain a unique value.
UNIQUE HASH ON	Hash index for the table. This parameter is used for equality predicates. UNIQUE HASH ON requires that a primary key be defined.
HashColumnName	Column defined in the table that is to participate in the hash key of this table. The columns specified in the hash index must be identical to the columns in the primary key. If you specify the AS SelectQuery clause, you must define HashColumnName on the table you are creating.
PAGES = PrimaryPages	Sizes the hash index to reflect the expected number of pages in your table. To determine the value for PrimaryPages, divide the number of expected rows in your table by 256. For example, if your table has 256,000 rows, specify 1000 for PrimaryPages (256000/256=1000). The value for PrimaryPages must be a positive constant and must be greater than 0. If your estimate for PrimaryPages is too small, performance may be degraded.
[ON COMMIT {DELETE|PRESERVE} ROWS]	The optional statement specifies whether to delete or preserve rows when a transaction that touches a global temporary table is committed. If not specified, the rows of the temporary table are deleted.
[AGING LRU [ON|OFF]]	If specified, defines the LRU aging policy for the table. The LRU aging policy defines the type of aging (least recently used (LRU)), the aging state (ON or OFF) and the LRU aging attributes. Set the aging state to either ON or OFF. ON indicates that the aging state is enabled and aging is done automatically. OFF indicates that the aging state is disabled and aging is not done automatically. In both cases, the aging policy is defined. The default is ON. LRU attributes are defined by calling the ttAgingLRUConfig and/or the ttAgingTableLRUConfig built-in procedures. LRU attributes are not defined at the SQL level. See ttAgingLRUConfig and ttAgingTableLRUConfig in the Oracle TimesTen In-Memory Database Reference and Implementing an Aging Policy in Your Tables in the Oracle TimesTen In-Memory Database Operations Guide for more information.
[AGING USE ColumnName...[ON|OFF]]	If specified, defines the time-based aging policy for the table. The time-based aging policy defines the type of aging (time-based), the aging state (ON or OFF) and the time-based aging attributes. Set the aging state to either ON or OFF. ON indicates that the aging state is enabled and aging is done automatically. OFF indicates that the aging state is disabled and aging is not done automatically. In both cases, the aging policy is defined. The default is ON. Time-based aging attributes are defined at the SQL level and are specified by the LIFETIME and CYCLE clauses. Specify ColumnName as the name of the column used for time-based aging. Define the column as NOT NULL and of data type TIMESTAMP or DATE. The value of this column is subtracted from SYSDATE, truncated using the specified unit (second, minute, hour, day) and then compared to the LIFETIME value. If the result is greater than the LIFETIME value, then the row is a candidate for aging. The values of the column that you use for aging are updated by your applications. If the value of this column is unknown for some rows, and you do not want the rows to be aged, define the column with a large default value (the column cannot be NULL). You can define your aging column with a data type of TT_TIMESTAMP or TT_DATE. If you choose data type TT_DATE, then you must specify the LIFETIME unit as days. If you specify the AS SelectQuery clause, you must define the ColumnName on the table you are creating. For more information about time-based aging, see Implementing an Aging Policy in Your Tables in Oracle TimesTen In-Memory Database Operations Guide.
LIFETIME Num1 {SECOND[S]| MINUTE[S]|HOUR[S]| DAY[S]}	LIFETIME is a time-based aging attribute and is a required clause. Specify the LIFETIME clause after the AGING USE ColumnName clause. The LIFETIME clause specifies the minimum amount of time data is kept in cache. Specify Num1 as a positive integer constant to indicate the unit of time expressed in seconds, minutes, hours or days that rows should be kept in cache. Rows that exceed the LIFETIME value are aged out (deleted from the table). If you define your aging column with data type TT_DATE, then you must specify DAYS as the LIFETIME unit. The concept of time resolution is supported. If DAYS is specified as the time resolution, then all rows whose timestamp belongs to the same day are aged out at the same time. If HOURS is specified as the time resolution, then all rows with timestamp values within that hour are aged at the same time. A LIFETIME of 3 days is different than a LIFETIME of 72 hours (3*24) or a LIFETIME of 432 minutes (3*24*60).
[CYCLE Num2 {SECOND[S] |MINUTE[S]|HOUR[S]| DAY[S]}]	CYCLE is a time-based aging attribute and is optional. Specify the CYCLE clause after the LIFETIME clause. The CYCLE clause indicates how often the system should examine rows to see if data exceeds the specified LIFETIME value and should be aged out (deleted). Specify Num2 as a positive integer constant. If you do not specify the CYCLE clause, then the default value is 5 minutes. If you specify 0 for Num2, then the aging thread wakes up every second. If the aging state is OFF, then aging is not done automatically and the CYCLE clause is ignored.
AS SelectQuery	If specified, creates a new table from the contents of the result set of the SelectQuery. The rows returned by SelectQuery are inserted into the table. Data types and data type lengths are derived from SelectQuery. SelectQuery is a valid SELECT statement that may or may not contain a subquery. See SELECT for information on the SELECT statement. You can specify a statement level optimizer hint after the SELECT verb. For more information on statement level optimizer hints, see Statement Level Optimizer Hints.

Column Definition: TimesTen Classic

SQL Syntax

You can only use the keyword, ENABLE, when defining columns in the CREATE TABLE statement.

For all data types other than LOBs, the syntax is as follows:

ColumnName ColumnDataType
 [DEFAULT DefaultVal]
 [[NOT] INLINE]
 [PRIMARY KEY | UNIQUE | 
 NULL [UNIQUE] | 
 NOT NULL [ENABLE] [PRIMARY KEY | UNIQUE] 
]

For LOB data types, you cannot create a primary key or unique constraint on LOB columns. In addition, LOB data types are stored out of line, so the INLINE attribute cannot be specified.

LOB data types are not supported with TimesTen Scaleout.

For all LOB data types, the syntax is:

ColumnName ColumnDataType
 [DEFAULT DefaultVal] [[NOT] NULL [ENABLE]] |
 [[NOT] NULL [ENABLE]] [DEFAULT DefaultVal]

Parameters

The column definition has the following parameters:

Parameter	Description
ColumnName	Name to be assigned to one of the columns in the new table. No two columns in the table can be given the same name. A table can have a maximum of 1000 columns. If you specify the AS SelectQuery clause, ColumnName is optional. The number of column names must match the number of columns in SelectQuery.
ColumnDataType	Type of data the column can contain. Some data types require that you indicate a length. See Data Types for the data types that can be specified. If you specify the AS SelectQuery clause, do not specify ColumnDataType.
DEFAULT DefaultVal	Indicates that if a value is not specified for the column in an INSERT statement, the default value DefaultVal is inserted into the column. The default value specified must have a type that is compatible with the data type of the column. A default value can be as long as the data type of the associated column allows. You cannot assign a default value for the ROWID data type or for columns in read-only cache groups. In addition, you cannot use a function within the DEFAULT clause. The following are the supported data types for DefaultVal: NULL Constant expression (an expression that is evaluated to a constant value) SYSDATE and GETDATE (SYSDATE and GETDATE) SYSTEM_USER (SYSTEM_USER) If the default value is one of the users, the data type of the column must be either CHAR or VARCHAR2 and the width of the column must be at least 30 characters. If you specify the AS SelectQuery clause, optionally, you can specify the DEFAULT clause on the table you are creating.
INLINE|NOT INLINE	By default, variable-length columns whose declared column length is greater than 128 bytes are stored out of line. Variable-length columns whose declared column length is less than or equal to 128 bytes are stored inline. The default behavior can be overridden during table creation through the use of the INLINE and NOT INLINE keywords. If you specify the AS SelectQuery clause, optionally, you can specify the INLINE | NOT INLINE clause on the table you are creating.
NULL	Indicates that the column can contain NULL values. If you specify the AS SelectQuery clause, optionally, you can specify NULL on the table you are creating. If you specify NULL, you cannot specify ENABLE.
NOT NULL [ENABLE]	Indicates that the column cannot contain NULL values. If NOT NULL is specified, any statement that attempts to place a NULL value in the column is rejected. If you specify the AS SelectQuery clause, optionally, you can specify NOT NULL [ENABLE] on the table you are creating. If you specify NOT NULL, you can optionally specify ENABLE. Because NOT NULL constraints are always enabled, you are not required to specify ENABLE. You can only use the keyword, ENABLE, when defining columns in the CREATE TABLE statement.
UNIQUE	A unique constraint placed on the column. No two rows in the table may have the same value for this column. TimesTen creates a unique range index to enforce uniqueness. So a column with a unique constraint can use more memory and time during execution than a column without the constraint. Cannot be used with PRIMARY KEY. If you specify the AS SelectQuery clause, optionally, you can specify UNIQUE on the table you are creating.
PRIMARY KEY	A unique NOT NULL constraint placed on the column. No two rows in the table may have the same value for this column. Cannot be used with UNIQUE. If you specify the AS SelectQuery clause, optionally, you can specify PRIMARY KEY on the table you are creating.

Description for CREATE TABLE: TimesTen Classic

• If you are planning to load your tables with data, consider creating your tables without indexes. After the data is loaded, you can then create your indexes. This reduces the time it take to load the data into the tables.

• All columns participating in the primary key are NOT NULL.

• A PRIMARY KEY that is specified in the ColumnDefinition can only be specified for one column.

• You cannot specify a PRIMARY KEY in both the ColumnDefinition clause and the PRIMARY KEY clause.

• For both primary key and foreign key constraints, duplicate column names are not allowed in the constraint column list.

• You cannot update primary key column(s) unless you update the column(s) to the same value.

• There are performance considerations when you define out of line columns instead of inline columns:

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

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

  • Deleting data is slower because TimesTen performs more reclaim and logging operations.

  • Storing a column requires less overhead.

• If ON DELETE CASCADE is specified on a foreign key constraint for a child table, a user can delete rows from a parent table for which the user has the DELETE privilege without requiring explicit DELETE privilege on the child table.

• To change the ON DELETE CASCADE triggered action, drop then redefine the foreign key constraint.

• You cannot create a table that has a foreign key referencing a cached table.

• UNIQUE column constraint and default column values are not supported with materialized views.

• Use the ALTER TABLE statement to change the representation of the primary key index for a table.

• If you specify the AS SelectQuery clause:

  • Data types and data type lengths are derived from the SelectQuery. Do not specify data types on the columns of the table you are creating.

  • TimesTen defines on columns in the new table NOT NULL constraints that were explicitly created on the corresponding columns of the selected table if SelectQuery selects the column rather than an expression containing the column.

  • NOT NULL constraints that were implicitly created by TimesTen on columns of the selected table (for example, primary keys) are carried over to the new table. You can override the NOT NULL constraint on the selected table by defining the new column as NULL. For example:

    CREATE TABLE newtable (newcol NULL) AS SELECT (col) FROM tab;
    

  • NOT INLINE/INLINE attributes are carried over to the new table.

  • Unique keys, foreign keys, indexes and column default values are not carried over to the new table.

  • If all expressions in SelectQuery are columns, rather than expressions, then you can omit the columns from the table you are creating. In this case, the name of the columns are the same as the columns in SelectQuery. If the SelectQuery contains an expression rather than a simple column reference, either specify a column alias or name the column in the CREATE TABLE statement.

  • Do not specify foreign keys on the table you are creating.

  • Do not specify the SELECT FOR UPDATE clause in SelectQuery.

  • The ORDER BY clause is not supported when you use the AS SelectQuery clause.

  • SelectQuery cannot contain set operators UNION, MINUS, INTERSECT.

  • In a replicated environment, be aware of the following.

    To include a new table, including global temporary tables, into an active standby pair when the table is created, set DDL_REPLICATION_LEVEL to 2 or greater and DDL_REPLICATION_ACTION to INCLUDE before executing the CREATE TABLE statement on the active database. In this configuration, the table is included in the active standby pair and is replicated to all databases in the replication scheme.

    If DDL_REPLICATION_ACTION is set to EXCLUDE, then the new table is not included in the active standby pair but is replicated to all databases in the replication scheme. Any DML issued on that table will not be replicated, as the table will not be part of the replication scheme. To enable DML replication for the table, you must execute the ALTER ACTIVE STANDBY PAIR ... INCLUDE TABLE statement to include the table. In this case, the table must be empty and present on all databases before executing ALTER ACTIVE STANDBY PAIR ... INCLUDE TABLE, as the table contents will be truncated when this statement is executed.

    See ALTER SESSION for more information.

• By default, a range index is created to enforce the primary key. Use the UNIQUE HASH clause to specify a hash index for the primary key.

  • If your application performs range queries using a table's primary key, then choose a range index for that table by omitting the UNIQUE HASH clause.

  • If your application performs only exact match lookups on the primary key, then a hash index may offer better response time and throughput. In such a case, specify the UNIQUE HASH clause.

• A hash index is created with a fixed size that remains constant for the life of the table or until the hash index is resized with the ALTER TABLE statement or when the index is dropped and recreated. A smaller hash index results in more hash collisions. A larger hash index reduces collisions but can waste memory. Hash key comparison is a fast operation, so a small number of hash collisions should not cause a performance problem for TimesTen.

  To ensure that your hash index is sized correctly, your application must indicate the expected size of your table with the value of the RowPages parameter of the SET PAGES clause. Compute this value by dividing the number of expected rows in your table by 256. For example, if your table has 256,000 rows, specify 1000 for the value of RowPages (256000/256=1000).

• At most 16 columns are allowed in a hash key.

• ON DELETE CASCADE is supported on detail tables of a materialized view. If you have a materialized view defined over a child table, a deletion from the parent table causes cascaded deletes in the child table. This, in turn, triggers changes in the materialized view.

• The total number of rows reported by the DELETE statement does not include rows deleted from child tables as a result of the ON DELETE CASCADE action.

• For ON DELETE CASCADE: Since different paths may lead from a parent table to a child table, the following rule is enforced:

  • Either all paths from a parent table to a child table are "delete" paths or all paths from a parent table to a child table are "do not delete" paths. Specify ON DELETE CASCADE on all child tables on the "delete" path.

  • This rule does not apply to paths from one parent to different children or from different parents to the same child.

• For ON DELETE CASCADE, the following rule is also enforced.

  • If a table is reached by a "delete" path, then all its children are also reached by a "delete" path.

• For ON DELETE CASCADE with replication, the following restrictions apply:

  • The foreign keys specified with ON DELETE CASCADE must match between the Master and subscriber for replicated tables. Checking is done at runtime. If there is an error, the receiver thread stops working.

  • All tables in the delete cascade tree have to be replicated if any table in the tree is replicated. This restriction is checked when the replication scheme is created or when a foreign key with ON DELETE CASCADE is added to one of the replication tables. If an error is found, the operation is aborted. You may be required to drop the replication scheme first before trying to change the foreign key constraint.

  • You must stop the replication agent before adding or dropping a foreign key on a replicated table.

• The data in a global temporary table is private to the current connection and does not need to be secured between users. Thus, global temporary tables do not require object privileges.

• After you have defined an aging policy for the table, you cannot change the policy from LRU to time-based or from time-based to LRU. You must first drop aging and then alter the table to add a new aging policy.

• The aging policy must be defined to change the aging state.

• For the time-based aging policy, you cannot add or modify the aging column. This is because you cannot add or modify a NOT NULL column.

• LRU and time-based aging can be combined in one system. If you use only LRU aging, the aging thread wakes up based on the cycle specified for the whole database. If you use only time-based aging, the aging thread wakes up based on an optimal frequency. This frequency is determined by the values specified in the CYCLE clause for all tables. If you use both LRU and time-based aging, then the thread wakes up based on a combined consideration of both types.

• The following rules determine if a row is accessed or referenced for LRU aging:

  • Any rows used to build the result set of a SELECT statement.

  • Any rows used to build the result set of an INSERT ... SELECT statement.

  • Any rows that are about to be updated or deleted.

• Compiled commands are marked invalid and need recompilation when you either drop LRU aging from or add LRU aging to tables that are referenced in the commands.

• Call the ttAgingScheduleNow procedure to schedule the aging process immediately regardless of the aging state.

• Aging restrictions:

  • LRU aging and time-based aging are not supported on detail tables of materialized views.

  • LRU aging and time-based aging are not supported on global temporary tables.

  • You cannot drop the column that is used for time-based aging.

  • The aging policy and aging state must be the same in all sites of replication.

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

  • For LRU aging, if a child row is not a candidate for aging, neither this child row nor its parent row are deleted. ON DELETE CASCADE settings are ignored.

  • For time-based aging, if a parent row is a candidate for aging, then all child rows are deleted. ON DELETE CASCADE (whether specified or not) is ignored.

Column-Based Compression of Tables (TimesTen Classic)

You can compress tables at the column level, which stores data more efficiently. This eliminates redundant storage of duplicate values within columns and improves the performance of SQL queries that perform full table scans.

You can define one or more columns in a table to be compressed together, which is called a compressed column group. You can define one or more compressed column groups in each table.

A dictionary table is created for each compressed column group that contains a column with all the distinct values of the compressed column group. The compressed column group now contains a pointer to the row in the dictionary table for the appropriate value. The width of this pointer can be 1, 2, or 4 bytes long depending on the maximum number of entries you defined for the dictionary table. So if the sum of the widths of the columns in a compressed column group is wider than the 1, 2, or 4 byte pointer width, and if there are a lot of duplicate values of those column values, you have reduced the amount of space used by the table.

Figure 6-1 shows the compressed column group in the table pointing to the appropriate row in the dictionary table.

Figure 6-1 Column-Based Compression

Description of "Figure 6-1 Column-Based Compression"

The dictionary table has a column of pointers to each of the distinct values. When the user configures the maximum number of distinct entries for the compressed column group, the size of the compressed column group is set as follows:

• 1 byte for a maximum number of entries of 255 (2⁸-1). When the maximum number is between 1 and 255, the dictionary size is set to 255 (2⁸-1) values and the compressed column group pointer column is 1 byte.

• 2 bytes for a maximum number of entries of 65,535 (2¹⁶-1). When the maximum number is between 256 and 65,535, the dictionary size is set to 65,535 (2¹⁶-1) values and the compressed column group pointer column is 2 bytes.

• 4 bytes for a maximum number of entries of 4,294,967,295 (2³²-1). When the maximum number is between 65,536 and 4,294,967,295, the dictionary size is set to 4,294,967,295 (2³²-1) values and the compressed column group pointer column is 4 bytes. This is the default.

Syntax: Column-Based Compression (TimesTen Classic)

The syntax for ColumnBasedCompression is:

[COMPRESS (CompressColumns [,...])]

The CompressColumns syntax is as follows:

{ColumnDefinition | (ColumnDefinition [,...])} BY DICTIONARY 
   [MAXVALUES = CompressMax]

Parameters

ColumnBasedCompression syntax has the following parameters:

Parameter	Description
COMPRESS (CompressColumns [,...])	Defines a compressed column group for a table that is enabled for compression. This can include one or more columns in the table. However, a column can be included in only one compressed column group. Only INLINE columns are supported when you specify multiple columns in a compressed column group. An out-of-line column cannot be in a multi-column compression group. Each compressed column group is limited to a maximum of 16 columns.
BY DICTIONARY	Defines a compression dictionary for each compressed column group.
MAXVALUES = CompressMax	CompressMax is the total number of distinct values in the table and sets the size for the compressed column group pointer column to 1, 2, or 4 bytes and sets the size for the maximum number of entries in the dictionary table. For the dictionary table, NULL is counted as one unique value. CompressMax takes an integer between 1 and 429497295 (232-1). The maximum size defaults to size of 232-1 if the MAXVALUES clause is omitted, which uses 4 bytes for the pointer column. An error is thrown if the value is greater than 232-1.

Description: Column-Based Compression (TimesTen Classic)

• Compressed column groups can be added at the time of table creation or added later using ALTER TABLE. You can drop a compressed column group with the ALTER TABLE statement, but you must drop the entire group.

• You can create indexes on any columns in the table and on columns that exist in separate compression column groups. However, you cannot create single column compression groups on unique columns or on single column primary keys. You also cannot create unique indexes or primary keys where all the indexes or primary keys are in the same compression group.

• LOB columns cannot be compressed.

• Compression is not supported on columns in replicated tables, cache group tables, or on global temporary tables. You cannot create a table with the CREATE TABLE AS SELECT statement when defining column-based compression for that table in that statement.

• You cannot create materialized views on tables enabled for compression.

• Column-based compression is not supported with TimesTen Scaleout.

Examples: TimesTen Classic

A range index is created on partnumber because it is the primary key.

Command> CREATE TABLE price
          (partnumber INTEGER NOT NULL PRIMARY KEY,
           vendornumber INTEGER NOT NULL,
           vendpartnum CHAR(20) NOT NULL,
           unitprice DECIMAL(10,2),
           deliverydays SMALLINT,
           discountqty SMALLINT);
Command> INDEXES price;
Indexes on table SAMPLEUSER.PRICE:
 PRICE: unique range index on columns:
    PARTNUMBER
  1 index found.
1 index found on 1 table.

A hash index is created on column clubname, the primary key.

CREATE TABLE recreation.clubs
(clubname CHAR(15) NOT NULL PRIMARY KEY,
 clubphone SMALLINT,
 activity CHAR(18))
UNIQUE HASH ON (clubname) PAGES = 30;

A range index is created on the two columns membername and club because together they form the primary key.

Command> CREATE TABLE recreation.members
          (membername CHAR(20) NOT NULL,
           club CHAR(15) NOT NULL,
           memberphone SMALLINT,
           PRIMARY KEY (membername, club));
Command> INDEXES recreation.members;
Indexes on table RECREATION.MEMBERS:
  MEMBERS: unique range index on columns:
    MEMBERNAME
    CLUB
  1 index found on 1 table.

No hash index is created on the table recreation.events.

CREATE TABLE recreation.events
(sponsorclub CHAR(15),
 event CHAR(30),
 coordinator CHAR(20),
 results VARBINARY(10000));

A hash index is created on the column vendornumber.

CREATE TABLE purchasing.vendors
(vendornumber INTEGER NOT NULL PRIMARY KEY,
 vendorname CHAR(30) NOT NULL,
 contactname CHAR(30),
 phonenumber CHAR(15),
 vendorstreet CHAR(30) NOT NULL,
 vendorcity CHAR(20) NOT NULL,
 vendorstate CHAR(2) NOT NULL,
 vendorzipcode CHAR(10) NOT NULL,
 vendorremarks VARCHAR(60))
UNIQUE HASH ON (vendornumber) PAGES = 101;

A hash index is created on the columns membername and club because together they form the primary key.

CREATE TABLE recreation.members
    (membername CHAR(20) NOT NULL,
        club CHAR(15) NOT NULL,
        memberphone SMALLINT,
        PRIMARY KEY (membername, club))
    UNIQUE HASH ON (membername, club) PAGES = 100;

A hash index is created on the columns firstname and lastname because together they form the primary key in the table authors. A foreign key is created on the columns authorfirstname and authorlastname in the table books that references the primary key in the table authors.

CREATE TABLE authors
    (firstname VARCHAR(255) NOT NULL,
        lastname VARCHAR(255) NOT NULL,
        description VARCHAR(2000),
        PRIMARY KEY (firstname, lastname))
    UNIQUE HASH ON (firstname, lastname) PAGES=20;
CREATE TABLE books
    (title VARCHAR(100),
    authorfirstname VARCHAR(255),
    authorlastname VARCHAR(255),
    price DECIMAL(5,2),
    FOREIGN KEY (authorfirstname, authorlastname) 
    REFERENCES authors(firstname, lastname));

The following statement overrides the default character of VARCHAR columns and creates a table where one VARCHAR (10) column is NOT INLINE and one VARCHAR (144) is INLINE.

CREATE TABLE t1
    (c1 VARCHAR(10) NOT INLINE NOT NULL,
    c2 VARCHAR(144) INLINE NOT NULL);

The following statement creates a table with a UNIQUE column for book titles.

CREATE TABLE books
    (title VARCHAR(100) UNIQUE,
        authorfirstname VARCHAR(255),
        authorlastname VARCHAR(255),
        price DECIMAL(5,2),
        FOREIGN KEY (authorfirstname, authorlastname)
        REFERENCES authors(firstname, lastname));

The following statement creates a table with a default value of 1 on column x1 and a default value of SYSDATE on column d.

CREATE TABLE t1
    (x1 INT DEFAULT 1, d TIMESTAMP DEFAULT SYSDATE);

This example creates the rangex table and defines col1 as the primary key. A range index is created by default.

Command> CREATE TABLE rangex (col1 TT_INTEGER PRIMARY KEY);
Command> INDEXES rangex;
Indexes on table SAMPLEUSER.RANGEX:
  RANGEX: unique range index on columns:
    COL1
  1 index found
1 index found on 1 table.

The following statement illustrates the use of the ON DELETE CASCADE clause for parent/child tables of the HR schema. Tables with foreign keys have been altered to enable ON DELETE CASCADE.

ALTER TABLE countries
ADD CONSTRAINT countr_reg_fk
         FOREIGN KEY (region_id)
           REFERENCES regions(region_id) ON DELETE CASCADE;
ALTER TABLE locations
     ADD CONSTRAINT loc_c_id_fk
          FOREIGN KEY (country_id)
          	  REFERENCES countries(country_id) ON DELETE CASCADE;
ALTER TABLE departments
     ADD CONSTRAINT dept_loc_fk
         FOREIGN KEY (location_id)
           REFERENCES locations (location_id) ON DELETE CASCADE;
ALTER TABLE employees
     ADD CONSTRAINT     emp_dept_fk
         FOREIGN KEY (department_id)
           REFERENCES departments ON DELETE CASCADE;
ALTER TABLE employees
     ADD CONSTRAINT     emp_job_fk
         FOREIGN KEY (job_id)
           REFERENCES jobs (job_id);
ALTER TABLE job_history
     ADD CONSTRAINT     jhist_job_fk
         FOREIGN KEY (job_id)
           REFERENCES jobs;
ALTER TABLE job_history
     ADD CONSTRAINT     jhist_emp_fk
         FOREIGN KEY (employee_id)
           REFERENCES employees ON DELETE CASCADE;
ALTER TABLE job_history
     ADD CONSTRAINT     jhist_dept_fk
         FOREIGN KEY (department_id)
           REFERENCES departments ON DELETE CASCADE;
     ;

This example shows how time resolution works with aging.

If lifetime is three days (resolution is in days):

• If (SYSDATE - ColumnValue) <= 3, do not age.

• If (SYSDATE - ColumnValue) > 3, then the row is a candidate for aging.

• If (SYSDATE - ColumnValue) = 3 days, 22 hours, then the row is not aged out if you specified a lifetime of three days. The row would be aged out if you had specified a lifetime of 72 hours.

This example creates a table with LRU aging. Aging state is ON by default.

CREATE TABLE agingdemo
       (agingid NUMBER NOT NULL PRIMARY KEY,
        name  VARCHAR2 (20)
       )
       AGING LRU;
Command> DESCRIBE agingdemo;
Table USER.AGINGDEMO:
  Columns:
     *AGINGID NUMBER NOT NULL
     NAME VARCHAR2 (20) INLINE
     AGING LRU ON
1 table found.
(primary key columns are indicated with *)

This example creates a table with time-based aging. Lifetime is three days. Cycle is not specified, so the default is five minutes. Aging state is OFF.

CREATE TABLE agingdemo2
       (agingid NUMBER NOT NULL PRIMARY KEY,
        name  VARCHAR2 (20),
        agingcolumn TIMESTAMP NOT NULL
        )
        AGING USE agingcolumn LIFETIME 3 DAYS OFF;
Command> DESCRIBE agingdemo2;
Table USER.AGINGDEMO2:
  Columns:
     *AGINGID NUMBER NOT NULL
     NAME VARCHAR2 (20) INLINE
     AGINGCOLUMN TIMESTAMP (6) NOT NULL
  Aging use AGINGCOLUMN lifetime 3 days cycle 5 minutes off
1 table found.
(primary key columns are indicated with *)

This example generates an error message. It illustrates that after you create an aging policy, you cannot change it. You must drop aging and redefine aging.

CREATE TABLE agingdemo2
        (agingid NUMBER NOT NULL PRIMARY KEY,
        name  VARCHAR2 (20),
        agingcolumn TIMESTAMP NOT NULL
        )
        AGING USE agingcolumn LIFETIME 3 DAYS OFF;
ALTER TABLE agingdemo2
        ADD AGING LRU;
 2980: Cannot add aging policy to a table with an existing aging policy. Have to
drop the old aging first
The command failed.
DROP aging on the table and redefine with LRU aging.
ALTER TABLE agingdemo2
        DROP AGING;
ALTER TABLE agingdemo2
        ADD AGING LRU;
Command> DESCRIBE agingdemo2;
Table USER.AGINGDEMO2:
  Columns:
   *AGINGID                         NUMBER NOT NULL
    NAME                            VARCHAR2 (20) INLINE
    AGINGCOLUMN                     TIMESTAMP (6) NOT NULL
  Aging lru on
1 table found.
(primary key columns are indicated with *)

Attempt to create a table with time-based aging. Define aging column with data type TT_DATE and LIFETIME 3 hours. An error is generated because the LIFETIME unit must be expressed as DAYS.

Command> CREATE TABLE aging1 (col1 TT_INTEGER PRIMARY KEY, 
         col2 TT_DATE NOT NULL) AGING USE col2 LIFETIME 3 HOURS;
 2977: Only DAY lifetime unit is allowed with a TT_DATE column
The command failed.

Use AS SelectQuery clause to create the table emp. Select last_name from the employees table where employee_id between 100 and 105. You see six rows inserted into emp. First issue the SELECT statement to see rows that should be returned.

Command> SELECT last_name FROM employees 
         WHERE employee_id BETWEEN 100 AND 105;
< King >
< Kochhar >
< De Haan >
< Hunold >
< Ernst >
< Austin >
6 rows found.
Command> CREATE TABLE emp AS SELECT last_name FROM employees
         WHERE employee_id BETWEEN 100 AND 105;
6 rows inserted.
Command> SELECT * FROM emp;
< King >
< Kochhar >
< De Haan >
< Hunold >
< Ernst >
< Austin >
6 rows found.

Use AS SelectQuery to create table totalsal. Sum salary and insert result into totalsalary. Define alias s for SelectQuery expression.

Command> CREATE TABLE totalsal AS SELECT SUM (salary) s FROM employees;
1 row inserted.
Command> SELECT * FROM totalsal;
< 691400 >
1 row found.

Use AS SelectQuery to create table defined with column commission_pct. Set default to .3. First describe table employees to show that column commission_pct is of type NUMBER (2,2). For table c_pct, column commission_pct inherits type NUMBER (2,2) from column commission_pct of employees table.

Command> DESCRIBE employees;
Table SAMPLEUSER.EMPLOYEES:
  Columns:
   *EMPLOYEE_ID                     NUMBER (6) NOT NULL
    FIRST_NAME                      VARCHAR2 (20) INLINE
    LAST_NAME                       VARCHAR2 (25) INLINE NOT NULL
    EMAIL                           VARCHAR2 (25) INLINE UNIQUE NOT NULL
    PHONE_NUMBER                    VARCHAR2 (20) INLINE
    HIRE_DATE                       DATE NOT NULL
    JOB_ID                          VARCHAR2 (10) INLINE NOT NULL
    SALARY                          NUMBER (8,2)
    COMMISSION_PCT                  NUMBER (2,2)
    MANAGER_ID                      NUMBER (6)
    DEPARTMENT_ID                   NUMBER (4)

1 table found.
(primary key columns are indicated with *)
Command> CREATE TABLE c_pct (commission_pct DEFAULT .3) AS SELECT
         commission_pct FROM employees;
107 rows inserted.
Command> DESCRIBE c_pct;

Table SAMPLEUSER.C_PCT:
  Columns:
    COMMISSION_PCT                 NUMBER (2,2) DEFAULT .3

1 table found.
(primary key columns are indicated with *)

The following example creates the employees table where the job_id is compressed.

Command> CREATE TABLE EMPLOYEES
          (EMPLOYEE_ID NUMBER (6) PRIMARY KEY, 
           FIRST_NAME VARCHAR2(20), 
           LAST_NAME VARCHAR2(25) NOT NULL, 
           EMAIL VARCHAR2(25) NOT NULL, 
           PHONE_NUMBER VARCHAR2(20),
           HIRE_DATE DATE NOT NULL, 
           JOB_ID VARCHAR2(10) NOT NULL, 
           SALARY NUMBER (8,2), 
           COMMISSION_PCT NUMBER (2,2), 
           MANAGER_ID NUMBER(6), 
           DEPARTMENT_ID NUMBER(4)) 
          COMPRESS (JOB_ID BY DICTIONARY);

Command> DESCRIBE EMPLOYEES;
 
Table MYSCHEMA.EMPLOYEES:
  Columns:
   *EMPLOYEE_ID                     NUMBER (6) NOT NULL
    FIRST_NAME                      VARCHAR2 (20) INLINE
    LAST_NAME                       VARCHAR2 (25) INLINE NOT NULL
    EMAIL                           VARCHAR2 (25) INLINE NOT NULL
    PHONE_NUMBER                    VARCHAR2 (20) INLINE
    HIRE_DATE                       DATE NOT NULL
    JOB_ID                          VARCHAR2 (10) INLINE NOT NULL
    SALARY                          NUMBER (8,2)
    COMMISSION_PCT                  NUMBER (2,2)
    MANAGER_ID                      NUMBER (6)
    DEPARTMENT_ID                   NUMBER (4)
  COMPRESS ( JOB_ID BY DICTIONARY )
 
1 table found.
(primary key columns are indicated with *)

The following example shows that there are three dictionary table sizes. The value you specify for the maximum number of entries is rounded up to the next size. For example, specifying 400 as the maximum number of job IDs creates a dictionary table that can have at most 65535 entries. The default size of 2³²-1 is not shown in the DESCRIBE output.

Command> CREATE TABLE employees 
          (employee_id NUMBER(6) PRIMARY KEY, 
           first_name VARCHAR2(20), 
           last_name VARCHAR2(25), 
           email VARCHAR2(25) NOT NULL, 
           job_id VARCHAR2(10) NOT NULL, 
           manager_id NUMBER(6), 
           department_id NUMBER(4)) 
          COMPRESS (last_name BY DICTIONARY MAXVALUES=70000, 
             job_id BY DICTIONARY MAXVALUES=400, 
             department_id BY DICTIONARY MAXVALUES=100);

Command> DESCRIBE employees;
Table MYSCHEMA.EMPLOYEES:
  Columns:
   *EMPLOYEE_ID                     NUMBER (6) NOT NULL
    FIRST_NAME                      VARCHAR2 (20) INLINE
    LAST_NAME                       VARCHAR2 (25) INLINE
    EMAILS                          VARCHAR2 (25) INLINE NOT NULL
    JOB_ID                          VARCHAR2 (10) INLINE NOT NULL
    MANAGER_ID                      NUMBER (6)
    DEPARTMENT_ID                   NUMBER (4)
  COMPRESS ( LAST_NAME BY DICTIONARY,
             JOB_ID BY DICTIONARY MAXVALUES=65535,
             DEPARTMENT_ID BY DICTIONARY MAXVALUES=255 )
 
1 table found.
(primary key columns are indicated with *)

See also:

• ALTER TABLE
• DROP TABLE
• TRUNCATE TABLE
• UPDATE