ALTER TABLE

This statement is used to change the properties of a table. There are a number of variants.

This function is only available for SQL databases. For more detailed information on their effects see the relevant database documentation.

Changing Tablespace

ALTER TABLE tablename MOVE TABLESPACE new_tablespace_name

This form is used to MOVE a table to another tablespace. This involves a copy of the data blocks and the relevant indexes and word indexes are removed and re-created in the new tablespace.

Changing Logging

ALTER TABLE tablename LOGGING TO log_tablename
ALTER TABLE tablename NOLOGGING

These two forms enable and disable TABLE LOGGING.

Changing the physical blocks

ALTER TABLE tablename MOVE
ALTER TABLE tablename MOVE PARTITION partition_name

This form is used to de-chain tables or table partitions within the same tablespace. This can make accessing the table more efficient by removing overflow blocks. The ROWIDs may change so the relevant indexes and word indexes are rebuilt at the same time.

Changing column properties

ALTER TABLE tablename ADD [SCHEMA ONLY] ( coldesc [,...] )
ALTER TABLE tablename DROP [SCHEMA ONLY] (col_name [,...] )
ALTER TABLE tablename SET UNUSED (col_name [,...] )
ALTER TABLE tablename DROP UNUSED
ALTER TABLE tablename MODIFY [SCHEMA ONLY] ( coldesc [,...])
ALTER TABLE tablename RENAME COLUMN old_name TO new_name

These forms allow columns to be modified or dropped, new columns to be added, or column names to be changed. In ADD and MODIFY the coldesc is the same as that used in CREATE TABLE.

Adding the optional SCHEMA ONLY clause means the statement will alter only the KCML schema and not affect the actual database. This can be used, for instance, to add a column manually offline and later use this version of ALTER TABLE to reflect the change in the KCML schema.

The column names should not include any IPREFIX prefix.

You can't drop, rename or modify a partitioning column or a column mentioned in an index.

It is an error to try to change the properties of a column that is treated specially in the schema e.g. a serial column or a timestamp column. This special treatment should first be removed from the schema using ALTER TABLE SCHEMA.

If you are adding a mandatory column, i.e. one with a NOT NULL constraint, then there must be a sensible default value, perhaps set with the DEFAULT clause. Blank is not allowed as such a default for VARCHAR or BLOB types.

The following column attributes can be changed with MODIFY

DEFAULT	Allows setting a default value to be used instead of NULL for mandatory columns.
NAME	The long name column description. This can be a multilingual string in chevrons.
NULL, NOT NULL	If you change a column to be mandatory, i.e. NOT NULL, then there must be a default value defined. For VARCHAR or BLOB columns the default cannot be blanks.
OCCURS	You can increase or reduce the number of elements in the array. To convert an occurance back to a simple column set the OCCURS value to zero.
TOTALLED, NOT TOTALLED	This only affects the schema and has no effect on the database.
RANGE	This only affects the schema and has no effect on the database table. There is no guarantee that the existing data in the table will conform to this constraint. To remove a RANGE constraint use RANGE(ALL).
VALIDATE, NOT VALIDATE	This only affects the schema and has no effect on the database table. There is no guarantee that the existing data in the table will conform to this constraint. NOT VALIDATE removes the constraint.
PRIORITY	This only affects the schema and has no effect on the database table. To remove a priority set all three values to spaces
SPECIAL	This only affects the schema and has no effect on the database table. To remove this attribute use an empty string.

Additionally the data type can be changed within reason. You can't generally change a columns actual datatype but you are able to adjust upwards scale, precision and length where appropriate. INTEGER and NUMERIC are interchangeable but you generally can't reduce precision for numeric and integer columns if that would truncate existing data. If the numeric scale is reduced then Oracle will round any excess digits. CHAR, VARCHAR and HEX columns can have their length changed provided shrinking the length will not cause truncation of existing data.

When modifying column properties only list the properties to be changed as Oracle will error many cases of settting a property that is already set.

When a NULLable column is ADDed this is merely a change to Oracles dictionary and will be instantaneous. However if a column is given a DEFAULT value then that column has to be added to every row in a tablescan of the entire table (Oracle 10g only, on 11g it is also instantaneous). You may want to consider setting defaults using KI_DEFAULT_ROW and only use the DEFAULT clause for mandatory (NOT NULL) columns. For best performance columns should be NULLable unless they are in an index.

