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

4.1 Simple Property Graph Queries

The examples in this topic query vertices, edges, and properties of the graph.

Example 4-1 Find a Vertex with a Specified Vertex ID

This example find the vertex with vertex ID 1 in the connections graph.

SQL> select vid, k, v, vn, vt 
      from connectionsVT$ 
      where vid=1;

The output might be as follows:

     1 country     United States
     1 name        Robert Smith
     1 occupation  CEO of Example Corporation
     ...

Example 4-2 Find an Edge with a Specified Edge ID

This example find the edge with edge ID 100 in the connections graph.

SQL> select eid,svid,dvid,k,t,v,vn,vt 
      from connectionsGE$ 
      where eid=1000;

The output might be as follows:

    1000  1 2 weight  3  1  1 

In the preceding output, the K of the edge property is "weight" and the type ID of the value is 3, indicating a float value.

Example 4-3 Perform Simple Counting

This example performs simple counting in the connections graph.

SQL> -- Get the total number of K/V pairs of all the vertices
SQL> select /*+ parallel */ count(1) 
       from connectionsVT$;

    299

SQL> -- Get the total number of K/V pairs of all the edges
SQL> select /*+ parallel(8) */ count(1) 
       from connectionsGE$;
    164

SQL> -- Get the total number of vertices
SQL> select /*+ parallel */ count(distinct vid) 
       from connectionsVT$;

    78

SQL> -- Get the total number of edges
SQL> select /*+ parallel */ count(distinct eid) 
       from connectionsGE$;

    164

Example 4-4 Get the Set of Property Keys Used

This example gets the set of property keys used for the vertices n the connections graph.

SQL> select /*+ parallel */ distinct k 
      from connectionsVT$;

company
show
occupation
type
team
religion
criminal charge
music genre
genre
name
role
political party
country

13 rows selected.

SQL> -- get the set of property keys used for edges
SQL> select /*+ parallel */ distinct k 
       from connectionsGE$;

weight

Example 4-5 Find Vertices with a Value

This example finds vertices with a value (of any property) that is of String type, and where and the value contains two adjacent occurrences of a, e, i, o, or u, regardless of case.n the connections graph.

SQL> select vid, t, k, v 
       from connectionsVT$ 
      where t=1 
        and regexp_like(v, '([aeiou])\1', 'i');

     6        1  name  Jordan Peele 
     6        1  show  Key and Peele
    54        1  name  John Green
        ...

It is usually hard to leverage a B-Tree index for the preceding kind of query because it is difficult to know beforehand what kind of regular expression is going to be used. For the above query, you might get the following execution plan. Note that full table scan is chosen by the optimizer.

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                |    15 |   795 |    28    (0)| 00:00:01 |       |       |        |      |           |
|   1 |  PX COORDINATOR      |                |       |       |             |          |       |       |        |      |           |
|   2 |   PX SEND QC (RANDOM)| :TQ10000       |    15 |   795 |    28    (0)| 00:00:01 |       |       |  Q1,00 | P->S | QC (RAND) |
|   3 |    PX BLOCK ITERATOR |                |    15 |   795 |    28    (0)| 00:00:01 |     1 |     8 |  Q1,00 | PCWC |           |
|*  4 |     TABLE ACCESS FULL| CONNECTIONSVT$ |    15 |   795 |    28    (0)| 00:00:01 |     1 |     8 |  Q1,00 | PCWP |           |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter(INTERNAL_FUNCTION("V") AND  REGEXP_LIKE ("V",U'([aeiou])\005C1','i') AND "T"=1 AND INTERNAL_FUNCTION("K"))
Note
-----
   - Degree of Parallelism is 2 because of table property

If the Oracle Database In-Memory option is available and memory is sufficient, it can help performance to place the table (full table or a set of relevant columns) in memory. One way to achieve that is as follows:

SQL> alter table connectionsVT$ inmemory;
Table altered.

Now, entering the same SQL containing the regular expression shows a plan that performs a "TABLE ACCESS INMEMORY FULL".

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)  | Time     | Pstart| Pstop |    TQ    |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |    15 |   795 |    28     (0)| 00:00:01 |       |       |        |      |            |
|   1 |  PX COORDINATOR               |                |       |       |              |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)         | :TQ10000       |    15 |   795 |    28     (0)| 00:00:01 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR          |                |    15 |   795 |    28     (0)| 00:00:01 |     1 |     8 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS INMEMORY FULL| CONNECTIONSVT$ |    15 |   795 |    28     (0)| 00:00:01 |     1 |     8 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter(INTERNAL_FUNCTION("V") AND  REGEXP_LIKE ("V",U'([aeiou])\005C1','i') AND "T"=1 AND INTERNAL_FUNCTION("K"))
Note
-----
   - Degree of Parallelism is 2 because of table property

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

Oracle Text can process text that is directly stored in the database. The text can be short strings (such as names or addresses), or it can be full-length documents. These documents can be in a variety of textual format.

The text can also be in many different languages. Oracle Text can handle any space-separated languages (including character sets such as Greek or Cyrillic). In addition, Oracle Text is able to handle the Chinese, Japanese and Korean pictographic languages)

Because the property graph feature uses NVARCHAR typed column for better support of Unicode, it is highly recommended that UTF8 (AL32UTF8) be used as the database character set.

To create an Oracle Text index on the vertices table (or edges table), the ALTER SESSION privilege is required. For example:

SQL> grant alter session to <YOUR_USER_SCHEMA_HERE>;

If customization is required, also grant the EXECUTE privilege on CTX_DDL:

SQL> grant execute on ctx_ddl to <YOUR_USER_SCHEMA_HERE>;

The following shows some example statements for granting these privileges to SCOTT.

SQL> conn / as sysdba
Connected.
SQL> -- This is a PDB setup -- 
SQL> alter session set container=orcl;
Session altered.

SQL> grant execute on ctx_ddl to scott;
Grant succeeded.

SQL> grant alter session to scott;
Grant succeeded.

Example 4-6 Create a Text Index

This example creates an Oracle Text index on the vertices table (V column) of the connections graph in the SCOTT schema. Note that the Oracle Text index created here is for all property keys, not just one or a subset of property keys. In addition, if a new property is added to the graph and the property value is of String data type, then it will automatically be included in the same text index.

The example uses the OPG_AUTO_LEXER lexer owned by MDSYS.

SQL> execute opg_apis.create_vertices_text_idx('scott', 'connections', pref_owner=>'MDSYS', lexer=>'OPG_AUTO_LEXER', dop=>2);

If customization is desired, you can use the ctx_ddl.create_preference API. For example:

SQL> -- The following requires access privilege to CTX_DDL
SQL> exec ctx_ddl.create_preference('SCOTT.OPG_AUTO_LEXER', 'AUTO_LEXER');

PL/SQL procedure successfully completed.

SQL> execute opg_apis.create_vertices_text_idx('scott', 'connections', pref_owner=>'scott', lexer=>'OPG_AUTO_LEXER', dop=>2);

PL/SQL procedure successfully completed.

You can now use a rich set of functions provided by Oracle Text to perform queries against graph elements.

Note:

If you no longer need an Oracle Text index, you can use the drop_vertices_text_idx or opg_apis.drop_edges_text_idx API to drop it. The following statements drop the text indexes on the vertices and edges of a graph named connections owned by SCOTT:

SQL> exec opg_apis.drop_vertices_text_Idx('scott', 'connections');
SQL> exec opg_apis.drop_edges_text_Idx('scott', 'connections');

Example 4-7 Find a Vertex that Has a Property Value

The following example find a vertex that has a property value (of string type) containing the keyword "Smith".

SQL> select vid, k, t, v 
       from connectionsVT$ 
      where t=1 
        and contains(v, 'Smith', 1) > 0 
      order by score(1) desc
      ;

The output and SQL execution plan from the preceding statement may appear as follows. Note that DOMAIN INDEX appears as an operation in the execution plan.

     1  name    1  Robert Smith

Execution Plan
----------------------------------------------------------
Plan hash value: 1619508090

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Rows  | Bytes | Cost (%CPU) | Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |     1 |    56 |     5  (20) | 00:00:01 |       |       |
|   1 |  SORT ORDER BY                      |                 |     1 |    56 |     5  (20) | 00:00:01 |       |       |
|*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| CONNECTIONSVT$  |     1 |    56 |     4    (0)| 00:00:01 | ROWID | ROWID |
|*  3 |    DOMAIN INDEX                     | CONNECTIONSXTV$ |       |       |     4    (0)| 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T"=1 AND INTERNAL_FUNCTION("K") AND INTERNAL_FUNCTION("V"))
   3 - access("CTXSYS"."CONTAINS"("V",'Smith',1)>0)

Example 4-8 Fuzzy Match

The following example finds a vertex that has a property value (of string type) containing variants of "ameriian" (a deliberate misspelling for this example) Fuzzy match is used.

SQL> select vid, k, t, v 
       from connectionsVT$ 
      where contains(v, 'fuzzy(ameriian,,,weight)', 1) > 0 
      order by score(1) desc;

The output and SQL execution plan from the preceding statement may appear as follows.

     8 role      1  american business man
     9 role      1  american business man
     4 role      1  american economist
     6 role      1  american comedian actor
     7 role      1  american comedian actor
     1 occupation 1 44th president of United States of America

6 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1619508090

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |     1 |    56 |     5  (20)| 00:00:01 |       |       |
|   1 |  SORT ORDER BY                      |                 |     1 |    56 |     5  (20)| 00:00:01 |       |       |
|*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| CONNECTIONSVT$  |     1 |    56 |     4   (0)| 00:00:01 | ROWID | ROWID |
|*  3 |    DOMAIN INDEX                     | CONNECTIONSXTV$ |       |       |     4   (0)| 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(INTERNAL_FUNCTION("K") AND INTERNAL_FUNCTION("V"))

Example 4-9 Query Relaxation

The following example is a sophisticated Oracle Text query that implements query relaxation, which enables you to execute the most restrictive version of a query first, progressively relaxing the query until the required number of matches is obtained. Using query relaxation with queries that contain multiple strings, you can provide guidance for determining the “best” matches, so that these appear earlier in the results than other potential matches.

This example searchs for "american actor" with a query relaxation sequence.

