ALTER TABLE

The ALTER TABLE statement changes an existing table definition.

The ALTER 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:

Required Privilege

No privilege is required for the table owner.

ALTER ANY TABLE for another user's table.

For ALTER TABLE...ADD FOREIGN KEY, the owner of the altered table must have the REFERENCES privilege on the table referenced by the foreign key clause.

After reviewing this section, see:

ALTER TABLE: Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout. Column-based compression and aging are not supported.

See:

ALTER TABLE: Usage with TimesTen Classic

See:

SQL Syntax for ALTER TABLE: TimesTen Scaleout

To change the distribution key in TimesTen Scaleout:

ALTER TABLE [Owner.]TableName DistributionClause;

To add a primary key constraint and optionally specify a global or local index:

Note: The (CreateIndexStmt) is the clause used to represent 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. See "CREATE INDEX" for details. 

ALTER TABLE [Owner.]TableName ADD CONSTRAINT ConstraintName
  PRIMARY KEY (ColumnName [,... ]) [(UsingIndexClause2)];

UsingIndexClause2::= USING INDEX {GLOBAL|LOCAL} [USE HASH INDEX PAGES=RowPages|CURRENT]|
USING INDEX (CreateIndexStmt)

Note:

You cannot use ALTER TABLE to drop a primary key constraint. To drop the constraint, you must drop and recreate the table.

To add a unique constraint on a column and optionally specify a global or local index:

Note: The (CreateIndexStmt) is the clause used to represent the TimesTen CREATE INDEX statement. The parentheses ( ) are required. You must create a unique index as that is the requirement for a unique constraint. See CREATE INDEX for details. 

ALTER TABLE Owner.]TableName
ADD UNIQUE (ColumnName)
[UsingIndexClause1];

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

To add one column:

ALTER TABLE [Owner.]TableName 
  ADD [COLUMN] ColumnName ColumnDataType
    [DEFAULT DefaultVal] [[NOT] INLINE] [UNIQUE] [NULL]
  [COMPRESS (CompressColumns [,...])];

To add multiple columns:

ALTER TABLE [Owner.]TableName 
 ADD (ColumnName ColumnDataType 
     [DEFAULT DefaultVal] [[NOT] INLINE] [UNIQUE] [NULL] [,... ] );

To add a NOT NULL column (note that the DEFAULT clause is required): 

ALTER TABLE [Owner.]TableName
  ADD [COLUMN] ColumnName ColumnDataType
    NOT NULL [ENABLE] DEFAULT DefaultVal [[NOT] INLINE] [UNIQUE];

To add multiple NOT NULL columns (note that the DEFAULT clause is required): 

ALTER TABLE [Owner.]TableName
ADD (ColumnName ColumnDataType
  NOT NULL [ENABLE] DEFAULT DefaultVal [[NOT] INLINE] [UNIQUE] [,...]);

To remove columns. 

ALTER TABLE [Owner.]TableName 
DROP {[COLUMN] ColumnName | (ColumnName [,... ] )};

To add a foreign key and optionally add ON DELETE CASCADE: 

ALTER TABLE [Owner.]TableName 
ADD [CONSTRAINT ForeignKeyName] 
  FOREIGN KEY (ColumnName [,...]) REFERENCES RefTableName
    [(ColumnName [,...])] [ON DELETE CASCADE];

To remove a foreign key:

ALTER TABLE [Owner.]TableName 
DROP CONSTRAINT ForeignKeyName;

To resize a hash index:

ALTER TABLE [Owner.]TableName SET PAGES = RowPages | CURRENT;

To change the primary key to use a hash index:

ALTER TABLE [Owner.]TableName
USE HASH INDEX PAGES = RowPages | CURRENT;

To change the primary key to use a range index with the USE RANGE INDEX clause: 

ALTER TABLE [Owner.]TableName USE RANGE INDEX;

To change the default value of a column:

ALTER TABLE [Owner.]TableName
MODIFY (ColumnName DEFAULT DefaultVal);

To drop a unique constraint on a column:

ALTER TABLE Owner.]TableName DROP UNIQUE (ColumnName);

To remove the default value of a column that is nullable, by changing it to NULL: 

ALTER TABLE [Owner.]TableName MODIFY (ColumnName DEFAULT NULL);

Parameters for ALTER TABLE ADD CONSTRAINT PRIMARY KEY: TimesTen Scaleout

Parameter Description
ALTER TABLE [Owner.]TableName Start of ALTER TABLE statement. Name of table required. Owner i optional.
ADD CONSTRAINT ConstraintName PRIMARY KEY Clause indicating that the table is to be altered by adding a primary key constraint. ConstraintName is the name of the constraint. Once you add the primary key constraint, you cannot drop it. You must drop the table.
(ColumnName [,…]) (ColumnName) is required and specifies the column(s) to use for the primary key constraint.
[UsingIndexClause2] UsingIndexClause2 is optional and is described in the remainder of this table. You cannot specify two USING INDEX clauses in the ALTER TABLE definition.
USING INDEX {GLOBAL|LOCAL} Part of [UsingIndexClause2]: If specified, indicates if a global or local index is to be created for the primary key.
USE HASH INDEX PAGES = RowPages|CURRENT Part of the USING INDEX {GLOBAL|LOCAL} clause and is optional. If specified, indicates a unique hash index is to be created for the primary key. If not specified, a unique range index is created. Can be used for both global and local indexes.

The PAGES clause is required and enables you to specify the expected page count value for the table.

If you specify RowPages, the number of pages is used to calculate the page count value. To determine the value for RowPages, divide the number of expected rows in your table by 256. For example, if your table has 256,000 rows, specify 1000 for RowPages (256000/256=1000).

The value for RowPages must be a positive constant and must be greater than 0.

If you specify CURRENT, the current number of rows in the table is used to calculate the page count value.

TimesTen recommends that you do not specify PAGES=CURRENT if there are no rows in your table.

USING INDEX (CreateIndexStmt) Part of the [UsingIndexClause2] 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. See CREATE INDEX for information on the CREATE INDEX statement.

Parameters for ALTER TABLE ADD UNIQUE CONSTRAINT: TimesTen Scaleout

Parameter Description
ALTER TABLE [Owner.]TableName Start of ALTER TABLE statement. Name of table required. Owner is optional.
ADD UNIQUE (ColumnName [,…]) Clause indicating that the table is to be altered by adding a unique constraint. (ColumnName) is required and specifies the column(s) to be used for the unique constraint.
[UsingIndexClause1] UsingIndexClause1 is optional and is described in the remainder of this table. You cannot specify two USING INDEX clauses in the ALTER 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 unique constraint.
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 unique constraint. See CREATE INDEX for information on the CREATE INDEX statement.

Additional Parameters for ALTER TABLE: TimesTen Scaleout

Parameter Description

[Owner.] TableName

Identifies the table to be altered.

DistributionClause

See "CREATE TABLE" for information on syntax.

UNIQUE

Specifies that in the column ColumnName each row must contain a unique value.

MODIFY

Specifies that an attribute of a given column is to be changed to a new value.

DEFAULT [DefaultVal |NULL]

