Using Console to Create Tables in Oracle NoSQL Database Cloud Service

Learn how to create and manage Oracle NoSQL Database Cloud Service tables and indexes from the console.

This article has the following topics:

Creating a Compartment

When you sign up for Oracle Cloud Infrastructure, Oracle creates your tenancy with a root compartment that holds all your cloud resources. You then create additional compartments within the tenancy (root compartment) and corresponding policies to control access to the resources in each compartment. Before you create an Oracle NoSQL Database Cloud Service table, Oracle recommends that you set up the compartment where you want the table to belong.

You create compartments in Oracle Cloud Infrastructure Identity and Access Management (IAM). See Setting Up Your Tenancy and Managing Compartments in Oracle Cloud Infrastructure Documentation.

Creating Singleton Tables

You can create new Oracle NoSQL Database Cloud Service table from the NoSQL console.

The NoSQL console lets you create the Oracle NoSQL Database Cloud Service tables in two modes:

Simple Input Mode: You can use this mode to create the NoSQL Database Cloud Service table declaratively, that is, without writing a DDL statement.
Advanced DDL Input Mode: You can use this mode to create the NoSQL Database Cloud Service table using a DDL statement.

Creating Table: Simple Input Mode

Learn how to create a table from the NoSQL console by using the Simple Input table creation mode.

To create a table:

Access the NoSQL console from the Infrastructure Console. See Accessing the Service from the Infrastructure Console .
Click Create Table.
In the Create Table dialog, select Simple input for Table Creation Mode.
Under Reserved Capacity, you have the following options.
- Always Free Configuration:
  
  Enable the toggle button to create an Always Free NoSQL table. Disabling the toggle button creates a regular NoSQL table. You can create up to three Always Free NoSQL tables in the tenancy. If you have three Always Free NoSQL tables in the tenancy, the toggle button to create an Always Free SQL table is disabled.
  
  If you enable the toggle button to create an Always Free NoSQL table, the Read capacity, Write capacity, and Disk storage fields are assigned default values. The Capacity mode becomes Provisioned Capacity. These values cannot be changed.
  If you want to create a regular table, then disable the toggle button. You will be able to enter the appropriate capacity values for the table.
  - Read Capacity (ReadUnits): Enter the number of read units. See Estimating Capacity to learn about read units.
  - Write Capacity (WriteUnits): Enter the number of write units. See Estimating Capacity to learn about write units.
  - Disk Storage (GB): Specify the disk space in gigabytes (GB) to be used by the table. See Estimating Capacity to learn about storage capacity.
- Capacity mode
  
  You can specify the option for Capacity mode as Provisioned Capacity or On Demand Capacity. Provisioned Capacity and On Demand Capacity modes are mutually exclusive options. If you enable On Demand Capacity for a table, you don't need to specify the read/write capacity of the table. You are charged for the actual read and write units usage, not the provisioned usage.
  Enabling On Demand Capacity for a table is a good option if any of the following are true:
  1. You create new tables with unknown workloads.
  2. You have unpredictable application traffic.
  3. You prefer the ease of paying for only what you use.
  Limitations of enabling On Demand Capacity for a table:
  1. On Demand Capacity limits the capacity of the table to 5,000 writes and 10,000 reads.
  2. The number of tables with On Demand Capacity per tenant is limited to 3.
  3. You pay more per unit for On Demand Capacity table units than provisioned table units.
  Selecting On Demand Capacity disables Always Free Configuration. The Read Capacity and Write Capacity input boxes become read-only and show the text On Demand Capacity. The On Demand Capacity tables will show On Demand Capacity in their read and write capacity columns. If Capacity mode is On Demand Capacity then the Always Free control is disabled.
