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.
- 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;