SQL> select vid, k, t, v  
       from connectionsVT$ 
      where CONTAINS (v,
 '<query>
   <textquery lang="ENGLISH" grammar="CONTEXT">
     <progression>
       <seq>{american} {actor}</seq>
       <seq>{american} NEAR {actor}</seq>
       <seq>{american} AND {actor}</seq>
       <seq>{american} ACCUM {actor}</seq>
     </progression>
   </textquery>
   <score datatype="INTEGER" algorithm="COUNT"/>
  </query>') > 0;

The output and SQL execution plan from the preceding statement may appear as follows.

     7 role       1 american comedian actor
     6 role       1 american comedian actor
    44 occupation 1 actor
     8 role       1 american business man
    53 occupation 1 actor film producer
    52 occupation 1 actor
     4 role       1 american economist
    47 occupation 1 actor
     9 role       1 american business man

9 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2158361449

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                 |       1 |      56 |       4   (0)| 00:00:01 |         |         |
|*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| CONNECTIONSVT$  |       1 |      56 |       4   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   DOMAIN INDEX                     | CONNECTIONSXTV$ |         |         |       4   (0)| 00:00:01 |         |         |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(INTERNAL_FUNCTION("K") AND INTERNAL_FUNCTION("V"))
   2 - access("CTXSYS"."CONTAINS"("V",'<query>      <textquery lang="ENGLISH" grammar="CONTEXT">
          <progression>       <seq>{american} {actor}</seq>    <seq>{american} NEAR {actor}</seq>
          <seq>{american} AND {actor}</seq>        <seq>{american} ACCUM {actor}</seq>    </progression>
          </textquery>    <score datatype="INTEGER" algorithm="COUNT"/> </query>')>0)

Example 4-10 Find an Edge

Just as with vertices, you can create an Oracle Text index on the V column of the edges table (GE$) of a property graph. The following example uses the OPG_AUTO_LEXER lexer owned by MDSYS.

SQL> exec opg_apis.create_edges_text_idx('scott', 'connections', pref_owner=>'mdsys', lexer=>'OPG_AUTO_LEXER', dop=>4);

If customization is required, use the ctx_ddl.create_preference API.

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

This topic provides graph navigation and pattern matching examples using the example property graph named connections. Most of the SQL statements are relatively simple, but they can be used as building blocks to implement requirements that are more sophisticated. It is generally best to start from something simple, and progressively add complexity.

Example 4-11 Who Are a Person's Collaborators?

The following SQL ststement finds all entities that a vertex with ID 1 collaborates with. For simplicity, it considers only outgoing relationships.

SQL> select dvid, el, k, vn, v 
       from connectionsGE$ 
      where svid=1 
        and el='collaborates';

Note:

To find the specific vertex ID of interest, you can perform a text query on the property graph using keywords or fuzzy matching. (For details and examples, see Text Queries on Property Graphs.)

The preceding example's output and execution plan may be as follows.

     2 collaborates weight 1 1
    21 collaborates weight 1 1
    22 collaborates weight 1 1
      ....
    26 collaborates weight 1 1


10 rows selected.


-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name            | Rows  | Bytes | Cost (%CPU)| Time      | Pstart| Pstop |    TQ    |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |                 |    10 |   460 |     2     (0)| 00:00:01 |       |       |        |      |        |
|   1 |  PX COORDINATOR                              |                 |       |       |              |          |       |       |        |      |        |
|   2 |   PX SEND QC (RANDOM)                        | :TQ10000        |    10 |   460 |     2     (0)| 00:00:01 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX PARTITION HASH ALL                     |                 |    10 |   460 |     2     (0)| 00:00:01 |     1 |     8 |  Q1,00 | PCWC |        |
|*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| CONNECTIONSGE$  |    10 |   460 |     2     (0)| 00:00:01 |     1 |     8 |  Q1,00 | PCWP |        |
|*  5 |      INDEX RANGE SCAN                        | CONNECTIONSXSE$ |    20 |       |     1     (0)| 00:00:01 |     1 |     8 |  Q1,00 | PCWP |        |
-------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter(INTERNAL_FUNCTION("EL") AND "EL"=U'collaborates' AND INTERNAL_FUNCTION("K") AND INTERNAL_FUNCTION("V"))
   5 - access("SVID"=1)

Example 4-12 Who Are a Person's Collaborators and What are Their Occupations?

The following SQL statement finds collaborators of the vertex with ID 1, and the occupation of each collaborator. A join with the vertices table (VT$) is required.

SQL> select dvid, vertices.v 
       from connectionsGE$, connectionsVT$ vertices 
      where svid=1 
        and el='collaborates' 
        and dvid=vertices.vid 
        and vertices.k='occupation';

The preceding example's output and execution plan may be as follows.

    21  67th United States Secretary of State
    22  68th United States Secretary of State
    23  chancellor
    28  7th president of Iran
    19  junior United States Senator from New York
...


--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |                 |     7 |   525 |     7   (0)| 00:00:01 |       |       |     |    |         |
|   1 |  PX COORDINATOR                               |                 |       |       |            |          |       |       |     |    |         |
|   2 |   PX SEND QC (RANDOM)                         | :TQ10000        |     7 |   525 |     7   (0)| 00:00:01 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    NESTED LOOPS                               |                 |     7 |   525 |     7   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |         |
|   4 |     PX PARTITION HASH ALL                     |                 |    10 |   250 |     2   (0)| 00:00:01 |     1 |     8 |  Q1,00 | PCWC |         |
|*  5 |      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| CONNECTIONSGE$  |    10 |   250 |     2   (0)| 00:00:01 |     1 |     8 |  Q1,00 | PCWP |         |
|*  6 |       INDEX RANGE SCAN                        | CONNECTIONSXSE$ |    20 |       |     1   (0)| 00:00:01 |     1 |     8 |  Q1,00 | PCWP |         |
|   7 |     PARTITION HASH ITERATOR                   |                 |     1 |       |     0   (0)| 00:00:01 |   KEY |   KEY |  Q1,00 | PCWP |         |
|*  8 |      TABLE ACCESS BY LOCAL INDEX ROWID        | CONNECTIONSVT$  |       |       |            |          |   KEY |   KEY |  Q1,00 | PCWP |         |
|*  9 |       INDEX UNIQUE SCAN                       | CONNECTIONSXQV$ |     1 |       |     0   (0)| 00:00:01 |   KEY |   KEY |  Q1,00 | PCWP |         |
--------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter(INTERNAL_FUNCTION("EL") AND "EL"=U'collaborates')
   6 - access("SVID"=1)
   8 - filter(INTERNAL_FUNCTION("VERTICES"."V"))
   9 - access("DVID"="VERTICES"."VID" AND "VERTICES"."K"=U'occupation')
       filter(INTERNAL_FUNCTION("VERTICES"."K"))

Example 4-13 Find a Person's Enemies and Aggregate Them by Their Country

The following SQL statement finds enemies (that is, those with the feuds relationship) of the vertex with ID 1, and aggregates them by their countries. A join with the vertices table (VT$) is required.

SQL>   select vertices.v, count(1) 
         from connectionsGE$, connectionsVT$ vertices 
        where svid=1 
          and el='feuds'  
          and dvid=vertices.vid 
          and vertices.k='country' 
     group by vertices.v;

The example's output and execution plan may be as follows. In this case, the vertex with ID 1 has 3 enemies in the United States and 1 in Russia.

United States      3
Russia             1


------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name            | Rows   | Bytes  | Cost (%CPU)| Time      | Pstart| Pstop |     TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |                 |      5 |    375 |      5  (20)| 00:00:01 |        |        |        |      |         |
|   1 |  PX COORDINATOR                               |                 |        |        |             |          |        |        |        |      |         |
|   2 |   PX SEND QC (RANDOM)                         | :TQ10001        |      5 |    375 |      5  (20)| 00:00:01 |        |        |  Q1,01 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY                              |                 |      5 |    375 |      5  (20)| 00:00:01 |        |        |  Q1,01 | PCWP |         |
|   4 |     PX RECEIVE                                |                 |      5 |    375 |      5  (20)| 00:00:01 |        |        |  Q1,01 | PCWP |         |
|   5 |      PX SEND HASH                             | :TQ10000        |      5 |    375 |      5  (20)| 00:00:01 |        |        |  Q1,00 | P->P | HASH     |
|   6 |       HASH GROUP BY                           |                 |      5 |    375 |      5  (20)| 00:00:01 |        |        |  Q1,00 | PCWP |         |
|   7 |        NESTED LOOPS                           |                 |      5 |    375 |      4   (0)| 00:00:01 |        |        |  Q1,00 | PCWP |         |
|   8 |     PX PARTITION HASH ALL                     |                 |      5 |    125 |      2   (0)| 00:00:01 |      1 |      8 |  Q1,00 | PCWC |         |
|*  9 |      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| CONNECTIONSGE$  |      5 |    125 |      2   (0)| 00:00:01 |      1 |      8 |  Q1,00 | PCWP |         |
|* 10 |       INDEX RANGE SCAN                        | CONNECTIONSXSE$ |     20 |        |      1   (0)| 00:00:01 |      1 |      8 |  Q1,00 | PCWP |         |
|  11 |     PARTITION HASH ITERATOR                   |                 |      1 |        |      0   (0)| 00:00:01 |    KEY |    KEY |  Q1,00 | PCWP |         |
|* 12 |      TABLE ACCESS BY LOCAL INDEX ROWID        | CONNECTIONSVT$  |        |        |             |          |    KEY |    KEY |  Q1,00 | PCWP |         |
|* 13 |       INDEX UNIQUE SCAN                       | CONNECTIONSXQV$ |      1 |        |      0   (0)| 00:00:01 |    KEY |    KEY |  Q1,00 | PCWP |         |
------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   9 - filter(INTERNAL_FUNCTION("EL") AND "EL"=U'feuds')
  10 - access("SVID"=1)
  12 - filter(INTERNAL_FUNCTION("VERTICES"."V"))
  13 - access("DVID"="VERTICES"."VID" AND "VERTICES"."K"=U'country')
       filter(INTERNAL_FUNCTION("VERTICES"."K"))

Example 4-14 Find a Person's Collaborators, and aggregate and sort them

The following SQL statement finds the collaborators of the vertex with ID 1, aggregates them by their country, and sorts them in ascending order.

SQL> select vertices.v, count(1) 
      from connectionsGE$, connectionsVT$ vertices 
     where svid=1 
       and el='collaborates' 
       and dvid=vertices.vid 
       and vertices.k='country' 
  group by vertices.v 
  order by count(1) asc;

The example output and execution plan may be as follows. In this case, the vertex with ID 1 has the most collaborators in the United States.

Germany        1
Japan          1
Iran           1
United States  7


---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                         | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ    |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                  |                 |    10 |   750 |     9    (23)| 00:00:01 |       |       |        |      |        |
|   1 |  PX COORDINATOR                                   |                 |       |       |              |          |       |       |        |      |        |
|   2 |   PX SEND QC (ORDER)                              | :TQ10002        |    10 |   750 |     9    (23)| 00:00:01 |       |       |  Q1,02 | P->S | QC (ORDER) |
|   3 |    SORT ORDER BY                                  |                 |    10 |   750 |     9    (23)| 00:00:01 |       |       |  Q1,02 | PCWP |        |
|   4 |     PX RECEIVE                                    |                 |    10 |   750 |     9    (23)| 00:00:01 |       |       |  Q1,02 | PCWP |        |
|   5 |      PX SEND RANGE                                | :TQ10001        |    10 |   750 |     9    (23)| 00:00:01 |       |       |  Q1,01 | P->P | RANGE  |
|   6 |       HASH GROUP BY                               |                 |    10 |   750 |     9    (23)| 00:00:01 |       |       |  Q1,01 | PCWP |        |
|   7 |        PX RECEIVE                                 |                 |    10 |   750 |     9    (23)| 00:00:01 |       |       |  Q1,01 | PCWP |        |
|   8 |     PX SEND HASH                                  | :TQ10000        |    10 |   750 |     9    (23)| 00:00:01 |       |       |  Q1,00 | P->P | HASH   |
|   9 |      HASH GROUP BY                                |                 |    10 |   750 |     9    (23)| 00:00:01 |       |       |  Q1,00 | PCWP |        |
|  10 |       NESTED LOOPS                                |                 |    10 |   750 |     7     (0)| 00:00:01 |       |       |  Q1,00 | PCWP |        |
|  11 |        PX PARTITION HASH ALL                      |                 |    10 |   250 |     2     (0)| 00:00:01 |     1 |     8 |  Q1,00 | PCWC |        |
|* 12 |         TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | CONNECTIONSGE$  |    10 |   250 |     2     (0)| 00:00:01 |     1 |     8 |  Q1,00 | PCWP |        |
|* 13 |          INDEX RANGE SCAN                         | CONNECTIONSXSE$ |    20 |       |     1     (0)| 00:00:01 |     1 |     8 |  Q1,00 | PCWP |        |
|  14 |        PARTITION HASH ITERATOR                    |                 |     1 |       |     0     (0)| 00:00:01 |   KEY |   KEY |  Q1,00 | PCWP |        |
|* 15 |         TABLE ACCESS BY LOCAL INDEX ROWID         | CONNECTIONSVT$  |       |       |        |                |   KEY |   KEY |  Q1,00 | PCWP |        |
|* 16 |          INDEX UNIQUE SCAN                        | CONNECTIONSXQV$ |     1 |       |     0     (0)| 00:00:01 |   KEY |   KEY |  Q1,00 | PCWP |        |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  12 - filter(INTERNAL_FUNCTION("EL") AND "EL"=U'collaborates')
  13 - access("SVID"=1)
  15 - filter(INTERNAL_FUNCTION("VERTICES"."V"))
  16 - access("DVID"="VERTICES"."VID" AND "VERTICES"."K"=U'country')
       filter(INTERNAL_FUNCTION("VERTICES"."K"))

4.4 Navigation Options: CONNECT BY and Parallel Recursion

The CONNECT BY clause and parallel recursion provide options for advanced navigation and querying.

  • CONNECT BY lets you navigate and find matches in a hierarchical order. To follow outgoing edges, you can use prior dvid = svid to guide the navigation.

  • Parallel recursion lets you perform navigation up to a specified number of hops away.

The examples use a property graph named connections.

Example 4-15 CONNECT WITH

The following SQL statement follows the outgoing edges by 1 hop.

SQL> select G.dvid
       from connectionsGE$ G
      start with svid = 1
    connect by nocycle prior dvid = svid and level <= 1;

The preceding example's output and execution plan may be as follows.

         2
         3
         4
         5
         6
         7
         8
         9
        10
        ...
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                 |     7 |   273 |     3  (67)| 00:00:01 |       |       |        |      |            |
|*  1 |  CONNECT BY WITH FILTERING|                 |       |       |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR          |                 |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)    | :TQ10000        |     2 |    12 |     0   (0)| 00:00:01 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX PARTITION HASH ALL |                 |     2 |    12 |     0   (0)| 00:00:01 |     1 |     8 |  Q1,00 | PCWC |            |
|*  5 |      INDEX RANGE SCAN     | CONNECTIONSXSE$ |     2 |    12 |     0   (0)| 00:00:01 |     1 |     8 |  Q1,00 | PCWP |            |
|*  6 |   FILTER                  |                 |       |       |            |          |       |       |        |      |            |
|   7 |    NESTED LOOPS           |                 |     5 |    95 |     1   (0)| 00:00:01 |       |       |        |      |            |
|   8 |     CONNECT BY PUMP       |                 |       |       |            |          |       |       |        |      |            |
|   9 |     PARTITION HASH ALL    |                 |     2 |    12 |     0   (0)| 00:00:01 |     1 |     8 |        |      |            |
|* 10 |      INDEX RANGE SCAN     | CONNECTIONSXSE$ |     2 |    12 |     0   (0)| 00:00:01 |     1 |     8 |        |      |            |
------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("SVID"=PRIOR "DVID")
       filter(LEVEL<=2)
   5 - access("SVID"=1)
   6 - filter(LEVEL<=2)
  10 - access("connect$_by$_pump$_002"."prior dvid "="SVID")

To extend from 1 hop to multiple hops, change 1 in the preceding example to another integer. For example, to change it to 2 hops, specify: level <= 2

Example 4-16 Parallel Recursion

The following SQL statement uses recursion within the WITH clause to perform navigation up to 4 hops away, a using recursively defined graph expansion: g_exp references g_exp in the query, and that defines the recursion. The example also uses the PARALLEL optimizer hint for parallel execution.

SQL> WITH g_exp(svid, dvid, depth) as
  ( 
    select svid as svid, dvid as dvid, 0 as depth
      from connectionsGE$
     where svid=1
   union all
     select g2.svid,  g1.dvid, g2.depth + 1
       from g_exp g2, connectionsGE$ g1
      where g2.dvid=g1.svid
        and g2.depth <= 3
  )   
select  /*+ parallel(4) */ dvid, depth
  from  g_exp
 where svid=1
;

The example's output and execution plan may be as follows. Note that CURSOR DURATION MEMORY is chosen in the execution, which indicates the graph expansion stores the intermediate data in memory.

        22          4
        25          4
        24          4
         1          4

        23          4
        33          4
        22          4
        22          4
       ...         ...


Execution Plan
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |                            |   801 | 31239 |   147   (0)| 00:00:01 |       |       |        |      |            |
|   1 |  TEMP TABLE TRANSFORMATION                   |                            |       |       |            |          |       |       |        |      |            |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)    | SYS_TEMP_0FD9D6614_11CB2D2 |       |       |            |          |       |       |        |      |            |
|   3 |    UNION ALL (RECURSIVE WITH) BREADTH FIRST  |                            |       |       |            |          |       |       |        |      |            |
|   4 |     PX COORDINATOR                           |                            |       |       |            |          |       |       |        |      |            |
|   5 |      PX SEND QC (RANDOM)                     | :TQ20000                   |     2 |    12 |     0   (0)| 00:00:01 |       |       |  Q2,00 | P->S | QC (RAND)  |
|   6 |       LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6614_11CB2D2 |       |       |            |          |       |       |  Q2,00 | PCWP |            |
|   7 |        PX PARTITION HASH ALL                 |                            |     2 |    12 |     0   (0)| 00:00:01 |     1 |     8 |  Q2,00 | PCWC |            |
|*  8 |         INDEX RANGE SCAN                     | CONNECTIONSXSE$            |     2 |    12 |     0   (0)| 00:00:01 |     1 |     8 |  Q2,00 | PCWP |            |
|   9 |     PX COORDINATOR                           |                            |       |       |            |          |       |       |        |      |            |
|  10 |      PX SEND QC (RANDOM)                     | :TQ10000                   |   799 |    12M|    12   (0)| 00:00:01 |       |       |  Q1,00 | P->S | QC (RAND)  |
|  11 |       LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6614_11CB2D2 |       |       |            |          |       |       |  Q1,00 | PCWP |            |
|* 12 |        HASH JOIN                             |                            |   799 |    12M|    12   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|  13 |         BUFFER SORT (REUSE)                  |                            |       |       |            |          |       |       |  Q1,00 | PCWP |            |
|  14 |          PARTITION HASH ALL                  |                            |   164 |   984 |     2   (0)| 00:00:01 |     1 |     8 |  Q1,00 | PCWC |            |
|  15 |           INDEX FAST FULL SCAN               | CONNECTIONSXDE$            |   164 |   984 |     2   (0)| 00:00:01 |     1 |     8 |  Q1,00 | PCWP |            |
|  16 |         PX BLOCK ITERATOR                    |                            |       |       |            |          |       |       |  Q1,00 | PCWC |            |
|* 17 |          TABLE ACCESS FULL                   | SYS_TEMP_0FD9D6614_11CB2D2 |       |       |            |          |       |       |  Q1,00 | PCWP |            |
|  18 |   PX COORDINATOR                             |                            |       |       |            |          |       |       |        |      |            |
|  19 |    PX SEND QC (RANDOM)                       | :TQ30000                   |   801 | 31239 |   135   (0)| 00:00:01 |       |       |  Q3,00 | P->S | QC (RAND)  |
|* 20 |     VIEW                                     |                            |   801 | 31239 |   135   (0)| 00:00:01 |       |       |  Q3,00 | PCWP |            |
|  21 |      PX BLOCK ITERATOR                       |                            |   801 |    12M|   135   (0)| 00:00:01 |       |       |  Q3,00 | PCWC |            |
|  22 |       TABLE ACCESS FULL                      | SYS_TEMP_0FD9D6614_11CB2D2 |   801 |    12M|   135   (0)| 00:00:01 |       |       |  Q3,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   8 - access("SVID"=1)
  12 - access("G2"."DVID"="G1"."SVID")
  17 - filter("G2"."INTERNAL_ITERS$"=LEVEL AND "G2"."DEPTH"<=3)
  20 - filter("SVID"=1)

4.5 Pivot

The PIVOT clause lets you dynamically add columns to a table to create a new table.

The schema design (VT$ and GE$) of the property graph is narrow ("skinny") rather than wide ("fat"). This means that if a vertex or edge has multiple properties, those property keys, values, data types, and so on will be stored using multiple rows instead of multiple columns. Such a design is very flexible in the sense that you can add properties dynamically without having to worry about adding too many columns or even reaching the physical maximum limit of number of columns a table may have. However, for some applications you may prefer to have a wide table if the properties are somewhat homogeneous.

Example 4-17 Pivot

The following CREATE TABLE ... AS SELECT statement uses PIVOT to add four columns: ‘company’,’ occupation’,’ name’, and ‘religion’.

SQL> CREATE TABLE table pg_wide
as
 with G AS (select vid, k, t, v
              from connectionsVT$
           )
 select *
   from G
  pivot (
    min(v) for k in ('company', 'occupation', 'name', 'religion')
  );

Table created.

The following DESCRIBE statement shows the definition of the new table, including the four added columns. (The output is reformatted for readability.)

SQL> DESCRIBE pg_wide;
 Name                                                  Null?    Type
--------------------------------------------------- -------- --------------------
 VID                                                   NOT NULL NUMBER
 T                                                              NUMBER(38)
 'company'                                                      NVARCHAR2(15000)
 'occupation'                                                   NVARCHAR2(15000)
 'name'                                                         NVARCHAR2(15000)
 'religion'                                                     NVARCHAR2(15000)

4.6 SQL-Based Property Graph Analytics

In addition to the analytical functions offered by the in-memory analyst, the property graph feature in Oracle Spatial and Graph supports several native, SQL-based property graph analytics.

The benefits of SQL-based analytics are:

  • Easier analysis of larger graphs that do not fit in physical memory

  • Cheaper analysis since no graph data is transferred outside the database

  • Better analysis using the current state of a property graph database

  • Simpler analysis by eliminating the step of synchronizing an in-memory graph with the latest updates from the graph database

However, when a graph (or a subgraph) fits in memory, then running analytics provided by the in-memory analyst usually provides better performance than using SQL-based analytics.

Because many of the analytics implementation require using intermediate data structures, most SQL- (and PL/SQL-) based analytics APIs have parameters for working tables (wt). A typical flow has the following steps:

  1. Prepare the working table or tables.

  2. Perform analytics (one or multiple calls).

  3. Perform cleanup

The following subtopics provide SQL-based examples of some popular types of property graph analytics.

4.6.1 Shortest Path Examples

The following examples demonstrate SQL-based shortest path analytics.

Example 4-18 Shortest Path Setup and Computation

Consider shortest path, for example. Internally, Oracle Database uses the bidirectional Dijkstra algorithm. The following code snippet shows an entire prepare, perform, and cleanup  workflow.

set serveroutput on
  
DECLARE   
  wt1 varchar2(100);  -- intermediate working tables
  n number;
  path    varchar2(1000);
  weights varchar2(1000);
BEGIN
  -- prepare
  opg_apis.find_sp_prep('connectionsGE$', wt1);
  dbms_output.put_line('working table name    ' || wt1);

  -- compute
  opg_apis.find_sp(
     'connectionsGE$',
      1,                          -- start vertex ID
      53,                         -- destination vertex ID
      wt1,                        -- working table (for Dijkstra expansion)
      dop => 1,                   -- degree of parallelism
      stats_freq=>1000,           -- frequency to collect statistics
      path_output => path,        -- shortest path (a sequence of vertices)
      weights_output => weights,  -- edge weights
      options => null
      );
  dbms_output.put_line('path    ' || path);
  dbms_output.put_line('weights ' || weights);

  -- cleanup (commented out here; see text after the example)
  -- opg_apis.find_sp_cleanup('connectionsGE$', wt1);
END;
/

This example may produce the following output. Note that if no working table name is provided, the preparation step will automatically generate a temporary table name and create it. Because the temporary working table name uses the session ID, your output will probably be different.

working table name    "CONNECTIONSGE$$TWFS12"
path    1 3    52 53
weights 4 3 1    1 1

PL/SQL procedure successfully completed.

If you want to know the definition of the working table or tables, then skip the cleanup phase (as shown in the preceding example that comments out the call to find_sp_cleanup). After the computation is done, you can describe the working table or tables.

SQL> describe "CONNECTIONSGE$$TWFS12"
 Name                Null?    Type
 --------- -------- ----------------------------
 NID                            NUMBER
 D2S                            NUMBER
 P2S                            NUMBER
 D2T                            NUMBER
 P2T                            NUMBER
 F                            NUMBER(38)
 B                            NUMBER(38)

For advanced users who want to try different table creation options, such as using in-memory or advanced compression, you can pre-create the preceding working table and pass the name in.

Example 4-19 Shortest Path: Create Working Table and Perform Analytics

The following statements show some advanced options, first creating a working table with the same column structure and basic compression enabled, then passing it to the SQL-based computation. The code optimizes the intermediate table for computations with CREATE TABLE compression and in-memory options.

create table connections$MY_EXP(
 NID                            NUMBER,
 D2S                            NUMBER,
 P2S                            NUMBER,
 D2T                            NUMBER,
 P2T                            NUMBER,
 F                            NUMBER(38),
 B                            NUMBER(38)
) compress nologging;


DECLARE
  wt1 varchar2(100) := 'connections$MY_EXP';
  n number;
  path    varchar2(1000);
  weights varchar2(1000);
BEGIN
  dbms_output.put_line('working table name    ' || wt1);

  -- compute
  opg_apis.find_sp(
     'connectionsGE$',
      1,
      53,
      wt1,
      dop => 1,
      stats_freq=>1000,
      path_output => path,
      weights_output => weights,
      options => null
      );
  dbms_output.put_line('path    ' || path);
  dbms_output.put_line('weights ' || weights);

  -- cleanup
  -- opg_apis.find_sp_cleanup('connectionsGE$', wt1);
END;
/

At the end of the computation, if the working table has not been dropped or truncated, you can check the content of the working table, as follows. Note that the working table structure may vary between releases.

SQL> select * from connections$MY_EXP;
       NID        D2S        P2S        D2T        P2T          F          B
---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          0            1.000E+100                     1         -1
        53 1.000E+100                     0                    -1          1
        54 1.000E+100                     1         53         -1          1
        52 1.000E+100                     1         53         -1          1
         5          1          1 1.000E+100                     0         -1
        26          1          1 1.000E+100                     0         -1
         8       1000          1 1.000E+100                     0         -1
         3          1          1          2         52          0          0
        15          1          1 1.000E+100                     0         -1
        21          1          1 1.000E+100                     0         -1
        19          1          1 1.000E+100                     0         -1
       ...

Example 4-20 Shortest Path: Perform Multiple Calls to Same Graph

To perform multiple calls to the same graph, only a single call to the preparation step is needed. The following shows an example of computing shortest path for multiple pairs of vertices in the same graph.

DECLARE
  wt1 varchar2(100);  -- intermediate working tables
  n number;
  path    varchar2(1000);
  weights varchar2(1000);
BEGIN
  -- prepare
  opg_apis.find_sp_prep('connectionsGE$', wt1);
  dbms_output.put_line('working table name    ' || wt1);

  -- find shortest path from vertex 1 to vertex 53
  opg_apis.find_sp( 'connectionsGE$', 1, 53,
      wt1, dop => 1, stats_freq=>1000, path_output => path, weights_output => weights, options => null);
  dbms_output.put_line('path    ' || path);
  dbms_output.put_line('weights ' || weights);

  -- find shortest path from vertex 2 to vertex 36
  opg_apis.find_sp( 'connectionsGE$', 2, 36,
      wt1, dop => 1, stats_freq=>1000, path_output => path, weights_output => weights, options => null);
  dbms_output.put_line('path    ' || path);
  dbms_output.put_line('weights ' || weights);

  -- find shortest path from vertex 30 to vertex 4
  opg_apis.find_sp( 'connectionsGE$', 30, 4,
      wt1, dop => 1, stats_freq=>1000, path_output => path, weights_output => weights, options => null);
  dbms_output.put_line('path    ' || path);
  dbms_output.put_line('weights ' || weights);

  -- cleanup
  opg_apis.find_sp_cleanup('connectionsGE$', wt1);
END;
/

The example's output may be as follows: three shortest paths have been found for the multiple pairs of vertices provided.

working table name    "CONNECTIONSGE$$TWFS12"
path    1 3    52 53
weights 4 3 1   1 1
path    2    36
weights 2 1   1
path    30 21    1 4
weights 4 3 1   1 1

PL/SQL procedure successfully completed.

4.6.2 Collaborative Filtering Overview and Examples

Collaborative filtering, also referred to as social filtering, filters information by using the recommendations of other people. Collaborative filtering is widely used in systems that recommend purchases based on purchases by others with similar preferences.

The following examples demonstrate SQL-based collaborative filtering analytics.

Example 4-21 Collaborative Filtering Setup and Computation

This example shows how to use SQL-based collaborative filtering, specifically using matrix factorization to recommend telephone brands to customers. This example assumes there exists a graph called "PHONES" in the database. This example graph contains customer and item vertices, and edges with a 'rating' label linking some customer vertices to other some item vertices. The rating labels have a numeric value corresponding to the rating that a specific customer (edge OUT vertex) assigned to the specified product (edge IN vertex).

The following figure shows this graph.

Figure 4-1 Phones Graph for Collaborative Filtering

Description of Figure 4-1 follows
Description of "Figure 4-1 Phones Graph for Collaborative Filtering"
The following code shows an end-to-end flow to run the SQL-based collaborative filtering algorithm, which internally uses the matrix factorization algorithm.
set serveroutput on

DECLARE
  wt_l varchar2(32);  -- working tables
  wt_r varchar2(32);
  wt_l1 varchar2(32);
  wt_r1 varchar2(32);
  wt_i varchar2(32);
  wt_ld varchar2(32);
  wt_rd varchar2(32);
  edge_tab_name    varchar2(32) := 'phonesge$';
  edge_label       varchar2(32) := 'rating';
  rating_property  varchar2(32) := '';
  iterations       integer      := 100;
  min_error        number       := 0.001;
  k                integer      := 5;
  learning_rate    number       := 0.001;
  decrease_rate    number       := 0.95;
  regularization   number       := 0.02;
  dop              number       := 2;
  tablespace       varchar2(32) := null;
  options          varchar2(32) := null; 
BEGIN

  -- prepare
  opg_apis.cf_prep(edge_tab_name,wt_l,wt_r,wt_l1,wt_r1,wt_i,wt_ld,wt_rd);
  dbms_output.put_line('working table wt_l ' || wt_l);
  dbms_output.put_line('working table wt_r ' || wt_r);
  dbms_output.put_line('working table wt_l1 ' || wt_l1);
  dbms_output.put_line('working table wt_r1 ' || wt_r1);
  dbms_output.put_line('working table wt_i ' || wt_i);
  dbms_output.put_line('working table wt_ld ' || wt_ld);
  dbms_output.put_line('working table wt_rd ' || wt_rd);

  -- compute
  opg_apis.cf(edge_tab_name,edge_label,rating_property,iterations,
              min_error,k,learning_rate,decrease_rate,regularization,dop,
              wt_l,wt_r,wt_l1,wt_r1,wt_i,wt_ld,wt_rd,tablespace,options);
END;
/
The flow starts by creating the temporary working tables that are later on passed to the computation. At the end of computation, the example may produce the following output. Note that if no working table name is provided, the preparation step will automatically generate a temporary table name and create it. Because the temporary working table name uses the session ID, your output will probably be different.
working table wt_l    "PHONESGE$$CFL57"
working table wt_r    "PHONESGE$$CFR57"
working table wt_l1    "PHONESGE$$CFL157"
working table wt_r1    "PHONESGE$$CFR157"
working table wt_i    "PHONESGE$$CFI57"
working table wt_ld    "PHONESGE$$CFLD57"
working table wt_rd    "PHONESGE$$CFRD57"

PL/SQL procedure successfully completed.

Example 4-22 Collaborative Filtering: Validating the Intermediate Error

At the end of every computation, you can check the current error of the algorithm with the following query as long as the data in the working tables has not been already deleted. The following SQL query illustrates how to get the intermediate error of a current run of the collaborative filtering algorithm.

SELECT /*+ parallel(48) */ SQRT(SUM((w1-w2)*(w1-w2) + 
              <regularization>/2 * (err_reg_l+err_reg_r))) AS err 
  FROM <wt_i>;

Note that the regularization parameter and the working table name (parameter wt_i) should be replaced according to the values used when running the OPG_APIS.CF algorithm. In the preceding previous example, replace <regularization> with 0.02 and <wt_i> with "PHONESGE$$CFI149" as follows:

SELECT /*+ parallel(48) */ SQRT(SUM((w1-w2)*(w1-w2) + 0.02/2 * (err_reg_l+err_reg_r))) AS err 
  FROM "PHONESGE$$CFI149";

This query may produce the following output.

       ERR
----------
4.82163662

f the value of the current error is too high or if the predictions obtained from the matrix factorization results of the collaborative filtering are not yet useful, you can run more iterations of the algorithm, by reusing the working tables and the progress made so far. The following example shows how to make predictions using the SQL-based collaborative filtering.

Example 4-23 Collaborative Filtering: Making Predictions

The result of the collaborative filtering algorithm is stored in the tables wt_l and wt_r, which are the two factors of a matrix product. These matrix factors should be used when making the predictions of the collaborative filtering.

In a typical flow of the algorithm, the two matrix factors can be used to make the predictions before calling the OPG_APIS.CF_CLEANUP procedure, or they can be copied and persisted into other tables for later use. The following example demonstrates the latter case:

DECLARE
  wt_l varchar2(32);  -- working tables
  wt_r varchar2(32);
  wt_l1 varchar2(32);
  wt_r1 varchar2(32);
  wt_i varchar2(32);
  wt_ld varchar2(32);
  wt_rd varchar2(32);
  edge_tab_name    varchar2(32) := 'phonesge$';
  edge_label       varchar2(32) := 'rating';
  rating_property  varchar2(32) := '';
  iterations       integer      := 100;
  min_error        number       := 0.001;
  k                integer      := 5;
  learning_rate    number       := 0.001;
  decrease_rate    number       := 0.95;
  regularization   number       := 0.02;
  dop              number       := 2;
  tablespace       varchar2(32) := null;
  options          varchar2(32) := null; 
BEGIN

  -- prepare
  opg_apis.cf_prep(edge_tab_name,wt_l,wt_r,wt_l1,wt_r1,wt_i,wt_ld,wt_rd);

  -- compute
  opg_apis.cf(edge_tab_name,edge_label,rating_property,iterations,
              min_error,k,learning_rate,decrease_rate,regularization,dop,
              wt_l,wt_r,wt_l1,wt_r1,wt_i,wt_ld,wt_rd,tablespace,options);
  
  -- save only these two tables for later predictions
  EXECUTE IMMEDIATE 'CREATE TABLE customer_mat AS SELECT * FROM ' || wt_l;
  EXECUTE IMMEDIATE 'CREATE TABLE item_mat AS SELECT * FROM ' || wt_r;

  -- cleanup
  opg_apis.cf_cleanup('phonesge$',wt_l,wt_r,wt_l1,wt_r1,wt_i,wt_ld,wt_rd);
END;
/

This example will produce the only the following output.

PL/SQL procedure successfully completed.

Now that the matrix factors are saved in the tables customer_mat and item_mat, you can use the following query to check the "error" (difference) between the real values (those values that previously existed in the graph as 'ratings') and the estimated predictions (the result of the matrix multiplication in a certain customer row and item column).

Note that the following query is customized with a join on the vertex table in order return an NVARCHAR property of the vertices (for example, the name property) instead of a numeric ID. This query will return all the predictions for every single customer vertex to every item vertex in the graph.

SELECT /*+ parallel(48) */ MIN(vertex1.v) AS customer, 
                           MIN(vertex2.v) AS item, 
                           MIN(edges.vn) AS real, 
                           SUM(l.v * r.v) AS predicted
FROM PHONESGE$ edges, 
      CUSTOMER_MAT l, 
      ITEM_MAT r, 
      PHONESVT$ vertex1,   
      PHONESVT$ vertex2
WHERE l.k = r.k
  AND l.c = edges.svid(+)
  AND r.p = edges.dvid(+)
  AND l.c = vertex1.vid
  AND r.p = vertex2.vid
GROUP BY l.c, r.p
ORDER BY l.c, r.p  -- This order by clause is optional
;

This query may produce an output similar to the following (some rows are omitted for brevity).

CUSTOMER	ITEM		REAL	PREDICTED
------------------------------------------------
Adam		Apple 	 	5	3.67375703
Adam		Blackberry		3.66079652
Adam		Danger			2.77049596
Adam		Ericsson		4.21764858
Adam		Figo			3.10631337
Adam		Google		4 	4.42429022
Adam		Huawei		3	3.4289115
Ben		Apple	  	   	2.82127589
Ben		Blackberry	2	2.81132282
Ben		Danger		3	2.12761307
Ben		Ericsson	3   	3.2389595
Ben		Figo	  	    	2.38550534
Ben		Google		   	3.39765075
Ben		Huawei		    	2.63324582
...
Don		Apple		    	1.3777496
Don		Blackberry	1 	1.37288909
Don		Danger		1 	1.03900439
Don		Ericsson	   	1.58172236
Don		Figo		1	1.16494421
Don		Google			1.65921807
Don		Huawei		1	1.28592648
Erik		Apple		3	2.80809351
Erik		Blackberry	3	2.79818695
Erik		Danger			2.11767182
Erik		Ericsson	3 	3.2238255
Erik		Figo			2.3743591
Erik		Google		3	3.38177526
Erik		Huawei		3	2.62094201

If you want to check only some rows to decide whether the prediction results are ready or more iterations of the algorithm should be run, the previous query can be wrapped in an outer query. The following example will select only the first 11 results.

SELECT /*+ parallel(48) */ * FROM (
SELECT /*+ parallel(48) */ MIN(vertex1.v) AS customer, 
                           MIN(vertex2.v) AS item, 
                           MIN(edges.vn) AS real, 
                           SUM(l.v * r.v) AS predicted
FROM PHONESGE$ edges, 
     CUSTOMER_MAT l, 
     ITEM_MAT r, 
     PHONESVT$ vertex1,   
     PHONESVT$ vertex2
WHERE l.k = r.k
  AND l.c = edges.svid(+)
  AND r.p = edges.dvid(+)
  AND l.c = vertex1.vid
  AND r.p = vertex2.vid
GROUP BY l.c, r.p
ORDER BY l.c, r.p
) WHERE rownum <= 11;

This query may produce an output similar to the following.

CUSTOMER	ITEM		REAL	PREDICTED
------------------------------------------------
Adam		Apple 	 	5	3.67375703
Adam		Blackberry		3.66079652
Adam		Danger			2.77049596
Adam		Ericsson		4.21764858
Adam		Figo			3.10631337
Adam		Google		4 	4.42429022
Adam		Huawei		3	3.4289115
Ben		Apple	  	   	2.82127589
Ben		Blackberry	2	2.81132282
Ben		Danger		3	2.12761307
Ben		Ericsson	3   	3.2389595

To get a prediction for a specific vertex (customer, item, or both) the query can be restricted with the desired ID values. For example, to get the predicted value of vertex 1 (customer) and vertex 105 (item), you can use the following query.

SELECT /*+ parallel(48) */ MIN(vertex1.v) AS customer, 
                           MIN(vertex2.v) AS item, 
                           MIN(edges.vn) AS real, 
                           SUM(l.v * r.v) AS predicted
FROM PHONESGE$ edges, 
     CUSTOMER_MAT l, 
     ITEM_MAT r, 
     PHONESVT$ vertex1,   
     PHONESVT$ vertex2
WHERE l.k = r.k
  AND l.c = edges.svid(+)
  AND r.p = edges.dvid(+)
  AND l.c = vertex1.vid 
  AND vertex1.vid = 1 /* Remove to get all predictions for item 105 */
  AND r.p = vertex2.vid 
  AND vertex2.vid = 105 /* Remove to get all predictions for customer 1 */
                        /* Remove both lines to get all predictions */
GROUP BY l.c, r.p
ORDER BY l.c, r.p;

This query may produce an output similar to the following.

CUSTOMER	ITEM		REAL	PREDICTED
------------------------------------------------
Adam		Ericsson		4.21764858

4.7 Property Graph Query Language (PGQL)

PGQL is a SQL-like query language for property graph data structures that consist of vertices that are connected to other vertices by edges, each of which can have key-value pairs (properties) associated with them.

The language is based on the concept of graph pattern matching, which allows you to specify patterns that are matched against vertices and edges in a data graph.

The property graph support provides two ways to execute Property Graph Query Language (PGQL) queries through Java APIs:

For more information about PGQL, see https://pgql-lang.org.

4.7.1 Creating a Property Graph using PGQL

CREATE PROPERTY GRAPH is a PGQL DDL statement to create a graph from database tables. The graph is stored in the property graph schema.

The CREATE PROPERTY GRAPH statement starts with the name you give the graph, followed by a set of vertex tables and edge tables. The graph can have no vertex tables or edge tables (an empty graph), or vertex tables and no edge tables (a graph with only vertices and no edges), or both vertex tables and edge tables (a graph with vertices and edges). However, a graph cannot be specified with only edge tables and no vertex tables.

Consider the following example:

  • PERSONS is a table with columns ID, NAME, and ACCOUNT_NUMBER. A row is added to this table for every person who has an account.
  • TRANSACTIONS is a table with columns FROM_ACCOUNT, TO_ACCOUNT, DATE, and AMOUNT. A row is added into this table in the database every time money is transferred from a FROM_ACCOUNT to a TO_ACCOUNT.

A straightforward mapping of tables to graphs is as follows. The graph concepts mapped are: vertices, edges, labels, properties.

  • Vertex tables: A table that contains data entities is a vertex table.
    • Each row in the vertex table is a vertex.
    • The columns in the vertex table are properties of the vertex.
    • The name of the vertex table is the default label for this set of vertices. Alternatively, you can specify a label name as part of the CREATE PROPERTY GRAPH statement.
  • Edge tables: An edge table can be any table that links two vertex tables, or a table that has data that indicates an action from a source entity to a target entity. For example, a transfer of money from FROM_ACCOUNT to TO_ACCOUNT is a natural edge.
    • Foreign key relationships can give guidance on what links are relevant in your data. CREATE PROPERTY GRAPH will default to using foreign key relationships to identify edges.
    • Some of the properties of an edge table can be the properties of the edge. For example, an edge from FROM_ACCOUNT to TO_ACCOUNT can have properties DATE and AMOUNT.
    • The name of an edge table is the default label for this set of edges. Alternatively, you can specify a label name as part of the CREATE PROPERTY GRAPH statement.
  • Keys:
    • Keys in a vertex table: The key of a vertex table identifies a unique vertex in the graph. The key can be specified in the CREATE PROPERTY GRAPH statement; otherwise, it defaults to the primary key of the table. If there are duplicate rows in the table, the CREATE PROPERTY GRAPH statement will return an error.
    • Key in an edge table: The key of an edge table uniquely identifies an edge in the graph. The KEY clause when specifying source and destination vertices uniquely identifies the source and destination vertices.

    The following is an example CREATE PROPERTY GRAPH statement for the tables PERSONS and TRANSACTIONS.

    CREATE PROPERTY GRAPH bank_transfers
         VERTEX TABLES (persons KEY(account_number))
         EDGE TABLES(
                      transactions KEY (from_acct, to_acct, date, amount)
                      SOURCE KEY (from_account) REFERENCES persons
                      DESTINATION KEY (to_account) REFERENCES persons
                      PROPERTIES (date, amount)
           )
    
  • Table aliases: Vertex and edge tables must have unique names. If you need to identify multiple vertex tables from the same relational table, or multiple edge tables from the same relational table, you must use aliases. For example, you can create two vertex tables PERSONS and PERSONS_ID from one table PERSONS, as in the following example.
    CREATE PROPERTY GRAPH bank_transfers
         VERTEX TABLES (persons KEY(account_number)
                        persons_id AS persons KEY(id))
    
  • REFERENCES clause: This connects the source and destination vertices of an edge to the corresponding vertex tables.

For more details, see: https://pgql-lang.org/spec/latest/#creating-a-property-graph.

4.7.2 Pattern Matching with PGQL

Pattern matching is done by specifying one or more path patterns in the MATCH clause. A single path pattern matches a linear path of vertices and edges, while more complex patterns can be matched by combining multiple path patterns, separated by comma. Value expressions (similar to their SQL equivalents) are specified in the WHERE clause and let you filter out matches, typically by specifying constraints on the properties of the vertices and edges

For example, assume a graph of TCP/IP connections on a computer network, and you want to detect cases where someone logged into one machine, from there into another, and from there into yet another. You would query for that pattern like this:

SELECT id(host1) AS id1, id(host2) AS id2, id(host3) AS id3         /* choose what to return */
FROM MATCH
    (host1) -[connection1]-> (host2) -[connection2]-> (host3)       /* single linear path pattern to match */
WHERE
    connection1.toPort = 22 AND connection1.opened = true AND
    connection2.toPort = 22 AND connection2.opened = true AND
    connection1.bytes > 300 AND                                     /* meaningful amount of data was exchanged */
    connection2.bytes > 300 AND
    connection1.start < connection2.start AND                       /* second connection within time-frame of first */
    connection2.start + connection2.duration < connection1.start + connection1.duration
GROUP BY id1, id2, id3                                              /* aggregate multiple matching connections */ 

For more examples of pattern matching, see the relevant section of the PGQL specification.

4.7.3 Edge Patterns Have a Direction with PGQL

An edge pattern has a direction, as edges in graphs do. Thus, (a) <-[]- (b) specifies a case where b has an edge pointing at a, whereas (a) -[]-> (b) looks for an edge in the opposite direction.

The following example finds common friends of April and Chris who are older than both of them.

SELECT friend.name, friend.dob
FROM MATCH                  /* note the arrow directions below */
  (p1:person) -[:likes]-> (friend) <-[:likes]- (p2:person)
WHERE
  p1.name = 'April' AND p2.name ='Chris' AND
  friend.dob > p1.dob AND friend.dob > p2.dob
ORDER BY friend.dob DESC 

For more examples of edge patterns, see the relevant section of the PGQL specification here.

4.7.4 Vertex and Edge Labels with PGQL

Labels are a way of attaching type information to edges and nodes in a graph, and can be used in constraints in graphs where not all nodes represent the same thing. For example:

SELECT p.name
FROM MATCH (p:person) -[e1:likes]-> (m1:movie),
     MATCH (p) -[e2:likes]-> (m2:movie)
WHERE m1.title = 'Star Wars'
  AND m2.title = 'Avatar'

For more examples of label expressions, see the relevant section of the PGQL specification here.

4.7.5 Variable-Length Paths with PGQL

Variable-length path patterns have a quantifier like * to match a variable number of vertices and edges. Using a PATH macro, you can specify a named path pattern at the start of a query that can be embedded into the MATCH clause any number of times, by referencing its name. The following example finds all of the common ancestors of Mario and Luigi.

PATH has_parent AS () -[:has_father|has_mother]-> ()
SELECT ancestor.name
FROM MATCH (p1:Person) -/:has_parent*/-> (ancestor:Person)
   , MATCH (p2:Person) -/:has_parent*/-> (ancestor)
WHERE
  p1.name = 'Mario' AND
  p2.name = 'Luigi'

The preceding path specification also shows the use of anonymous constraints, because there is no need to define names for intermediate edges or nodes that will not be used in additional constraints or query results. Anonymous elements can have constraints, such as [:has_father|has_mother] -- the edge does not get a variable name (because it will not be referenced elsewhere), but it is constrained.

For more examples of variable-length path pattern matching, see the relevant section of the PGQL specification here.

4.7.6 Aggregation and Sorting with PGQL

Like SQL, PGQL has support for the following:

  • GROUP BY to create groups of solutions

  • MIN, MAX, SUM, and AVG aggregations

  • ORDER BY to sort results

And for many other familiar SQL constructs.

For GROUP BY and aggregation, see the relevant section of the PGQL specification here. For ORDER BY, see the relevant section of the PGQL specification here.

4.8 Executing PGQL Queries Directly Against Oracle Database

This topic explains how you can execute PGQL queries directly against the graph in Oracle Database (as opposed to in-memory).

Property Graph Query Language (PGQL) queries can be executed against disk-resident property graph data stored in Oracle Database. PGQL on Oracle Database (RDBMS) provides a Java API for executing PGQL queries. Logic in PGQL on RDBMS translates a submitted PGQL query into an equivalent SQL query, and the resulting SQL is executed on the database server. PGQL on RDBMS then wraps the SQL query results with a convenient PGQL result set API.

This PGQL query execution flow is shown in the following figure.

Figure 4-2 PGQL on Oracle Database (RDBMS)

Description of Figure 4-2 follows
Description of "Figure 4-2 PGQL on Oracle Database (RDBMS)"

The basic execution flow is:

  1. The PGQL query is submitted to PGQL on RDBMS through a Java API.

  2. The PGQL query is translated to SQL.

  3. The translated SQL is submitted to Oracle Database by JDBC.

  4. The SQL result set is wrapped as a PGQL result set and returned to the caller.

The ability to execute PGQL queries directly against property graph data stored in Oracle Database provides several benefits.

  • PGQL provides a more natural way to express graph queries than SQL manually written to query schema tables, including VT$, VD$, GE$, and GT$.

  • PGQL queries can be executed without the need to load a snapshot of your graph data into PGX, so there is no need to worry about staleness of frequently updated graph data.

  • PGQL queries can be executed against graph data that is too large to fit in memory.

  • The robust and scalable Oracle SQL engine can be used to execute PGQL queries.

  • Mature tools for management, monitoring and tuning of Oracle Database can be used to tune and monitor PGQL queries.

4.8.1 PGQL Features Supported

PGQL is a SQL-like query language for querying property graph data. It is based on the concept of graph pattern matching and allows you to specify, among other things, topology constraints, paths, filters, sorting and aggregation.

The Java API for PGQL defined in the oracle.pg.rdbms.pgql package supports the PGQL specification with a few exceptions. (The PGQL specification can be found at https://pgql-lang.org).

The following features of PGQL are not supported.

  • Shortest path
  • ARRAY_AGG aggregation
  • IN and NOT IN predicates
  • Single CHEAPEST path and TOP-K CHEAPEST path using COST functions
  • Case-insensitive matching of uppercased references to labels and properties

In addition, the following features of PGQL require special consideration.

4.8.1.1 Temporal Types

The temporal types DATE, TIMESTAMP and TIMESTAMP WITH TIMEZONE are supported in PGQL queries.

All of these value types are represented internally using the Oracle SQL TIMESTAMP WITH TIME ZONE type. DATE values are automatically converted to TIMESTAMP WITH TIME ZONE by assuming the earliest time in UTC+0 timezone (for example, 2000-01-01 becomes 2000-01-01 00:00:00.00+00:00). TIMESTAMP values are automatically converted to TIMESTAMP WITH TIME ZONE by assuming UTC+0 timezone (for example, 2000-01-01 12:00:00.00 becomes 2000-01-01 12:00:00.00+00:00).

Temporal constants are written in PGQL queries as follows.

  • DATE 'YYYY-MM-DD'

  • TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF'

  • TIMESTAMP WITH TIMEZONE 'YYYY-MM-DD HH24:MI:SS.FFTZH:TZM'

Some examples are DATE '2000-01-01', TIMESTAMP '2000-01-01 14:01:45.23', TIMESTAMP WITH TIMEZONE '2000-01-01 13:00:00.00-05:00', and TIMESTAMP WITH TIMEZONE '2000-01-01 13:00:00.00+01:00'.

In addition, temporal values can be obtained by casting string values to a temporal type. The supported string formats are:

  • DATE 'YYYY-MM-DD'

  • TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF' and 'YYYY-MM-DD"T"HH24:MI:SS.FF'

  • TIMESTAMP WITH TIMEZONE 'YYYY-MM-DD HH24:MI:SS.FFTZH:TZM' and 'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM'.

Some examples are CAST ('2005-02-04' AS DATE), CAST ('1990-01-01 12:00:00.00' AS TIMESTAMP), CAST ('1985-01-01T14:05:05.00-08:00' AS TIMESTAMP WITH TIMEZONE).

When consuming results from a PgqlResultSet object, getObject returns a java.sql.Timestamp object for temporal types.

Bind variables can only be used for the TIMESTAMP WITH TIMEZONE temporal type in PGQL, and a setTimestamp method that takes a java.sql.Timestamp object as input is used to set the bind value. As a simpler alternative, you can use a string bind variable in a CAST statement to bind temporal values (for example, CAST (? AS TIMESTAMP WITH TIMEZONE) followed by setString(1, "1985-01-01T14:05:05.00-08:00")). See also Using Bind Variables in PGQL Queries for more information about bind variables.

4.8.1.2 Type Casting

Type casting is supported in PGQL with a SQL-style CAST (VALUE AS DATATYPE) syntax, for example CAST('25' AS INT), CAST (10 AS STRING), CAST ('2005-02-04' AS DATE), CAST(e.weight AS STRING). Supported casting operations are summarized in the following table. Y indicates that the conversion is supported, and N indicates that it is not supported. Casting operations on invalid values (for example, CAST('xyz' AS INT)) or unsupported conversions (for example, CAST (10 AS TIMESTAMP)) return NULL instead of raising a SQL exception.

Table 4-1 Type Casting Support in PGQL (From and To Types)

“to” type from STRING from INT from LONG from FLOAT from DOUBLE from BOOLEAN from DATE from TIMESTAMP from TIMESTAMP WITH TIMEZONE

to STRING

Y

Y

Y

Y

Y

Y

Y

Y

Y

to INT

Y

Y

Y

Y

Y

Y

N

N

N

to LONG

Y

Y

Y

Y

Y

Y

N

N

N

to FLOAT

Y

Y

Y

Y

Y

Y

N

N

N

to DOUBLE

Y

Y

Y

Y

Y

Y

N

N

N

to BOOLEAN

Y

Y

Y

Y

Y

Y

N

N

N

to DATE

Y

N

N

N

N

N

Y

Y

Y

to TIMESTAMP

Y

N

N

N

N

N

Y

Y

Y

to TIMESTAMP WITH TIMEZONE

Y

N

N

N

N

N

Y

Y

Y

An example query that uses type casting is:

SELECT e.name, CAST (e.birthDate AS STRING) AS dob
FROM MATCH (e)
WHERE e.birthDate < CAST ('1980-01-01' AS DATE)
4.8.1.3 CONTAINS Built-in Function

A CONTAINS built-in function is supported. It is used in conjunction with an Oracle Text index on vertex and edge properties. CONTAINS returns true if a value matches an Oracle Text search string and false if it does not match.

An example query is:

SELECT v.name
FROM MATCH (v)
WHERE CONTAINS(v.abstract, 'Oracle')

See also Using a Text Index with PGQL Queries for more information about using full text indexes with PGQL.

4.8.2 Creating Property Graphs through CREATE PROPERTY GRAPH Statements

You can use PGQL to create property graphs from relational database tables. A CREATE PROPERTY GRAPH statement defines a set of vertex tables that are transformed into vertices and a set of edge tables that are transformed into edges. For each table a key, a label and a set of column properties can be specified. The column types CHAR, NCHAR, VARCHAR, VARCHAR2, NVARCHAR2 , NUMBER, LONG, FLOAT, DATE, TIMESTAMP and TIMESTAMP WITH TIMEZONE are supported for CREATE PROPERTY GRAPH column properties.

When a CREATE PROPERTY GRAPH statement is called, a property graph schema for the graph is created, and the data is copied from the source tables into the property graph schema tables. The graph is created as a one-time copy and is not automatically kept in sync with the source data.

Example 4-24 PgqlCreateExample1.java

This example shows how to create a property graph from a set of relational tables. Notice that the example creates tables Person, Hobby, and Hobbies, so they should not exist before running the example. The example also shows how to execute a query against a property graph.

import java.sql.Connection;
import java.sql.Statement;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to create a Property Graph from relational
 * data stored in Oracle Database executing a PGQL create statement.
 */
public class PgqlCreateExample1
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    Statement stmt = null;
    PgqlStatement pgqlStmt = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();
      conn.setAutoCommit(false);

      // Create relational data
      stmt = conn.createStatement();

      //Table Person
      stmt.executeUpdate(
        "create table Person(  " +
        "  id    NUMBER,       " +
        "  name  VARCHAR2(20), " +
        "  dob   TIMESTAMP     " +
        ")");

      // Insert some data
      stmt.executeUpdate("insert into Person values(1,'Alan', DATE '1995-05-26')");
      stmt.executeUpdate("insert into Person values(2,'Ben', DATE '2007-02-15')");
      stmt.executeUpdate("insert into Person values(3,'Claire', DATE '1967-11-30')");

      // Table Hobby
      stmt.executeUpdate(
        "create table Hobby(   " + 
        "  id    NUMBER,       " +
        "  name  VARCHAR2(20)  " +
        ")");

      // Insert some data
      stmt.executeUpdate("insert into Hobby values(1, 'Sports')");
      stmt.executeUpdate("insert into Hobby values(2, 'Music')");

      // Table Hobbies
      stmt.executeUpdate(
        "create table Hobbies( "+
        "  person    NUMBER, "+
        "  hobby     NUMBER, "+
        "  strength  NUMBER  "+
        ")");

      // Insert some data
      stmt.executeUpdate("insert into Hobbies values(1, 1, 20)");
      stmt.executeUpdate("insert into Hobbies values(1, 2, 30)");
      stmt.executeUpdate("insert into Hobbies values(2, 1, 10)");
      stmt.executeUpdate("insert into Hobbies values(3, 2, 20)");

      //Commit changes
      conn.commit();

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);

      // Create a PgqlStatement
      pgqlStmt = pgqlConn.createStatement();

      // Execute PGQL to create property graph
      String pgql = 
        "Create Property Graph " + graph + " " +
        "VERTEX TABLES ( " +
        "  Person " +
        "    Key(id) " +
        "    Label \"people\" +
        "    PROPERTIES(name AS \"first_name\", dob AS \"birthday\")," +
        "  Hobby  " +
        "    Key(id) Label \"hobby\"  PROPERTIES(name AS \"name\")" +
        ")" +
        "EDGE TABLES (" +
        "  Hobbies" +
        "    SOURCE KEY(person) REFERENCES Person " +
        "    DESTINATION KEY(hobby) REFERENCES Hobby " +
        "    LABEL \"likes\" PROPERTIES (strength AS \"score\")" +
        ")";
      pgqlStmt.execute(pgql);

      // Execute a PGQL query to verify Graph creation
      pgql =
        "SELECT p.\"first_name\", p.\"birthday\", h.\"name\", e.\"score\" " +
        "FROM MATCH (p:\"people\")-[e:\"likes\"]->(h:\"hobby\") ON " + graph;
      rs = pgqlStmt.executeQuery(pgql, "");

      // Print the results
      rs.print();
    }
    finally {
      // close the sql statment
      if (stmt != null) {
        stmt.close();
      }
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (pgqlStmt != null) {
        pgqlStmt.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

The output for PgqlCreateExample1.java is:

+---------------------------------------------------------+
|   first_name |   birthday            |   name |   score |
+---------------------------------------------------------+
| Alan         | 1995-05-25 17:00:00.0 | Music  | 30.0    |
| Claire       | 1967-11-29 16:00:00.0 | Music  | 20.0    |
| Ben          | 2007-02-14 16:00:00.0 | Sports | 10.0    |
| Alan         | 1995-05-25 17:00:00.0 | Sports | 20.0    |
+---------------------------------------------------------+

Example 4-25 PgqlCreateExample2.java

This example shows how a create property graph statement without specifying any keys. Notice that the example creates tables Person, Hobby, and Hobbies, so they should not exist before running the example.

import java.sql.Connection;
import java.sql.Statement;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to create a Property Graph from relational
 * data stored in Oracle Database executing a PGQL create statement.
 */
public class PgqlCreateExample2
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    Statement stmt = null;
    PgqlStatement pgqlStmt = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();
      conn.setAutoCommit(false);

      // Create relational data
      stmt = conn.createStatement();

      //Table Person
      stmt.executeUpdate(
        "create table Person(  " +
        "  id    NUMBER,       " +
        "  name  VARCHAR2(20), " +
        "  dob   TIMESTAMP,    " +
        "  CONSTRAINT pk_person PRIMARY KEY(id)" +
        ")");

      // Insert some data
      stmt.executeUpdate("insert into Person values(1,'Alan', DATE '1995-05-26')");
      stmt.executeUpdate("insert into Person values(2,'Ben', DATE '2007-02-15')");
      stmt.executeUpdate("insert into Person values(3,'Claire', DATE '1967-11-30')");

      // Table Hobby
      stmt.executeUpdate(
        "create table Hobby(   " + 
        "  id    NUMBER,       " +
        "  name  VARCHAR2(20), " +
        "  CONSTRAINT pk_hobby PRIMARY KEY(id)" +
        ")");

      // Insert some data
      stmt.executeUpdate("insert into Hobby values(1, 'Sports')");
      stmt.executeUpdate("insert into Hobby values(2, 'Music')");

      // Table Hobbies
      stmt.executeUpdate(
        "create table Hobbies( "+
        "  person    NUMBER, "+
        "  hobby     NUMBER, "+
        "  strength  NUMBER, "+
        "  CONSTRAINT fk_hobbies1 FOREIGN KEY (person) REFERENCES Person(id), "+
        "  CONSTRAINT fk_hobbies2 FOREIGN KEY (hobby)  REFERENCES Hobby(id)"+
        ")");

      // Insert some data
      stmt.executeUpdate("insert into Hobbies values(1, 1, 20)");
      stmt.executeUpdate("insert into Hobbies values(1, 2, 30)");
      stmt.executeUpdate("insert into Hobbies values(2, 1, 10)");
      stmt.executeUpdate("insert into Hobbies values(3, 2, 20)");

      //Commit changes
      conn.commit();

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);

      // Create a PgqlStatement
      pgqlStmt = pgqlConn.createStatement();

      // Execute PGQL to create property graph
      String pgql = 
        "Create Property Graph " + graph + " " +
        "VERTEX TABLES ( " +
        "  Person " +
        "    Label people +
        "    PROPERTIES ALL COLUMNS," +
        "  Hobby  " +
        "    Label hobby PROPERTIES ALL COLUMNS EXCEPT(id)" +
        ")" +
        "EDGE TABLES (" +
        "  Hobbies" +
        "    SOURCE Person DESTINATION Hobby " +
        "    LABEL likes NO PROPERTIES" +
        ")";
      pgqlStmt.execute(pgql);

      // Execute a PGQL query to verify Graph creation
      pgql =
        "SELECT p.NAME AS person, p.DOB, h.NAME AS hobby " +
        "FROM MATCH (p:people)-[e:likes]->(h:hobby) ON " + graph;
      rs = pgqlStmt.executeQuery(pgql, "");

      // Print the results
      rs.print();
    }
    finally {
      // close the sql statment
      if (stmt != null) {
        stmt.close();
      }
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (pgqlStmt != null) {
        pgqlStmt.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

The output for PgqlCreateExample2.java is:

+-----------------------------------------+
| PERSON |   DOB                 |  HOBBY |
+-----------------------------------------+
| Alan   | 1995-05-25 17:00:00.0 | Music  |
| Claire | 1967-11-29 16:00:00.0 | Music  |
| Ben    | 2007-02-14 16:00:00.0 | Sports |
| Alan   | 1995-05-25 17:00:00.0 | Sports |
+-----------------------------------------+

4.8.3 Dropping Property Graphs through DROP PROPERTY GRAPH Statements

You can use PGQL to drop property graphs. When a DROP PROPERTY GRAPH statement is called, all the property graph schema tables of the graph are dropped.

Example 4-26 PgqlDropExample1.java

This example shows how to drop a property graph.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to drop a Property executing a PGQL drop statement.
 */
public class PgqlDropExample1
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement pgqlStmt = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();
      conn.setAutoCommit(false);

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);

      // Create a PgqlStatement
      pgqlStmt = pgqlConn.createStatement();

      // Execute PGQL to drop property graph
      String pgql = "Drop Property Graph " + graph;
      pgqlStmt.execute(pgql);

    }
    finally {
      // close the statement
      if (pgqlStmt != null) {
        pgqlStmt.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

4.8.4 Using the oracle.pg.rdbms.pgql Java Package to Execute PGQL Queries

The Java API in the oracle.pg.rdbms.pgql package provides support for executing PGQL queries against Oracle Database. This topic explains how to use the Java API through a series of examples.

Note:

Effective with Release 20c, the following classes in the oracle.pg.rdbms package are deprecated:

oracle.pg.rdbms.OraclePgqlColumnDescriptorImpl
oracle.pg.rdbms.OraclePgqlColumnDescriptor
oracle.pg.rdbms.OraclePgqlExecutionFactory
oracle.pg.rdbms.OraclePgqlExecution
oracle.pg.rdbms.PgqlPreparedStatement
oracle.pg.rdbms.OraclePgqlResultElementImpl
oracle.pg.rdbms.OraclePgqlResultElement
oracle.pg.rdbms.OraclePgqlResultImpl
oracle.pg.rdbms.OraclePgqlResultIterable
oracle.pg.rdbms.OraclePgqlResultIteratorImpl
oracle.pg.rdbms.OraclePgqlResult
oracle.pg.rdbms.OraclePgqlResultSetImpl
oracle.pg.rdbms.OraclePgqlResultSet
oracle.pg.rdbms.OraclePgqlResultSetMetaDataImpl
oracle.pg.rdbms.OraclePgqlResultSetMetaData
oracle.pg.rdbms.PgqlSqlQueryTransImpl
oracle.pg.rdbms.PgqlSqlQueryTrans
oracle.pg.rdbms.PgqlStatement

You should instead use equivalent classes in oracle.pg.rdbms.pgql:

oracle.pg.rdbms.pgql.PgqlColumnDescriptorImpl
oracle.pg.rdbms.pgql.PgqlColumnDescriptor
oracle.pg.rdbms.pgql.PgqlConnection
oracle.pg.rdbms.pgql.PgqlExecution
oracle.pg.rdbms.pgql.PgqlPreparedStatement
oracle.pg.rdbms.pgql.PgqlResultElementImpl
oracle.pg.rdbms.pgql.PgqlResultElement
oracle.pg.rdbms.pgql.PgqlResultSetImpl
oracle.pg.rdbms.pgql.PgqlResultSet
oracle.pg.rdbms.pgql.PgqlResultSetMetaDataImpl
oracle.pg.rdbms.pgql.PgqlSqlTransImpl
oracle.pg.rdbms.pgql.PgqlSqlTrans
oracle.pg.rdbms.pgql.PgqlStatement

One difference between oracle.pg.rdbms.OraclePgqlResultSet and oracle.pg.rdbms.pgql.PgqlResultSet is that oracle.pg.rdbms.pgql.PgqlResultSet does not provide APIs to retrieve vertex and edge objects. Existing code using those interfaces should be changed to project IDs rather than OracleVertex and OracleEdge objects. You can obtain an OracleVertex or OracleEdge object from the projected ID values by calling OracleVertex.getInstance() or OracleEdge.getInstance(). (For an example, see Example 5-20.)

The following test_graph data set in Oracle flat file format will be used in the examples in subtopics that follow. The data set includes a vertex file (test_graph.opv) and an edge file (test_graph.ope).

test_graph.opv:

2,fname,1,Ray,,,person
2,lname,1,Green,,,person
2,mval,5,,,1985-01-01T12:00:00.000Z,person
2,age,2,,41,,person
0,bval,6,Y,,,person
0,fname,1,Bill,,,person
0,lname,1,Brown,,,person
0,mval,1,y,,,person
0,age,2,,40,,person
1,bval,6,Y,,,person
1,fname,1,John,,,person
1,lname,1,Black,,,person
1,mval,2,,27,,person
1,age,2,,30,,person
3,bval,6,N,,,person
3,fname,1,Susan,,,person
3,lname,1,Blue,,,person
3,mval,6,N,,,person
3,age,2,,35,,person

test_graph.ope:

4,0,1,knows,mval,1,Y,,
4,0,1,knows,firstMetIn,1,MI,,
4,0,1,knows,since,5,,,1990-01-01T12:00:00.000Z
16,0,1,friendOf,strength,2,,6,
7,1,0,knows,mval,5,,,2003-01-01T12:00:00.000Z
7,1,0,knows,firstMetIn,1,GA,,
7,1,0,knows,since,5,,,2000-01-01T12:00:00.000Z
17,1,0,friendOf,strength,2,,7,
9,1,3,knows,mval,6,N,,
9,1,3,knows,firstMetIn,1,SC,,
9,1,3,knows,since,5,,,2005-01-01T12:00:00.000Z
10,2,0,knows,mval,1,N,,
10,2,0,knows,firstMetIn,1,TX,,
10,2,0,knows,since,5,,,1997-01-01T12:00:00.000Z
12,2,3,knows,mval,3,,342.5,
12,2,3,knows,firstMetIn,1,TX,,
12,2,3,knows,since,5,,,2011-01-01T12:00:00.000Z
19,2,3,friendOf,strength,2,,4,
14,3,1,knows,mval,1,a,,
14,3,1,knows,firstMetIn,1,CA,,
14,3,1,knows,since,5,,,2010-01-01T12:00:00.000Z
15,3,2,knows,mval,1,z,,
15,3,2,knows,firstMetIn,1,CA,,
15,3,2,knows,since,5,,,2004-01-01T12:00:00.000Z
5,0,2,knows,mval,2,,23,
5,0,2,knows,firstMetIn,1,OH,,
5,0,2,knows,since,5,,,2002-01-01T12:00:00.000Z
6,0,3,knows,mval,3,,159.7,
6,0,3,knows,firstMetIn,1,IN,,
6,0,3,knows,since,5,,,1994-01-01T12:00:00.000Z
8,1,2,knows,mval,6,Y,,
8,1,2,knows,firstMetIn,1,FL,,
8,1,2,knows,since,5,,,1999-01-01T12:00:00.000Z
18,1,3,friendOf,strength,2,,5,
11,2,1,knows,mval,2,,1001,
11,2,1,knows,firstMetIn,1,OK,,
11,2,1,knows,since,5,,,2003-01-01T12:00:00.000Z
13,3,0,knows,mval,5,,,2001-01-01T12:00:00.000Z
13,3,0,knows,firstMetIn,1,CA,,
13,3,0,knows,since,5,,,2006-01-01T12:00:00.000Z
20,3,1,friendOf,strength,2,,3,
4.8.4.1 Basic Query Execution

Two main Java Interfaces, PgqlStatement and PgqlResultSet, are used for PGQL execution. This topic includes several examples of basic query execution.

Example 4-27 GraphLoaderExample.java

GraphLoaderExample.java loads some Oracle property graph data that will be used in subsequent examples in this topic.

import oracle.pg.rdbms.Oracle; 
import oracle.pg.rdbms.OraclePropertyGraph; 
import oracle.pg.rdbms.OraclePropertyGraphDataLoader;

/**
 * This example shows how to create an Oracle Property Graph 
 * and load data into it from vertex and edge flat files.
 */
public class GraphLoaderExample
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];
    String vertexFile         = args[idx++];
    String edgeFile           = args[idx++];

    Oracle oracle = null;
    OraclePropertyGraph opg = null;

    try {
      // Create a connection to Oracle
      oracle = new Oracle("jdbc:oracle:thin:@"+host+":"+port +":"+sid, user, password);

      // Create a property graph
      opg = OraclePropertyGraph.getInstance(oracle, graph);

      // Clear any existing data
      opg.clearRepository();

      // Load data from opv and ope files
      OraclePropertyGraphDataLoader opgLoader = OraclePropertyGraphDataLoader.getInstance();
      opgLoader.loadData(opg, vertexFile, edgeFile, 1);

      System.out.println("Vertices loaded:" + opg.countVertices());
      System.out.println("Edges loaded:" + opg.countEdges());

    }
    finally {
      // close the property graph
      if (opg != null) {
        opg.close();
      }
      // close oracle
      if (oracle != null) {
        oracle.dispose();
      }
    }
  }
}

