Background documentationALTER TABLE Statement (alter_table_statement) Locate this document in the navigation structure

 

The ALTER TABLE statement (alter_table_statement) changes the properties of a base table.

Structure

Syntax Syntax

  1. <alter_table_statement> ::=
      ALTER TABLE <table_name> <add_definition>
    | ALTER TABLE <table_name> <alter_definition>
    | ALTER TABLE <table_name> <column_change_definition>
    | ALTER TABLE <table_name> <drop_definition>
    | ALTER TABLE <table_name> <modify_definition>
    | ALTER TABLE <table_name> <referential_constraint_definition>
    | ALTER TABLE <table_name> <sample_definition>
    | ALTER TABLE <table_name> [NOT] CACHE
    | ALTER TABLE <table_name> [NOT] NOCACHE
End of the code.
Explanation
ALTER TABLE Statement

ADD Definition

Defines additional table properties.

ALTER Definition

Changes a CONSTRAINT or key definition.

COLUMN Change Definition

Changes column properties.

DROP Definition

Deletes table properties.

MODIFY Definition

Changes data types and column attributes.

Referential CONSTRAINT Definition

Defines a new referential constraint.

SAMPLE Definition

Defines a number of rows which the database system is to take into account when it determines table statistics (sample size).

You can exclude individual tables from an UPDATE STATISTICS run by setting a sample size of 0 for these tables.

If the name of the schema is not specified in the table name, the current schema is used. The table name must be the name of an existing base table. The table must not be a temporary base table. The current database user must have the ALTER privilege for the specified table.

CACHE

The specification of CACHE for a table means that data from the table (row values, LOB values, index data) that has been read into the data cache once remains there, as far as possible.

An area is reserved in the data cache for all tables with the CACHE option. The size of this reserved area is defined by the database parameter DataCachePinAreaSize. Data is only replaced in this reserved data cache area if inserting a new data page into the reserved area would cause the permitted size of this reserved area to be exceeded.

The CACHE option is particularly suited for tables that are accessed very frequently.

If CACHE is specified, you cannot specify NOCACHE. The specification of NOT CACHE for a table means that the CACHE option is revoked.

NOCACHE

The specification of NOCACHE for a table means that data from the table (row values, LOB values, index data) that is read into the data cache is removed from the data cache very quickly.

The NOCACHE option is suitable for tables for which a long retention of the data in the data cache is not useful, either because the table is very seldom accessed or because there is no locality of accesses.

If NOCACHE is specified, you cannot specify CACHE. The specification of NOT NOCACHE for a table means that the NOCACHE option is revoked.

More Information

Privileges: Overview