In the Name field, enter a table name that is unique within your tenancy.
Table names must conform to Oracle NoSQL Database Cloud Service naming conventions. See Oracle NoSQL Database Cloud Service Limits .
In the Primary Key Columns section, enter primary key details:
- Column Name: Enter a column name for the primary key in your table. See Oracle NoSQL Database Cloud Service Limits to learn about column naming requirements.
- Type: Select the data type for your primary key column.
- Precision:This is applicable for TIMESTAMP typed columns only. Timestamp values have precision in fractional seconds that range from 0 to 9. For example, a precision of 0 means that no fractional seconds are stored, 3 means that the timestamp stores milliseconds and 9 means a precision of nanoseconds. 0 is the minimum precision, and 9 is the maximum.
- Set as Shard Key: Click this option to set this primary key column as shard key. Shard key is to distribute data across the Oracle NoSQL Database Cloud Service cluster for increased efficiency, and to position records that share the shard key locally for easy reference and access. Records that share the shard key are stored in the same physical location and can be accessed atomically and efficiently.
- + Another Primary Key Column: Click this button to add more columns while creating a composite (multi-column) primary key.
- Use the up and down arrows to change the sequence of columns while creating a composite primary key.
In the Columns section, enter non-primary column details:
- Column Name: Enter the column name. Ensure that you conform to column naming requirements described in Oracle NoSQL Database Cloud Service Limits .
- Type: Select the data type for your column.
- Precision:This is applicable for TIMESTAMP typed columns only. Timestamp values have precision in fractional seconds that range from 0 to 9. For example, a precision of 0 means that no fractional seconds are stored, 3 means that the timestamp stores milliseconds and 9 means a precision of nanoseconds. 0 is the minimum precision, and 9 is the maximum.
- Size: This is applicable for BINARY typed columns only. Specify the size in bytes to make the binary a fixed binary.
- Default Value: (optional) Supply a default value for the column.
  Note
  
  Default values can not be specified for binary and JSON data type columns.
- Value is Not Null: Click this option to specify that a column must always have a value.
- + Another Column: Click this button to add more columns.
- Click the delete icon to delete a column.
(Optional) To specify advanced options, click Show Advanced Options and enter advanced details:
- Table Time to Live (Days): (optional) Specify expiration duration (no. of days) for the rows in the table. After the number of days, the rows expire automatically, and are no longer available. The default value is zero, indicating no expiration time.
  Note
  
  Updating Table Time to Live (TTL) will not change the TTL value of any existing data in the table. The new TTL value will only apply to those rows that are added to the table after this value is modified and to the rows for which no overriding row-specific value has been supplied.
In the Tags section, enter:
- Tag Namespace: Select a tag namespace from the select list. A tag namespace is like a container for your tag keys. It is case insensitive and must be unique across the tenancy.
- Tag Key: Enter the name to use to refer to the tag. A tag key is case insensitive and must be unique within a namespace.
- Value: Enter the value to give your tag.
- + Additional Tag: Click to add more tags.
Click Create table.

The table is created and listed in the NoSQL console.

To view help for the current page, click the help link at the top of the page.

Creating Table: Advanced DDL Input Mode

Learn how to create a table from the NoSQL console by using the Advanced DDL Input table creation mode.

To create a table:

Access the NoSQL console from the Infrastructure Console. See Accessing the Service from the Infrastructure Console .
Click Create Table.
In the Create Table window, select Advanced DDL Input for Table Creation Mode.
Under Reserved Capacity, you have the following options.
- Always Free Configuration:
  
  Enable the toggle button to create an Always Free NoSQL table. Disabling the toggle button creates a regular NoSQL table. You can create up to three Always Free NoSQL tables in the tenancy. If you have three Always Free NoSQL tables in the tenancy, the toggle button to create an Always Free SQL table is disabled.
  
  If you enable the toggle button to create an Always Free NoSQL table, the Read capacity, Write capacity, and Disk storage fields are assigned default values. The Capacity mode becomes Provisioned Capacity. These values cannot be changed.
  If you want to create a regular table, then disable the toggle button. You will be able to enter the appropriate capacity values for the table.
  - Read Capacity (ReadUnits): Enter the number of read units. See Estimating Capacity to learn about read units.
  - Write Capacity (WriteUnits): Enter the number of write units. See Estimating Capacity to learn about write units.
  - Disk Storage (GB): Specify the disk space in gigabytes (GB) to be used by the table. See Estimating Capacity to learn about storage capacity.