GraphLoaderExample.java gives the following output for test_graph.

Vertices loaded:4
Edges loaded:17

Example 4-28 PgqlExample1.java

PgqlExample1.java executes a PGQL query and prints the query result. PgqlConnection is used to obtain a PgqlStatement. Next, it calls the executeQuery method of PgqlStatement, which returns a PgqlResultSet object. PgqlResultSet provides a print() method, which shows results in a tabular mode.

The PgqlResultSet and PgqlStatement objects should be closed after consuming the query result.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to execute a basic PGQL query against disk-resident 
 * PG data stored in Oracle Database and iterate through the result.
 */
public class PgqlExample1
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Execute query to get a PgqlResultSet object
      String pgql = 
        "SELECT v.\"fname\" AS fname, v.\"lname\" AS lname, v.\"mval\" AS mval "+
        "FROM MATCH (v)";
      rs = ps.executeQuery(pgql, /* query string */ 
                           ""    /* options */);

      // Print the results
      rs.print();
    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

PgqlExample1.java gives the following output for test_graph (which can be loaded using GraphLoaderExample.java code).

+---------------------------------------+
| FNAME | LNAME | MVAL                  |
+---------------------------------------+
| Susan | Blue  | false                 |
| Bill  | Brown | y                     |
| Ray   | Green | 1985-01-01 04:00:00.0 |
| John  | Black | 27                    |
+---------------------------------------+