Specifies that the column has a default value, DefaultVal. If NULL, specifies that the default value of the columns is to be dropped. If a column with a default value of SYSDATE is added, the value of the column of the existing rows only is the system date at the time the column was added. If the default value is one of the USER functions the column value is the user value of the session that executed the ALTER TABLE statement. Currently, you cannot assign a default value for the ROWID data type.

Altering the default value of a column has no impact on existing rows.

Note: To add a NOT NULL column to a table that is part of a replication scheme, DDL_REPLICATON_LEVEL must be 3 or greater.

ColumnName

Name of the column participating in the ALTER TABLE statement. A new column cannot have the same name as an existing column or another new column. If you add a NOT NULL column, you must include the DEFAULT clause.

ColumnDataType

Type of the column to be added. Some types require additional parameters. See "Data Types" for the data types that can be specified.

NOT NULL [ENABLE]

If you add a column, you can specify NOT NULL. If you specify NOT NULL, then you must include the DEFAULT clause. Optionally, you can specify ENABLE after the NOT NULL clause. Because NOT NULL constraints are always enabled, you are not required to specify ENABLE.

INLINE|NOT INLINE

By default, variable-length columns whose declared column length is > 128 bytes are stored out of line. Variable-length columns whose declared column length is <= 128 bytes are stored inline. The default behavior can be overridden during table creation through the use of the INLINE and NOT INLINE keywords.

CONSTRAINT

Specifies that a foreign key is to be dropped. Optionally specifies that an added foreign key is named by the user.
DROP UNIQUE (ColumnName) Indicates that unique constraint is to dropped. ColumnName is the name of the constraint.

ForeignKeyName

Name of the foreign key to be added or dropped. All foreign keys are assigned a default name by the system if the name was not specified by the user. Either the user-provided name or system name can be specified in the DROP FOREIGN KEY clause.

FOREIGN KEY

Specifies that a foreign key is to be added.

REFERENCES

Specifies that the foreign key references another table.

RefTableName

The name of the table that the foreign key references.

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

USE HASH INDEX PAGES = RowPages | CURRENT

Changes primary key to use a hash index. If the primary key already uses a hash index, then this clause is equivalent to the SET PAGES clause.

USE RANGE INDEX

Changes primary key to use a range index. If the primary key already uses a range index, TimesTen ignores this clause.

SET PAGES = RowPages | CURRENT

Resizes the hash index to reflect the expected number of pages in the table. If you specify CURRENT, the current number of rows in the table is used to calculate the page count value. If you specify RowPages, the number of pages is used. To determine the value for RowPages, divide the number of expected rows in your table by 256. For example, if your table has 256,000 rows, specify 1000 for RowPages (256000/256=1000).

The value for RowPages must be a positive constant and must be greater than 0.

TimesTen recommends that you do not specify PAGES=CURRENT if there are no rows in your table.

If your estimate is too small, performance may be degraded. See Column Definition: TimesTen Scaleout for more information on hash indexes.

Description for ALTER TABLE ADD PRIMARY KEY: TimesTen Scaleout

You have the option of specifying an additional clause after the PRIMARY KEY clause in your ALTER 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. 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 ALTER TABLE definition. Specify one clause or the other or specify neither.

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

Description for ALTER TABLE ADD UNIQUE: TimesTen Scaleout

You have the option of specifying an additional clause after the UNIQUE clause in your ALTER TABLE definition. This clause enables you to specify a global or local index for the unique 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.

  • 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 unique constraint. 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 ALTER TABLE definition. Specify one clause or the other or specify neither.

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

Additional ALTER TABLE Information: TimesTen Scaleout

  • You can alter tables to change defaults or add and drop columns and constraints. However, you cannot change the distribution scheme unless the table is empty. In addition, you cannot drop a constraint that is named in the DISTRIBUTE BY REFERENCE clause. See CREATE TABLE for information on the distribution schemes. See Altering Tables in Oracle TimesTen In-Memory Database Scaleout User's Guide for more information.

  • The ALTER TABLE statement cannot be used to alter a temporary table.

  • The ALTER TABLE ADD [COLUMN] ColumnName statement adds one or more new columns to an existing table. When you add one or more columns, the new columns are added to the end of all existing rows of the table in one new partition.

  • Columns referenced by materialized views cannot be dropped.

  • You cannot use the ALTER TABLE statement to add a column, drop a column, or add a constraint for cache group tables.

  • Only one partition is added to the table per statement regardless of the number of columns added.

  • You can ALTER a table to add a NOT NULL column with a default value. The DEFAULT clause is required.

    You cannot use the column as a primary key column. Specifically, you cannot specify the column in the statement: ALTER TABLE ADD ConstraintName PRIMARY KEY (ColumnName [,...]).

  • NULL is the initial value for all added columns, unless a default value is specified for the new column.

  • The total number of columns in the table cannot exceed 1000. In addition, the total number of partitions in a table cannot exceed 1000, one of which is used by TimesTen.

  • Do not specify the ADD CONSTRAINT ... PRIMARY KEY clause on a global temporary table.

  • As the result of an ALTER TABLE ADD statement, an additional read occurs for each new partition during queries. Therefore, altered tables may have slightly degraded performance. The performance can only by restored by dropping and recreating the table, or by using the ttMigrate create -c -relaxedUpgrade command, and restoring the table using the ttRestore -r -relaxedUpgrade command. Dropping the added column does not recover the lost performance or decrease the number of partitions.

  • When you use the ALTER TABLE DROP statement to remove one or more columns from an existing table, dropped columns are removed from all current rows of the table. Subsequent SQL statements must not attempt to make any use of the dropped columns. You cannot drop columns that are in the table's primary key. You cannot drop columns that are in any of the table's foreign keys until you have dropped all foreign keys. You cannot drop columns that are indexed until all indexes on the column have been dropped. ALTER TABLE cannot be used to drop all of the columns of a table. Use DROP TABLE instead.

  • When a column is dropped from a table, all commands referencing that table need to be recompiled. An error may result at recompilation time if a dropped column was referenced. The application must re-prepare those commands, and rebuild any parameters and result columns. When a column is added to a table, the commands that contain a SELECT * statement are invalidated. Only these commands must be re-prepared. All other commands continue to work as expected.

  • When you drop a column, the column space is not freed.

  • When you add a UNIQUE constraint, there is overhead incurred (in terms of additional space and additional time). This is because an index is created to maintain the UNIQUE constraint. You cannot use the DROP INDEX statement to drop an index used to maintain the UNIQUE constraint.

  • A UNIQUE constraint and its associated index cannot be dropped if it is being used as a unique index on a replicated table.

  • Use ALTER TABLE...USE RANGE INDEX if your application performs range queries over a table's primary key.

  • Use ALTER TABLE...USE HASH INDEX if your application performs exact match lookups on a table's primary key.

  • An error is generated if a table has no primary key and either the USE HASH INDEX clause or the USE RANGE INDEX clause is specified.

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

  • 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, a second 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 ALTER TABLE ADD/DROP CONSTRAINT statement has the following restrictions:

    • When a foreign key is dropped, TimesTen also drops the index associated with the foreign key. Attempting to drop an index associated with a foreign key using the regular DROP INDEX statement results in an error.

    • Foreign keys cannot be added or dropped on views or temporary tables.

    • You cannot use ALTER TABLE to drop a primary key constraint. You would have to drop and recreate the table in order to drop the constraint.

