SQL GRAPH_TABLE Queries

5 SQL GRAPH_TABLE Queries

You can query a SQL property graph using the GRAPH_TABLE operator to express graph pattern matching queries.

Graph pattern matching allows you to define a path pattern and match it against a graph to obtain a set of solutions. You must provide the graph to be queried as an input to the GRAPH_TABLE operator along with the MATCH clause containing the graph patterns to be searched as shown:

SELECT * FROM GRAPH_TABLE (students_graph
  MATCH
  (a IS person) -[e IS friends]-> (b IS person WHERE b.name = 'Mary')
  WHERE a.name='John'
  COLUMNS (a.name AS person_a, b.name AS person_b)
);

A basic GRAPH_TABLE query is made up of the following components:

FROM clause: It includes the GRAPH_TABLE operator which takes the input graph name as the first parameter.
MATCH clause: It expresses the graph element patterns (vertex or edge pattern) to be searched on the SQL property graph. It can optionally include an element pattern WHERE clause as seen in the preceding example ((b IS person WHERE b.name = 'Mary')) query. This in-line WHERE clause can access any matched variable.
WHERE clause: This is an optional out-of-line WHERE clause. Similar to the element pattern WHERE clause, it has access to all the graph pattern variables and expresses a predicate that applies to the entire pattern in the MATCH clause.
COLUMNS clause: This contains the query output columns.

5.1 About Graph Patterns

A SQL GRAPH_TABLE query is composed of graph patterns.

Graph patterns are expressed between the input graph name and the COLUMNS clause in a GRAPH-TABLE query.

Graph patterns are made up of one or more vertex and edge patterns. For example, the following graph pattern has two vertex patterns and one edge pattern:

(v1) -[e]-> (v2)

A vertex pattern is enclosed in parentheses and specifies how to match a single vertex. An edge pattern is enclosed by a square bracket with delimiters on the left and right side of the edge pattern and specifies how to match a single edge.

Also, the available arrow tokens for edge patterns are summarized in the following table:

Table 5-1 Arrow Tokens for Edge Patterns

Directionality	Bracketed Syntax	Abbreviated Syntax^Foot 1
Directed to the right	`-[ ]->`	`->`
Directed to the left	`<-[ ]-`	`->`
Any directed edge (right or left)	`<-[ ]->` or `-[ ]-`	`-`

^Footnote 1

There are no brackets for the arrows in the “abbreviated syntax” column.
All edge labels will be considered as no edge label is specified. Hence, filtering on a specific edge is not supported.

A graph element pattern (which can either be a vertex or an edge pattern) may in turn optionally include:

An element variable.
A label expression which is that part in an element pattern that starts with the keyword IS and is followed by a list of one or more label names. If there is more than one label name, then these are separated by vertical bars.
An element pattern WHERE clause which expresses a search condition on the element variable declared by the element pattern.

The following sections explain the graph pattern concepts more in detail:

Graph Element Variables
Vertex and edge pattern variables ranges over vertices and edges respectively.
Label Expressions
A label expression in a vertex or an edge element pattern is introduced by the keyword IS.
Accessing Label Properties
You can access a property inside a graph element pattern, in the out-of-line WHERE clause or in the COLUMNS clause.

Parent topic: SQL GRAPH_TABLE Queries

5.1.1 Graph Element Variables

Vertex and edge pattern variables ranges over vertices and edges respectively.

For example, consider the following graph pattern which contains three graph element variables.

(v1)–[e]->(v2)

In the preceding graph pattern, v1 and v2 are two vertex pattern variables and e is an edge pattern variable.

Ensure that you apply the following rules for the graph pattern variables:

You cannot use the same variable name for both a vertex and an edge.
You can use the same variable name in two different vertex patterns as shown:
MATCH (a IS person) -> (a IS person)

In the preceding example, the vertex variable a is used in two vertex patterns - (a IS person) and (a IS person). This implies that the two vertex patterns that declare the same vertex variable must bind to the same vertex. Thus the vertex variable binds to a unique vertex but the vertex pattern can appear multiple times in the same graph pattern.
You can use the same variable name in two different edge patterns.
Anonymous (that is, omitted) vertex and edge variables are supported. See Example 5-8.

Parent topic: About Graph Patterns

5.1.2 Label Expressions

A label expression in a vertex or an edge element pattern is introduced by the keyword IS.

For example, in the following graph pattern, the vertex pattern associated with the graph element variable v1 has the label person. Also, the edge pattern associated with the graph element variable e contains the label friendOf:

(v1 IS person)–[e IS friendOf]->(v2)

If the label is omitted in a graph element pattern, then the default is to query all vertices or edges.

A label expression can also include an optional in-line SQL search condition that can access any matched variable. When accessing a property, you must specify a graph pattern variable.

The supported vertex and edge label expressions are described in the following table:

Table 5-2 Supported Vertex and Edge Label Expressions