Example 4-29 PgqlExample2.java

PgqlExample2.java shows a PGQL query with a temporal filter on an edge property.

  • PgqlResultSet provides an interface for consuming the query result that is very similar to the java.sql.ResultSet interface.
  • A next() method allows moving through the query result, and a close() method allows releasing resources after the application is fiished reading the query result.
  • In addition, PgqlResultSet provides getters for String, Integer, Long, Float, Double, Boolean, LocalDateTime, and OffsetDateTime, and it provides a generic getObject() method for values of any type.
import java.sql.Connection;

import java.text.SimpleDateFormat;

import java.util.Date;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.pgql.lang.ResultSet;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to execute a PGQL query with a temporal edge 
 * property filter against disk-resident PG data stored in Oracle Database 
 * and iterate through the result.
 */
public class PgqlExample2
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;
    ResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Create a Pgql connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Execute query to get a ResultSet object
      String pgql = 
        "SELECT v.\"fname\" AS n1, v2.\"fname\" AS n2, e.\"firstMetIn\" AS loc "+
        "FROM MATCH (v)-[e:\"knows\"]->(v2) "+
        "WHERE e.\"since\" > TIMESTAMP '2000-01-01 00:00:00.00+00:00'";
      rs = ps.executeQuery(pgql, "");

      // Print results
      printResults(rs);
    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }

  /**
   * Prints a PGQL ResultSet
   */
  static void printResults(ResultSet rs) throws Exception
  {
    StringBuffer buff = new StringBuffer("");
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSXXX");
    while (rs.next()) {
      buff.append("[");
      for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
        // use generic getObject to handle all types
        Object mval = rs.getObject(i);
        String mStr = "";
        if (mval instanceof java.lang.String) {
          mStr = "STRING: "+mval.toString();
        }
        else if (mval instanceof java.lang.Integer) {
          mStr = "INTEGER: "+mval.toString();
        }
        else if (mval instanceof java.lang.Long) {
          mStr = "LONG: "+mval.toString();
        }
        else if (mval instanceof java.lang.Float) {
          mStr = "FLOAT: "+mval.toString();
        }
        else if (mval instanceof java.lang.Double) {
          mStr = "DOUBLE: "+mval.toString();
        }
        else if (mval instanceof java.sql.Timestamp) {
          mStr = "DATE: "+sdf.format((Date)mval);
        }
        else if (mval instanceof java.lang.Boolean) {
          mStr = "BOOLEAN: "+mval.toString();
        }
        if (i > 1) {
          buff.append(",\t");
        }
        buff.append(mStr);
      }
      buff.append("]\n");
    }
    System.out.println(buff.toString());
  }
}