Examples: Add Primary Key Constraint Using Global Indexes in TimesTen Scaleout

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

Create a table. Alter the table and add a primary key constraint. Specify the USING INDEX GLOBAL clause. Drop the table.
Command> CREATE TABLE mytab1 (c TT_INTEGER NOT NULL, b TT_INTEGER NOT NULL, 
           a TT_INTEGER NOT NULL) DISTRIBUTE BY HASH (a,b);
Command> ALTER TABLE mytab1 ADD CONSTRAINT pk PRIMARY KEY (c,b) USING INDEX GLOBAL;
Command> indexes mytab1;

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

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

Create a table. Alter the table adding a primary key constraint. Specify the USING INDEX GLOBAL with the USE HASH INDEX PAGES clause. Drop the table. 

Command> CREATE TABLE mytab1 (c TT_INTEGER NOT NULL, b TT_INTEGER NOT NULL, 
           a TT_INTEGER NOT NULL) DISTRIBUTE BY HASH (a,b);
Command> ALTER TABLE mytab1 ADD CONSTRAINT pk PRIMARY KEY (c,b) 
           USING INDEX GLOBAL USE HASH INDEX PAGES =200;
Command> INDEXES mytab1;

Indexes on table SAMPLEUSER.MYTAB1:
  PK: global unique hash index on columns:
    C
    B
  1 index found.

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

Create a table. Alter the table adding a primary key constraint. Specify the USING INDEX (CreateIndexStmt) clause. The (CreateIndexStmt) clause is the TimesTen CREATE INDEX statement. See CREATE INDEX for information on this statement. 

Command> CREATE TABLE mytab1 (c TT_INTEGER NOT NULL, b TT_INTEGER NOT NULL, 
           a TT_INTEGER NOT NULL) DISTRIBUTE BY HASH (a,b);
Command> ALTER TABLE mytab1 ADD CONSTRAINT pk PRIMARY KEY (c,b) 
           USING INDEX (CREATE GLOBAL UNIQUE HASH INDEX myglobalix ON mytab1 (c,b) PAGES =200);
Command> indexes mytab1;

Indexes on table SAMPLEUSER.MYTAB1:
  MYGLOBALIX: global unique hash index on columns:
    C
    B
  1 index found.

1 index found on 1 table.
Command> DROP TABLE mytab1;
This example illustrates that you cannot use the USING INDEX GLOBAL|LOCAL clause with the USING INDEX (CreateIndexStmt) clause.
Command> CREATE TABLE mytab1 (c TT_INTEGER NOT NULL, b TT_INTEGER NOT NULL, 
           a TT_INTEGER NOT NULL) DISTRIBUTE BY HASH (a,b);
Command> ALTER TABLE mytab1 ADD CONSTRAINT pk PRIMARY KEY (c,b) 
           USING INDEX GLOBAL USE HASH INDEX PAGES = 200 
           USING INDEX (CREATE GLOBAL UNIQUE HASH INDEX myglobalix ON mytab1 (c,b) PAGES =200);
 1001: Syntax error in SQL statement  before or at: "USING", character position: 102