Vertex Label Expression	Edge Label Expression	Description
`(a)`	`[e]`	The vertex graph pattern variable `a` may match a vertex with any label. The edge graph pattern variable `e` may match an edge with any label.
`()`	`[]`	The vertex pattern has no label and can match any vertex. The edge pattern has no label and can match any edge. When a graph pattern variable is not specified, a unique vertex or edge variable name is internally generated by the system. Therefore, you cannot reference the vertex or edge elsewhere in the query, as it is unknown.
`(IS person)`	`[IS friend_of]`	The vertex pattern has only the `person` label. The edge pattern has only the `friend_of` label. When a graph pattern variable is not specified, a unique vertex or edge variable name is internally generated by the system. Therefore, you cannot reference the vertex or edge elsewhere in the query, as it is unknown.
`(IS person\|place\|thing)`	`[IS friend_of\|student_of]`	The vertex pattern has an alternation of three labels, `person`, `place` and `thing`. This implies that the vertex pattern can match any vertex having those labels. The edge pattern has an alternation of two labels, `friend_of` and `student_of`. This implies that the edge pattern can match any edge having those labels. As there is no explicit graph pattern variable in the vertex or edge pattern, you cannot reference this vertex or edge elsewhere in the query.
`(a IS person\|place\|thing)`	`[e IS friend_of\|student_of]`	Same as the preceding table entry. However, the vertex and edge patterns contain `a` and `e` as vertex and edge graph pattern variables respectively. Therefore, you can reference the vertex or edge using the respective graph pattern variables elsewhere in the query. See Example 5-12 which describes a `GRAPH_TABLE` query that uses label disjunction in the vertex pattern.
`(a IS person),` `(a IS car)`	`(a)–[e IS L1]->(b),` `(a)–[e is L2]->(b)`	The vertex pattern `a IS person` implies that `a` must match vertices having the label `person`, and the vertex pattern `a IS car` implies that `a` must match vertices having the label `car`. Therefore, this represents that `a` must match vertices having both `person` and `car` as labels, effectively an `AND` of these two conditions. Also, you can reference a vertex as `a` elsewhere in the query. The edge pattern `e IS L1` implies that `e` must match edges having the label `L1`, and the edge pattern `e IS L2` implies that `e` must match edges having the label `L2`. Therefore, this represents that `e` must match edges having both `L1` and `L2` as labels, effectively an `AND` of these two conditions. Also, you can reference an edge as `e` elsewhere in the query. See Example 5-13 which describes a `GRAPH_TABLE` query that uses conjunction of labels in the vertex pattern.
`(a IS person WHERE a.name = 'Fred')`	`[e IS student_of WHERE e.subject = 'Arts']`	The vertex pattern has a label `person` and a vertex graph pattern variable `a`, which is qualified in the element pattern `WHERE` clause. The edge pattern has a label `student_of` and an edge graph pattern variable `e`, which is qualified in the element pattern `WHERE` clause. The only graph pattern variable that is visible within an element pattern is the graph pattern variable defined locally by the element pattern. Graph pattern variables from another element patterns cannot be accessed. See Example 5-5.

Parent topic: About Graph Patterns

5.1.3 Accessing Label Properties

You can access a property inside a graph element pattern, in the out-of-line WHERE clause or in the COLUMNS clause.

Consider the following graph element pattern where a is a graph element variable and name is a property name:

(a IS person WHERE a.name='John')

You can then reference the property in the WHERE clause inside the graph element pattern as a.name. This means a.name references the property name of the graph element bound to the graph pattern variable a.

Also, the following conditions apply when accessing a property:

The property name is part of at least one table that satisfies the label expression.
A graph variable name must always be used to access a property.
At the time of the GRAPH_TABLE query compilation, certain type checking rules apply for the vertex or edge table properties. See Type Compatibility Rules for Determining Property Types for more information.

The following examples describe a few scenarios for determining property types when querying SQL property graphs. Note that Example 5-1 to Example 5-3 refer to the SQL property graph definition for g1 which contains height as a shared property across different labels.

Example 5-1 Determining the Property Type for a Single Label

The data type for a.height in the following query is FLOAT:

SELECT * FROM GRAPH_TABLE (g1
MATCH
(a IS person)
COLUMNS (a.height)
);

The query output is as shown:

HEIGHT
----------
       1.8
      1.65
      1.75
       1.7

Example 5-2 Determining Union Compatible Property Type for Two Different Labels

The data type for a.height in the following query is the union compatible type between FLOAT and BINARY_DOUBLE:

SELECT * FROM GRAPH_TABLE (g1
MATCH
(a IS person|t3)
COLUMNS (a.height)
);

The query output is as shown:

    HEIGHT
----------
  1.8E+000
 1.65E+000
 1.75E+000
  1.7E+000
  1.8E+000
 1.65E+000

In the SQL property graph g1, the property type for height associated with the labels person and t3 is FLOAT and BINARY_DOUBLE respectively. BINARY_DOUBLE takes precedence over FLOAT and hence the resulting output property type for a.height is BINARY_DOUBLE.

Example 5-3 No Union Compatible Property Type for Two Different Labels

Error is thrown for the following query as the data type for a.height is not union compatible across the tables, person (FLOAT) and t2 (VARCHAR):

SELECT * FROM GRAPH_TABLE (g1
  MATCH
   (a IS person|t2)
  COLUMNS (a.height)
  );

On execution. the preceding query throws the error - ORA-01790: expression must have same datatype as corresponding expression

Example 5-4 Determining Union Compatible Property Type for Shared Labels

Consider the SQL property graph definition for g3 which uses a shared label (t) that is associated with a shared property name (height).

When querying g3, the data type for a.height in the following GRAPH_TABLE query is BINARY_DOUBLE:

SELECT * FROM GRAPH_TABLE (g3
MATCH
(a IS t)
COLUMNS (a.height)
);

The query output is a union of the property columns across all the graph element tables sharing the label. Also, the property type is BINARY_DOUBLE as per the Type Compatibility Rules for Determining Property Types:

    HEIGHT
