Previous Next JavaScript must be enabled to correctly display this content

## 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.
- Enable before-image generation for any writes to the table. For details, see before_image_statement in the Semantics section.

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 | before_image_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

    before_image_statement ::= ENABLE BEFORE IMAGE [ttl_definition]

    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 "(" ")"
    ttl_definition ::= USING TTL INT_CONSTANT (HOURS | DAYS)

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.

before_image_statement

  The `ENABLE BEFORE IMAGE` clause for a table enables the generation and persistent storage of before-images for any write operations on the table. The before-image is the table row before it gets updated or deleted by a DML operation. For insert operations, the before-image will be empty.

  You can use this clause to enable before-images if your table is not enabled for before-images and your application would like to receive before-images from a subscription stream

  In addition to enabling before-images on the table using the SQL statement, you must also enable before-images in the subscription configuration through the Streams API. For more details on Oracle NoSQL Database Streams API, see Streams Developer'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-32 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-33 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-34 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-35 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-36 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-37 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.

Example 5-38 Enable before-images for table write operations

Use the following DDL statement to enable before-images generation for any writes to a table:

    ALTER TABLE stream_acct ENABLE BEFORE IMAGE USING TTL 10 DAYS

The statement above enables before-images generation with a TTL value of 10 days. The before-images for writes on the `stream_acct` table are stored on the disk for 10 days after they are generated. After this duration, the before-images expire freeing up the disk space and will not appear in the stream.

You can also enable before-images without a TTL definition as follows:

    ALTER TABLE stream_acct ENABLE BEFORE IMAGE

The statement above enables before-images generation on the `stream_acct` table. The generated before-images remain for 24 hours, unless adjusted by a TTL value.

Note:

- The expiration of table rows is not impacted when you enable before-images with a TTL definition.
- It is possible that a row will expire before its before-image TTL has expired.
- Before-images are beneficial when used in conjunction with streaming. For more details, see the Before-images Streaming section.
- The before-images TTL governs the time window a before-image can be placed on the stream.
- If you update a preexisting before-images TTL, then the before-images that are generated after the TTL update reflect the new expiry. The existing before-images expiry remain unaffected.

You can use the following DDL statement to disable the generation of before-images:

    ALTER TABLE stream_acct DISABLE BEFORE IMAGE

The statement above disables before-images generation on the `stream_acct` table. There is no impact to after-images.