...USING INDEX GLOBAL USE HASH INDEX PAGES = 200 USING INDEX (CREATE G...
                                                 ^^^^^
The command failed.

Examples: Add Unique Constraint Using Global Indexes in TimesTen Scaleout

These examples show various uses of the syntax for using global indexes with ALTER TABLE ADD UNIQUE CONSTRAINT.

Create a table. Alter the table adding a unique constraint. Drop the table. Create the table again adding a unique constraint and specifying the USING INDEX GLOBAL clause. 

Command> CREATE TABLE mytab1 (c TT_INTEGER NOT NULL, b TT_INTEGER NOT NULL, 
           a TT_INTEGER NOT NULL) DISTRIBUTE BY HASH (a,b);
Command> ALTER TABLE mytab1 ADD UNIQUE (a);
Command> indexes mytab1;

Indexes on table SAMPLEUSER.MYTAB1:
  TTUNIQUE_6E6: unique range index on columns:
    A
  1 index found.

1 index found on 1 table.
Command> DROP TABLE mytab1;
Command> CREATE TABLE mytab1 (c TT_INTEGER NOT NULL, b TT_INTEGER NOT NULL, 
           a TT_INTEGER NOT NULL) DISTRIBUTE BY HASH (a,b);
Command> ALTER TABLE mytab1 ADD UNIQUE (a) USING INDEX GLOBAL;
Command> indexes mytab1;

Indexes on table SAMPLEUSER.MYTAB1:
  $GUA8C5B4ECE6D8: global unique range index on columns:
    A
  1 index found.

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

Create a table. Alter the table adding a unique constraint and use the USING INDEX (CreateIndexStmt) clause to create a local unique index. Alter the table a second time adding another unique constaint. Use the USING INDEX (CreateIndexStmt) clause to create a global unique index. 

Command> CREATE TABLE mytab1 (c TT_INTEGER NOT NULL, b TT_INTEGER NOT NULL, 
           a TT_INTEGER NOT NULL) DISTRIBUTE BY HASH (a,b);
Command> ALTER TABLE mytab1 ADD UNIQUE (b) USING INDEX (CREATE UNIQUE INDEX myuniqueidxB ON mytab1 (b));
Command> indexes mytab1;

Indexes on table SAMPLEUSER.MYTAB1:
  MYUNIQUEIDXB: unique range index on columns:
    B
  1 index found.

1 index found on 1 table.
Command> ALTER TABLE mytab1 ADD UNIQUE (c) USING INDEX (CREATE GLOBAL UNIQUE INDEX myuniqueidxC ON mytab1 (c));
Command> indexes mytab1;

Indexes on table SAMPLEUSER.MYTAB1:
  MYUNIQUEIDXB: unique range index on columns:
    B
  MYUNIQUEIDXC: global unique range index on columns:
    C
  2 indexes found.

2 indexes found on 1 table.
Command> DROP TABLE mytab1;

Additional Examples for ALTER TABLE: TimesTen Scaleout

Table 6-6 shows the rules associated with altering tables. Supporting examples follow.

Table 6-6 ALTER TABLE Rules

ALTER Statement Comment 
ALTER TABLE t1 ADD CONSTRAINT c1
 PRIMARY KEY (p);

The primary key constraint is added to the table. The distribution key is not changed.

 
CREATE TABLE t1 (c1 NUMBER, 
 c2 VARCHAR2 (10));

ALTER TABLE t1 
 DISTRIBUTE BY HASH (c1);

The operation succeeds if the table is empty. If the table is not empty, the operation fails because the distribution key cannot be changed on tables that are not empty. 

ALTER TABLE t1 ADD CONSTRAINT c1
 FOREIGN KEY (f1)REFERENCES t2 (c2);

The operation succeeds. The distribution of the t1 table is not related to the c1 constraint. 

CREATE TABLE t1...CONSTRAINT fk1...
 DISTRIBUTE BY REFERENCE(fk1);

ALTER TABLE t1 DROP CONSTRAINT(fk1);

The operation fails. The foreign key is used to distribute the table.

These examples support the information in the Table 6-6 table:

Use ALTER TABLE to Add a Primary Key Constraint

This example creates the mytable table without a primary key or distribution clause. The table is distributed by hash on a hidden column. Then the ALTER TABLE statement is used to add a primary key constraint. The operation succeeds but the distribution key is not changed. 

Command> CREATE TABLE mytable (col1 NUMBER NOT NULL, col2 VARCHAR2 (32));
Command> describe mytable;
 
Table SAMPLEUSER.MYTABLE:
  Columns:
    COL1                            NUMBER NOT NULL
    COL2                            VARCHAR2 (32) INLINE
  DISTRIBUTE BY HASH
 
1 table found.
(primary key columns are indicated with *)

Now alter the table to add the primary key. The operation succeeds. The distribution scheme and distribution key do not change.

Command> ALTER TABLE mytable ADD CONSTRAINT c1 PRIMARY KEY (col1);
Command> describe mytable;
 
Table SAMPLEUSER.MYTABLE:
  Columns:
   *COL1                            NUMBER NOT NULL
    COL2                            VARCHAR2 (32) INLINE
  DISTRIBUTE BY HASH
 
1 table found.
(primary key columns are indicated with *)

Add a Primary Key Constraint on Table Distributed on Unique Column

This example creates the mytab table and distributes the data by hash on the id2 unique column. The example then alters the mytab table adding the primary key constraint on the id column. A ttIsql describe command shows the table remains distributed by hash on the id2 column. 

Command> CREATE TABLE mytab (id TT_INTEGER NOT NULL, id2 TT_INTEGER UNIQUE, 
           id3 TT_INTEGER) distribute by hash (id2);
Command> ALTER TABLE mytab ADD CONSTRAINT c1 PRIMARY KEY (id);                  Command> describe mytab;
 
Table SAMPLEUSER.MYTAB:
  Columns:
   *ID                              TT_INTEGER NOT NULL
    ID2                             TT_INTEGER UNIQUE
    ID3                             TT_INTEGER
  DISTRIBUTE BY HASH (ID2)
 
1 table found.
(primary key columns are indicated with *)

Use ALTER TABLE to Change a Distribution Key

This example shows that you can use the ALTER TABLE statement to change the distribution key, but only if the table is empty. 

Command> CREATE TABLE mytable2 (col1 NUMBER NOT NULL, col2 VARCHAR2 (32))
         DISTRIBUTE BY HASH (col1,col2);
Command> describe mytable2;
 
Table SAMPLEUSER.MYTABLE2:
  Columns:
    COL1                            NUMBER NOT NULL
    COL2                            VARCHAR2 (32) INLINE
  DISTRIBUTE BY HASH (COL1, COL2)
 
1 table found.
(primary key columns are indicated with *)

Use the ALTER TABLE statement to change the distribution key to col1. The operation succeeds because the table is empty. 

Command> ALTER TABLE mytable2 DISTRIBUTE BY HASH (col1);
Command> describe mytable2;
 
Table SAMPLEUSER.MYTABLE2:
  Columns:
    COL1                            NUMBER NOT NULL
    COL2                            VARCHAR2 (32) INLINE
  DISTRIBUTE BY HASH (COL1)
 
1 table found.
(primary key columns are indicated with *)

Insert a row of data and attempt to change the distribution key back to col1, col2. The operation fails because the table is not empty. 

Command> INSERT INTO mytable2 VALUES (10, 'test');
1 row inserted.
Command> commit;
Command> ALTER TABLE mytable2 DISTRIBUTE BY HASH (col1,col2);
 1069: Table not empty. Alter table distribution is only permitted on empty
tables.
The command failed.

Add a Foreign Key Constraint That Is Not Part of a Distribution Key

This example first describes the accounts and accounts2 tables. The example then alters the accounts2 table, adding a foreign key constraint. Since this constraint is not part of the accounts2 table distribution, the operation succeeds. 

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

Command> describe accounts2;
 
Table SAMPLEUSER.ACCOUNTS2:
  Columns:
   *ACCOUNTS2_ID                    NUMBER (10) NOT NULL
    ACCOUNT_ORIG_ID                 NUMBER (10) NOT NULL
    STATUS                          NUMBER (2) NOT NULL
  DISTRIBUTE BY HASH (ACCOUNTS2_ID)
 
1 table found.
(primary key columns are indicated with *)

Command> ALTER TABLE accounts2 ADD CONSTRAINT accounts2_fk FOREIGN KEY 
           (account_orig_id) REFERENCES accounts (account_id);

Use the ttIsql indexes command to show the accounts2_fk constraint is created successfully. 

Command> indexes accounts2;
 
Indexes on table SAMPLEUSER.ACCOUNTS2:
  ACCOUNTS2: unique range index on columns:
    ACCOUNTS2_ID
  ACCOUNTS2_FK: non-unique range index on columns:
    ACCOUNT_ORIG_ID
    (foreign key index references table SAMPLEUSER.ACCOUNTS(ACCOUNT_ID))
  2 indexes found.
 
2 indexes found on 1 table.

Attempt to Drop a Foreign Key Constraint Used as a Distribution Key

This example attempts to drop the fk_accounts constraint. Since the constraint is used as the distribution key, the operation fails. 

Command> describe transactions;
 
Table SAMPLEUSER.TRANSACTIONS:
  Columns:
   *TRANSACTION_ID                  NUMBER (10) NOT NULL
   *ACCOUNT_ID                      NUMBER (10) NOT NULL
   *TRANSACTION_TS                  TIMESTAMP (6) NOT NULL
    DESCRIPTION                     VARCHAR2 (60) INLINE
    OPTYPE                          CHAR (1) NOT NULL
    AMOUNT                          NUMBER (6,2) NOT NULL
  DISTRIBUTE BY REFERENCE (FK_ACCOUNTS)
 
1 table found.
(primary key columns are indicated with *)

Command> ALTER TABLE transactions DROP CONSTRAINT fk_accounts;
 1072: Dropping a table's reference by distribution foreign key is not allowed.
The command failed.

SQL Syntax for ALTER TABLE: TimesTen Classic

To add one column:

ALTER TABLE [Owner.]TableName ADD [COLUMN] ColumnName ColumnDataType
  [DEFAULT DefaultVal] [[NOT] INLINE] [UNIQUE] [NULL]
  [COMPRESS (CompressColumns [,...])];

To add multiple columns:

ALTER TABLE [Owner.]TableName ADD (ColumnName ColumnDataType 
  [DEFAULT DefaultVal] [[NOT] INLINE] [UNIQUE] [NULL] [,... ] )
  [COMPRESS (CompressColumns [,...])];

To add a NOT NULL column (note that the DEFAULT clause is required): 

ALTER TABLE [Owner.]TableName
  ADD [COLUMN] ColumnName ColumnDataType
    NOT NULL [ENABLE] DEFAULT DefaultVal [[NOT] INLINE] [UNIQUE]
  [COMPRESS (CompressColumns [,...])]

To add multiple NOT NULL columns (note that the DEFAULT clause is required): 

ALTER TABLE [Owner.]TableName
  ADD (ColumnName ColumnDataType
       NOT NULL [ENABLE] DEFAULT DefaultVal [[NOT] INLINE] [UNIQUE] [,...])
  [COMPRESS (CompressColumns [,...])]

The CompressColumns syntax is as follows: 

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

To remove columns. 

ALTER TABLE [Owner.]TableName 
  DROP {[COLUMN] ColumnName | (ColumnName [,... ] )}

Note:

If removing columns in a compressed column group, all columns in the compressed column group must be specified.

To add a primary key constraint using a range index:

ALTER TABLE [Owner.]TableName ADD CONSTRAINT ConstraintName
  PRIMARY KEY (ColumnName [,... ])

To add a primary key constraint using a hash index:

ALTER TABLE [Owner.]TableName ADD CONSTRAINT ConstraintName
  PRIMARY KEY (ColumnName [,... ])
  USE HASH INDEX PAGES = RowPages | CURRENT

To add a foreign key and optionally add ON DELETE CASCADE: 

ALTER TABLE [Owner.]TableName 
ADD [CONSTRAINT ForeignKeyName] FOREIGN KEY
    (ColumnName [,...]) REFERENCES RefTableName
       [(ColumnName [,...])] [ON DELETE CASCADE]

To remove a foreign key:

ALTER TABLE [Owner.]TableName 
DROP CONSTRAINT ForeignKeyName

Note:

You cannot use ALTER TABLE to drop a primary key constraint. To drop the constraint, drop and recreate the table.

To resize a hash index:

ALTER TABLE [Owner.]TableName
SET PAGES = RowPages | CURRENT

To change the primary key to use a hash index:

ALTER TABLE [Owner.]TableName
USE HASH INDEX PAGES = RowPages | CURRENT

To change the primary key to use a range index with the USE RANGE INDEX clause: 

ALTER TABLE [Owner.]TableName
USE RANGE INDEX

To change the default value of a column:

ALTER TABLE [Owner.]TableName
MODIFY (ColumnName DEFAULT DefaultVal)

To add or drop a unique constraint on a column:

ALTER TABLE Owner.]TableName
{ADD | DROP} UNIQUE (ColumnName)

