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).

Property Graph for tables and their foreign keys

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