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.

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

SQL Syntax for CREATE TABLE

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

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 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 (324) or a `LIFETIME` of 432 minutes (324*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

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.

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

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

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

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

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 (2³²-1). The maximum size defaults to size of 2³²-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 2³²-1.

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 (2³²-1).

The maximum size defaults to size of 2³²-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 2³²-1.

Description: Column-Based Compression

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.

Create Table 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 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 *)