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.

Note:

In JSON collection tables, you can use the ALTER TABLE statement to only modify the default TTL values of a table. Any other schema alteration is not supported and an error message is returned.

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.

If you want to follow along with the examples, create tables and insert the data as described in Tables used in the Examples.

Example 5-20 Add a field to the table schema

ALTER TABLE stream_acct(ADD acct_balance INTEGER DEFAULT 0)

Explanation: In this example, you add a new field acct_balance with a default value of 0 to the TV streaming application. The new field is added to rows when the rows are retrieved at any time from the table. The on-disk format is updated to include the new field when a row is written back to the table. If you supply a default value in the ALTER TABLE statement, the value is populated into the new field as rows are retrieved, or when rows are written and your application has not supplied a value for the new field. If a default value is not supplied, a NULL value is populated instead. The field can be added as a top-level field (a table column) or it may be deeply nested inside a hierarchical record, in which case the field is specified through a path.

Example 5-21 Add a middle name to the otherNames field in the users table. 

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

Explanation: The otherNames field is an array of records, where each record in the array includes the first and last name fields. In this example, you use the ALTER statement to add the middle name field to the record that is nested within the otherNames array. You are adding the middle name field to a fixed schema record data type that exists within an array. Hence, you must specify the path to the field in the ALTER TABLE statement.

Example 5-22 Remove schema fields in the table schema

ALTER TABLE stream_acct(DROP acct_balance)

Explanation: In this example, you delete the acct_balance field from the TV streaming application schema. You can drop any field in the schema other than the primary key.

If you try removing the primary key field, you get an error as shown below.

ALTER TABLE stream_acct(DROP acct_id)

Output (showing error):

Error handling command ALTER TABLE stream_acct(DROP acct_id): Error: at (1, 27) Cannot remove a primary key field: acct_id

Example 5-23 Add a region

The add regions clause lets you link an existing Multi-Region Table (MR Table) with new regions in a multi-region Oracle NoSQL Database environment. You use this clause to expand MR Tables to new regions. For more information on creating new regions, see Create Remote Regions.

Associate a new region with an existing MR Table using the DDL command shown below. 

ALTER TABLE <table_name> ADD REGIONS <region_name>

Explanation: Here, table_name is an MR table and region_name is an existing region. For more information, see CREATE REGION Statement.

Example 5-24 Remove a region

The drop regions clause lets you disconnect an existing MR Table from a participating region in a multi-region Oracle NoSQL Database environment. You use this clause to contract MR Tables to fewer regions.

To remove an MR Table from a specific region in a multi-region Oracle NoSQL Database setup, you must run the following steps from all the other participating regions. 

ALTER TABLE <table_name> DROP REGIONS <comma_separated_list_of_regions>

Explanation: Here, table_name is an MR Table and comma_separated_list_of_regions is the list of regions to be dropped.

Example 5-25 Modify the Time-To-Live value of the table

Time-to-Live (TTL) is a mechanism that allows you to set a time frame on table rows, after which the rows expire automatically and are not available anymore. By default, every table that you create has a TTL value of zero, indicating that it has no expiration time.

You can use ALTER TABLE statement to change the TTL value for any table. You can specify the TTL with a number, followed by either HOURS or DAYS. 

ALTER TABLE stream_acct USING TTL 15 DAYS

Explanation: In the above statement, you add an expiry of 15 days to the new rows that get added to the TV streaming application table.

Note:

Altering the TTL value for a table does not change the TTL value for existing rows in the table. Rather, it will only change the default TTL value of the rows created after altering the table. To modify the TTL of every record in a table, you must iterate through each record of the table and update its TTL value.