----------
  1.8E+000
 1.65E+000
 1.75E+000
  1.7E+000
  1.8E+000
 1.65E+000

Parent topic: About Graph Patterns

5.2 Variable-Length Path Patterns

Variable-length graph patterns provide advanced querying support for SQL property graphs.

Variable-length graph patterns require recursion such that there is a variable number of joins when translated into a relational query.

Bounded recursive path patterns that include one or more of the following quantifiers are supported:

Table 5-3 Quantifier Support for Variable-Length Graph Patterns

Quantifier	Description
`{n}`	Exactly `n`
`{n, m}`	Between `n` and `m` (inclusive)
`{, m}`	Between `0` and `m` (inclusive)
`?`	`0` or `1`

Note that the maximum upper bound limit for the quantifiers in the preceding table is 10.

See Example 5-14 for sample GRAPH_TABLE queries using the quantifiers described in the preceding table.

Parent topic: SQL GRAPH_TABLE Queries

5.3 Complex Path Patterns

You can query a SQL property graph using complex path patterns.

Cyclic Path Patterns

Vertex and edge path patterns can form cycles. For instance, consider the following graph pattern:

MATCH (a IS person) -[IS friends]-> (a IS person)

The preceding graph pattern describes a single path pattern, and it contains the vertex variable a twice. Thus, this finds cycles in the graph such that a binds to a person that has a friends edge to itself.

Also, note the following:

The label person for the vertex variable a need not be repeated twice. The result is the same with or without repeating the label expression.
You can use multiple in-line WHERE clauses to add conditions on the same pattern variable.
Using the same edge variable twice in a path pattern also has the semantics that the edges must be the same.

Cycles can be longer than a single edge. See Example 5-11.

Multiple Path Patterns

A MATCH clause may have more than one path pattern, in a comma-separated list. For instance, the following example shows two path patterns:

MATCH (a IS person WHERE a.name='John') -[IS student_of]-> (b IS university),
(a IS person WHERE a.name='John') -[IS friends]-> (c IS person)

Any graph pattern variables in common between two path patterns denotes an overlap between the path patterns. In the preceding example, the vertex variable a is shared. Note that the variable a must bind to the same graph element table in each element pattern of the graph pattern, and thus there is an implicit natural inner join on such repeated graph pattern variables.

If there are no shared variables between the two path patterns, then the resulting output set is a cross product of the outputs of the individual path patterns. See Example 5-9 and Example 5-10.

Parent topic: SQL GRAPH_TABLE Queries

5.4 Vertex and Edge Identifiers

You can uniquely identify each vertex and edge in a SQL property graph with the VERTEX_ID and EDGE_ID operators, respectively, in a GRAPH_TABLE query.

Graph element identifiers are based on the key value defined for the graph element tables. Therefore, it is important to note the following:

Graphs in TRUSTED mode may produce duplicate identifiers for different vertices if some key columns do not have a UNIQUE constraint.
Graphs in ENFORCED mode are guaranteed to always produce unique identifiers.

The VERTEX_ID and EDGE_ID operators can be used in any expression appearing in the COLUMNS or WHERE clause in a GRAPH_TABLE query.

Note:

In order to use the VERTEX_ID and EDGE_ID operators, you must ensure that you have the READ or SELECT privilege on both the property graph object and its underlying database tables.

The input to the VERTEX_ID operator is a single vertex graph pattern variable coming from a matched vertex pattern as shown:

MATCH (v) COLUMNS(VERTEX_ID(v) AS v_id)

Similarly, the EDGE_ID operator takes as input a single edge graph pattern variable coming from a matched edge pattern as shown:

MATCH (v1)-[e]->(v2) COLUMNS(EDGE_ID(e) AS e_id)

The output of these operators is a vertex or an edge identifier of JSON data type. The following shows an example of a JSON output describing the vertex identifier:

{
  "GRAPH_OWNER": "GRAPHUSER",
  "GRAPH_NAME": "STUDENTS_GRAPH",
  "ELEM_TABLE": "PERSONS",
  "KEY_VALUE": {
    "PERSON_ID": 1
  }
}

In the preceding JSON output:

GRAPH_OWNER: Owner of the property graph object
GRAPH_NAME: Name of the property graph object
ELEM_TABLE: Name of the vertex table
KEY_VALUE: Name and value of the key column

The same list of JSON output fields apply to an edge identifier also. However, the ELEM_TABLE field represents the name of an edge table. Also, all operations that can be performed on a JSON data type can be performed on the vertex and edge identifiers.

See Example 5-19 for more information.

VERTEX_EQUAL and EDGE_EQUAL Predicates

The VERTEX_EQUAL and EDGE_EQUAL predicates can be used to, respectively, compare two vertex and edge identifiers and return TRUE if they are equal.

The inputs to the VERTEX_EQUAL predicate are two vertex graph pattern variables. Similarly for EDGE_EQUAL, both inputs must be edge graph pattern variables. These predicates can be used in the WHERE clause in a GRAPH_TABLE query.

See Example 5-20 for more information.

Parent topic: SQL GRAPH_TABLE Queries

5.5 Running GRAPH_TABLE Queries at a Specific SCN

You can run a GRAPH_TABLE query at a given System Change Number (SCN) or timestamp value.

The graph name, which is the first operand of a GRAPH_TABLE query, can be associated with either of the following clauses:

AS OF SCN: Refer to Example 5-17
AS OF TIMESTAMP: Refer to Example 5-18

Parent topic: SQL GRAPH_TABLE Queries

