Previous Next JavaScript must be enabled to correctly display this content

## Using MR_COUNTERs

Learn to use SQL statements to create and manage MR_COUNTERs in Multi-Region tables.

The MR_COUNTER data type is a Conflict-free Replicated Data Type (CRDT) counter. CRDTs provide a way for concurrent modifications to be merged across regions without user intervention.

In a Multi-Region setup of an Oracle NoSQL Database, copies of the same data must be stored in multiple regions and data may be concurrently modified in different regions. The MR_COUNTER data type ensures that though data modifications happen simultaneously on different regions, data always gets automatically merged into a consistent state.

Currently, Oracle NoSQL Database supports only Positive-Negative (PN) MR_COUNTER data type. The PN counters are suitable for increment and decrement operations. For example, you can use these counters to count the number of viewers live streaming a football match from a website at any point. When the viewers go offline, you need to decrement the counter.

You can only define MR_COUNTERs while creating a table or while modifying a table.

Create table using MR_COUNTER data type

You can declare a table column of the MR_COUNTER data type in a CREATE TABLE statement. MR_COUNTER is a subtype of one of the following data types: INTEGER, LONG, NUMBER.

    CREATE TABLE Users (
      id integer,
      firstname string,
      lastname string,
      age integer,
      income integer,
      count integer AS MR_COUNTER,
      primary key (id)
    ) IN REGIONS FRA,LON;

You can use the MR_COUNTER data type for a Multi-Region table only. You can't use it in regular tables. In the statement above, you create a Multi-Region table in FRA and LON regions with `count` as an INTEGER MR_COUNTER data type. You can define multiple columns as MR_COUNTER data type in a Multi-Region table.

You can also declare a field in a JSON document as MR_COUNTER.

    CREATE TABLE IF NOT EXISTS JSONPersons (
      id integer,
      person JSON (counter as INTEGER MR_COUNTER,
                   books.count as LONG MR_COUNTER),
      primary key (id)
    ) IN REGIONS FRA,LON;

In the statement above, you are identifying two of the fields in the JSON document `person` as MR_COUNTERs. The first field `counter` is an INTEGER MR_COUNTER data type. The second field `count` is within a nested JSON document `books`. The `count` field is of LONG MR_COUNTER data type.

Insert rows into a Multi-Region table

You can use the INSERT statement to insert data into a Multi-Region table with the MR_COUNTER column. You can add rows using one of the following options. Both the options insert a default value of zero to the MR_COUNTER column.

1. Option 1: Supply the keyword DEFAULT to the MR_COUNTER column.

       INSERT INTO Users VALUES (10, "David", "Morrison", 25, 100000,
           DEFAULT);

   In the statement above, you supply a value DEFAULT to the `count` MR_COUNTER.

       SELECT * FROM Users;

   Output:

       {"id":10,"firstname":"David","lastname":"Morrison","age":25,"income":100000,"count":0}

2. Option 2: Skip the MR_COUNTER column value by including only the required column values in the INSERT statement.

       INSERT INTO Users(id, firstname, lastname) VALUES (20, "John", "Anderson");

   In the statement above, you supply values to specific columns. The SQL engine inserts the values to the corresponding columns, a default value zero to the MR_COUNTER, and a null value to all the other columns.

       SELECT * FROM Users WHERE id = 20;

   Output:

       {"id":20,"firstname":"John","lastname":"Anderson","age":null,"income":null,"count":0}

If an MR_COUNTER is a part of the JSON document, you must supply a zero value explicitly to the MR_COUNTER.

Note:

- You can't supply the keyword DEFAULT while inserting a JSON MR_COUNTER.
- The system will return an error if you try to insert data into an MR table without supplying a value to the declared JSON MR_COUNTER field or using the keyword DEFAULT.

In the sample below, you insert a row into `JSONPersons` table. As it includes JSON MR_COUNTERs `counter` and `count` in the `people` document, you supply a zero value explicitly to these MR_COUNTERs.

    INSERT INTO JSONPersons VALUES (
       1,
      {
          "firstname":"David",
          "lastname":"Morrison",
          "age":25,
          "income":100000,
          "counter": 0,
          "books" : {
            "Title1" : "Gone with the wind",
            "Title2" : "Oliver Twist",
            "count" : 0
          }
        }
    );