- Capacity mode
  
  You can specify the option for Capacity mode as Provisioned Capacity or On Demand Capacity. Provisioned Capacity and On Demand Capacity modes are mutually exclusive options. If you enable On Demand Capacity for a table, you don't need to specify the read/write capacity of the table. You are charged for the actual read and write units usage, not the provisioned usage.
  Enabling On Demand Capacity for a table is a good option if any of the following are true:
  1. You create new tables with unknown workloads.
  2. You have unpredictable application traffic.
  3. You prefer the ease of paying for only what you use.
  Limitations of enabling On Demand Capacity for a table:
  1. On Demand Capacity limits the capacity of the table to 5,000 writes and 10,000 reads.
  2. The number of tables with On Demand Capacity per tenant is limited to 3.
  3. You pay more per unit for On Demand Capacity table units than provisioned table units.
  Selecting On Demand Capacity disables Always Free Configuration. The Read Capacity and Write Capacity input boxes become read-only and show the text On Demand Capacity. The On Demand Capacity tables will show On Demand Capacity in their read and write capacity columns. If Capacity mode is On Demand Capacity then the Always Free control is disabled.
In the DDL input section, enter the create table DDL statement for Query. You may get an error that your statement is Incomplete or faulty. See Debugging SQL statement errors in the OCI console to learn about possible errors in the OCI console and how to fix them. See Developers Guide for examples on create table statement.
(Optional) To specify advanced options, click Show Advanced Options and enter advanced details:
- Tag Namespace: Select a tag namespace from the select list. A tag namespace is like a container for your tag keys. It is case insensitive and must be unique across the tenancy.
- Tag Key: Enter the name to use to refer to the tag. A tag key is case insensitive and must be unique within a namespace.
- Value: Enter the value to give your tag.
- + Additional Tag: Click to add more tags.
Click Create Table.

The table is created and listed in the NoSQL console.

To view help for the current page, click the help link at the top of the page.

Creating a child table

With Oracle NoSQL Database, you can create tables in a hierarchical structure( as parent-child tables).

Table Hierarchies

You can use the create table statement to create a table as a child table of another table, which then becomes the parent of the new table. This is done by using a composite name (a name_path) for the child table. A composite name consists of a number N (N > 1) of identifiers separated by dots. The last identifier is the local name of the child table and the first N-1 identifiers are the name of the parent.

   
           A   
         /   \ 
      A.B    A.G  
       /  
    A.B.C   
     /  
   A.B.C.D

The top-most parent table is A. The child table B gets the composite name A.B. The next level of child table C gets the composite name A.B.C and so on.

Properties of child tables:

You cannot specify the Read capacity, Write capacity, or Disk storage limits while creating a child table. The child table shares the corresponding values from the parent table.
A child table is counted against a tenancy's total number of tables.
A parent table and its child tables are always in the same compartment.
Metric information is collected and aggregated at the parent level. No metrics are visible at the child tables level.
A child table has its own tags independent of the parent table.
A child table also inherits the capacity pricing model of the parent table. For example, if the parent table is configured with On Demand Capacity, the child table can also be configured with the same capacity pricing model.

Transactions in parent-child tables

A parent table and a child table share the same shard key. Using child tables, you can achieve ACID transactions across multiple objects using the following two simple steps:

Declare a table as a child of another table.
Use writeMutliple API to add operations for both parent and child tables.

If child tables are not there, achieving ACID transactions across multiple objects is a tedious procedure. Without child tables, you do the following:

Find the shard key values for all the objects that you want to include in a transaction.
Make sure that the shard keys for all the objects are equal.
Use writeMutliple API to add every object to a collection.

Use child tables to easily achieve ACID transactions across multiple objects.