5.6 Privileges to Query a SQL Property Graph

You must have the READ or SELECT object privilege to query a SQL property graph.

If you are the graph creator, then you can allow other graph users to query your graph by granting any one of the following privileges:

GRANT READ ON PROPERTY GRAPH <graph_name> TO <schema_user>;
GRANT SELECT ON PROPERTY GRAPH <graph_name> TO <schema_user>;

It is important to note that granting the preceding privileges allows access only to the property graph object and not to its underlying database tables.

This allows the graph user to successfully run GRAPH_TABLE queries on your graph without having access to the underlying tables. For example:

GRANT READ ON PROPERTY GRAPH students_graph TO hr;

SQL> conn hr/<password_for_hr>;
Connected.
SQL> SELECT * FROM GRAPH_TABLE (graphuser.students_graph MATCH (a IS person) COLUMNS (a.name AS person_a));

PERSON_A
----------
John
Mary
Bob
Alice

However, to perform GRAPH_TABLE queries with VERTEX_ID and EDGE_ID operators, the graph user must additionally have READ or SELECT privilege on the underlying database tables.

Parent topic: SQL GRAPH_TABLE Queries

5.7 Examples for SQL Property Graph Queries

This section contains a few examples for querying a SQL property graph with fixed-length and variable-length graph pattern matching queries.

All the queries shown in the examples are run on the SQL property graph, students_graph, created in Example 4-1:

Example 5-5 GRAPH_TABLE Query Using An Edge Pattern Directed Left-To-Right

The following example shows a GRAPH_TABLE query containing an edge pattern (-[e IS friends]->) which is directed from left-to-right:

SELECT * FROM GRAPH_TABLE (students_graph
  MATCH
  (a IS person) -[e IS friends]-> (b IS person WHERE b.name='Alice')
  WHERE a.name='Mary'
  COLUMNS (a.name AS person_a, b.name AS person_b)
);

The code produces the following output:

PERSON_A   PERSON_B
---------- ----------
Mary       Alice

Example 5-6 GRAPH_TABLE Query Using An Edge Pattern Directed Right-To-Left

The following example shows a GRAPH_TABLE query containing an edge pattern (<-[e IS friends]-) which is directed from right-to-left:

SELECT * FROM GRAPH_TABLE (students_graph
  MATCH
  (a IS person) <-[e IS friends]- (b IS person WHERE b.name='Mary')
  WHERE a.name='Alice'
  COLUMNS (a.name AS person_a, b.name AS person_b)
);

The code produces the following output:

PERSON_A   PERSON_B
---------- ----------
Alice      Mary

Example 5-7 GRAPH_TABLE Query Using Any-Directed Edge Pattern

The following example shows a GRAPH_TABLE query which contains any-directed edge pattern (-[e IS friends]-):

SELECT * FROM GRAPH_TABLE (students_graph
  MATCH
  (a IS person) -[e IS friends] - (b IS person WHERE b.name='Alice' OR b.name='Mary')
  WHERE (a.name='Alice' OR a.name='Mary')
  COLUMNS (a.name AS person_a, b.name AS person_b)
);

The code produces the following output:

PERSON_A   PERSON_B
---------- ----------
Mary       Alice
Alice      Mary

Example 5-8 GRAPH_TABLE Query Using an Anonymous Edge Variable

The following example shows a GRAPH_TABLE query where the edge element variable is omitted:

SELECT * FROM GRAPH_TABLE (students_graph
  MATCH
  (a IS person) -[]-> (b IS person)
  COLUMNS (a.name AS person_a, b.name AS person_b)
  );

Alternatively, you can replace the bracketed syntax for the edge pattern (-[]->) in the preceding query with an abbreviated syntax ->.

The code produces the following output:

PERSON_A   PERSON_B
---------- ----------
Mary       John
Bob        Mary
John       Bob
Mary       Alice

Example 5-9 GRAPH_TABLE Query Using Multiple Path Patterns

The following example shows a GRAPH_TABLE query containing two path patterns (a)->(b), (a)->(c)) which have a common vertex as shown:

SELECT * FROM GRAPH_TABLE (students_graph
  MATCH
  (a IS person WHERE a.name = 'John') -> (b IS person),(a IS person WHERE a.name = 'John') -> (c IS university)
  COLUMNS (a.name AS person_a, b.name AS person_b,c.name as university)
  );

The preceding code produces the following output:

PERSON_A   PERSON_B   UNIVERSITY
---------- ---------- ----------
John       Bob        ABC

Example 5-10 GRAPH_TABLE Query Using Disjoint Path Patterns

The following example shows a GRAPH_TABLE query containing two disjoint path patterns:

SELECT * FROM GRAPH_TABLE (students_graph
MATCH (a IS person WHERE a.name='John') -[IS student_of]-> (b IS university),
(x IS person) -[IS friends]-> (y IS person)
COLUMNS (a.name AS a, b.name as university, x.name AS x, y.name as y)
);

The resulting output is as shown:

A          UNIVERSITY X          Y
---------- ---------- ---------- ----------
John       ABC        Mary       John
John       ABC        Bob        Mary
John       ABC        John       Bob
John       ABC        Mary       Alice

Example 5-11 GRAPH_TABLE Query Using Cyclic Path Patterns

The following example uses a cyclic path pattern (MATCH (a)-[]->(b)-[]->(c)-[]->(a)) as shown. Note that the example uses the same vertex pattern variable name a (which is bound to person) twice. Thus, this finds cycles in the graph containing three edges that finally bind to a itself.