To remove the default value of a column that is nullable, by changing it to NULL: 

ALTER TABLE [Owner.]TableName
MODIFY (ColumnName DEFAULT NULL)

To add LRU aging:

ALTER TABLE [Owner.]TableName
ADD AGING LRU [ON | OFF]

To add time-based aging:

ALTER TABLE [Owner.]TableName
ADD AGING USE ColumnName LIFETIME num1
    {SECOND[S] | MINUTE[S] | HOUR[S] | DAY[S]}
     [CYCLE num2 {SECOND[S] | MINUTE[S] | HOUR[S] | DAY[S] }]
    [ON | OFF]

To change the aging state:

ALTER TABLE [Owner.]TableName
SET AGING {ON | OFF}

To drop aging:

ALTER TABLE [Owner.]TableName
DROP AGING

To change the lifetime for time-based aging:

ALTER TABLE [Owner.]TableName
SET AGING LIFETIME num1 {SECOND[S] | MINUTE[S] | HOUR[S] | DAY[S]}

To change the cycle for time-based aging:

ALTER TABLE [Owner.]TableName
SET AGING CYCLE num2 {SECOND[S] | MINUTE[S] | HOUR[S] | DAY[S]}

Parameters for ALTER TABLE: TimesTen Classic

Parameter Description

[Owner.] TableName

Identifies the table to be altered.

UNIQUE

Specifies that in the column ColumnName each row must contain a unique value.

MODIFY

Specifies that an attribute of a given column is to be changed to a new value.

DEFAULT [DefaultVal |NULL]

Specifies that the column has a default value, DefaultVal. If NULL, specifies that the default value of the columns is to be dropped. If a column with a default value of SYSDATE is added, the value of the column of the existing rows only is the system date at the time the column was added. If the default value is one of the USER functions the column value is the user value of the session that executed the ALTER TABLE statement. Currently, you cannot assign a default value for the ROWID data type.

Altering the default value of a column has no impact on existing rows.

Note: To add a NOT NULL column to a table that is part of a replication scheme, DDL_REPLICATON_LEVEL must be 3 or greater.

ColumnName

Name of the column participating in the ALTER TABLE statement. A new column cannot have the same name as an existing column or another new column. If you add a NOT NULL column, you must include the DEFAULT clause.

ColumnDataType

Type of the column to be added. Some types require additional parameters. See Data Types for the data types that can be specified.

NOT NULL [ENABLE]

If you add a column, you can specify NOT NULL. If you specify NOT NULL, then you must include the DEFAULT clause. Optionally, you can specify ENABLE after the NOT NULL clause. Because NOT NULL constraints are always enabled, you are not required to specify ENABLE.

INLINE|NOT INLINE

By default, variable-length columns whose declared column length is > 128 bytes are stored out of line. Variable-length columns whose declared column length is <= 128 bytes are stored inline. The default behavior can be overridden during table creation through the use of the INLINE and NOT INLINE keywords.

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.

If you define multiple columns for a compression group, you must specify the columns as INLINE. 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.

See Column-Based Compression of Tables (TimesTen Classic) for details on compression 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 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.

See Column-Based Compression of Tables (TimesTen Classic) for details on maximum sizing for compression dictionaries.

ADD CONSTRAINT ConstraintName PRIMARY KEY (ColumnName

[,... ] ) [USE HASH INDEX PAGES = RowPages | CURRENT]

Adds a primary key constraint to the table. Columns of the primary key must be defined as NOT NULL.

Specify ConstraintName as the name of the index used to enforce the primary key constraint. Specify ColumnName as the name(s) of the NOT NULL column(s) used for the primary key.

Specify the USE HASH INDEX clause to use a hash index for the primary key. If not specified, a range index is used for the primary key constraint.

If you specify CURRENT, the current number of rows in the table is used to calculate the page count value. If you specify RowPages, the number of pages is used. To determine the value for RowPages, divide the number of expected rows in your table by 256. For example, if your table has 256,000 rows, specify 1000 for RowPages (256000/256=1000).

The value for RowPages must be a positive constant and must be greater than 0.

TimesTen recommends that you do not specify PAGES=CURRENT if there are no rows in your table.

If your estimate is too small, performance may be degraded. See Column Definition: TimesTen Classic for more information on hash indexes.

Note: Before you use ADD CONSTRAINT to add a named primary key constraint, be aware that you cannot use ALTER TABLE to drop a primary key constraint. You would have to drop and recreate the table in order to drop the constraint.

CONSTRAINT

Specifies that a foreign key is to be dropped. Optionally specifies that an added foreign key is named by the user.

ForeignKeyName

Name of the foreign key to be added or dropped. All foreign keys are assigned a default name by the system if the name was not specified by the user. Either the user-provided name or system name can be specified in the DROP FOREIGN KEY clause.

FOREIGN KEY

Specifies that a foreign key is to be added.

REFERENCES

Specifies that the foreign key references another table.

RefTableName

The name of the table that the foreign key references.

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

USE HASH INDEX PAGES = RowPages | CURRENT

Changes primary key to use a hash index. If the primary key already uses a hash index, then this clause is equivalent to the SET PAGES clause.

USE RANGE INDEX

Changes primary key to use a range index. If the primary key already uses a range index, TimesTen ignores this clause.

SET PAGES = RowPages | CURRENT

Resizes the hash index to reflect the expected number of pages in the table. If you specify CURRENT, the current number of rows in the table is used to calculate the page count value. If you specify RowPages, the number of pages is used. To determine the value for RowPages, divide the number of expected rows in your table by 256. For example, if your table has 256,000 rows, specify 1000 for RowPages (256000/256=1000).