Authorization in a child table:

If you don't own a table and you want to read from, delete or insert into this table, two conditions must be met:

You have the specific privilege (READ/INSERT/DELETE) for the child table.
You have the same privileges, or at least the read privilege, for the parent table of the specific child table in the hierarchy.

See IAM policies for authorization for more details.

For example, if you want to insert data into the child table myTable.child1, which you don't own, then you must have the INSERT privilege on the child table and READ and/or INSERT privileges on myTable. Granting privileges to child tables is independent of granting privileges to the parent table. That means you can give specific privileges to the child table without giving the same privilege to its parent table. Any parent/child join queries require the relevant privileges on all tables used in the query. See Using Left Outer joins with parent-child tables for more details.

Creating a child table

Click on the parent table to view its details. The list of child tables already present for the parent is displayed.
In the left navigation menu, under Resources, click Child tables.
- The list of child tables for the parent table is displayed. To create a child table, click Create Child Table.
You can choose Simple input method or Advanced DDL input method to create the child table.
Specify a name for the child table. This is automatically prefixed with the name of the parent table followed by a dot. Specify the list of columns and primary key columns.
The Set as shard key checkbox is not shown while creating a child table, as the child tables inherit their shard key from their top-level parent table.

Note

The Read Capacity, Write Capacity, and Disk Storage fields are not specified because a child table inherits these limits from the top-level table. The limits set for the top-level table are automatically applied to the child table.

Viewing the details of a child table

You can view the details of a child table after it is created.
Description of viewtbl-childtbl.png follows

Creating a Global Active table

You can create a Global Active table from the NoSQL console.

See Global Active tables in NDCS to get an overview of what a Global Active table is and when you need to create a Global Active table.

To create a Global Active table:

First, create a singleton table withat least one JSON column. Access the NoSQL console from the Infrastructure Console. See Accessing the Service from the Infrastructure Console . Click Create Table. You can create the table using one of the following:
- Creating Table: Simple Input Mode
- Creating Table: Advanced DDL Input Mode
Freeze the schema of the table created. Under Table information, you can view the details of the table after it is created. The schema state of the table is currently Mutable. Click Freeze to freeze the schema of the table. The Schema state changes to Frozen.
A NoSQL Database table becomes a Global Active table only after you add a regional table replica of it. The regional table replica is created in another region.
In the Table Details page, click Replicas under Resources. You get a list of replicas of the table. Click Add replica.
In the Add replica page, the list of regions is listed in the drop-down for the Replication region. Choose the region where you want to create a regional replica. Choose the read capacity and write capacity of the table in the replica region. Storage capacity cannot be changed and remains the same as in the sender region table. Click Add replica.
Note

You can choose to have the same read capacity and write capacity of the sender region table in the replicas. In that case, the values need not be changed or edited.

The singleton table is now changed to a Global Active table. The regional table replica will be initialized with the data of the table in the sender region. Soon after the regional table replica is created and all data is copied to the regional table replica, you see that the Local replica initialized completeness becomes 100%.

Creating Indexes

Learn how to create indexes in Oracle NoSQL Database Cloud Service tables from the NoSQL console.

To create indexes:

Access the NoSQL console from the Infrastructure Console. See Accessing the Service from the Infrastructure Console .
The NoSQL console lists all the tables in the tenancy.
In the Table Details page, select the Indexes tab under Resources.
You will see a list of all the indexes added to the table.
Click Add Index.
In the Create Index window, enter a name for the index that is unique within the table. See Oracle NoSQL Database Cloud Service Limits to learn about the naming restrictions for indexes.
In the Index Columns section, enter index details:
- Index Column Name: Select the column that you would like included in the index.
- + Another Index Column: Click this button to include another column in the index.
- Use the up and down arrow to change the sequence of the columns in the index being created.
- Click the delete icon next to any column to remove it from the index being created.
Click Create Index.
The index is created.

To view help for the current page, click the help link at the top of the page.