SELECT * FROM GRAPH_TABLE (students_graph
  MATCH
  (a IS person) -[IS friends]-> (b IS person) -[IS friends]->
  (c IS person) -[IS friends]-> (a)
  COLUMNS (a.name AS person_a, b.name AS person_b, c.name AS person_c)
  );

The preceding code produces the following output:

PERSON_A   PERSON_B   PERSON_C
---------- ---------- ----------
Bob        Mary       John
John       Bob        Mary
Mary       John       Bob

Example 5-12 GRAPH_TABLE Query Using Label Disjunction

The following example uses label disjunction in the vertex label expression:

SELECT * FROM GRAPH_TABLE (students_graph
MATCH
(a is person|university)
COLUMNS (a.name, a.dob)
);

The code produces the following output:

NAME       DOB
---------- ---------
John       13-JUN-63
Mary       25-SEP-82
Bob        11-MAR-66
Alice      01-FEB-87
ABC        NULL
XYZ        NULL

6 rows selected.

Example 5-13 GRAPH_TABLE Query Using Label Conjunction

The following example uses label conjunction in the vertex label expression:

SELECT * FROM GRAPH_TABLE (students_graph
MATCH
(a IS person), (a IS person_ht)
COLUMNS (a.name as name, a.dob as dob, a.height as height )
);

The code produces the following output:

NAME       DOB           HEIGHT
---------- --------- ----------
John       13-JUN-63        1.8
Mary       25-SEP-82       1.65
Bob        11-MAR-66       1.75
Alice      01-FEB-87        1.7

Example 5-14 GRAPH_TABLE Queries Using Recursive Path Patterns with Bounded Quantifiers

The following example uses a recursive path pattern to retrieve all friends within two hops:

SELECT * FROM GRAPH_TABLE (students_graph
MATCH (a is person WHERE a.name='Mary') -[is friends]->{2} (b is person)
COLUMNS (a.name AS a , b.name AS b)
);

The preceding code produces the following output:

A          B
---------- ----------
Mary       Bob

The following example uses a recursive path pattern to retrieve all friends between one and two hops (inclusive):

SELECT * FROM GRAPH_TABLE (students_graph
MATCH (a is person WHERE a.name='Mary') -[is friends]->{1, 2} (b is person)
COLUMNS (a.name AS a , b.name AS b)
);

The preceding code produces the following output:

A          B
---------- ----------
Mary       Alice
Mary       John
Mary       Bob

The following example uses a recursive path pattern to retrieve all friends by performing from zero to two iterations:

SELECT * FROM GRAPH_TABLE (students_graph
MATCH (a is person WHERE a.name='Mary') -[is friends]->{,2} (b is person)
COLUMNS (a.name AS a , b.name AS b)
);

The preceding code produces the following output:

A          B
---------- ----------
Mary       Mary
Mary       Alice
Mary       John
Mary       Bob

Note that in the first line of the preceding output, Mary is bound to both the element pattern variables, a and b. This is because the GRAPH_TABLE query includes a zero hop iteration and therefore, the vertex pattern to the left and the vertex pattern to the right must bind to the same graph element.

Example 5-15 GRAPH_TABLE Query Using Bind Variables

The example declares a bind variable, name and assigns a value as shown:

SQL> variable name VARCHAR2(10);
SQL> BEGIN
  2  :name := 'Bob';
  3  END;
  4  /

PL/SQL procedure successfully completed.

Using this bind variable, the following GRAPH_TABLE query is performed:

SELECT * FROM GRAPH_TABLE (students_graph
  MATCH
  (a IS person) -[e IS friends]-> (b IS person WHERE b.name=:name)
  WHERE a.name='John'
  COLUMNS (a.name AS person_a,
           b.name AS person_b,
           e.meeting_date AS met_on)
);

The code produces the following output:

A          B          MET_ON
---------- ---------- ---------
John       Bob        01-SEP-00

Example 5-16 GRAPH_TABLE Query Invoking a PL/SQL function Inside an Expression and in the COLUMNS Clause

The example declares a user defined function(UDF) as shown:

CREATE OR REPLACE FUNCTION get_age(
    id NUMBER
)
RETURN NUMBER
AS
    age NUMBER := 0;
BEGIN
    -- get age
      SELECT (EXTRACT(YEAR from SYSDATE) - EXTRACT(YEAR from birthdate))
      INTO age 
      FROM persons
      WHERE person_id=id;
    -- return age
    RETURN age;
END;
/

Function created.

The following GRAPH_TABLE query invokes the UDF inside an expression in the WHERE clause and again in the COLUMNS clause:

SELECT * FROM GRAPH_TABLE (students_graph
  MATCH
   (a IS person) -[e IS friends]-> (b IS person)
  WHERE (get_age(a.person_id) > 50)
  COLUMNS (a.name AS a,
        get_age(a.person_id) AS age,
           b.name AS b,
           e.meeting_date AS met_on)
  );

The code produces the following output:

A                 AGE B          MET_ON
---------- ---------- ---------- ---------
John               60 Bob        01-SEP-00
Bob                57 Mary       10-JUL-01

Example 5-17 GRAPH_TABLE Query Using SCN

Determine the current SCN value of the database as shown:

SQL>  SELECT TIMESTAMP_TO_SCN(SYSDATE) FROM DUAL;

TIMESTAMP_TO_SCN(SYSDATE)
-------------------------
                  2117789