The value for RowPages must be a positive constant and must be greater than 0.

TimesTen recommends that you do not specify PAGES=CURRENT if there are no rows in your table.

If your estimate is too small, performance may be degraded. See Column Definition: TimesTen Classic for more information on hash indexes.

ADD AGING LRU [ON | OFF]

Adds least recently used (LRU) aging to an existing table that has no aging policy defined.

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.

ADD AGING USE ColumnName...[ON| OFF]

Adds time-based aging to an existing table that has no aging policy defined.

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

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]

Specify the LIFETIME clause after the ADD AGING USE ColumnName clause if you are adding the time-based aging policy to an existing table. Specify the LIFETIME clause after the SET AGING clause to change the LIFETIME setting.

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]}

Specify the optional CYCLE clause after the LIFETIME clause if you are adding the time-based aging policy to an existing table.

CYCLE is a time-based aging attribute.

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.

Specify the CYCLE clause after the SET AGING clause to change the CYCLE setting.

SET AGING {ON|OFF}

Changes the aging state. The aging policy must be previously defined. ON enables automatic aging. OFF disables automatic aging. To control aging with an external scheduler, then disable aging and invoke the ttAgingScheduleNow built-in procedure.

DROP AGING

Drops the aging policy from the table. After you define an aging policy, you cannot alter it. Drop aging, then redefine.

SET AGING LIFETIME Num1 {SECOND[S]| MINUTE[S]|HOUR[S] |DAY[S]}

Use this clause to change the lifetime for time-based aging.

Num1 must be a positive integer constant.

If you defined your aging column with data type TT_DATE, then you must specify DAYS as the LIFETIME unit.

SET AGING CYCLE Num2 {SECOND[S]| MINUTE[S]| HOUR[S]|DAY[S]}

Use this clause to change the cycle for time-based aging.

Num2 must be a positive integer constant.

Description for ALTER TABLE: TimesTen Classic

  • The ALTER TABLE statement cannot be used to alter a temporary table.

  • The ALTER TABLE ADD [COLUMN] ColumnName statement adds one or more new columns to an existing table. When you add one or more columns, the new columns are added to the end of all existing rows of the table in one new partition.

  • The ALTER TABLE ADD or DROP COLUMN statement can be used to add or drop columns from replicated tables.

    Do not use ALTER TABLE to alter a replicated table that is part of a TWOSAFE BY REQUEST transaction.

  • Columns referenced by materialized views cannot be dropped.

  • You cannot use the ALTER TABLE statement to add a column, drop a column, or add a constraint for cache group tables.

  • Only one partition is added to the table per statement regardless of the number of columns added.

  • You can ALTER a table to add a NOT NULL column with a default value. The DEFAULT clause is required. Restrictions include:

    • You cannot use the column as a primary key column. Specifically, you cannot specify the column in the statement: ALTER TABLE ADD ConstraintName PRIMARY KEY (ColumnName [,...]).

    • You cannot use the column for time-based aging. Specifically, you cannot specify the column in the statement ALTER TABLE ADD AGING USE ColumnName.

      Note:

      To add a NOT NULL column to a table that is part of a replication scheme, DDL_REPLICATON_LEVEL must be 3 or greater.

  • NULL is the initial value for all added columns, unless a default value is specified for the new column.

  • The total number of columns in the table cannot exceed 1000. In addition, the total number of partitions in a table cannot exceed 1000, one of which is used by TimesTen.

  • Use the ADD CONSTRAINT ... PRIMARY KEY clause to add a primary key constraint to a regular table or to a detailed or materialized view table. Do not use this clause on a table that already has a primary key.

  • If you use the ADD CONSTRAINT... PRIMARY KEY clause to add a primary key constraint, and you do not specify the USE HASH INDEX clause, then a range index is used for the primary key constraint.

  • If a table is replicated and the replication agent is active, you cannot use the ADD CONSTRAINT ... PRIMARY KEY clause. Stop the replication agent first.

  • Do not specify the ADD CONSTRAINT ... PRIMARY KEY clause on a global temporary table.

  • Do not specify the ADD CONSTRAINT ... PRIMARY KEY clause on a cache group table because cache group tables defined with a primary key must be defined in the CREATE CACHE GROUP statement.

  • As the result of an ALTER TABLE ADD statement, an additional read occurs for each new partition during queries. Therefore, altered tables may have slightly degraded performance. The performance can only by restored by dropping and recreating the table, or by using the ttMigrate create -c -relaxedUpgrade command, and restoring the table using the ttRestore -r -relaxedUpgrade command. Dropping the added column does not recover the lost performance or decrease the number of partitions.

  • When you use the ALTER TABLE DROP statement to remove one or more columns from an existing table, dropped columns are removed from all current rows of the table. Subsequent SQL statements must not attempt to make any use of the dropped columns. You cannot drop columns that are in the table's primary key. You cannot drop columns that are in any of the table's foreign keys until you have dropped all foreign keys. You cannot drop columns that are indexed until all indexes on the column have been dropped. ALTER TABLE cannot be used to drop all of the columns of a table. Use DROP TABLE instead.

  • When a column is dropped from a table, all commands referencing that table need to be recompiled. An error may result at recompilation time if a dropped column was referenced. The application must re-prepare those commands, and rebuild any parameters and result columns. When a column is added to a table, the commands that contain a SELECT * statement are invalidated. Only these commands must be re-prepared. All other commands continue to work as expected.

  • When you drop a column, the column space is not freed.

  • When you add a UNIQUE constraint, there is overhead incurred (in terms of additional space and additional time). This is because an index is created to maintain the UNIQUE constraint. You cannot use the DROP INDEX statement to drop an index used to maintain the UNIQUE constraint.

  • A UNIQUE constraint and its associated index cannot be dropped if it is being used as a unique index on a replicated table.

  • Use ALTER TABLE...USE RANGE INDEX if your application performs range queries over a table's primary key.

  • Use ALTER TABLE...USE HASH INDEX if your application performs exact match lookups on a table's primary key.

  • An error is generated if a table has no primary key and either the USE HASH INDEX clause or the USE RANGE INDEX clause is specified.

  • Make sure to stop the replication agent before adding or dropping a foreign key on a replicated table.

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

  • 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, a second 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.

  • The ALTER TABLE ADD/DROP CONSTRAINT statement has the following restrictions:

    • When a foreign key is dropped, TimesTen also drops the index associated with the foreign key. Attempting to drop an index associated with a foreign key using the regular DROP INDEX statement results in an error.

    • Foreign keys cannot be added or dropped on tables in a cache group.

    • Foreign keys cannot be added or dropped on views or temporary tables.

    • You cannot use ALTER TABLE to drop a primary key constraint. You would have to drop and recreate the table in order to drop the constraint.

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

  • 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 right away regardless if the aging state is ON or OFF.

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

  • Aging restrictions:

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

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

  • Restrictions for column-based compression of tables:

    • You can add compressed column groups with the ALTER TABLE statement only if the table was enabled for compression at table creation. You can add uncompressed columns to any table, including tables enabled for compression. Refer to Column-Based Compression of Tables (TimesTen Classic) for more details on adding compressed column groups to a table.

    • You cannot modify columns of a compressed column group.

    • You can drop all columns within a compressed column group with the ALTER TABLE command; when removing columns in a compressed column group, all columns in the compressed column group must be specified for removal.

    • You cannot use ALTER TABLE to modify an existing uncompressed column to make it compressed. For example: 

      Command> create table mytab (a varchar2 (30), b int, c int) compress ((a,b)
           by dictionary);
