Oracle Database 23c as Graph Database–SQL Property Graph for network style querying

I bet you are used to relational data structures that you query using SQL. And so do I. And there is nothing in terms of data that a good SQL query cannot answer. OK, the query can become quite long – with inline expressions and multiple joins – but it can answer almost any question without fail. While that is true, there are different perspectives on data possible. Rather than the tables and foreign keys/join condition view that we tend to take from the relational (pure SQL) world, there is a view on data that focuses on the network structure of data: the data set is defined in terms of vertices and edges. Nodes in a network and the relationships between these nodes. Some data – or: sometimes data – is better represented and analyzed from that perspective.

A quick example: tables in our database frequently references other tables through foreign keys. Using the Data Dictionary Views, we can use SQL queries to learn about these dependencies between tables. Some of those queries are not intuitive to read or write. Some questions cannot easily be answered – at least for someone not well versed in SQL and in the structure of the Data Dictionary.

This particular aspect of a relational database can easily be sketched as a property graph: one vertex type (table) and one edge type (foreign key).

Using a SQL Property Graph, the question whether we have any tables that reference themselves becomes as simple as:

MATCH (a IS database_table) -[fk IS foreign_key]-> (b IS database_table) WHERE a.table_name = b.table_name

Read this is: start with all vertices of type database_table. Follow all their edges of type foreign_key to the database_table vertex at the other end of the edge. Then look for all cases where two vertices are in fact the same table. That is a case of a self referencing foreign key.

And to find pairs of tables that both reference the same (lookup or parent) table, we can simply write:

MATCH (a IS database_table) -[fk IS foreign_key]-> (b IS database_table) <-[fk2 IS foreign_key]- ( c IS database_table)
WHERE a.table_name != c.table_name

This starts in the same way: start with all vertices of type database_table. Follow all their edges of type foreign_key to the database_table vertex at the other end of the edge. Then find all incoming (hence the <- symbol) edges to this vertex. And eliminate results where the two tables with the same common table reference are in fact the same table.

To find tables that have multiple foreign keys to one specific table:

MATCH (a IS database_table) -[fk IS foreign_key]-> (b IS database_table) <-[fk2 IS foreign_key]- ( c IS database_table)
WHERE a.table_name = c.table_name and fk.constraint_name != fk2.constraint_name

Start with all vertices of type database_table. Follow all their edges of type foreign_key to the database_table vertex at the other end of the edge. Then find all incoming (hence the <- symbol) edges to this vertex. And only keep results where the two tables with the same common table reference are the same table but the foreign key constraints are different.

And one more: find nested hierarchies of child, parent and grandparent table.

MATCH (a IS database_table) -[fk IS foreign_key]-> (b IS database_table) -[fk2 IS foreign_key]-> ( c IS database_table)

As before, start with all vertices of type database_table. Follow all their edges of type foreign_key to the database_table vertex at the other end of the edge. And from there (the parent table) follow again all edges of type foreign_key to the database_table vertex at the other end of the edge (bringing us to the grand parent).

I have created a database schema with Formula One data – the schema used by Alex Nuijten and Patrick Barel in their book Modern Oracle Database Programming – and executed these queries.

The database schema is visualized here:

Can we quickly spot self referencing foreign keys? Tables with multiple foreign keys to the same table? Nested hierarchies?   Well, perhaps with a nice diagram like this one we could. It would be less easy if we do not have the diagram, if the diagram is not correct or if the number of tables is several 100 or even more than 1000.

In those cases, the property graph approach can help us.

Self referencing foreign keys:

There are none. That is a little bit disappointing.

What about common tables – tables referenced by multiple tables:

Any cases of tables having more than one foreign key to another table:

And what about nested hierarchies – trios of child, parent and grandparent table:

You may not have a huge interest in this information. Or you may feel well equipped to write your own SQL to learn this information. However, using the property graph approach can make certain investigations and data explorations much simpler and more intuitive. Not just for you but for your colleagues who may not be such wizards in SQL. If for no other reason, give the property graph some thought.

What I have not shown yet is the actual creation of the property graph. Before you can start querying against table_references_graph you need to create that object. The DDL statement to create the property graph looks as follows:

The graph definition includes all vertex tables (in this case only one) as well as all edge tables (again, here only one). Multiple labels (types of edges or vertices) can be associated with the same table. Every edge and vertex needs a KEY definition – a unique column or combination of columns. An edge needs to be defined with a source (where does the edge start from) and a destination (which vertex table is the target of the edge) For each edge and vertex, properties are defined – values based on columns or column expressions. 

Edge and Vertex tables in a Property Graph cannot be defined on a View (unfortunately): they must have a real table or materialized view as their underpinning. I hope that limitation will be lifted in the future. I consider it a serious hindrance for using the property graph!

In this example, I have created two materialized views on data dictionary views, as follows:

These materialized views represent the vertex (tables_mv) and edges (foreign_keys_mv) respectively. It is a bit of an unfortunate workaround – but it will (have to) do.

Resources

SQL scripts demonstrated in this article: https://github.com/lucasjellema/gitpod-oracle-database-23c-free/blob/main/explorations/sql-property-graph.sql.

GitHub Repo with sources for this article: https://github.com/lucasjellema/gitpod-oracle-database-23c-free.

Oracle Database 23c Free environment with the Formular One data schema can be started using a Gitpod workspace definition. Read about it in this article: https://technology.amis.nl/database/live-handson-environment-for-modern-oracle-database-programming/