Altering Tables

You can alter tables in TimesTen Scaleout 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 ALTER TABLE in Oracle TimesTen In-Memory Database SQL Reference.

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

Table 7-1 ALTER TABLE Rules for Distribution Schemes

ALTER statement	Comment
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.
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.

Examples include:

• Use ALTER TABLE to Add a Primary Key Constraint

• Use ALTER TABLE to Change the Distribution Key

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

Use ALTER TABLE to Change the 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 *)