Command> alter table mytab add (d int) compress (c by dictionary);
2246: Cannot change compression clause for already defined column C 
The command failed.

Understanding Partitions When Using ALTER TABLE in TimesTen

When you create a table, an initial partition is created. If you ALTER the table, and add additional columns, secondary partitions are created. There is one secondary partition created for each ALTER TABLE statement. For a column in secondary partitions, you cannot create a primary key constraint on the column or use the column for time-based aging.

You can use ttMigrate -r -relaxedUpgrade to condense multiple partitions. This means the initial partition plus one or more secondary partitions are condensed into a single partition called the initial partition. Once you condense the partitions, you can then ALTER the table and add a primary key constraint on the column or use the column for time-based aging. This is because the columns are no longer in secondary partitions but are now in the initial partition.

If your database is involved in replication and you want to condense multiple partitions, you must use the StoreAttribute TABLE DEFINITION CHECKING RELAXED (of the CREATE REPLICATION statement). Run ttMigrate -r -relaxedUpgrade on both the master and subscriber or on either the master or subscriber by using -duplicate.

Use ttSchema to view partition numbers for columns. ttSchema displays secondary partition number 1 as partition 1, secondary partition number 2 as partition 2 and so on.

As an example, create a table MyTab with 2 columns. Then ALTER the table adding 2 columns (Col3 and Col4) with the NOT NULL DEFAULT clause. 

Command> CREATE TABLE MyTab (Col1 NUMBER, Col2 VARCHAR2 (30));
Command> ALTER TABLE MyTab ADD (Col3 NUMBER NOT NULL DEFAULT 10, Col4 TIMESTAMP
            NOT NULL DEFAULT TIMESTAMP '2012-09-03 12:00:00');

Use ttSchema to verify Col3 and Col4 are in secondary partition 1. 

ttschema -DSN sampledb_1122
-- Database is in Oracle type mode
create table TESTUSER.MYTAB (
        COL1 NUMBER,
        COL2 VARCHAR2(30 BYTE) INLINE,
        COL3 NUMBER NOT NULL DEFAULT 10,
        COL4 TIMESTAMP(6) NOT NULL DEFAULT TIMESTAMP '2012-09-03 12:00:00');
-- column COL3 partition 1
-- column COL4 partition 1

Attempt to add a primary key constraint on Col3 and time-based aging on Col4. You see errors because you can neither add a primary key constraint nor add time-based aging to a column that is not in the initial partition. 

Command> ALTER TABLE MyTab ADD CONSTRAINT PriKey PRIMARY KEY (Col3);
 2419: All columns in a primary key constraint must be in the initial partition;
column COL3 was added by ALTER TABLE
The command failed.

Command> ALTER TABLE MyTab ADD AGING USE Col4 LIFETIME 3 DAYS;
 3023: Aging column must be in the initial partition; column COL4 was added by
ALTER TABLE
The command failed.

Use ttMigrate with the -relaxedUpgrade option to condense the partitions. Then use ttSchema to verify the partitions are condensed and there are no columns in secondary partition 1. 

ttMigrate -c dsn=sampledb_1122 test.migrate
 
Saving user PUBLIC
User successfully saved.
 
Saving table TESTUSER.MYTAB
  Saving rows...
  0/0 rows saved.
Table successfully saved.

ttDestroy sampledb_1122

ttMigrate -r -relaxedUpgrade
 dsn=sampledb_1122 test.migrate
 
Restoring table TESTUSER.MYTAB
  Restoring rows...
  0/0 rows restored.
Table successfully restored.

ttSchema DSN=sampledb_1122
-- Database is in Oracle type mode
create table TESTUSER.MYTAB (
        COL1 NUMBER,
        COL2 VARCHAR2(30 BYTE) INLINE,
        COL3 NUMBER NOT NULL DEFAULT 10,
        COL4 TIMESTAMP(6) NOT NULL DEFAULT TIMESTAMP '2012-09-03 12:00:00');

Now add a primary key constraint on Col3 and time-based aging on Col4. The results are successful because Col3 and Col4 are in the initial partition as a result of ttMigrate. Use ttSchema to verify results. 

Command> ALTER TABLE MyTab ADD CONSTRAINT PriKey PRIMARY KEY (Col3);
Command> ALTER TABLE MyTab ADD AGING USE Col4 LIFETIME 3 DAYS;

ttschema sampledb_1122
-- Database is in Oracle type mode
create table TESTUSER.MYTAB (
        COL1 NUMBER,
        COL2 VARCHAR2(30 BYTE) INLINE,
        COL3 NUMBER NOT NULL DEFAULT 10,
        COL4 TIMESTAMP(6) NOT NULL DEFAULT TIMESTAMP '2012-09-03 12:00:00')
    AGING USE COL4 LIFETIME 3 days CYCLE 5 minutes ON;
 
    alter table TESTUSER.MYTAB add constraint PRIKEY primary key (COL3);

Examples for ALTER TABLE: TimesTen Classic

Add returnrate column to parts table. 

ALTER TABLE parts ADD COLUMN returnrate DOUBLE;

Add numsssign and prevdept columns to contractor table. 

ALTER TABLE contractor
  ADD ( numassign INTEGER, prevdept CHAR(30) );

Remove addr1 and addr2 columns from employee table. 

ALTER TABLE employee DROP ( addr1, addr2 );

Drop the UNIQUE title column of the books table. 

ALTER TABLE books DROP UNIQUE (title);

Add the x1 column to the t1 table with a default value of 5: 

ALTER TABLE t1 ADD (x1 INT DEFAULT 5);

Change the default value of column x1 to 2: 

ALTER TABLE t1 MODIFY (x1 DEFAULT 2);

Alter table primarykeytest to add the primary key constraint c1. Use the ttIsql INDEXES command to show that the primary key constraint c1 is created and a range index is used: 

Command> CREATE TABLE primarykeytest (col1 TT_INTEGER NOT NULL);
Command> ALTER TABLE primarykeytest ADD CONSTRAINT c1 
         PRIMARY KEY (col1);
Command> INDEXES primarykeytest;

Indexes on table SAMPLEUSER.PRIMARYKEYTEST:
  C1: unique range index on columns:
    COL1
  1 index found.

1 index found on 1 table.

Alter table prikeyhash to add the primary key constraint c2 using a hash index. Use the ttIsql INDEXES command to show that the primary key constraint c2 is created and a hash index is used: 

Command> CREATE TABLE prikeyhash (col1 NUMBER (3,2) NOT NULL);
Command> ALTER TABLE prikeyhash ADD CONSTRAINT c2
           PRIMARY KEY (col1) USE HASH INDEX PAGES = 20;
Command> INDEXES prikeyhash;

Indexes on table SAMPLEUSER.PRIKEYHASH:
  C2: unique hash index on columns:
    COL1
  1 index found.

1 table found.

Attempt to add a primary key constraint on a table already defined with a primary key. You see an error:

