5.7 SQL-Based Property Graph Query and Analytics

You can use SQL to query property graph data in Oracle Spatial and Graph.

For the property graph support in Oracle Spatial and Graph, all the vertices and edges data are persisted in relational form in Oracle Database. For detailed information about the Oracle Spatial and Graph property graph schema objects, see Property Graph Schema Objects for Oracle Database.

This chapter provides examples of typical graph queries implemented using SQL. The audience includes DBAs as well as application developers who understand SQL syntax and property graph schema objects.

The benefits of querying directly property graph using SQL include:

• There is no need to bring data outside Oracle Database.

• You can leverage the industry-proven SQL engine provided by Oracle Database.

• You can easily join or integrate property graph data with other data types (relational, JSON, XML, and so on).

• You can take advantage of existing Oracle SQL tuning and database management tools and user interface.

The examples assume that there is a property graph named connections in the current schema. The SQL queries and example output are for illustration purpose only, and your output may be different depending on the data in your connections graph. In some examples, the output is reformatted for readability.

• Simple Property Graph Queries
  The examples in this topic query vertices, edges, and properties of the graph.
• Text Queries on Property Graphs
  If values of a property (vertex property or edge property) contain free text, then it might help performance to create an Oracle Text index on the V column.
• Navigation and Graph Pattern Matching
  A key benefit of using a graph data model is that you can easily navigate across entities (people, movies, products, services, events, and so on) that are modeled as vertices, following links and relationships modeled as edges. In addition, graph matching templates can be defined to do such things as detect patterns, aggregate individuals, and analyze trends.
• Navigation Options: CONNECT BY and Parallel Recursion
  The CONNECT BY clause and parallel recursion provide options for advanced navigation and querying.
• Pivot
  The PIVOT clause lets you dynamically add columns to a table to create a new table.
• SQL-Based Property Graph Analytics
  In addition to the analytical functions offered by the graph server (PGX), the property graph feature in Oracle Spatial and Graph supports several native, SQL-based property graph analytics.