Table Hierarchies
The Oracle NoSQL Database enables tables to exist in a parent-child relationship. This is known as table hierarchies.
Description of the illustration table-hierarchy.png
- Root Node: The topmost node of a tree or the node that does not have any parent node is called the root node. In the above diagram, A is the root node.
- Parent Node: The immediate predecessors of a node are called its parent nodes. For example, in the above diagram, A is the parent of B and G.
- Child Node: The immediate successors of a node are called its child nodes. For example, in the above diagram, B and G are children of node A.
- Leaf Node: The node that does not have any child node is called a leaf node. In the above diagram, C, D, H and I are leaf nodes.
- Ancestor: All the predecessor nodes on the paths from the root to that node are called its ancestor nodes. For example, in the above diagram, A and G are ancestors of H and I. A is the ancestor of G, H and I.
- Descendant: All the successor nodes on the paths from that node to the leaf nodes are called its descendant nodes. For example, in the above diagram, H and I are descendants of A and G. G, H and I are descendants of A.
- Sibling: Children of the same parent node are called siblings. For example, in the above diagram, B and G are siblings, as they are both children of node A. Similarly H and I are also siblings as they are children of node G.
The create table statement allows for a table to be created as a child 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.
Description of the illustration semantic-representation-table-hierarchy.png
Semantics
- A child table inherits the primary key columns of its parent table. This is done implicitly, without including the parent columns in the create table statement of the child. For example, in the following Example 5-13 example, table A.B has an extra column, called ida, and its primary key columns are ida and idb. Similarly, table A.B.C has 2 extra columns, ida and idb, and its primary key columns are ida, idb, and idc. The inherited columns are placed first in the schema of a child table.
- All tables in the hierarchy have the same shard key columns, which are specified in the create table statement of the root table. So, in our example, the common shard key is column ida. Trying to include a shard key clause in the create table statement of a non-root table will raise an error.
- A parent table cannot be dropped before its children are dropped.
- When two rows RC and RP from a child table C and its parent table P, respectively, have the same values on their common primary key columns, we say that RP and RC match, or that RP contains RC. In this case, RP and RC will also be co-located physically, because they have the same shard key. Given that a child table always has more primary key columns than its parent, a parent row may contain multiple child rows, but a child row will match with at most one parent row.
Note:
Oracle NoSQL Database does not require that all the rows in a child table have a matching row in the parent table. In other words, a referential integrity constraint is not enforced.Given that the Oracle NoSQL Database model includes arrays and maps, one may wonder why are child tables needed? After all, for each parent row, its matching child rows could be stored in the parent row itself inside an array or map. However, doing so could lead to very large parent rows, resulting in bad performance. This is especially true given the append-only architecture of the Oracle NoSQL Database store, which implies that a new version of the whole row is created every time the row is updated. So, child tables should be considered when each parent row contains a lot of child rows and/or the child rows are large. If, in addition, the child rows are not accessed very often or if they are updated very frequently, using child tables becomes even more appealing.
Example 5-13 Table Hierarchy
The following statements create a table hierarchy, that is a tree of tables connected by parent-child relationships. A is the root table, A.B and A.G are children of A, and A.B.C is a child of A.B (and a grandchild of A).
CREATE TABLE A (ida INTEGER, a1 STRING, a2 INTEGER, PRIMARY KEY(ida))CREATE TABLE A.B (idb INTEGER, b1 STRING, a2 STRING, PRIMARY KEY(idb))CREATE TABLE A.B.C (idc INTEGER, b1 STRING, c2 STRING, PRIMARY KEY(idc))CREATE TABLE A.G (idg INTEGER, g1 STRING, g2 DOUBLE, PRIMARY KEY(idg))Table Hierarchy in a Multi-Region table:
You can create child tables in an existing Multi-Region architecture.
Example 5-14 Table Hierarchy in a Multi-Region table - create table in two regions
users in two regions, FRA and LON.CREATE TABLE users (
id INTEGER,
name STRING,
team STRING,
PRIMARY KEY (id))
IN REGIONS FRA,LONCREATE TABLE users.userdet (
pan INTEGER,
address STRING,
email STRING,
PRIMARY KEY(pan))REATE TABLE users.userinfo (pan INTEGER, address STRING, email STRING, PRIMARY KEY(pan) IN REGIONS FRA,LON)Error handling command CREATE TABLE users.userinfo (
pan INTEGER,
address STRING,
email STRING,
PRIMARY KEY(pan) IN REGIONS FRA,LON): Error: at (5, 24) missing ')' at 'IN', at line 5:24
rule stack: [parse, statement, create_table_statement]desc as json table users.userdet{
"json_version" : 1,
"type" : "table",
"name" : "userdet",
"parent" : "users",
"regions" : {
"2" : "FRA",
"1" : "LON"
},
"fields" : [{
"name" : "id",
"type" : "INTEGER",
"nullable" : false
}, {
"name" : "pan",
"type" : "INTEGER",
"nullable" : false
}, {
"name" : "address",
"type" : "STRING",
"nullable" : true
}, {
"name" : "email",
"type" : "STRING",
"nullable" : true
}],
"primaryKey" : ["id", "pan"],
"shardKey" : ["id"]
}