Command> CREATE TABLE oneprikey (col1 VARCHAR2 (30) NOT NULL, 
         col2 TT_BIGINT NOT NULL, col3 CHAR (15) NOT NULL, 
         PRIMARY KEY (col1,col2));
Command> ALTER TABLE oneprikey ADD CONSTRAINT c2 
         PRIMARY KEY (col1,col2);
 2235: Table can have only one primary key
The command failed.

Attempt to add a primary key constraint on a column that is not defined as NOT NULL. You see an error: 

Command> CREATE TABLE prikeynull (col1 CHAR (30));
Command> ALTER TABLE prikeynull ADD CONSTRAINT c3 
         PRIMARY KEY (col1);
 2236: Nullable column cannot be part of a primary key
The command failed.

This example illustrates the use of range and hash indexes. It creates the pkey table with col1 as the primary key. A range index is created by default. The table is then altered to change the index on col1 to a hash index. The table is altered again to change the index back to a range index. 

Command> CREATE TABLE pkey (col1 TT_INTEGER PRIMARY KEY, col2 VARCHAR2 (20));
Command> INDEXES pkey;
Indexes on table SAMPLEUSER.PKEY:
   PKEY: unique range index on columns:
    COL1
 1 index found.
1 index found on 1 table.

Alter the pkey table to use a hash index: 

Command> ALTER TABLE pkey USE HASH INDEX PAGES = CURRENT;
Command> INDEXES pkey;
Indexes on table SAMPLEUSER.PKEY:
  PKEY: unique hash index on columns:
    COL1
  1 index found.
1 table found.

Alter the pkey table to use a range index with the USE RANGE INDEX clause: 

Command> ALTER TABLE pkey USE RANGE INDEX;
Command> INDEXES pkey;
Indexes on table SAMPLEUSER.PKEY:
  PKEY: unique range index on columns:
    COL1
  1 index found.
1 table found.

This example generates an error when attempting to alter a table to define either a range or hash index on a column without a primary key.

Command> CREATE TABLE myindex (Ccl1 CHAR (20));
Command> ALTER TABLE myindex USE RANGE INDEX;
 2810: The table has no primary key so cannot change its index type
The command failed.
Command> ALTER TABLE myindex USE HASH INDEX PAGES = CURRENT;
 2810: The table has no primary key so cannot change its index type
The command failed.

These examples show how time resolution works with aging. In this example, lifetime is three days.

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

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

  • If (SYSDATE - ColumnValue) = 3 days, 22 hours, then row is not aged out because lifetime was specified in days. The row would be aged out if lifetime had been specified as 72 hours.

This example alters a table by adding LRU aging. The table has no previous aging policy. The aging state is ON by default. 

ALTER TABLE agingdemo3 ADD AGING LRU;
Command> DESCRIBE agingdemo3;
Table USER.AGINGDEMO3:
  Columns:
   *AGINGID                         NUMBER NOT NULL
    NAME                            VARCHAR2 (20) INLINE
  Aging lru on
1 table found.
(primary key columns are indicated with *)

This example alters a table by adding time-based aging. The table has no previous aging policy. The agingcolumn column is used for aging. LIFETIME is 2 days. CYCLE is 30 minutes. 

ALTER TABLE agingdemo4
       ADD AGING USE agingcolumn LIFETIME 2 DAYS CYCLE 30 MINUTES;
Command> DESCRIBE agingdemo4;
Table USER.AGINGDEMO4:
  Columns:
   *AGINGID                         NUMBER NOT NULL
    NAME                            VARCHAR2 (20) INLINE
    AGINGCOLUMN                     TIMESTAMP (6) NOT NULL
  Aging use AGINGCOLUMN lifetime 2 days cycle 30 minutes on

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

CREATE TABLE agingdemo5
       (agingid NUMBER NOT NULL PRIMARY KEY
       ,name VARCHAR2 (20)
       ,agingcolumn TIMESTAMP NOT NULL
       )
       AGING USE agingcolumn LIFETIME 3 DAYS OFF;
ALTER TABLE agingdemo5
      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 agingdemo5
      DROP AGING;
ALTER TABLE agingdemo5
       ADD AGING LRU;
Command> DESCRIBE agingdemo5;
Table USER.AGINGDEMO5:
  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 *)

This example alters a table by setting the aging state to OFF. The table has been defined with a time-based aging policy. If you set the aging state to OFF, aging is not done automatically. This is useful to use an external scheduler to control the aging process. Set aging state to OFF and then call the ttAgingScheduleNow procedure to start the aging process. 

Command> DESCRIBE agingdemo4;
Table USER.AGINGDEMO4:
  Columns:
   *AGINGID                         NUMBER NOT NULL
    NAME                            VARCHAR2 (20) INLINE
    AGINGCOLUMN                     TIMESTAMP (6) NOT NULL
  Aging use AGINGCOLUMN lifetime 2 days cycle 30 minutes on

ALTER TABLE AgingDemo4
       SET AGING OFF;

Note that when you describe agingdemo4, the aging policy is defined and the aging state is set to OFF. 

Command> DESCRIBE agingdemo4;
Table USER.AGINGDEMO4:
  Columns:
   *AGINGID                         NUMBER NOT NULL
    NAME                            VARCHAR2 (20) INLINE
    AGINGCOLUMN                     TIMESTAMP (6) NOT NULL
  Aging use AGINGCOLUMN lifetime 2 days cycle 30 minutes off
1 table found.
(primary key columns are indicated with *)

Call ttAgingScheduleNow to invoke aging with an external scheduler: 

Command> CALL ttAgingScheduleNow ('agingdemo4');

Attempt to alter a table adding the aging column and then use that column for time-based aging. An error is generated.

Command> DESCRIBE x;
Table USER1.X:
  Columns:
   *ID                              TT_INTEGER NOT NULL
1 table found.
(primary key columns are indicated with *)
Command> ALTER TABLE x ADD COLUMN t TIMESTAMP;
Command> ALTER TABLE x ADD AGING USE t LIFETIME 2 DAYS;
 2993: Aging column cannot be nullable
The command failed.

Attempt to alter the LIFETIME clause for a table defined with time-based aging. The aging column is defined with data type TT_DATE. An error is generated because the LIFETIME unit is not expressed in DAYS. 

Command> CREATE TABLE aging1 (col1 TT_DATE NOT NULL) AGING USE 
         col1 LIFETIME 2 DAYS;
Command> ALTER TABLE aging1 SET AGING LIFETIME 2 HOURS;
 2977: Only DAY lifetime unit is allowed with a TT_DATE column
The command failed.

Alter the employees table to add a new compressed column of state, which contains the full name of the state. Note that the employees table already has a compressed column group consisting of job_id and manager_id. 

Command> ALTER TABLE employees 
         ADD COLUMN state VARCHAR2(20) 
         COMPRESS (state 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)
    STATE                           VARCHAR2 (20) INLINE
  COMPRESS ( ( JOB_ID, MANAGER_ID ) BY DICTIONARY,
             STATE BY DICTIONARY )
 
1 table found.
(primary key columns are indicated with *)

The following example drops the compressed column state from the employees table: 

Command> ALTER TABLE employees
 DROP state;
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, MANAGER_ID ) BY DICTIONARY )
 
1 table found.
(primary key columns are indicated with *)

See Also