PgqlExample2.java gives the following output for test_graph (which can be loaded using GraphLoaderExample.java code).

[STRING: Susan, STRING: Bill,   STRING: CA]
[STRING: Susan, STRING: John,   STRING: CA]
[STRING: Susan, STRING: Ray,    STRING: CA]
[STRING: Bill,  STRING: Ray,    STRING: OH]
[STRING: Ray,   STRING: John,   STRING: OK]
[STRING: Ray,   STRING: Susan,  STRING: TX]
[STRING: John,  STRING: Susan,  STRING: SC]
[STRING: John,  STRING: Bill,   STRING: GA]

Example 4-30 PgqlExample3.java

PgqlExample3.java shows a PGQL query with grouping and aggregation.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to execute a PGQL query with aggregation 
 * against disk-resident PG data stored in Oracle Database and iterate 
 * through the result.
 */
public class PgqlExample3
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;
    PgqlResultSet rs = null;

    try {
      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Create a Pgql connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Execute query to get a ResultSet object
      String pgql = 
        "SELECT v.\"fname\" AS \"fname\", COUNT(v2) AS \"friendCnt\" "+
        "FROM MATCH (v)-[e:\"friendOf\"]->(v2) "+
        "GROUP BY v "+
        "ORDER BY \"friendCnt\" DESC";
      rs = ps.executeQuery(pgql, "");

      // Print results
      rs.print();
    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

PgqlExample3.java gives the following output for test_graph (which can be loaded using GraphLoaderExample.java code).

+-------------------+
| fname | friendCnt |
+-------------------+
| John  | 2         |
| Bill  | 1         |
| Ray   | 1         |
| Susan | 1         |
+-------------------+

Example 4-31 PgqlExample4.java

PgqlExample4.java shows a PGQL path query.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to execute a path query in PGQL against 
 * disk-resident PG data stored in Oracle Database and iterate 
 * through the result.
 */
public class PgqlExample4
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Create a Pgql connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

  // Execute query to get a ResultSet object
      String pgql = 
        "PATH fof AS ()-[:\"friendOf\"|\"knows\"]->() "+
        "SELECT v2.\"fname\" AS friend "+
        "FROM MATCH (v)-/:fof*/->(v2) "+
        "WHERE v.\"fname\" = 'John' AND v != v2";
      rs = ps.executeQuery(pgql, "");

      // Print results
      rs.print();
    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

PgqlExample4.java gives the following output for test_graph(which can be loaded using GraphLoaderExample.java code).

+--------+
| FRIEND |
+--------+
| Susan  |
| Bill   |
| Ray    |
+--------+
4.8.4.2 Security Techniques for PGQL Queries

Programs executing dynamic queries might be subject to injection attacks that could compromise integrity and functioning of the applications.

This topic presents some techniques that can be used to prevent injection attacks when building PGQL queries using string concatenation.

4.8.4.2.1 Using Bind Variables in PGQL Queries

Bind variables can be used in PGQL queries for better performance and increased security. Constant scalar values in PGQL queries can be replaced with bind variables. Bind variables are denoted by a '?' (question mark). Consider the following two queries that select people who are older than a constant age value.

// people older than 30
SELECT v.fname AS fname, v.lname AS lname, v.age AS age
FROM MATCH (v)
WHERE v.age > 30

// people older than 40
SELECT v.fname AS fname, v.lname AS lname, v.age AS age
FROM MATCH (v) 
WHERE v.age > 40

The SQL translations for these queries would use the constants 30 and 40 in a similar way for the age filter. The database would perform a hard parse for each of these queries. This hard parse time can often exceed the execution time for simple queries.

You could replace the constant in each query with a bind variable as follows.

SELECT v.fname AS fname, v.lname AS lname, v.age AS age
FROM MATCH (v)
WHERE v.age > ?

This will allow the SQL engine to create a generic cursor for this query, which can be reused for different age values. As a result, a hard parse is no longer required to execute this query for different age values, and the parse time for each query will be drastically reduced.

In addition, applications that use bind variables in PGQL queries are less vulnerable to injection attacks than those that use string concatenation to embed constant values in PGQL queries.

See also Oracle Database SQL Tuning Guide for more information on cursor sharing and bind variables.

The PgqlPreparedStatement interface can be used to execute queries with bind variables as shown in PgqlExample5.java. PgqlPreparedStatement provides several set methods for different value types that can be used to set values for query execution.

There are a few limitations with bind variables in PGQL. Bind variables can only be used for constant property values. That is, vertices and edges cannot be replaced with bind variables. Also, once a particular bind variable has been set to a type, it cannot be set to a different type. For example, if setInt(1, 30) is executed for an PgqlPreparedStatement, you cannot call setString(1, "abc") on that same PgqlPreparedStatement.

Example 4-32 PgqlExample5.java

PgqlExample5.java shows how to use bind variables with a PGQL query.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlPreparedStatement;
import oracle.pg.rdbms.pgql.PgqlResultSet;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to use bind variables with a PGQL query.
 */
public class PgqlExample5
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlPreparedStatement pps = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Create a Pgql connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Query string with a bind variable (denoted by ?)
      String pgql = 
        "SELECT v.\"fname\" AS fname, v.\"lname\" AS lname, v.\"age\" AS age "+
        "FROM MATCH (v) "+
        "WHERE v.\"age\" > ?";

      // Create a PgqlPreparedStatement
      pps = pgqlConn.prepareStatement(pgql);

      // Set filter value to 30
      pps.setInt(1, 30);

      // execute query
      rs = pps.executeQuery();

      // Print query results
      System.out.println("-- Values for v.\"age\" > 30 --");
      rs.print();
      // close result set
      rs.close();

      // set filter value to 40
      pps.setInt(1, 40);

      // execute query
      rs = pps.executeQuery();

      // Print query results
      System.out.println("-- Values for v.\"age\" > 40 --");
      rs.print();
      // close result set
      rs.close();
    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (pps != null) {
        pps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

PgqlExample5.java has the following output for test_graph (which can be loaded using GraphLoaderExample.java code).

-- Values for v.age > 30 --
+---------------------+
| fname | lname | age |
+---------------------+
| Susan | Blue  | 35  |
| Bill  | Brown | 40  |
| Ray   | Green | 41  |
+---------------------+
-- Values for v.age > 40 --
+---------------------+
| fname | lname | age |
+---------------------+
| Ray   | Green | 41  |
+---------------------+

Example 4-33 PgqlExample6.java

PgqlExample6.java shows a query with two bind variables: one String variable and one Timestamp variable.

import java.sql.Connection;
import java.sql.Timestamp;

import java.time.OffsetDateTime;
import java.time.ZoneOffset;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlPreparedStatement;
import oracle.pg.rdbms.pgql.PgqlResultSet;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to use multiple bind variables with a PGQL query.
 */
public class PgqlExample6
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlPreparedStatement pps = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Create a Pgql connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Query string with multiple bind variables
      String pgql = 
        "SELECT v1.\"fname\" AS fname1, v2.\"fname\" AS fname2 "+
        "FROM MATCH (v1)-[e:\"knows\"]->(v2) "+
        "WHERE e.\"since\" < ? AND e.\"firstMetIn\" = ?";

      // Create a PgqlPreparedStatement
      pps = pgqlConn.prepareStatement(pgql);

      // Set e.since < 2006-01-01T12:00:00.00Z
      Timestamp t = Timestamp.valueOf(OffsetDateTime.parse("2006-01-01T12:00:01.00Z").atZoneSameInstant(ZoneOffset.UTC).toLocalDateTime());
      pps.setTimestamp(1, t);
      // Set e.firstMetIn = 'CA'
      pps.setString(2, "CA");

      // execute query
      rs = pps.executeQuery();

      // Print query results
      System.out.println("-- Values for e.\"since\" <  2006-01-01T12:00:01.00Z AND e.\"firstMetIn\" = 'CA' --");
      rs.print();
      // close result set
      rs.close();

      // Set e.since < 2000-01-01T12:00:00.00Z
      t = Timestamp.valueOf(OffsetDateTime.parse("2000-01-01T12:00:00.00Z").atZoneSameInstant(ZoneOffset.UTC).toLocalDateTime());
      pps.setTimestamp(1, t);
      // Set e.firstMetIn = 'TX'
      pps.setString(2, "TX");

      // execute query
      rs = pps.executeQuery();

      // Print query results
      System.out.println("-- Values for e.\"since\" <  2000-01-01T12:00:00.00Z AND e.\"firstMetIn\" = 'TX' --");
      rs.print();
      // close result set
      rs.close();
    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (pps != null) {
        pps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

PgqlExample6.java gives the following output for test_graph (which can be loaded using GraphLoaderExample.java code).

-- Values for e."since" <  2006-01-01T12:00:01.00Z AND e."firstMetIn" = 'CA' --
+-----------------+
| FNAME1 | FNAME2 |
+-----------------+
| Susan  | Bill   |
| Susan  | Ray    |
+-----------------+
-- Values for e."since" <  2000-01-01T12:00:00.00Z AND e."firstMetIn" = 'TX' --
+-----------------+
| FNAME1 | FNAME2 |
+-----------------+
| Ray    | Bill   |
+-----------------+
4.8.4.2.2 Verifying PGQL Identifiers

For some parts of a PGQL query the parser does not allow use of bind variables. In such cases, the input can be verified using the printIdentifier method in package oracle.pgql.lang.ir.PgqlUtils.

Consider the following query execution that concatenates the graph against which the graph pattern will be matched:

stmt.executeQuery("SELECT n.name FROM MATCH (n) ON " + graphName, "");

In order to avoid injection, the identifier graphName should be verified as follows:

stmt.executeQuery("SELECT n.name FROM MATCH (n) ON " + PgqlUtils.printIdentifier(graphName), "");
4.8.4.3 Using a Text Index with PGQL Queries

PGQL queries executed against Oracle Database can use Oracle Text indexes created for vertex and edge properties. After creating a text index, you can use the CONTAINS operator to perform a full text search. CONTAINS has two arguments: a vertex or edge property, and an Oracle Text search string. Any valid Oracle Text search string can be used, including advanced features such as wildcards, stemming, and soundex.

Example 4-34 PgqlExample7.java

PgqlExample7.java shows how to execute a CONTAINS query.

import java.sql.CallableStatement;
import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to use an Oracle Text index with a PGQL query.
 */
public class PgqlExample7
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Create text index with SQL API
      CallableStatement cs = null;
      // text index on vertices
      cs = conn.prepareCall(
        "begin opg_apis.create_vertices_text_idx(:1,:2); end;"
      );
      cs.setString(1,user);
      cs.setString(2,graph);
      cs.execute();
      cs.close();
      // text index on edges
      cs = conn.prepareCall(
        "begin opg_apis.create_edges_text_idx(:1,:2); end;"
      );
      cs.setString(1,user);
      cs.setString(2,graph);
      cs.execute();
      cs.close(); 

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Query using CONTAINS text search operator on vertex property
      // Find all vertices with an lname property value that starts with 'B'
      String pgql = 
        "SELECT v.\"fname\" AS fname, v.\"lname\" AS lname "+
        "FROM MATCH (v) "+
        "WHERE CONTAINS(v.\"lname\",'B%')";

      // execute query
      rs = ps.executeQuery(pgql, "");

      // print results
      System.out.println("-- Vertex Property Query --");
      rs.print();

      // close result set
      rs.close();

      // Query using CONTAINS text search operator on edge property
      // Find all knows edges with a firstMetIn property value that ends with 'A'
      pgql = 
        "SELECT v1.\"fname\" AS fname1, v2.\"fname\" AS fname2, e.\"firstMetIn\" AS loc "+
        "FROM MATCH (v1)-[e:\"knows\"]->(v2) "+
        "WHERE CONTAINS(e.\"firstMetIn\",'%A')";

      // execute query
      rs = ps.executeQuery(pgql, "");

      // print results
      System.out.println("-- Edge Property Query --");
      rs.print();

    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

PgqlExample7.java has the following output for test_graph (which can be loaded using GraphLoaderExample.java code).

-- Vertex Property Query --
+---------------+
| FNAME | LNAME |
+---------------+
| Susan | Blue  |
| Bill  | Brown |
| John  | Black |
+---------------+
-- Edge Property Query --
+-----------------------+
| FNAME1 | FNAME1 | LOC |
+-----------------------+
| Susan  | Bill   | CA  |
| John   | Bill   | GA  |
| Susan  | John   | CA  |
| Susan  | Ray    | CA  |
+-----------------------+
4.8.4.4 Obtaining the SQL Translation for a PGQL Query

You can obtain the SQL translation for a PGQL query through methods in PgqlStatement and PgqlPreparedStatement. The raw SQL for a PGQL query can be useful for several reasons:

  • You can execute the SQL directly against the database with other SQL-based tools or interfaces (for example, SQL*Plus or SQL Developer).

  • You can customize and tune the generated SQL to optimize performance or to satisfy a particular requirement of your application.

  • You can build a larger SQL query that joins a PGQL subquery with other data stored in Oracle Database (such as relational tables, spatial data, and JSON data).

Example 4-35 PgqlExample8.java

PgqlExample8.java shows how to obtain the raw SQL translation for a PGQL query. The translateQuery method of PgqlStatement returns an PgqlSqlQueryTrans object that contains information about return columns from the query and the SQL translation itself.

The translated SQL returns different columns depending on the type of "logical" object or value projected from the PGQL query. A vertex or edge projected in PGQL has two corresponding columns projected in the translated SQL:

  • $IT : id type – NVARCHAR(1): 'V' for vertex or 'E' for edge

  • $ID : vertex or edge identifier – NUMBER: same content as VID or EID columns in VT$ and GE$ tables

A property value or constant scalar value projected in PGQL has four corresponding columns projected in the translated SQL:

  • $T : value type – NUMBER: same content as T column in VT$ and GE$ tables

  • $V: value – NVARCHAR2(15000): same content as V column in VT$ and GE$ tables

  • $VN: number value – NUMBER: same content as VN column in VT$ and GE$ tables

  • $VT: temporal value – TIMESTAMP WITH TIME ZONE: same content as VT column in VT$ and GE$ tables

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlColumnDescriptor;
import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlStatement;
import oracle.pg.rdbms.pgql.PgqlSqlQueryTrans;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to obtain the SQL translation for a PGQL query.
 */
public class PgqlExample8
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Create a Pgql connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // PGQL query to be translated
      String pgql = 
        "SELECT v1, v1.\"fname\" AS fname1, e, e.\"since\" AS since "+
        "FROM MATCH (v1)-[e:\"knows\"]->(v2)";

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Get the SQL translation
      PgqlSqlQueryTrans sqlTrans = ps.translateQuery(pgql,"");

      // Get the return column descriptions
      PgqlColumnDescriptor[] cols = sqlTrans.getReturnTypes();

      // Print column descriptions
      System.out.println("-- Return Columns -----------------------");
      printReturnCols(cols);

      // Print SQL translation
      System.out.println("-- SQL Translation ----------------------");
      System.out.println(sqlTrans.getSqlTranslation());
    }
    finally {
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }

  /**
   * Prints return columns for a SQL translation
   */
  static void printReturnCols(PgqlColumnDescriptor[] cols) throws Exception
  {
    StringBuffer buff = new StringBuffer("");

    for (int i = 0; i < cols.length; i++) {

      String colName = cols[i].getColName();
      PgqlColumnDescriptor.Type colType = cols[i].getColType();
      int offset = cols[i].getSqlOffset();

      String readableType = "";
      switch(colType) {
        case VERTEX:
          readableType = "VERTEX";
          break;
        case EDGE:
          readableType = "EDGE";
          break;
        case VALUE:
          readableType = "VALUE";
          break;
      }

      buff.append("colName=["+colName+"] colType=["+readableType+"] offset=["+offset+"]\n");
    }
    System.out.println(buff.toString());
  }
}

PgqlExample8.java has the following output for test_graph (which can be loaded using GraphLoaderExample.java code).

-- Return Columns -----------------------
colName=[v1] colType=[VERTEX] offset=[1]
colName=[fname1] colType=[VALUE] offset=[3]
colName=[e] colType=[EDGE] offset=[7]
colName=[since] colType=[VALUE] offset=[9]
-- SQL Translation ----------------------
SELECT n'V' AS "V1$IT",
T0$0.SVID AS "V1$ID",
T0$1.T AS "FNAME1$T",
T0$1.V AS "FNAME1$V",
T0$1.VN AS "FNAME1$VN",
T0$1.VT AS "FNAME1$VT",
n'E' AS "E$IT",
T0$0.EID AS "E$ID",
T0$0.T AS "SINCE$T",
T0$0.V AS "SINCE$V",
T0$0.VN AS "SINCE$VN",
T0$0.VT AS "SINCE$VT"
FROM ( SELECT L.EID, L.SVID, L.DVID, L.EL, R.K, R.T, R.V, R.VN, R.VT
  FROM "SCOTT".TEST_GRAPHGT$ L,
       (SELECT * FROM "SCOTT".TEST_GRAPHGE$ WHERE K=n'since' ) R
  WHERE L.EID = R.EID(+)
) T0$0,
( SELECT L.VID, L.VL, R.K, R.T, R.V, R.VN, R.VT
  FROM "SCOTT".TEST_GRAPHVD$ L,
       (SELECT * FROM "SCOTT".TEST_GRAPHVT$ WHERE K=n'fname' ) R
  WHERE L.VID = R.VID(+)
) T0$1
WHERE T0$0.SVID=T0$1.VID AND
(T0$0.EL = n'knows' AND T0$0.EL IS NOT NULL)

Example 4-36 PgqlExample9.java

You can also obtain the SQL translation for PGQL queries with bind variables. In this case, the corresponding SQL translation will also contain bind variables. The PgqlSqlQueryTrans interface has a getSqlBvList method that returns an ordered List of Java Objects that should be bound to the SQL query (the first Object on the list should be set at position 1, and the second should be set at position 2, and so on).

PgqlExample9.java shows how to get and execute the SQL for a PGQL query with bind variables.

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;

import java.util.List;

import oracle.pg.rdbms.pgql.PgqlColumnDescriptor;
import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlPreparedStatement;
import oracle.pg.rdbms.pgql.PgqlSqlQueryTrans;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to obtain and execute the SQL translation for a 
 * PGQL query that uses bind variables.
 */
public class PgqlExample9
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlPreparedStatement pgqlPs = null;

    PreparedStatement sqlPs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Create a Pgql connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Execute query to get a ResultSet object
      String pgql = 
        "SELECT v1, v1.\"fname\" AS fname1, v1.\"age\" AS age, ? as constVal "+
        "FROM MATCH (v1) "+
        "WHERE  v1.\"fname\" = ? OR v1.\"age\" < ?";


      // Create a PgqlStatement
      pgqlPs = pgqlConn.prepareStatement(pgql);

      // set bind values
      pgqlPs.setDouble(1, 2.05d);
      pgqlPs.setString(2, "Bill");
      pgqlPs.setInt(3, 35);

      // Get the SQL translation
      PgqlSqlQueryTrans sqlTrans = pgqlPs.translateQuery("");

      // Get the SQL String
      String sqlStr = sqlTrans.getSqlTranslation();

      // Get the return column descriptions
      PgqlColumnDescriptor[] cols = sqlTrans.getReturnTypes();

      // Get the bind values
      List<Object> bindVals = sqlTrans.getSqlBvList();

      // Print column descriptions
      System.out.println("-- Return Columns -----------------------");
      printReturnCols(cols);

      // Print SQL translation
      System.out.println("-- SQL Translation ----------------------");
      System.out.println(sqlStr);

      // Print Bind Values
      System.out.println("\n-- Bind Values --------------------------");
      for (Object obj : bindVals) {
        System.out.println(obj.toString());
      }

      // Execute Query
      // Get PreparedStatement
      sqlPs = conn.prepareStatement("SELECT COUNT(*) FROM ("+sqlStr+")");
      // Set bind values and execute the PreparedStatement
      executePs(sqlPs, bindVals);

      // Set new bind values in the PGQL PreparedStatement 
      pgqlPs.setDouble(1, 3.02d);
      pgqlPs.setString(2, "Ray");
      pgqlPs.setInt(3, 30);

      // Print Bind Values
      bindVals = sqlTrans.getSqlBvList();
      System.out.println("\n-- Bind Values --------------------------");
      for (Object obj : bindVals) {
        System.out.println(obj.toString());
      }

      // Execute the PreparedStatement with new bind values
      executePs(sqlPs, bindVals);
    }
    finally {
      // close the SQL statement
      if (sqlPs != null) {
        sqlPs.close();
      }
      // close the statement
      if (pgqlPs != null) {
        pgqlPs.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }

  /**
   * Executes a SQL PreparedStatement with the input bind values
   */
  static void executePs(PreparedStatement ps, List<Object> bindVals) throws Exception
  {
    ResultSet rs = null;
    try {
      // Set bind values
      for (int idx = 0; idx < bindVals.size(); idx++) {
        Object o = bindVals.get(idx);
        // String
        if (o instanceof java.lang.String) {
          ps.setNString(idx + 1, (String)o);
        }
        // Int
        else if (o instanceof java.lang.Integer) {
          ps.setInt(idx + 1, ((Integer)o).intValue());
        }
        // Long
        else if (o instanceof java.lang.Long) {
          ps.setLong(idx + 1, ((Long)o).longValue());
        }
        // Float
        else if (o instanceof java.lang.Float) {
          ps.setFloat(idx + 1, ((Float)o).floatValue());
        }
        // Double
        else if (o instanceof java.lang.Double) {
          ps.setDouble(idx + 1, ((Double)o).doubleValue());
        }
       // Timestamp
       else if (o instanceof java.sql.Timestamp) {
         ps.setTimestamp(idx + 1, (Timestamp)o);
       }
       else {
         ps.setString(idx + 1, bindVals.get(idx).toString());
       }
     }

      // Execute query
      rs = ps.executeQuery();
      if (rs.next()) {
        System.out.println("\n-- Execute Query: Result has "+rs.getInt(1)+" rows --");
      }
    }
    finally {
      // close the SQL ResultSet
      if (rs != null) {
        rs.close();
      }
    }
  }

  /**
   * Prints return columns for a SQL translation
   */
  static void printReturnCols(PgqlColumnDescriptor[] cols) throws Exception
  {
    StringBuffer buff = new StringBuffer("");

    for (int i = 0; i < cols.length; i++) {

      String colName = cols[i].getColName();
      PgqlColumnDescriptor.Type colType = cols[i].getColType();
      int offset = cols[i].getSqlOffset();

      String readableType = "";
      switch(colType) {
        case VERTEX:
          readableType = "VERTEX";
          break;
        case EDGE:
          readableType = "EDGE";
          break;
        case VALUE:
          readableType = "VALUE";
          break;
      }

      buff.append("colName=["+colName+"] colType=["+readableType+"] offset=["+offset+"]\n");
    }
    System.out.println(buff.toString());
  }
}