The following GRAPH_TABLE query using the preceding SCN value as shown:

SELECT * FROM GRAPH_TABLE (students_graph AS OF SCN 2117789
  MATCH
   (a IS person) -[e]-> (b IS person)
  COLUMNS (a.name AS a, b.name AS b, e.meeting_date AS met_on)
  );

The query produces the following output:

A          B          MET_ON
---------- ---------- ---------
Mary       John       19-SEP-00
Bob        Mary       10-JUL-01
John       Bob        01-SEP-00
Mary       Alice      19-SEP-00

Example 5-18 GRAPH_TABLE Query Using TIMESTAMP

The following GRAPH_TABLE query uses a TIMESTAMP value as shown:

SQL> SELECT * FROM GRAPH_TABLE (students_graph AS OF TIMESTAMP SYSTIMESTAMP
  MATCH
   (a IS person WHERE a.name='John') -[e]-> (b IS person)
  COLUMNS (a.name AS a, b.name AS b, e.meeting_date AS met_on)
  );

The query produces the following output:

A          B          MET_ON
---------- ---------- ---------
John       Bob        01-SEP-00

Example 5-19 GRAPH_TABLE Query Using the VERTEX_ID and EDGE_ID Identifiers

SELECT * FROM GRAPH_TABLE (students_graph
MATCH
 (a IS person ) -[e IS friends]-> (b IS person)
COLUMNS (JSON_SERIALIZE(VERTEX_ID(a)) AS id_a , JSON_SERIALIZE(EDGE_ID(e)) AS id_e)
);

The query produces a JSON data type output that includes the graph owner, graph name and graph element table name and the key value as shown:

