1. SQL Reference Guide
  2. Table Management
  3. ALTER TABLE Statement

ALTER TABLE Statement

The behavior of NoSQL Database, when a schema evolves using ALTER TABLE statement, is designed primarily for large data sets (tens to hundreds of billions of records). Simply put, big data is larger and more complex data sets, especially from new data sources. These data sets are so voluminous that traditional data processing software can’t manage them. But these massive volumes of data are used to address business problems you wouldn’t have been able to tackle before. Therefore, when you modify the table schema with ALTER TABLE statement, NoSQL Database does not modify every record in the table and re-write them back to disk. Instead, it uses the notion of a default value, and that value gets inserted when a reader reads data that was written with a previous version of the schema.

However, in the case of identity columns, there is no way to generate a value other than by writing a record, hence when a user alters a table and adds an identity column, any reads of that column for records that were written prior to the later table yields a null value for the identity column.

You can use the alter table command to perform the following operations.
  • Add schema fields to the table schema
  • Remove schema fields from the table schema
  • Modify schema fields in the table schema
  • Add region
  • Remove region
  • Modify identity definition
  • Remove identity
  • Modify the Time-To-Live value of the table

You can specify only one type of operation in a single command. For example, you cannot remove a schema field and set the TTL value together.

Syntax

alter_table_statement ::= 
   ALTER TABLE name_path (alter_field_statements | ttl_definition)

alter_field_statements ::= 
   "(" alter_field_statement ("," alter_field_statement)* ")"

alter_field_statement ::= 
   add_field_statement | drop_field_statement | modify_field_statement | alter_regions_statement

add_field_statement ::= 
   ADD schema_path type_definition 
   [default_definition | identity_definition | uuid_definition | mr_counter_definition]
   [comment]

drop_field_statement ::= DROP schema_path

modify_field_statement ::= 
   (MODIFY schema_path identity_definition) | 
   (DROP IDENTITY)

alter_regions_statement ::= add_regions_statement | drop_regions_statement

add_regions_statement ::= ADD REGIONS region_names

drop_regions_statement ::= DROP REGIONS region_names

region_names ::= region_name ["," region_name]*

schema_path ::= init_schema_path_step ("." schema_path_step)*
init_schema_path_step ::= id ("[" "]")*
    schema_path_step ::= id ("[" "]")* | VALUES "(" ")"

Semantics

modify_field_statement

You can use the MODIFY keyword to modify only an identity column.

add_field_statement
Adding a field does not affect the existing rows in the table. If a field is added, its default value or NULL will be used as the value of this field in existing rows that do not contain it. The field to add may be a top-level field (i.e. A table column) or it may be deeply nested inside a hierarchical table schema. As a result, the field is specified via a path. The path syntax is a subset of the one used in queries and is described in the Path Expressions section.

Note:

The mr_counter_definition parameter declares the type of a column to be the MR_COUNTER datatype. This data type can be used only in a multi-region table.
drop_field_statement

Dropping a field does not affect the existing rows in the table. If a field is dropped, it will become invisible inside existing rows that do contain the field. The field to drop may be a top-level field (i.e. A table column) or it may be deeply nested inside a hierarchical table schema. As a result, the field is specified via a path. The path syntax is a subset of the one used in queries and is described in the Path Expressions section.

add_regions_statement
The add regions clause lets you link an existing MR Table with new regions in a multi-region Oracle NoSQL Database environment. This clause is used in expanding MR Tables to new regions. See Use Case 2: Expand a Multi-Region Table in the Administrator's Guide.

Note:

This clause will not work with MR child tables. Instead, alter the parent table to add a new region. This will automatically add the region to all the child tables in the hierarchy.
drop_regions_statement

The drop regions clause lets you disconnect an existing MR Table from a participating region in a multi-region Oracle NoSQL Database environment. This clause is used in contracting MR Tables to fewer regions. See Use Case 3: Contract a Multi-Region Table Administrator's Guide.

Example 5-12 Alter Table

The following example adds a middle name into the names stored in other_names.

ALTER TABLE users (ADD otherNames[].middle STRING);

Example 5-13 Alter Table

The following example modifies the TTL of the table to 5 hours.

ALTER TABLE users USING TTL 5 days;