PgqlExample9.java has the following output for test_graph (which can be loaded using GraphLoaderExample.java code).

–-- Return Columns -----------------------
colName=[v1] colType=[VERTEX] offset=[1]
colName=[fname1] colType=[VALUE] offset=[3]
colName=[age] colType=[VALUE] offset=[7]
colName=[constVal] colType=[VALUE] offset=[11]
-- SQL Translation ----------------------
SELECT n'V' AS "V1$IT",
T0$0.VID AS "V1$ID",
T0$0.T AS "FNAME1$T",
T0$0.V AS "FNAME1$V",
T0$0.VN AS "FNAME1$VN",
T0$0.VT AS "FNAME1$VT",
T0$1.T AS "AGE$T",
T0$1.V AS "AGE$V",
T0$1.VN AS "AGE$VN",
T0$1.VT AS "AGE$VT",
4 AS "CONSTVAL$T",
to_nchar(?,'TM9','NLS_Numeric_Characters=''.,''') AS "CONSTVAL$V",
? AS "CONSTVAL$VN",
to_timestamp_tz(null) AS "CONSTVAL$VT"
FROM ( SELECT L.VID, L.VL, R.K, R.T, R.V, R.VN, R.VT
  FROM "SCOTT".TEST_GRAPHVD$ L,
       (SELECT * FROM "SCOTT".TEST_GRAPHVT$ WHERE K=n'fname' ) R
  WHERE L.VID = R.VID(+)
) T0$0,
( SELECT L.VID, L.VL, R.K, R.T, R.V, R.VN, R.VT
  FROM "SCOTT".TEST_GRAPHVD$ L,
       (SELECT * FROM "SCOTT".TEST_GRAPHVT$ WHERE K=n'age' ) R
  WHERE L.VID = R.VID(+)
) T0$1
WHERE T0$0.VID=T0$1.VID AND
((T0$0.T = 1 AND T0$0.V = ?) OR T0$1.VN < ?)

-- Bind Values --------------------------
2.05
2.05
Bill
35
-- Execute Query: Result has 2 rows --

-- Bind Values --------------------------
3.02
3.02
Ray
30
-- Execute Query: Result has 1 rows --
4.8.4.5 Additional Options for PGQL Translation and Execution

Several options are available to influence PGQL query translation and execution. The following are the main ways to set query options:

  • Through explicit arguments to executeQuery and translateQuery

  • Through flags in the options string argument of executeQuery and translateQuery

  • Through Java JVM arguments.

The following table summarizes the available query arguments for PGQL translation and execution.

Table 4-2 PGQL Translation and Execution Options

Option Default Explict Argument Options Flag JVM Argument

Degree of parallelism

0

parallel

none

none

Timeout

unlimited

timeout

none

none

Dynamic sampling

2

dynamicSampling

none

none

Maximum number of results

unlimited

maxResults

none

none

GT$ table usage

on

none

USE_GT_TAB=F

-Doracle.pg.rdbms.pgql.useGtTab=false

CONNECT BY usage

off

none

USE_RW=F

-Doracle.pg.rdbms.pgql.useRW=false

Distinct recursive WITH usage

off

none

USE_DIST_RW=T

-Doracle.pg.rdbms.pgql.useDistRW=true

Maximum path length

unlimited

none

MAX_PATH_LEN=n

-Doracle.pg.rdbms.pgql.maxPathLen=n

Set partial

false

none

EDGE_SET_PARTIAL=T

-Doracle.pg.rdbms.pgql.edgeSetPartial=true

Project null properties

true

none

PROJ_NULL_PROPS=F

-Doracle.pg.rdbms.pgql.projNullProps=false

VT$ VL column usage

on

none

USE_VL_COL=F

-Doracle.pg.rdbms.pgql.useVLCol=false

4.8.4.5.1 Query Options Controlled by Explicit Arguments

Some query options are controlled by explicit arguments to methods in the Java API.

  • The executeQuery method of PgqlStatement has explicit arguments for timeout in seconds, degree of parallelism, optimizer dynamic sampling, and maximum number of results.

  • The translateQuery method has explicit arguments for degree of parallelism, optimizer dynamic sampling, and maximum number of results. PgqlPreparedStatement also provides those same additional arguments for executeQuery and translateQuery.

Example 4-37 PgqlExample10.java

PgqlExample10.java shows PGQL query execution with additional options controlled by explicit arguments.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to execute a PGQL query with various options.
 */
public class PgqlExample10
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Execute query to get a ResultSet object
      String pgql = 
        "SELECT v1.\"fname\" AS fname1, v2.\"fname\" AS fname2 "+
        "FROM MATCH (v1)-[:\"friendOf\"]->(v2)";
      rs = ps.executeQuery(pgql /* query string */, 
                           100  /* timeout (sec): 0 is default and implies no timeout */,
                           2    /* parallel: 1 is default */,
                           6    /* dynamic sampling: 2 is default */,
                           50   /* max results: -1 is default and implies no limit */,
                           ""   /* options */);

      // Print query results
      rs.print();
    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

PgqlExample10.java gives the following output for test_graph (which can be loaded using GraphLoaderExample.java code).

+-----------------+
| FNAME1 | FNAME2 |
+-----------------+
| Ray    | Susan  |
| John   | Susan  |
| Bill   | John   |
| Susan  | John   |
| John   | Bill   |
+-----------------+
4.8.4.5.2 Using the GT$ Skeleton Table

The property graph relational schema defines a GT$ skeleton table that stores a single row for each edge in the graph, no matter how many properties an edge has. This skeleton table is populated by default so that PGQL query execution can take advantage of the GT$ table and avoid sorting operations on the GE$ table in many cases, which gives a significant performance improvement.

You can add "USE_GT_TAB=F" to the options argument of executeQuery and translateQuery or use -Doracle.pg.rdbms.pgql.useGtTab=false in the Java command line to turn off GT$ table usage.

Example 4-38 PgqlExample11.java

PgqlExample11.java shows a query that uses the GT$ skeleton table.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlSqlQueryTrans;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to avoid using the GT$ skeleton table for
 * PGQL query execution.
 */
