Oracle NoSQL Database Cloud Service is a serverless, fully managed data store that delivers predictable single-digit millisecond response times and data replication for high availability. Developers can focus on building applications using any of the document, fixed-schema, and key-value models, without managing the underlying infrastructure, operations, and maintenance. We are pleased to announce the addition of the new Table Hierarchy model to this list. With this new feature, developers have additional flexibility to choose the best data model that meets their business and application workload requirements. This flexibility empowers businesses to solely focus on innovations for their customers with highly responsive and rich user experiences. Oracle’s NoSQL database offers significantly more flexibility compared to its competitors and no other competitor offers table hierarchies.

Learn more: Oracle NoSQL Database Cloud Service.

Table Hierarchy

In the domain of NoSQL databases, storing denormalized data is a given, and even advised. The underlying distributed nature of the NoSQL architecture makes it well-suited for scalability because it is fast and efficient. However, joining data across distributed partitions is prohibitively expensive, and conflicts with the goals of predictability, low latency, and scalability.

Table Hierarchies are a powerful feature in Oracle NoSQL Database which breaks this norm by offering high scalability while still providing the benefits of data normalization. A NoSQL table hierarchy is an ideal data model for applications that need some data normalization, but also require predictable, low latency at scale. A table hierarchy links distinct tables and therefore enables left outer joins, combining rows from two or more tables based on related columns between them. Such joins execute efficiently because rows from the parent-child tables are co-located in the same database shard.

Important Properties of Table Hierarchies in Oracle NoSQL Database Cloud Service

• A parent table and its child tables are always stored in the same compartment.
• A child table is counted against a tenancy’s total number of tables.
• A child table has its own tags which are independent of the parent table.
• 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.
• Metric information is collected and aggregated at the parent level.  Put differently, reads and writes performed at the child level are charged for and visible at the parent level. No metrics are visible at the child tables level.
• Authorization to the child table is handled via IAM policies. You need READ, INSERT, or DELETE privileges for the child table depending on the operations you perform; you need at least READ privilege on 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 will also be configured with the same pricing.

Learn more: Table Hierarchy: The Hidden Gem of Oracle NoSQL Database – Part 1

Benefits

• Storing data in a parent-child hierarchy is highly efficient – Parent and child rows are stored in separate NoSQL tables, reducing the size of parent rows compared to the single parent with child rows stored in nested arrays or maps. Write operations on parent or child tables create new versions of smaller rows and store these changes efficiently, given the append-only storage architecture of Oracle NoSQL Database.
• Highly performant for read and write workloads – The parent and its child rows reside in the same shard, enabling write and read operations to achieve high performance because all records in the hierarchy can be read or written in a single network call.
• Highly flexible for fine-grained authorization – Access rights to a parent or child table can be configured individually based on conditions at run-time and offer granular and flexible authorization.
• Scalable ACID transactions – Scalability, low latency, and ACID are balanced by co-locating parent and child data in the same shard.
• Table joins – Data can be queried using the nested table SQL operator or left outer join SQL operator.

Create a Child Table

The following interfaces can be used to create a NoSQL child table:

• Oracle Cloud Infrastructure (OCI) NoSQL cloud service console
• Oracle Cloud Infrastructure CLI
• Terraform
• Oracle NoSQL Database APIs available in Java, Python, Node.js, C#, and Go SDKs
• Oracle Cloud Infrastructure SDKs built on the REST API

Method I: Oracle NoSQL Database OCI Console

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. 

Creating a child table is simple. Sign in to your OCI account; in the services left panel, select Databases → Tables.