ID_A                        ID_E
--------------------------- ------------------------------
{"GRAPH_OWNER":"GRAPHUSER", {"GRAPH_OWNER":"GRAPHUSER","GR
"GRAPH_NAME":"STUDENTS_GRAP APH_NAME":"STUDENTS_GRAPH","EL
H","ELEM_TABLE":"PERSONS"," EM_TABLE":"FRIENDS","KEY_VALUE
KEY_VALUE":{"PERSON_ID":1}} ":{"FRIENDSHIP_ID":1}}

{"GRAPH_OWNER":"GRAPHUSER", {"GRAPH_OWNER":"GRAPHUSER","GR
"GRAPH_NAME":"STUDENTS_GRAP APH_NAME":"STUDENTS_GRAPH","EL
H","ELEM_TABLE":"PERSONS"," EM_TABLE":"FRIENDS","KEY_VALUE
KEY_VALUE":{"PERSON_ID":2}} ":{"FRIENDSHIP_ID":2}}

{"GRAPH_OWNER":"GRAPHUSER", {"GRAPH_OWNER":"GRAPHUSER","GR
"GRAPH_NAME":"STUDENTS_GRAP APH_NAME":"STUDENTS_GRAPH","EL
H","ELEM_TABLE":"PERSONS"," EM_TABLE":"FRIENDS","KEY_VALUE
KEY_VALUE":{"PERSON_ID":2}} ":{"FRIENDSHIP_ID":3}}

{"GRAPH_OWNER":"GRAPHUSER", {"GRAPH_OWNER":"GRAPHUSER","GR
"GRAPH_NAME":"STUDENTS_GRAP APH_NAME":"STUDENTS_GRAPH","EL
H","ELEM_TABLE":"PERSONS"," EM_TABLE":"FRIENDS","KEY_VALUE
KEY_VALUE":{"PERSON_ID":3}} ":{"FRIENDSHIP_ID":4}}

Example 5-20 GRAPH_TABLE Query Using the VERTEX_EQUAL Predicate

SELECT * FROM GRAPH_TABLE (students_graph
MATCH
 (a IS person WHERE a.name='John') -[e IS friends]->{,1} (b IS person)
WHERE VERTEX_EQUAL(a,b)
COLUMNS (JSON_SERIALIZE(VERTEX_ID(a)) AS id_a , JSON_SERIALIZE(VERTEX_ID(b)) AS id_b)
);

The query produces a JSON data type output that includes the graph owner, graph name and graph element table name and the key value as shown:

ID_A                        ID_B
--------------------------- ---------------------------
{"GRAPH_OWNER":"GRAPHUSER", {"GRAPH_OWNER":"GRAPHUSER",
"GRAPH_NAME":"STUDENTS_GRAP "GRAPH_NAME":"STUDENTS_GRAP
H","ELEM_TABLE":"PERSONS"," H","ELEM_TABLE":"PERSONS","
KEY_VALUE":{"PERSON_ID":1}} KEY_VALUE":{"PERSON_ID":1}}

Setting Up Sample Data in the Database

Parent topic: SQL GRAPH_TABLE Queries

5.7.1 Setting Up Sample Data in the Database

In order to create the SQL property graph, students_graph, shown in Creating a SQL Property Graph, the following sample tables with data need to be set up in the database.

Connect to the database as the schema user.

Run the following SQL script to create the university, persons, students, and friendships tables with sample data in the database.

CREATE TABLE university (
    id NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
    name VARCHAR2(10),
    CONSTRAINT u_pk PRIMARY KEY (id));

INSERT INTO university (name) VALUES ('ABC');
INSERT INTO university (name) VALUES ('XYZ');

CREATE TABLE persons (
     person_id NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT
     BY 1),
     name VARCHAR2(10),
     birthdate DATE,
     height FLOAT DEFAULT ON NULL 0,
     hr_data JSON,
     CONSTRAINT person_pk PRIMARY KEY (person_id)
   );

INSERT INTO persons (name, height, birthdate, hr_data)
       VALUES ('John', 1.80, to_date('13/06/1963', 'DD/MM/YYYY'), '{"department":"IT","role":"Software Developer"}');

INSERT INTO persons (name, height, birthdate, hr_data)
       VALUES ('Mary', 1.65, to_date('25/09/1982', 'DD/MM/YYYY'), '{"department":"HR","role":"HR Manager"}');

INSERT INTO persons (name, height, birthdate, hr_data)
       VALUES ('Bob', 1.75, to_date('11/03/1966', 'DD/MM/YYYY'), '{"department":"IT","role":"Technical Consultant"}');

INSERT INTO persons (name, height, birthdate, hr_data)
       VALUES ('Alice', 1.70, to_date('01/02/1987', 'DD/MM/YYYY'), '{"department":"HR","role":"HR Assistant"}');

CREATE TABLE student_of (
      s_id NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
      s_univ_id NUMBER,
      s_person_id NUMBER,
      subject VARCHAR2(10),
      CONSTRAINT stud_pk PRIMARY KEY (s_id),
      CONSTRAINT stud_fk_person FOREIGN KEY (s_person_id) REFERENCES persons(person_id),
      CONSTRAINT stud_fk_univ FOREIGN KEY (s_univ_id) REFERENCES university(id)
    );

INSERT INTO student_of(s_univ_id, s_person_id,subject) VALUES (1,1,'Arts');
INSERT INTO student_of(s_univ_id, s_person_id,subject) VALUES (1,3,'Music');
INSERT INTO student_of(s_univ_id, s_person_id,subject) VALUES (2,2,'Math');
INSERT INTO student_of(s_univ_id, s_person_id,subject) VALUES (2,4,'Science');

CREATE TABLE friends (
    friendship_id NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
    person_a NUMBER,
    person_b NUMBER,
    meeting_date DATE,
    CONSTRAINT fk_person_a_id FOREIGN KEY (person_a) REFERENCES persons(person_id),
    CONSTRAINT fk_person_b_id FOREIGN KEY (person_b) REFERENCES persons(person_id),
    CONSTRAINT fs_pk PRIMARY KEY (friendship_id)
);

INSERT INTO friends (person_a, person_b, meeting_date) VALUES (1, 3, to_date('01/09/2000', 'DD/MM/YYYY'));
INSERT INTO friends (person_a, person_b, meeting_date) VALUES (2, 4, to_date('19/09/2000', 'DD/MM/YYYY'));
INSERT INTO friends (person_a, person_b, meeting_date) VALUES (2, 1, to_date('19/09/2000', 'DD/MM/YYYY'));
INSERT INTO friends (person_a, person_b, meeting_date) VALUES (3, 2, to_date('10/07/2001', 'DD/MM/YYYY'));

Parent topic: Examples for SQL Property Graph Queries

5.8 Supported Features and Limitations for Querying a SQL Property Graph

This section provides the list of supported and unsupported features for querying a SQL Property Graph.

Supported Features

Single label, no label, label disjunction and label conjunction are supported in label expressions inside a graph pattern. For more information, see:
- Table 5-2 in Label Expressions
- Examples for SQL Property Graph Queries
Any directed edge patterns (MATCH (a)-[e]-(b) are supported.
See Example 5-7.
Anonymous vertex (MATCH ()-[e]->()) and edge (MATCH (a)-[]->(b)) variables are supported.
See Example 5-8.
Complex path pattern queries are supported.
See Example 5-9, Example 5-10 and Example 5-11.
Bounded recursive path pattern queries are supported.
See Example 5-14.
Bind variables are supported inside a WHERE clause.
See Example 5-15.
VERTEX_ID and EDGE_ID operators that uniquely identify a vertex and an edge respectively can be used within a SQL GRAPH_TABLE query.
- See Vertex and Edge Identifiers.
- See Example 5-19.
VERTEX_EQUAL and EDGE_EQUAL predicates for matching vertex and edge identifiers are supported.
- See Vertex and Edge Identifiers.
- See Example 5-20.
SQL and JSON expressions are supported inside WHERE and COLUMNS clauses.
See Example 4-6.
JSON simplified syntax is supported to access properties of type JSON.
See Example 4-6.
PL/SQL functions are supported inside a WHERE or COLUMNS clause.
See Example 5-16.
Single line and multi-line comments are supported within a graph query.
All identifiers within a GRAPH_TABLE query, such as graph names, alias names, graph element pattern variable names, labels and property names follow the standard SQL rules about case sensitivity:
- Identifiers within double quotes are case sensitive.
- Identifiers not enclosed in double quotes are implicitly converted to uppercase and enclosed in double quotes.
SQL hints are supported inside and outside the GRAPH_TABLE query for tuning.
See Tuning SQL Property Graph Queries for more information.
You can query a graph defined in another schema if you have the required privileges.
See Granting System and Object Privileges for SQL Property Graphs for more information.

Limitations

Variable-length pattern matching goals (such as ANY, ALL, ALL SHORTEST, ANY CHEAPEST, and so on) are not supported.
Path pattern variables (MATCH p = (n)-[e]->(m)) are not supported.
Clauses such as COST and TOTAL_COST are not supported.
Inline subqueries and LATERAL inline views are not supported.
SQL Macros are not supported.

Parent topic: SQL GRAPH_TABLE Queries

5.9 Tuning SQL Property Graph Queries

You can tune a SQL GRAPH_TABLE query using the EXPLAIN PLAN statement.

A GRAPH_TABLE query is internally translated into equivalent SQL. You can therefore generate the EXPLAIN PLAN for the property graph query as shown:

SQL> EXPLAIN PLAN FOR SELECT * FROM GRAPH_TABLE (students_graph
MATCH (a is person)-[e is friends]-> (b is person)
COLUMNS (a.name AS a , b.name AS b)
);
Explained.

The EXPLAIN PLAN can be viewed as shown:

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format=>'ALL'));

Plan hash value: 1420380663
-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |     4 |   264 |    10  (10)| 00:00:01 |
|*  1 |  HASH JOIN          |             |     4 |   264 |    10  (10)| 00:00:01 |
|*  2 |   HASH JOIN         |             |     4 |   184 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| PERSONS     |     4 |    80 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| FRIENDSHIPS |     4 |   104 |     3   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL | PERSONS     |     4 |    80 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$B92C7F25
   3 - SEL$B92C7F25 / "A"@"SEL$213F43E5"
   4 - SEL$B92C7F25 / "E"@"SEL$213F43E5"
   5 - SEL$B92C7F25 / "B"@"SEL$213F43E5"

You can tune the preceding GRAPH_TABLE query by using optimizer hints. For instance, the following example uses the PARALLEL hint and the hint usage can be seen in the following execution plan:

SQL> EXPLAIN PLAN FOR SELECT /*+ PARALLEL(4) */ * FROM GRAPH_TABLE (students_graph
MATCH (a is person)-[e is friends]-> (b is person)
COLUMNS (a.name AS a , b.name AS b)
);
Explained.

SQL>  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format=>'ALL'));

Plan hash value: 1486901074
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |             |     4 |   264 |     4   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR                  |             |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)            | :TQ10000    |     4 |   264 |     4   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    NESTED LOOPS                  |             |     4 |   264 |     4   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   4 |     NESTED LOOPS                 |             |     4 |   264 |     4   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   5 |      NESTED LOOPS                |             |     4 |   184 |     3   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   6 |       PX BLOCK ITERATOR          |             |       |       |            |          |  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS FULL         | FRIENDSHIPS |     4 |   104 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   8 |       TABLE ACCESS BY INDEX ROWID| PERSONS     |     1 |    20 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|*  9 |        INDEX UNIQUE SCAN         | PERSON_PK   |     1 |       |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|* 10 |      INDEX UNIQUE SCAN           | PERSON_PK   |     1 |       |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  11 |     TABLE ACCESS BY INDEX ROWID  | PERSONS     |     1 |    20 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$B92C7F25
   7 - SEL$B92C7F25 / "E"@"SEL$213F43E5"
   8 - SEL$B92C7F25 / "A"@"SEL$213F43E5"
   9 - SEL$B92C7F25 / "A"@"SEL$213F43E5"
  10 - SEL$B92C7F25 / "B"@"SEL$213F43E5"
  11 - SEL$B92C7F25 / "B"@"SEL$213F43E5"

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
  0 -  STATEMENT

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
           -  PARALLEL(4)
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Degree of Parallelism is 4 because of hint