public class PgqlExample11
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Execute query to get a ResultSet object
      String pgql = 
        "SELECT id(v1), id(v2) "+
        "FROM MATCH (v1)-[knows]->(v2)";

      // Get the SQL translation with GT table
      PgqlSqlQueryTrans sqlTrans = ps.translateQuery(pgql,"");

      // Print SQL translation
      System.out.println("-- SQL Translation with GT Table ----------------------");
      System.out.println(sqlTrans.getSqlTranslation());

      // Get the SQL translation without GT table
      sqlTrans = ps.translateQuery(pgql,"USE_GT_TAB=F");

      // Print SQL translation
      System.out.println("-- SQL Translation without GT Table -------------------------");
      System.out.println(sqlTrans.getSqlTranslation());

    }
    finally {
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

PgqlExample11.java gives the following output for test_graph (which can be loaded using GraphLoaderExample.java code).

-- SQL Translation with GT Table ----------------------
SELECT 7 AS "id(v1)$T",
to_nchar(T0$0.SVID,'TM9','NLS_Numeric_Characters=''.,''') AS "id(v1)$V",
T0$0.SVID AS "id(v1)$VN",
to_timestamp_tz(null) AS "id(v1)$VT",
7 AS "id(v2)$T",
to_nchar(T0$0.DVID,'TM9','NLS_Numeric_Characters=''.,''') AS "id(v2)$V",
T0$0.DVID AS "id(v2)$VN",
to_timestamp_tz(null) AS "id(v2)$VT"
FROM "SCOTT".TEST_GRAPHGT$ T0$0
-- SQL Translation without GT Table -------------------------
SELECT 7 AS "id(v1)$T",
to_nchar(T0$0.SVID,'TM9','NLS_Numeric_Characters=''.,''') AS "id(v1)$V",
T0$0.SVID AS "id(v1)$VN",
to_timestamp_tz(null) AS "id(v1)$VT",
7 AS "id(v2)$T",
to_nchar(T0$0.DVID,'TM9','NLS_Numeric_Characters=''.,''') AS "id(v2)$V",
T0$0.DVID AS "id(v2)$VN",
to_timestamp_tz(null) AS "id(v2)$VT"
FROM (SELECT DISTINCT EID, SVID, DVID,EL FROM "SCOTT".TEST_GRAPHGE$) T0$0
4.8.4.5.3 Path Query Options

A few options are available for executing path queries in PGQL. There are two basic evaluation methods available in Oracle SQL: CONNECT BY or recursive WITH clauses. Recursive WITH is the default evaluation method. In addition, you can further modify the recursive WITH evaluation method to include a DISTINCT modifier during the recursive step of query evaluation. Computing distinct vertices at each step helps prevent a combinatorial explosion in highly connected graphs. The DISTINCT modifier is not added by default because it requires a specific parameter setting in the database ("_recursive_with_control"=8).

You can also control the maximum length of paths searched. Path length in this case is defined as the number of repetitions allowed when evaluating the * and + operators. The default maximum length is unlimited.

Path evaluation options are summarized as follows.

  • CONNECT BY: To use CONNECT BY, specify 'USE_RW=F' in the options argument or specify -Doracle.pg.rdbms.pgql.useRW=false in the Java command line.

  • Distinct Modifier in Recursive WITH: To use the DISTINCT modifier in the recursive step, first set "_recursive_with_control"=8 in your database session, then specify 'USE_DIST_RW=T' in the options argument or specify -Doracle.pg.rdbms.pgql.useDistRW=true in the Java command line. You will encounter ORA-32486: unsupported operation in recursive branch of recursive WITH clause if "_recursive_with_control" has not been set to 8 in your session.

  • Path Length Restriction: To limit maximum number of repetitions when evaluating * and + to n, specify 'MAX_PATH_LEN=n' in the query options argument or specify -Doracle.pg.rdbms.pgql.maxPathLen=n in the Java command line.

Example 4-39 PgqlExample12.java

PgqlExample12.java shows path query translations under various options.

import java.sql.Connection;
import java.sql.Statement;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlSqlQueryTrans;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to use various options with PGQL path queries.
 */
public class PgqlExample12
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Set "_recursive_with_control"=8 to enable distinct optimization
      // optimization for recursive with
      Statement stmt = conn.createStatement();
      stmt.executeUpdate("alter session set \"_recursive_with_control\"=8");
      stmt.close();

      // Path Query to illustrate options      
      String pgql = 
        "PATH fof AS ()-[:\"friendOf\"]->() "+
        "SELECT id(v1), id(v2) "+
        "FROM MATCH (v1)-/:fof*/->(v2) "+
        "WHERE id(v1) = 2";


      // get SQL translation with defaults - Non-distinct Recursive WITH
      PgqlSqlQueryTrans sqlTrans = 
        ps.translateQuery(pgql /* query string */, 
                          2    /* parallel: default is 1 */,
                          2    /* dynamic sampling: default is 2 */,
                          -1   /* max results: -1 implies no limit */,
                          ""   /* options */);
      System.out.println("-- Default Path Translation --------------------");
      System.out.println(sqlTrans.getSqlTranslation()+"\n");

      // get SQL translation with DISTINCT reachability optimization
      sqlTrans = 
        ps.translateQuery(pgql /* query string */, 
                          2    /* parallel: default is 1 */,
                          2    /* dynamic sampling: default is 2 */,
                          -1   /* max results: -1 implies no limit */,
                          " USE_DIST_RW=T " /* options */);
      System.out.println("-- DISTINCT RW Path Translation --------------------");
      System.out.println(sqlTrans.getSqlTranslation()+"\n");

      // get SQL translation with CONNECT BY
      sqlTrans = 
        ps.translateQuery(pgql /* query string */, 
                          2    /* parallel: default is 1 */,
                          2    /* dynamic sampling: default is 2 */,
                          -1   /* max results: -1 implies no limit */,
                          " USE_RW=F " /* options */);
      System.out.println("-- CONNECT BY Path Translation --------------------");
      System.out.println(sqlTrans.getSqlTranslation()+"\n");
    }
    finally {
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

PgqlExample12.java gives the following output for test_graph (which can be loaded using GraphLoaderExample.java code).

-- Default Path Translation --------------------
SELECT /*+ parallel(2) */ * FROM(SELECT 7 AS "id(v1)$T",
to_nchar(T0$0.SVID,'TM9','NLS_Numeric_Characters=''.,''') AS "id(v1)$V",
T0$0.SVID AS "id(v1)$VN",
to_timestamp_tz(null) AS "id(v1)$VT",
7 AS "id(v2)$T",
to_nchar(T0$0.DVID,'TM9','NLS_Numeric_Characters=''.,''') AS "id(v2)$V",
T0$0.DVID AS "id(v2)$VN",
to_timestamp_tz(null) AS "id(v2)$VT"
FROM (/*Path[*/SELECT DISTINCT SVID, DVID
FROM (
SELECT 2 AS SVID, 2 AS DVID
FROM SYS.DUAL
WHERE EXISTS(
SELECT 1
FROM "SCOTT".TEST_GRAPHVT$
WHERE VID = 2)
UNION ALL
SELECT SVID,DVID FROM
(WITH RW (ROOT, DVID) AS
( SELECT ROOT, DVID FROM
(SELECT SVID ROOT, DVID
FROM (SELECT T0$0.SVID AS SVID,
T0$0.DVID AS DVID
FROM "SCOTT".TEST_GRAPHGT$ T0$0
WHERE T0$0.SVID = 2 AND
(T0$0.EL = n'friendOf' AND T0$0.EL IS NOT NULL))
) UNION ALL
SELECT RW.ROOT, R.DVID
FROM (SELECT T0$0.SVID AS SVID,
T0$0.DVID AS DVID
FROM "SCOTT".TEST_GRAPHGT$ T0$0
WHERE (T0$0.EL = n'friendOf' AND T0$0.EL IS NOT NULL)) R, RW
WHERE RW.DVID = R.SVID )
CYCLE DVID SET cycle_col TO 1 DEFAULT 0
SELECT ROOT SVID, DVID FROM RW))/*]Path*/) T0$0
WHERE T0$0.SVID = 2)

-- DISTINCT RW Path Translation --------------------
SELECT /*+ parallel(2) */ * FROM(SELECT 7 AS "id(v1)$T",
to_nchar(T0$0.SVID,'TM9','NLS_Numeric_Characters=''.,''') AS "id(v1)$V",
T0$0.SVID AS "id(v1)$VN",
to_timestamp_tz(null) AS "id(v1)$VT",
7 AS "id(v2)$T",
to_nchar(T0$0.DVID,'TM9','NLS_Numeric_Characters=''.,''') AS "id(v2)$V",
T0$0.DVID AS "id(v2)$VN",
to_timestamp_tz(null) AS "id(v2)$VT"
FROM (/*Path[*/SELECT DISTINCT SVID, DVID
FROM (
SELECT 2 AS SVID, 2 AS DVID
FROM SYS.DUAL
WHERE EXISTS(
SELECT 1
FROM "SCOTT".TEST_GRAPHVT$
WHERE VID = 2)
UNION ALL
SELECT SVID,DVID FROM
(WITH RW (ROOT, DVID) AS
( SELECT ROOT, DVID FROM
(SELECT SVID ROOT, DVID
FROM (SELECT T0$0.SVID AS SVID,
T0$0.DVID AS DVID
FROM "SCOTT".TEST_GRAPHGT$ T0$0
WHERE T0$0.SVID = 2 AND
(T0$0.EL = n'friendOf' AND T0$0.EL IS NOT NULL))
) UNION ALL
SELECT DISTINCT RW.ROOT, R.DVID
FROM (SELECT T0$0.SVID AS SVID,
T0$0.DVID AS DVID
FROM "SCOTT".TEST_GRAPHGT$ T0$0
WHERE (T0$0.EL = n'friendOf' AND T0$0.EL IS NOT NULL)) R, RW
WHERE RW.DVID = R.SVID )
CYCLE DVID SET cycle_col TO 1 DEFAULT 0
SELECT ROOT SVID, DVID FROM RW))/*]Path*/) T0$0
WHERE T0$0.SVID = 2)

-- CONNECT BY Path Translation --------------------
SELECT /*+ parallel(2) */ * FROM(SELECT 7 AS "id(v1)$T",
to_nchar(T0$0.SVID,'TM9','NLS_Numeric_Characters=''.,''') AS "id(v1)$V",
T0$0.SVID AS "id(v1)$VN",
to_timestamp_tz(null) AS "id(v1)$VT",
7 AS "id(v2)$T",
to_nchar(T0$0.DVID,'TM9','NLS_Numeric_Characters=''.,''') AS "id(v2)$V",
T0$0.DVID AS "id(v2)$VN",
to_timestamp_tz(null) AS "id(v2)$VT"
FROM (/*Path[*/SELECT DISTINCT SVID, DVID
FROM (
SELECT 2 AS SVID, 2 AS DVID
FROM SYS.DUAL
WHERE EXISTS(
SELECT 1
FROM "SCOTT".TEST_GRAPHVT$
WHERE VID = 2)
UNION ALL
SELECT SVID, DVID
FROM
(SELECT CONNECT_BY_ROOT T0$0.SVID AS SVID, T0$0.DVID AS DVID
FROM(
SELECT T0$0.SVID AS SVID,
T0$0.DVID AS DVID
FROM "SCOTT".TEST_GRAPHGT$ T0$0
WHERE (T0$0.EL = n'friendOf' AND T0$0.EL IS NOT NULL)) T0$0
START WITH T0$0.SVID = 2
CONNECT BY NOCYCLE PRIOR DVID = SVID))/*]Path*/) T0$0
WHERE T0$0.SVID = 2)

The query plan for the first query with the default recursive WITH strategy should look similar to the following.

-- default RW
---------------------------------------------------------------------------------------
| Id  | Operation                                         | Name                      |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                  |                           |
|   1 |  TEMP TABLE TRANSFORMATION                        |                           |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)         | SYS_TEMP_0FD9D6662_37AA44 |
|   3 |    UNION ALL (RECURSIVE WITH) BREADTH FIRST       |                           |
|   4 |     PX COORDINATOR                                |                           |
|   5 |      PX SEND QC (RANDOM)                          | :TQ20000                  |
|   6 |       LOAD AS SELECT (CURSOR DURATION MEMORY)     | SYS_TEMP_0FD9D6662_37AA44 |
|   7 |        PX PARTITION HASH ALL                      |                           |
|*  8 |     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED     | TEST_GRAPHGT$             |
|*  9 |      INDEX RANGE SCAN                             | TEST_GRAPHXSG$            |
|  10 |     PX COORDINATOR                                |                           |
|  11 |      PX SEND QC (RANDOM)                          | :TQ10000                  |
|  12 |       LOAD AS SELECT (CURSOR DURATION MEMORY)     | SYS_TEMP_0FD9D6662_37AA44 |
|  13 |        NESTED LOOPS                               |                           |
|  14 |     PX BLOCK ITERATOR                             |                           |
|* 15 |      TABLE ACCESS FULL                            | SYS_TEMP_0FD9D6662_37AA44 |
|  16 |     PARTITION HASH ALL                            |                           |
|* 17 |      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED    | TEST_GRAPHGT$             |
|* 18 |       INDEX RANGE SCAN                            | TEST_GRAPHXSG$            |
|  19 |   PX COORDINATOR                                  |                           |
|  20 |    PX SEND QC (RANDOM)                            | :TQ30001                  |
|  21 |     VIEW                                          |                           |
|  22 |      HASH UNIQUE                                  |                           |
|  23 |       PX RECEIVE                                  |                           |
|  24 |        PX SEND HASH                               | :TQ30000                  |
|  25 |     HASH UNIQUE                                   |                           |
|  26 |      VIEW                                         |                           |
|  27 |       UNION-ALL                                   |                           |
|  28 |        PX SELECTOR                                |                           |
|* 29 |         FILTER                                    |                           |
|  30 |          FAST DUAL                                |                           |
|  31 |          PARTITION HASH SINGLE                    |                           |
|* 32 |           INDEX SKIP SCAN                         | TEST_GRAPHXQV$            |
|  33 |        VIEW                                       |                           |
|* 34 |         VIEW                                      |                           |
|  35 |          PX BLOCK ITERATOR                        |                           |
|  36 |           TABLE ACCESS FULL                       | SYS_TEMP_0FD9D6662_37AA44 |
---------------------------------------------------------------------------------------

The query plan for the second query that adds a DISTINCT modifier in the recursive step should look similar to the following.

--------------------------------------------------------------------------------------------
| Id  | Operation                                              | Name                      |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                       |                           |
|   1 |  TEMP TABLE TRANSFORMATION                             |                           |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)              | SYS_TEMP_0FD9D6669_37AA44 |
|   3 |    UNION ALL (RECURSIVE WITH) BREADTH FIRST            |                           |
|   4 |     PX COORDINATOR                                     |                           |
|   5 |      PX SEND QC (RANDOM)                               | :TQ20000                  |
|   6 |       LOAD AS SELECT (CURSOR DURATION MEMORY)          | SYS_TEMP_0FD9D6669_37AA44 |
|   7 |        PX PARTITION HASH ALL                           |                           |
|*  8 |     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED          | TEST_GRAPHGT$             |
|*  9 |      INDEX RANGE SCAN                                  | TEST_GRAPHXSG$            |
|  10 |     PX COORDINATOR                                     |                           |
|  11 |      PX SEND QC (RANDOM)                               | :TQ10001                  |
|  12 |       LOAD AS SELECT (CURSOR DURATION MEMORY)          | SYS_TEMP_0FD9D6669_37AA44 |
|  13 |        SORT GROUP BY                                   |                           |
|  14 |     PX RECEIVE                                         |                           |
|  15 |      PX SEND HASH                                      | :TQ10000                  |
|  16 |       SORT GROUP BY                                    |                           |
|  17 |        NESTED LOOPS                                    |                           |
|  18 |         PX BLOCK ITERATOR                              |                           |
|* 19 |          TABLE ACCESS FULL                             | SYS_TEMP_0FD9D6669_37AA44 |
|  20 |         PARTITION HASH ALL                             |                           |
|* 21 |          TABLE ACCESS BY LOCAL INDEX ROWID BATCHED     | TEST_GRAPHGT$             |
|* 22 |           INDEX RANGE SCAN                             | TEST_GRAPHXSG$            |
|  23 |   PX COORDINATOR                                       |                           |
|  24 |    PX SEND QC (RANDOM)                                 | :TQ30001                  |
|  25 |     VIEW                                               |                           |
|  26 |      HASH UNIQUE                                       |                           |
|  27 |       PX RECEIVE                                       |                           |
|  28 |        PX SEND HASH                                    | :TQ30000                  |
|  29 |     HASH UNIQUE                                        |                           |
|  30 |      VIEW                                              |                           |
|  31 |       UNION-ALL                                        |                           |
|  32 |        PX SELECTOR                                     |                           |
|* 33 |         FILTER                                         |                           |
|  34 |          FAST DUAL                                     |                           |
|  35 |          PARTITION HASH SINGLE                         |                           |
|* 36 |           INDEX SKIP SCAN                              | TEST_GRAPHXQV$            |
|  37 |        VIEW                                            |                           |
|* 38 |         VIEW                                           |                           |
|  39 |          PX BLOCK ITERATOR                             |                           |
|  40 |           TABLE ACCESS FULL                            | SYS_TEMP_0FD9D6669_37AA44 |
--------------------------------------------------------------------------------------------

The query plan for the third query that uses CONNECTY BY should look similar to the following.

-----------------------------------------------------------------------------
| Id  | Operation                                      | Name               |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                    |
|   1 |  VIEW                                          |                    |
|   2 |   HASH UNIQUE                                  |                    |
|   3 |    VIEW                                        |                    |
|   4 |     UNION-ALL                                  |                    |
|*  5 |      FILTER                                    |                    |
|   6 |       FAST DUAL                                |                    |
|   7 |       PARTITION HASH SINGLE                    |                    |
|*  8 |        INDEX SKIP SCAN                         | TEST_GRAPHXQV$     |
|*  9 |      VIEW                                      |                    |
|* 10 |       CONNECT BY WITH FILTERING                |                    |
|  11 |        PX COORDINATOR                          |                    |
|  12 |     PX SEND QC (RANDOM)                        | :TQ10000           |
|  13 |      PX PARTITION HASH ALL                     |                    |
|* 14 |       TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TEST_GRAPHGT$      |
|* 15 |        INDEX RANGE SCAN                        | TEST_GRAPHXSG$     |
|  16 |        NESTED LOOPS                            |                    |
|  17 |     CONNECT BY PUMP                            |                    |
|  18 |     PARTITION HASH ALL                         |                    |
|* 19 |      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | TEST_GRAPHGT$      |
|* 20 |       INDEX RANGE SCAN                         | TEST_GRAPHXSG$     |
-----------------------------------------------------------------------------

Example 4-40 PgqlExample13.java

PgqlExample13.java shows how to set length restrictions during path query evaluation.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to use the maximum path length option for 
 * PGQL path queries.
 */
public class PgqlExample13
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Path Query to illustrate options
      String pgql = 
        "PATH fof AS ()-[:\"friendOf\"]->() "+
        "SELECT v1.\"fname\" AS fname1, v2.\"fname\" AS fname2 "+
        "FROM MATCH (v1)-/:fof*/->(v2) "+
        "WHERE v1.\"fname\" = 'Ray'";


      // execute query for 1-hop
      rs = ps.executeQuery(pgql, " MAX_PATH_LEN=1 ");

      // print results
      System.out.println("-- Results for 1-hop ----------------");
      rs.print();

      // close result set
      rs.close();

      // execute query for 2-hop
      rs = ps.executeQuery(pgql, " MAX_PATH_LEN=2 ");

      // print results
      System.out.println("-- Results for 2-hop ----------------");
      rs.print();

      // close result set
      rs.close();

      // execute query for 3-hop
      rs = ps.executeQuery(pgql, " MAX_PATH_LEN=3 ");

      // print results
      System.out.println("-- Results for 3-hop ----------------");
      rs.print();

      // close result set
      rs.close();

    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

PgqlExample13.java has the following output for test_graph (which can be loaded using GraphLoaderExample.java code).

-- Results for 1-hop ----------------
+-----------------+
| FNAME1 | FNAME2 |
+-----------------+
| Ray    | Ray    |
| Ray    | Susan  |
+-----------------+
-- Results for 2-hop ----------------
+-----------------+
| FNAME1 | FNAME2 |
+-----------------+
| Ray    | Susan  |
| Ray    | Ray    |
| Ray    | John   |
+-----------------+
-- Results for 3-hop ----------------
+-----------------+
| FNAME1 | FNAME2 |
+-----------------+
| Ray    | Susan  |
| Ray    | Bill   |
| Ray    | Ray    |
| Ray    | John   |
+-----------------+
4.8.4.5.4 Options for Partial Object Construction

When reading edges from a query result, there are two possible behaviors when adding the start and end vertex to any local caches:

  • Add only the vertex ID, which is available from the edge itself. This option is the default, for efficiency.

  • Add the vertex ID, and retrieve all properties for the start and end vertex. For this behavior, you can call setPartial(true) on each OracleVertex object constructed from your PGQL query result set.

Example 4-41 PgqlExample14.java

PgqlExample14.java illustrates this difference in behavior. This program first executes a query to retrieve all edges, which causes the incident vertices to be added to a local cache. The second query retrieves all vertices. The program then prints each OracleVertex object to show which properties have been loaded.

import java.sql.Connection;

import oracle.pg.rdbms.Oracle;
import oracle.pg.rdbms.OraclePropertyGraph;
import oracle.pg.rdbms.OracleVertex;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows the behavior of setPartial(true) for OracleVertex objects
 * created from PGQL query results.
 */
public class PgqlExample14
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    Oracle oracle = null;
    OraclePropertyGraph opg = null;
    PgqlStatement ps = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Query to illustrate set partial
      String pgql =
        "SELECT id(e), label(e) "+
        "FROM MATCH (v1)-[e:\"knows\"]->(v2)";

      // execute query
      rs = ps.executeQuery(pgql, " ");

      // print results
      System.out.println("-- Results for edge query -----------------");
      rs.print();

      // close result set
      rs.close();

      // Create an Oracle Property Graph instance
      oracle = new Oracle(conn);
      opg = OraclePropertyGraph.getInstance(oracle,graph);

      // Query to retrieve vertices
      pgql =
        "SELECT id(v) "+
        "FROM MATCH (v)";

      // Get each vertex object in result and print with toString()
      rs = ps.executeQuery(pgql, " ");

      // iterate through result
      System.out.println("-- Vertex objects retrieved from vertex query --");
      while (rs.next()) {
        Long vid = rs.getLong(1);
        OracleVertex v = OracleVertex.getInstance(opg, vid);
        System.out.println(v.toString());
      }
      // close result set
      rs.close();

      // Execute the same query but call setPartial(true) for each vertex
      rs = ps.executeQuery(pgql, " ");
      System.out.println("-- Vertex objects retrieved from vertex query with setPartial(true) --");
      while (rs.next()) {
        Long vid = rs.getLong(1);
        OracleVertex v = OracleVertex.getInstance(opg, vid);
        v.setPartial(true);
        System.out.println(v.toString());
      }
      // close result set
      rs.close();
    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
      // close the property graph
      if (opg != null) {
        opg.close();
      }
      // close oracle
      if (oracle != null) {
        oracle.dispose();
      }
    }
  }
}

The output for PgqlExample14.java (which can be loaded using GraphLoaderExample.java code) is:

-- Results for edge query -----------------
+------------------+
| id(e) | label(e) |
+------------------+
| 6     | knows    |
| 11    | knows    |
| 10    | knows    |
| 5     | knows    |
| 4     | knows    |
| 13    | knows    |
| 9     | knows    |
| 12    | knows    |
| 8     | knows    |
| 7     | knows    |
| 14    | knows    |
| 15    | knows    |
+------------------+
-- Vertex objects retrieved from vertex query --
Vertex ID 3 [NULL] {}
Vertex ID 0 [NULL] {}
Vertex ID 2 [NULL] {}
Vertex ID 1 [NULL] {}
-- Vertex objects retrieved from vertex query with setPartial(true) --
Vertex ID 3 [NULL] {bval:bol:false, fname:str:Susan, lname:str:Blue, mval:bol:false, age:int:35}
Vertex ID 0 [NULL] {bval:bol:true, fname:str:Bill, lname:str:Brown, mval:str:y, age:int:40}
Vertex ID 2 [NULL] {fname:str:Ray, lname:str:Green, mval:dat:1985-01-01 04:00:00.0, age:int:41}
Vertex ID 1 [NULL] {bval:bol:true, fname:str:John, lname:str:Black, mval:int:27, age:int:30}
4.8.4.6 Querying Another User’s Property Graph

You can query another user’s property graph data if you have been granted the appropriate privileges in the database. For example, to query GRAPH1 in SCOTT’s schema, you must have READ privilege on SCOTT.GRAPH1GE$, SCOTT.GRAPH1VT$, SCOTT.GRAPH1GT$, and SCOTT.GRAPH1VD$.

Example 4-42 PgqlExample15.java

PgqlExample15.java shows how another user can query a graph in SCOTT’s schema.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to query a property graph located in another user's
 * schema. READ privilege on GE$, VT$, GT$ and VD$ tables for the other user's
 * property graph are required to avoid ORA-00942: table or view does not exist.
 */
public class PgqlExample15
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Set schema so that we can query Scott's graph
      pgqlConn.setSchema("SCOTT");

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Execute query to get a ResultSet object
      String pgql = 
        "SELECT v.\"fname\" AS fname, v.\"lname\" AS lname "+
        "FROM MATCH (v)";
      rs = ps.executeQuery(pgql, "");


      // Print query results
      rs.print();
    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

The following SQL statements create database user USER2 and grant the necessary privileges. You can also use the OraclePropertyGraph.grantAccess Java API to achieve the same effect.

SQL> grant connect, resource, unlimited tablespace to user2 identified by user2;

Grant succeeded.

SQL> grant read on scott.test_graphvt$ to user2;