The SELECT statement displays the following result:

    {"id":1,"person":{"age":25,"books":{"Title1":"Gone with the wind","Title2":"Oliver Twist","count":0},"counter":0,"firstname":"David","income":100000,"lastname":"Morrison"}};

Update MR_COUNTER

You can use the SET clause of the UPDATE statement to update MR_COUNTER in a Multi-Region table. You must only use the standard arithmetic computations to increment or decrement the value of MR_COUNTER. You can't use the UPDATE clauses to explicitly supply a value to MR_COUNTER or remove one from the table.

    UPDATE Users SET count = count + 10 WHERE id = 10 RETURNING *;

In the statement above, you increment the `count` value in the `Users` table by 10. The RETURNING clause fetches the following output:

    {"id":10,"firstname":"David","lastname":"Morrison","age":25,"income":100000,"count":10}

Similarly, you can update MR_COUNTER in a JSON document by incrementing or decrementing its value. You can access MR_COUNTER using its path expression as follows:

    UPDATE JSONPersons p SET p.person.books.count = p.person.books.count + 1 WHERE id = 1 RETURNING *;

In the statement above, you increment the MR_COUNTER `count` in the nested books document by one.

    {"id":1,"person":{"age":25,"books":{"Title1":"Gone with the wind","Title2":"Oliver Twist","count":1},"counter":0,"firstname":"David","income":100000,"lastname":"Morrison"}}

How system uses MR_COUNTER to handle concurrent modifications

When you create a Multi-Region table in different regions, it has the same definition. This implies, if you define any MR_COUNTER data type, it exists in both the remote and local regions. Every region can update the MR_COUNTER concurrently at its end. As all the Multi-Region tables in the participating regions are synchronized, the system automatically performs a merge on these concurrent modifications to reflect the latest updates of the MR_COUNTER without any user intervention.

Modify table to add or remove MR_COUNTER

You can use an ALTER TABLE statement to add or remove MR_COUNTER.

Adding MR_COUNTER

To add MR_COUNTER, use the ADD clause in the ALTER TABLE statement.

    ALTER TABLE Users (ADD countTwo INTEGER AS MR_COUNTER);

The statement above adds `countTwo` field as MR_COUNTER with a default value zero to the `Users` table.

The SELECT statement displays the following result:

    {"id":10,"firstname":"David","lastname":"Morrison","age":25,"income":100000,"count":10,"countTwo":0}
    {"id":20,"firstname":"John","lastname":"Anderson","age":null,"income":null,"count":0,"countTwo":0}

You can add MR_COUNTER to a JSON column as follows:

    ALTER TABLE JSONPersons (ADD JsonTwo JSON(counterTwo AS NUMBER MR_COUNTER));

The statement above adds a `JsonTwo` nested JSON document to the `JSONPersons` table and includes `counterTwo` field as MR_COUNTER with zero value:

    {
      "id" : 1,
      "person" : {
        "age" : 25,
        "books" : {
          "Title1" : "Gone with the wind",
          "Title2" : "Oliver Twist",
          "count" : 1
        },
        "counter" : 0,
        "firstname" : "David",
        "income" : 100000,
        "lastname" : "Morrison"
      },
      "JsonTwo" : {
        "counterTwo" : 0
      }
    }

Removing MR_COUNTER

To remove MR_COUNTER, use the DROP clause in the ALTER TABLE statement.

    ALTER TABLE Users (DROP countTwo);

The statement above removes `countTwo` MR_COUNTER from the `Users` table.

The SELECT statement displays the following result:

    {"id":10,"firstname":"David","lastname":"Morrison","age":25,"income":100000,"count":10}
    {"id":20,"firstname":"John","lastname":"Anderson","age":null,"income":null,"count":0}

You can remove a JSON document and its MR_COUNTER as follows:

    ALTER TABLE JSONPersons (DROP JsonTwo);

The statement above removes the `JSONTwo` nested JSON document from the `JSONPersons` table.

    {
      "id" : 1,
      "person" : {
        "age" : 25,
        "books" : {
          "Title1" : "Gone with the wind",
          "Title2" : "Oliver Twist",
          "count" : 1
        },
        "counter" : 0,
        "firstname" : "David",
        "income" : 100000,
        "lastname" : "Morrison"
      }
    }