Joins
Oracle NoSQL Database does not currently support the general join operators found in more traditional relational database systems. However, it does support a special kind of join among tables that belong to the same table hierarchy. These joins can be executed efficiently, because only co-located rows may match with each other. As a result, transferring very large amounts of data among servers is avoided.
A JOIN clause is used to combine rows from two or more tables, based on a related column between them. Joins are predominantly used in an Oracle NoSQL Database when a user is trying to extract data from tables that have a hierarchical relationship between them.
Why do you need hierarchical tables in anOracle NoSQL Database?
As the Oracle NoSQL Database supports datatypes like an array, maps, etc, you may think that for each parent row, its matching child rows could be stored in the parent row itself inside an array or a 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.
Hierarchical tables in an Oracle NoSQL Database are :
- Very efficient for write-heavy workloads.
- More flexible for fine-grained authorization. Authorization is permission given to a user to access a resource. In a fine-grained authorization, the access rights given to a user for a resource may vary by conditions at run-time. In a hierarchical setup, access rights given to the parent table might be different from the access rights given to the child table, and so it is more flexible.
How do hierarchical tables work in an Oracle NoSQL Database?
The KVStore’s replication nodes are organized into shards. A single shard contains
multiple replication nodes and a master node. A shard key is created to distribute data
across the Oracle NoSQL Database cluster for scalability. Records with the same shard
key are co-located for easy reference and access. In a hierarchical table, the 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. All tables in the hierarchy have the same shard key columns.
Joining tables in an Oracle NoSQL Database
There are two ways by which you can join tables in the same hierarchy in an Oracle NoSQL Database.
- NESTED TABLES clause
- LEFT OUTER JOIN