Grant succeeded.

SQL> grant read on scott.test_graphge$ to user2;

Grant succeeded.

SQL> grant read on scott.test_graphgt$ to user2;

Grant succeeded.

SQL> grant read on scott.test_graphvd$ to user2;

Grant succeeded.

The output for PgqlExample15.java for the test_graph data set when connected to the database as USER2 is as follows. Note that test_graph should have already been loaded (using GraphLoaderExample.java code) as GRAPH1 by user SCOTT before running PgqlExample15.

+---------------+
| FNAME | LNAME |
+---------------+
| Susan | Blue  |
| Bill  | Brown |
| Ray   | Green |
| John  | Black |
+---------------+
4.8.4.7 Using Query Optimizer Hints with PGQL

The Java API allows query optimizer hints that influence the join type when executing PGQL queries. The executeQuery and translateQuery methods in PgqlStatement and PgqlPreparedStatement accept the following strings in the options argument to influence the query plan for the corresponding SQL query.

  • ALL_EDGE_NL – Use Nested Loop join for all joins that involve the $GE and $GT tables.

  • ALL_EDGE_HASH – Use HASH join for all joins that involve the $GE and $GT tables.

  • ALL_VERTEX_NL – Use Nested Loop join for all joins that involve the $VT table.

  • ALL_VERTEX_HASH – Use HASH join for all joins that involve the $VT table.

Example 4-43 PgqlExample16.java

PgqlExample16.java shows how to use optimizer hints to influence the joins used for a graph traversal.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlSqlQueryTrans;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to use query optimizer hints with PGQL queries.
 */
public class PgqlExample16
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();
      // Query to illustrate join hints
      String pgql = 
        "SELECT id(v1), id(v4) "+
        "FROM MATCH (v1)-[:\"friendOf\"]->(v2)-[:\"friendOf\"]->(v3)-[:\"friendOf\"]->(v4)";

      // get SQL translation with hash join hint
      PgqlSqlQueryTrans sqlTrans = 
        ps.translateQuery(pgql /* query string */, 
                          " ALL_EDGE_HASH " /* options */);
      // print SQL translation
      System.out.println("-- Query with ALL_EDGE_HASH --------------------");
      System.out.println(sqlTrans.getSqlTranslation()+"\n");

      // get SQL translation with nested loop join hint
      sqlTrans = 
        ps.translateQuery(pgql /* query string */, 
                          " ALL_EDGE_NL " /* options */);
      // print SQL translation
      System.out.println("-- Query with ALL_EDGE_NL ---------------------");
      System.out.println(sqlTrans.getSqlTranslation()+"\n");
    }
    finally {
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

The output for PgqlExample16.java for test_graph (which can be loaded using GraphLoaderExample.java code) is:

-- Query with ALL_EDGE_HASH --------------------
SELECT /*+ USE_HASH(T0$0 T0$1 T0$2) */ 7 AS "id(v1)$T",
to_nchar(T0$0.SVID,'TM9','NLS_Numeric_Characters=''.,''') AS "id(v1)$V",
T0$0.SVID AS "id(v1)$VN",
to_timestamp_tz(null) AS "id(v1)$VT",
7 AS "id(v4)$T",
to_nchar(T0$2.DVID,'TM9','NLS_Numeric_Characters=''.,''') AS "id(v4)$V",
T0$2.DVID AS "id(v4)$VN",
to_timestamp_tz(null) AS "id(v4)$VT"
FROM "SCOTT".TEST_GRAPHGT$ T0$0,
"SCOTT".TEST_GRAPHGT$ T0$1,
"SCOTT".TEST_GRAPHGT$ T0$2
WHERE T0$0.DVID=T0$1.SVID AND
T0$1.DVID=T0$2.SVID AND
(T0$0.EL = n'friendOf' AND T0$0.EL IS NOT NULL) AND
(T0$1.EL = n'friendOf' AND T0$1.EL IS NOT NULL) AND
(T0$2.EL = n'friendOf' AND T0$2.EL IS NOT NULL)

-- Query with ALL_EDGE_NL ---------------------
SELECT /*+ USE_NL(T0$0 T0$1 T0$2) */ 7 AS "id(v1)$T",
to_nchar(T0$0.SVID,'TM9','NLS_Numeric_Characters=''.,''') AS "id(v1)$V",
T0$0.SVID AS "id(v1)$VN",
to_timestamp_tz(null) AS "id(v1)$VT",
7 AS "id(v4)$T",
to_nchar(T0$2.DVID,'TM9','NLS_Numeric_Characters=''.,''') AS "id(v4)$V",
T0$2.DVID AS "id(v4)$VN",
to_timestamp_tz(null) AS "id(v4)$VT"
FROM "SCOTT".TEST_GRAPHGT$ T0$0,
"SCOTT".TEST_GRAPHGT$ T0$1,
"SCOTT".TEST_GRAPHGT$ T0$2
WHERE T0$0.DVID=T0$1.SVID AND
T0$1.DVID=T0$2.SVID AND
(T0$0.EL = n'friendOf' AND T0$0.EL IS NOT NULL) AND
(T0$1.EL = n'friendOf' AND T0$1.EL IS NOT NULL) AND
(T0$2.EL = n'friendOf' AND T0$2.EL IS NOT NULL)

The query plan for the first query that uses ALL_EDGE_HASH should look similar to the following.

-----------------------------------------------
| Id  | Operation             | Name          |
-----------------------------------------------
|   0 | SELECT STATEMENT      |               |
|*  1 |  HASH JOIN            |               |
|*  2 |   HASH JOIN           |               |
|   3 |    PARTITION HASH ALL |               |
|*  4 |     TABLE ACCESS FULL | TEST_GRAPHGT$ |
|   5 |    PARTITION HASH ALL |               |
|*  6 |     TABLE ACCESS FULL | TEST_GRAPHGT$ |
|   7 |   PARTITION HASH ALL  |               |
|*  8 |    TABLE ACCESS FULL  | TEST_GRAPHGT$ |
-----------------------------------------------

The query plan for the second query that uses ALL_EDGE_NL should look similar to the following.

-----------------------------------------------------------------------
| Id  | Operation                                    | Name           |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |                |
|   1 |  NESTED LOOPS                                |                |
|   2 |   NESTED LOOPS                               |                |
|   3 |    PARTITION HASH ALL                        |                |
|*  4 |     TABLE ACCESS FULL                        | TEST_GRAPHGT$  |
|   5 |    PARTITION HASH ALL                        |                |
|*  6 |     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TEST_GRAPHGT$  |
|*  7 |      INDEX RANGE SCAN                        | TEST_GRAPHXSG$ |
|   8 |   PARTITION HASH ALL                         |                |
|*  9 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | TEST_GRAPHGT$  |
|* 10 |     INDEX RANGE SCAN                         | TEST_GRAPHXSG$ |
-----------------------------------------------------------------------

4.8.5 Modifying Property Graphs through INSERT, UPDATE, and DELETE Statements

PGQL supports INSERT, UPDATE, and DELETE operations on Property Graphs. The method execute in PgqlStatement lets you execute such DML operations. This topic provides several examples of such operations.

Note:

JDBC connection autocommit must be off in order to be able to execute INSERT, UPDATE, and DELETE statements.

Example 4-44 PgqlExample17.java (Insert)

PgqlExample17.java inserts several vertices and edges into a graph. Notice that the special property _ora_id is used to define ID values of vertices and edges. If the property _ora_id is omitted, a unique ID is generated for each new vertex or edge that is inserted into the graph.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to execute a PGQL INSERT operation.
 */
public class PgqlExample17
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();
      conn.setAutoCommit(false);

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Execute insert statement
      String pgql = 
        "INSERT VERTEX p1 LABELS (person) PROPERTIES (p1.\"_ora_id\" = 1, p1.fname = 'Jake') "+
        "     , VERTEX p2 LABELS (person) PROPERTIES (p2.\"_ora_id\" = 2, p2.fname = 'Amy')  "+
        "     , VERTEX p3 LABELS (person) PROPERTIES (p3.\"_ora_id\" = 3, p3.fname = 'Erik') "+
        "     , VERTEX p4 LABELS (person) PROPERTIES (p4.\"_ora_id\" = 4, p4.fname = 'Jane') "+
        "     , EDGE e1 BETWEEN p1 AND p2 LABELS (knows) PROPERTIES (e1.\"_ora_id\" = 1, e1.since = DATE '2003-04-21') "+
        "     , EDGE e2 BETWEEN p1 AND p3 LABELS (knows) PROPERTIES (e2.\"_ora_id\" = 2, e2.since = DATE '2010-02-10') "+
        "     , EDGE e3 BETWEEN p3 AND p4 LABELS (knows) PROPERTIES (e3.\"_ora_id\" = 3, e3.since = DATE '1999-01-03') ";
      ps.execute(pgql, /*  query string  */ 
                   "", /* query options  */
                   ""  /* modify options */);

      // Execute a query to verify insertion
      pgql = 
          "  SELECT id(p1) AS id1, p1.fname AS person1, id(p2) as id2, p2.fname AS person2, id(e) as e, e.since "+
          "    FROM MATCH (p1)-[e:knows]->(p2) "+
          "ORDER BY id1, id2";
      rs = ps.executeQuery(pgql, "");

      // Print the results
      rs.print();
    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

The output for PgqlExample17.java is:

+-----------------------------------------------------------+
| ID1 | PERSON1 | ID2 | PERSON2 | E | SINCE                 |
+-----------------------------------------------------------+
| 1   | Jake    | 2   | Amy     | 1 | 2003-04-20 17:00:00.0 |
| 1   | Jake    | 3   | Erik    | 2 | 2010-02-09 16:00:00.0 |
| 3   | Erik    | 4   | Jane    | 3 | 1999-01-02 16:00:00.0 |
+-----------------------------------------------------------+

For more examples of INSERT statement, see the relevant section of the PGQL specification here.

Example 4-45 PgqlExample18.java (Update)

PgqlExample18.java updates several properties of vertices and edges that are matched in the FROM clause of an UPDATE statement.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to execute a PGQL UPDATE operation.
 */
public class PgqlExample18
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();
      conn.setAutoCommit(false);

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Execute update statement
      String pgql = 
        "UPDATE p1 SET (p1.age = 47, p1.lname = 'Red'), "+
        "       p2 SET (p2.age = 29, p2.lname = 'White'), "+
        "        e SET (e.strength = 100) "+
        "FROM MATCH (p1) -[e:knows]-> (p2) "+
        "WHERE p1.fname = 'Jake' AND p2.fname = 'Amy'";
      ps.execute(pgql, /*  query string  */ 
                   "", /* query options  */
                   ""  /* modify options */);

      // Execute a query to verify update
      pgql = 
          "SELECT p1.fname AS fname1, p1.lname AS lname1, p1.age AS age1, "+
          "       p2.fname AS fname2, p2.lname AS lname2, p2.age AS age2, e.strength "+
          "FROM MATCH (p1) -[e:knows]-> (p2)";
      rs = ps.executeQuery(pgql, "");

      // Print the results
      rs.print();
    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

The output for PgqlExample18.java applied on a graph where PgqlExample17.java has been previously executed is:

+----------------------------------------------------------------+
| FNAME1 | LNAME1 | AGE1   | FNAME2 | LNAME2 | AGE2   | STRENGTH |
+----------------------------------------------------------------+
| Jake   | Red    | 47     | Amy    | White  | 29     | 100      |
| Jake   | Red    | 47     | Erik   | <null> | <null> | <null>   |
| Erik   | <null> | <null> | Jane   | <null> | <null> | <null>   |
+----------------------------------------------------------------+

For more examples of UPDATE statement, see the relevant section of the PGQL specification here.

Example 4-46 PgqlExample19.java (Delete)

PgqlExample19.java deletes edges that are matched in the FROM clause of a DELETE statement.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to execute a PGQL DELETE operation.
 */
public class PgqlExample19
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();
      conn.setAutoCommit(false);

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Execute delete statement
      String pgql = 
        "DELETE e "+
        "  FROM MATCH (p1) -[e:knows]-> (p2) "+
        " WHERE p1.fname = 'Jake'";
      ps.execute(pgql, /*  query string  */ 
                   "", /* query options  */
                   ""  /* modify options */);

      // Execute a query to verify delete
      pgql = 
          "SELECT p1.fname AS fname1, p2.fname AS fname2 "+
          "  FROM MATCH (p1) -[e:knows]-> (p2)";
      rs = ps.executeQuery(pgql, "");

      // Print the results
      rs.print();
    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

The output for PgqlExample19.java applied on a graph where PgqlExample18.java has been previously executed is:

+-----------------+
| FNAME1 | FNAME2 |
+-----------------+
| Erik   | Jane   |
+-----------------+

For more examples of DELETE statement, see the relevant section of the PGQL specification here.

Example 4-47 PgqlExample20.java (Multiple Modifications)

PgqlExample20.java executes multiple modifications in the same statement: an edge is inserted, vertex properties are updated, and another edge is deleted.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to execute a PGQL 
 * INSERT/UPDATE/DELETE operation.
 */
public class PgqlExample20
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();
      conn.setAutoCommit(false);

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Execute INSERT/UPDATE/DELETE statement
      String pgql = 
        "INSERT EDGE f BETWEEN p2 AND p1 LABELS (knows) PROPERTIES (f.since = e.since) "+
        "UPDATE p1 SET (p1.age = 30) "+
        "     , p2 SET (p2.age = 25) "+
        "DELETE e "+
        "  FROM MATCH (p1) -[e:knows]-> (p2) "+
        " WHERE p1.fname = 'Erik'";
      ps.execute(pgql, /*  query string  */ 
                   "", /* query options  */
                   ""  /* modify options */);

      // Execute a query to verify INSERT/UPDATE/DELETE
      pgql = 
          "SELECT p1.fname AS fname1, p1.age AS age1, "+
          "       p2.fname AS fname2, p2.age AS age2, e.since "+
          "  FROM MATCH (p1) -[e:knows]-> (p2)";
      rs = ps.executeQuery(pgql, "");

      // Print the results
      rs.print();
    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }

The output for PgqlExample20.java applied on a graph where PgqlExample19.java has been previously executed is:

+-------------------------------------------------------+
| FNAME1 | AGE1 | FNAME2 | AGE2 | SINCE                 |
+-------------------------------------------------------+
| Jane   | 25   | Erik   | 30   | 1999-01-02 16:00:00.0 |
+-------------------------------------------------------+

For more examples of INSERT/UPDATE/DELETE statements, see the relevant section of the PGQL specification here.

4.8.5.1 Additional Options for PGQL Statement Execution

Several options are available to influence PGQL statement execution. The following are the main ways to set query options:

  • Through flags in the modify options string argument of execute
  • Through Java JVM arguments.

The following table summarizes the main options for modifying PGQL statement execution.

Table 4-3 PGQL Statement Modification Options

Option Default Options Flag JVM Argument
Auto commit true if JDBC auto commit is off, false if JDBC auto commit is on AUTO_COMMIT=F -Doracle.pg.rdbms.pgql.autoCommit=false
Delete cascade true DELETE_CASCADE=F -Doracle.pg.rdbms.pgql.deleteCascade=false
4.8.5.1.1 Turning Off PGQL Auto Commit

When an INSERT, UPDATE, or DELETE operation is executed, a commit is performed automatically at the end of the PGQL execution so that changes are persisted on the RDBMS side.

The flag AUTO_COMMIT=F can be added to the options argument of execute or the flag Doracle.pg.rdbms.pgql.autoCommit=false can be set in the Java command line to turn off auto commit. Notice that when auto commit is off, you must perform any necessary commits or rollbacks on the JDBC connection in order to persist or cancel graph modifications.

Example 4-48 Turn Off Auto Commit and Roll Back Changes

PgqlExample21.java turns off auto commit and performs a rollback of the changes.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to modify a PGQL graph
 * with auto commit off.
 */
public class PgqlExample21
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();
      conn.setAutoCommit(false);

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Delete all the edges in the graph
      String pgql = 
        "DELETE e "+
        "  FROM MATCH () -[e]-> ()";
      ps.execute(pgql, /*  query string  */ 
                 "",   /* query options  */
                 "AUTO_COMMIT=F"  /* modify options */);

      // Execute a query to verify deletion
      pgql = 
          "SELECT COUNT(e) "+
          "  FROM MATCH () -[e]-> ()";
      rs = ps.executeQuery(pgql, "");

      // Print the results
      System.out.println("Number of edges after deletion:");
      rs.print();
      rs.close();

      // Rollback the changes. This is possible because
      // AUTO_COMMIT=F flag was used in execute
      conn.rollback();

      // Execute a query to verify rollback
      pgql = 
          "SELECT COUNT(e) "+
          "  FROM MATCH () -[e]-> ()";
      rs = ps.executeQuery(pgql, "");

      // Print the results
      System.out.println("Number of edges after rollback:");
      rs.print();
    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

PgqlExample21.java gives the following output for a graph with one edge:

Number of edges after deletion:
+----------+
| COUNT(e) |
+----------+
| 0        |
+----------+
Number of edges after rollback:
+----------+
| COUNT(e) |
+----------+
| 1        |
+----------+
4.8.5.1.2 Turning Off Cascading Deletion

When a vertex is deleted from a graph, all its input and output edges are also deleted automatically.

Using the flag DELETE_CASCADE=F in the options argument of execute of setting the flag or setting the flag Doracle.pg.rdbms.pgql.autoCommit=false in the Java command line lets you turn off cascading deletion. When a vertex with input or output edges is deleted and cascading deletion is off, an error is thrown to warn about the unsafe operation that you are trying to perform.

Example 4-49 Turn Off Cascading Deletion

PgqlExample22.java attempts to delete a vertex with an output edge when cascading deletion is off.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlStatement;
import oracle.pg.rdbms.pgql.PgqlToSqlException;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows the use of DELETE_CASCADE flag.
 */
public class PgqlExample22
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();
      conn.setAutoCommit(false);

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Delete all the vertices with output edges
      // This will throw an error
      String pgql = 
        "DELETE v "+
        "  FROM MATCH (v) -[e]-> ()";
      ps.execute(pgql, /*  query string  */ 
                 "",   /* query options  */
                 "DELETE_CASCADE=F"  /* modify options */);
    }
    catch (PgqlToSqlException ex){
      System.out.println("Error in execution: " + ex.getMessage());
    }
    finally {
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

PgqlExample22.java gives the following output for a graph with at least one edge:

Error in execution: Attempting to delete vertices with incoming/outgoing edges. Drop edges first or turn on DELETE_CASCADE option

4.8.6 Performance Considerations for PGQL Queries

Many factors affect the performance of PGQL queries in Oracle Database. The following are some recommended practices for query performance.

Query Optimizer Statistics

Good, up-to-date query optimizer statistics are critical for query performance. Ensure that you run OPG_APIS.ANALYZE_PG after any significant updates to your property graph data.

Parallel Query Execution

Use parallel query execution to take advantage of Oracle’s parallel SQL engine. Parallel execution often gives a significant speedup versus serial execution. Parallel execution is especially critical for path queries evaluated using the recursive WITH strategy.

See also the Oracle Database VLDB and Partitioning Guide for more information about parallel query execution.

Optimizer Dynamic Sampling

Due to the inherent flexibility of the graph data model, static information may not always produce optimal query plans. In such cases, dynamic sampling can be used by the query optimizer to sample data at run time for better query plans. The amount of data sampled is controlled by the dynamic sampling level used. Dynamic sampling levels range from 0 to 11. The best level to use depends on a particular dataset and workload, but levels of 2 (default), 6, or 11 often give good results.

See also Supplemental Dynamic Statistics in the Oracle Database SQL Tuning Guide.

Bind Variables

Use bind variables for constants whenever possible. The use of bind variables gives a very large reduction in query compilation time, which dramatically increases throughput for query workloads with queries that differ only in the constant values used. In addition, queries with bind variables are less vulnerable to injection attacks.

Path Queries

Path queries in PGQL that use the + (plus sign) or * (asterisk) operator to search for arbitrary length paths require special consideration because of their high computational complexity. You should use parallel execution and use the DISTINCT option for Recursive WITH (USE_DIST_RW=T) for the best performance. Also, for large, highly connected graphs, it is a good idea to use MAX_PATH_LEN=n to limit the number of repetitions of the recursive step to a reasonable number. A good strategy can be to start with a small repetition limit, and iteratively increase the limit to find more and more results.