Skip Headers
Oracle® TimesTen In-Memory Database SQL Reference
Release 11.2.1

Part Number E13070-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

CREATE TABLE

The CREATE TABLE statement defines a table.

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.

SQL syntax

The syntax for a persistent table is:

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

The syntax for a 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

The CREATE TABLE statement has the parameters:

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 table owner names must always match TimesTen table owner names.

For rules on creating names, see "Basic names".

GLOBAL TEMPORARY Specifies that the table being created is a 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 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.

Temporary tables cannot be used as part of a cache group or a replication scheme. Temporary tables are automatically excluded when DATASTORE level replication is defined.

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. See "Column Definition".

If you specify the AS SelectQuery clause, ColumnDefinition is optional.

ColumnName Names of the columns that form 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, then 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.
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. Only unique hash indexes are created. 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.

PrimaryPages Specifies the expected number of pages in the table. This n-umber affects the number of buckets that are allocated for the table's hash index. The minimum is 1. If your estimate is too small, performance is 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 procedure. LRU attributes are not defined at the SQL level.

For more information about LRU aging, see "Implementing aging in your tables" in Oracle TimesTen In-Memory Database Operations Guide.

[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 (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 aging in your tables" in Oracle TimesTen In-Memory Database Operations Guide.

LIFETIME Num1 {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 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 {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 aging is continuous and the aging thread never sleeps.

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.



Column Definition

SQL syntax

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

Parameters

The column definition has the 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. You can define a maximum of 255 columns in a table.

If you specify the AS SelectQuery clause, ColumnName is optional. The number of column names must match the number of columns in SelectQuery.

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 compatible type with the column's data type. A default value can be as long as the data type of the associated column allows.

Legal data types for DefaultVal can be one of:

If the default value is one of the users, the column's data type must be either CHAR or VARCHAR2 and the column's width 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.

ColumnDataType Type of data the column can contain. Some data types require that you indicate a length. See Chapter 1, "Data Types" for the data types that can be specified.

If you specify the AS SelectQuery clause, do not specify ColumnDataType.

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.

NOT NULL 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 on the table you are creating.

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. This means that 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

Examples

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

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.
1 table found.

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

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 3 days (resolution is in days):

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 3 days. Cycle is not specified, so the default is 5 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 6 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 employee_id 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 *)

See also


ALTER TABLE
DROP TABLE
TRUNCATE TABLE
UPDATE