• Click on the table to view its details. The list of child tables already present for the table (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 the “Simple input” method or the “Advanced DDL input” method to create the child table.

Note: The “Set as shard key” checkbox is not shown while creating a child table, as 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. Child tables consume the read, write, and storage limits from their top-level parent.

Method II: Oracle Cloud Infrastructure CLI

You can use the CREATE TABLE statement to create a table as a child table of another table when running an OCI CLI command. 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(s) of the parent(s).

DDL_TABLE="CREATE TABLE IF NOT EXISTS test_table(id INTEGER, name STRING, age STRING, info JSON, PRIMARY KEY(SHARD(id)))"

oci nosql table create --compartment-id $NOSQL_COMP_ID --name "test_table" --ddl-statement "$DDL_TABLE" --table-limits="{\"maxReadUnits\": 50,  \"maxStorageInGBs\": 25,  \"maxWriteUnits\": 50 }"  --wait-for-state SUCCEEDED --wait-for-state FAILED

DDL_TABLE="CREATE TABLE IF NOT EXISTS test_table.test_child(idc INTEGER, email STRING, address json, PRIMARY KEY(idc))"

oci nosql table create --compartment-id $NOSQL_COMP_ID --name "test_table.test_child" --ddl-statement "$DDL_TABLE" --wait-for-state SUCCEEDED --wait-for-state FAILED

Note: There is no need to specify the table-limits parameter for the child table because the child table shares its top parent’s table limits.

Note: hild tables are supported in OCI-CLI v3.13.0 and higher. 

Method III: Terraform

You can use the CREATE TABLE statement to create a table as a child table of another table in your terraform script. The child table naming is done the same as in method II using a path name.

variable "table_ddl_statement" {
  default = "CREATE TABLE IF NOT EXISTS test_table(id INTEGER, name STRING, age STRING, info JSON, PRIMARY KEY(SHARD(id)))"
}

variable "childtable_ddl_statement" {
  default = "CREATE TABLE IF NOT EXISTS test_table.test_child(idc INTEGER, email STRING, address json, PRIMARY KEY(idc))"
}

resource "oci_nosql_table" "test_table" {
  #Required
  compartment_id = var.compartment_ocid
  ddl_statement  = var.table_ddl_statement
  name           = "test_table"
  table_limits {
    #Required
    max_read_units     = "50"
    max_write_units    = "50"
    max_storage_in_gbs = "25"
  }
}

resource "oci_nosql_table" "child_table" {
  #Required
  compartment_id = var.compartment_ocid
  ddl_statement  = var.childtable_ddl_statement
  name           = "test_table.test_child"
  depends_on = [oci_nosql_table.test_table]
}

Note: here is no need to specify the table_limits parameter for the child table because the child table shares its top parent’s table limits.

Note: You must specify “depends_on” to manage the dependencies relationship between the parent and child table.

Example: Using Left Outer Joins and Nested Table

Data can be queried using the nested table SQL operator or the Left Outer Join (LOJ) SQL operator. We will use NoSQL data from an airline baggage tracking application example.

For this example, there are four tables in a parent-child relationship. The ticket and ticket.passengerInfo table with all the personal information of passengers. ticket.bagInfo and ticket.bagInfo.flightLegs which lists the information about the baggage.

We will create these tables using one of the methods provided above and populate them.

CREATE TABLE IF NOT EXISTS ticket(ticketNo LONG, confNo STRING, PRIMARY KEY(ticketNo))
CREATE TABLE IF NOT EXISTS ticket.bagInfo(id LONG,tagNum LONG,routing STRING,lastActionCode STRING,lastActionDesc STRING,lastSeenStation STRING,lastSeenTimeGmt TIMESTAMP(4),bagArrivalDate TIMESTAMP(4), PRIMARY KEY(id))
CREATE TABLE IF NOT EXISTS ticket.bagInfo.flightLegs(flightNo STRING, flightDate TIMESTAMP(4),fltRouteSrc STRING,fltRouteDest STRING,estimatedArrival TIMESTAMP(4), actions JSON, PRIMARY KEY(flightNo))
CREATE TABLE IF NOT EXISTS ticket.passengerInfo(contactPhone STRING, fullName STRING,gender STRING, PRIMARY KEY(contactPhone))

We will use SQL statements to load a small amount of data in a couple of tables only.  These statements can be executed in the console.

INSERT INTO ticket VALUES(1762344493810,"LE6J4Z")
INSERT INTO ticket VALUES(1762392135540,"DN3I4Q")
INSERT INTO ticket VALUES(1762376407826,"ZG8Z5N")
INSERT INTO ticket VALUES(1762355527825,"HJ4J4P")
INSERT INTO ticket VALUES(1762324912391,"LN0C8R")
INSERT INTO ticket.bagInfo VALUES(1762344493810,79039899165297,17657806255240,"MIA/LAX/MEL","OFFLOAD","OFFLOAD","MEL","2019-02-01T16:13:00Z","2019-02-01T16:13:00Z")
INSERT INTO ticket.bagInfo VALUES(1762392135540,79039899156435,17657806224224,"GRU/ORD/SEA","OFFLOAD","OFFLOAD","SEA","2019-02-15T21:21:00Z","2019-02-15T21:21:00Z")
INSERT INTO ticket.bagInfo VALUES(1762376407826,7903989918469,17657806240229,"JFK/MAD","OFFLOAD","OFFLOAD","MAD","2019-03-07T13:51:00Z","2019-03-07T13:51:00Z")

More information: https://github.com/oracle/nosql-examples/blob/master/BaggageSchema/parentchildtbls_loaddata.sql

Next, we will perform basic analytics on the data using some complex queries. In this case, we will fetch all the bag details for all passengers who have been issued a ticket using the left outer join clause and the nested table clause. In the LOJ example, it selects all the rows from the ticket table and if that customer does not have any bagInfo then there will be a null value. The first NESTED TABLES is a rewrite of the LOJ example shown above it, and as a result, the output will look similar. In the final NESTED TABLES example, we switched the ordering of the tables. In this case, we are starting with the bagInfo table. This query will list the passengers that have baggage and any customers traveling only with carry-on luggage will be ignored.  The NESTED TABLE SQL operator specifies 3 possible groups of tables, namely, the target table (first in the list), ancestors (tables that are ancestors to the target table), and descendants (tables that are descendants to the target table).  When using the ancestor clause the number of rows returned is equal to the number of rows in the target table.  When using the descendent clause a null value is returned if the parent doesn’t have a child row.  In the LOJ example, we are showing just the first 4 rows.    

SELECT a.ticketNo as a_ticketNo , confNo as a_confNo,  b.routing as b_routing, b.lastActionDesc as b_lastActionDesc 
FROM ticket a LEFT OUTER JOIN ticket.bagInfo b
ON a.ticketNo=b.ticketNo
ORDER by b.routing 

SELECT a.ticketNo as a_ticketNo , confNo as a_confNo,  b.routing as b_routing, b.lastActionDesc as b_lastActionDesc 
FROM   NESTED TABLES (ticket a descendants(ticket.bagInfo b))
ORDER by b.routing

SELECT a.ticketNo as a_ticketNo , confNo as a_confNo,  b.routing as b_routing, b.lastActionDesc as b_lastActionDesc 
FROM NESTED TABLES (ticket.bagInfo b ancestors (ticket a))
ORDER by b.routing

Nested Tables Vs LOJ comparison

We encourage you to try using Table Hierarchies in your next project.

Learn more:

• Using Left Outer joins with parent-child tables
• Using NESTED TABLES to join parent-child tables