When a column is dropped Oracle will have to tablescan to remove any data. NULL columns are not actually stored in the row and can be dropped efficiently. Similarly if a column is modified Oracle will have to scan the rows to reformat any not NULL columns. The statement may fail if this would imply losing precision on a number or truncating a string. To avoid this potentially lengthy tablescan you can opt to use the SET UNUSED variant which, on Oracle only, will leave the columns in the rows but remove the columns from all dictionaries which can be done without touching the data rows. This space can be recovered later either as a side effect of DROPing some other column or with the ALTER TABLE DROP UNUSED statement which will scan the table and release the space in each row. On other SQL databases, like PostgreSQL, SET UNUSED is identical to DROP and DROP UNUSED has no effect.

Resequencing columns

Generally columns are ADDed at the end of the row buffer though you can use the STARTBYTE property to specify the byte offset that you want a column to start at. This has to be the start of another column or it must fall in a gap between columns. If this is not possible then the column will be silently added at the end as usual. After an insert like this the existing columns to the right of the insert point are all shifted right by the size of the new column.

To move an existing column in the row buffer you can issue an ALTER TABLE MODIFY that lists all the columns in the row with the required STARTBYTE values. The list must be in ascending STARTBYTE order and you cannot change any other attribute in the statement. Overlaps are forbidden but gaps between columns are allowed.

Changing schema properties

ALTER TABLE tablename SCHEMA tableparm [,...]
ALTER TABLE tablename SCHEMA UPDATE

This first form allows other attributes of a table to be changed. The tableparm clause is a subset of that used in CREATE TABLE. The specific options supported are:

CASE	An ODBC attribute which does not affect the database.
EXCLUDE COLUMN	This is followed by the name of column that is to be checked to see if a row should be word search indexed and a list of possible values as hex literals in quotes. The datatype of the column must be CHAR(1). Use a column name of NULL to disable this feature.
IPREFIX	This is followed by a string literal in quotes. Use an empty string to remove an iprefix.
NAME	This is followed by a string literal in quotes or chevrons if multilanguage.
NOEDIT	This is followed by a string literal in quotes.
REVOKE	This is followed by a comma separated list in parentheses of the operations to be revoked: ALTER, DELETE, INSERT, SELECT and UPDATE. The list may be empty.
SERIAL	This is followed by a mode letter (one of N, A, P or M) and an optional column name. The column must have a datatype of INTEGER(4). Use a mode of N to disable a serial column. Mode N does not require a column name but the other modes do. If the column name is followed by DEFAULT then KCML will set the column in each row of the table to the row number and set the next available value for the sequence appropriately. This makes all the column values unique.
TIMESTAMP	This is followed by the name of column to receive the timestamp when a row is written or updated. The datatype of the column must be TIMESTAMP. Use a column name of NULL to disable a stamped column.
TYPESTAMP	This is followed by the name of column to receive the stamp when a row is written or updated. The datatype of the column must be INTEGER(1) UNSIGNED. Use a column name of NULL to disable a stamped column.
UPDATE	An ODBC attribute which does not affect the database.
USERSTAMP	This is followed by the name of column to receive the audit stamp when a row is written or updated. It must have a CHAR or VARCHAR datatype. Use a column name of NULL to disable a stamped column.
UUIDSTAMP	This is followed by the name of column to receive the UUID stamp when a row is written or updated and the named column is empty. It must have a CHAR or VARCHAR datatype. Use a column name of NULL to disable a stamped column.
DBIDSTAMP	This is followed by the name of column to receive the DB session UUID stamp when the column is in an audit table. It must have a CHAR or VARCHAR datatype. Use a column name of NULL to disable a stamped column.
ADC [ENABLE \| DISABLE]	Include or exclude the table from waking up the ADC daemon when DML is executed on the table.

The second form can be used to force the KDB schema to align with the actual schema of the Oracle table. It enumerates the column properties in the Oracle data dictionary and rewrites the KDB schema, if necessary, to ensure they agree.