Parent topic: SQL GRAPH_TABLE Queries

5.10 Type Compatibility Rules for Determining Property Types

When using shared property names that are union compatible, the property type is determined by certain type compatibility rules.

The following summarizes the rules for determining the type of a property for union compatible properties at the time of DDL creation and also during query compilation:

If expressions exposed by a same property of a shared label are character data, then the data type of the property is determined as follows:
- If all expressions are of data type CHAR of equal length, then the property has a data type CHAR of that length. If the expression are all of data type CHAR, but with different lengths, then the property type is VARCHAR2 with the length of the larger CHAR type.
- If any, or all of the expressions are of data type VARCHAR2, then the property has data type VARCHAR2. The length of the VARCHAR2 is the maximum length size of the input columns.
If expressions exposed by a same property of a shared label are numeric data, then the data type of the property is determined by numeric precedence:
- If any expression exposed by a property is of data type BINARY DOUBLE, then the property has the data type BINARY DOUBLE.
- If no expression defining the property are of data type BINARY DOUBLE, but any expression is of type BINARY FLOAT, then the property has data type BINARY FLOAT.
- If all expressions defining the property are of data type NUMBER, then the property has data type NUMBER.
If expressions exposed by a same property of a shared label are date and timestamp data, then the data type of the property is determined as follows:
- If all expressions are of data type DATE, then property has data type DATE.
- If any, or all of the expressions are of data type TIMESTAMP, then the property has data type TIMESTAMP.

Parent topic: SQL GRAPH_TABLE Queries

5.11 Viewing and Querying SQL Property Graphs Using SQL Developer

Using SQL Developer 23.1, you can view all the SQL property graphs existing in your database schema by expanding SQL Property Graphs under the Property Graph node in the Connections navigator.

Figure 5-1 SQL Property Graphs in SQL Developer

Description of "Figure 5-1 SQL Property Graphs in SQL Developer"

The following steps show an example for running graph queries on a SQL property graph:

Click on any SQL property graph.
This opens a SQL worksheet in another tab.
Run one or more graph queries in the SQL worksheet.
For example:

Figure 5-2 Running GRAPH_TABLE queries in SQL Developer

Description of "Figure 5-2 Running GRAPH_TABLE queries in SQL Developer"

Parent topic: SQL GRAPH_TABLE Queries