5 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:
-
Use the
oracle.pgx.apiJava package to query an in-memory snapshot of a graph that has been loaded into the in-memory analyst (PGX), as described in Using the In-Memory Graph Server (PGX). -
Use the
oracle.pg.rdbms.pgqlJava package to directly query graph data stored in Oracle Database, as described in Executing PGQL Queries Directly Against Oracle Database.
For more information about PGQL, see https://pgql-lang.org.
- Creating a Property Graph using PGQL
- Creating Property Graph Views Using PGQL
- Pattern Matching with PGQL
- Edge Patterns Have a Direction with PGQL
- Vertex and Edge Labels with PGQL
- Variable-Length Paths with PGQL
- Aggregation and Sorting with PGQL
- Executing PGQL Queries Against the In-Memory Graph Server (PGX)
This section describes the Java APIs that are used to execute PGQL queries in the In-Memory graph server (PGX). - 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).
5.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.
Parent topic: Property Graph Query Language (PGQL)
5.2 Creating Property Graph Views Using PGQL
You can create property graphs views on relational database tables.
The CREATE PROPERTY GRAPH statement in PGQL is used to create the property graph views.
See Example 2-1 for an example of a CREATE PROPERTY GRAPH statement used to create a property graph view.
The Java API in oracle.pg.rdbms.pgql package provides support for executing PGQL queries with a few exceptions.
Creation of property graph views is not supported when using SQLcl. However, once created, you can query property graph views with PGQL SELECT statements in SQLcl. Both creation and querying of property graph views are not supported when using Python API, or the graph visualization tool.
Also, the following PGQL SELECT features are not supported:
- Recursive queries
- Subqueries
in_degreeandout_degreefunctions- Bind variables
Parent topic: Property Graph Query Language (PGQL)
5.3 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.
Parent topic: Property Graph Query Language (PGQL)
5.4 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.
Parent topic: Property Graph Query Language (PGQL)
5.5 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.
Parent topic: Property Graph Query Language (PGQL)
5.6 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.
Parent topic: Property Graph Query Language (PGQL)
5.7 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.
Parent topic: Property Graph Query Language (PGQL)
5.8 Executing PGQL Queries Against the In-Memory Graph Server (PGX)
This section describes the Java APIs that are used to execute PGQL queries in the In-Memory graph server (PGX).
- Getting Started with PGQL
- Supported PGQL Features
The In-Memory graph server (PGX) supports all PGQL features exceptDROP PROPERTY GRAPH. - Java APIs for Executing CREATE PROPERTY GRAPH Statements
- Java APIs for Executing SELECT Queries
This section describes the APIs to executeSELECTqueries in the In-Memory graph server (PGX). - Java APIs for Executing UPDATE Queries
TheUPDATEqueries make changes to existing graphs using theINSERT,UPDATE, andDELETEoperations as detailed in the section Graph Modification of the PGQL 1.3 specification. - Security Tools for Executing PGQL Queries
To safeguard against query injection, bind variables can be used in place of literals whileprintIdentifier(String identifier)can be used in place of identifiers like graph names, labels, and property names. - Best Practices for Tuning PGQL Queries
This section describes best practices regarding memory allocation, parallelism, and query planning.
Parent topic: Property Graph Query Language (PGQL)
5.8.1 Getting Started with PGQL
This section provides an example on how to get started with PGQL. It assumes a
database realm that has been previously set up (follow the steps in 3.1.1 Prepare the Graph
Server for Database Authentication). It also assumes that the user has read
access to the HR schema.
First, create a graph with employees, departments, and employee works at
department, by executing a CREATE PROPERTY GRAPH statement.
Example 5-1 Creating a graph in the in-memory graph server (PGX)
The following statement creates a graph in the in-memory graph server (PGX)
String statement =
"CREATE PROPERTY GRAPH hr_simplified "
+ " VERTEX TABLES ( "
+ " hr.employees LABEL employee "
+ " PROPERTIES ARE ALL COLUMNS EXCEPT ( job_id, manager_id, department_id ), "
+ " hr.departments LABEL department "
+ " PROPERTIES ( department_id, department_name ) "
+ " ) "
+ " EDGE TABLES ( "
+ " hr.employees AS works_at "
+ " SOURCE KEY ( employee_id ) REFERENCES employees "
+ " DESTINATION departments "
+ " PROPERTIES ( employee_id ) "
+ " )";
session.executePgql(statement);
/**
* To get a handle to the graph, execute:
*/
PgxGraph g = session.getGraph("HR_SIMPLIFIED");
/**
* You can use this handle to run PGQL queries on this graph.
* For example, to find the department that “Nandita Sarchand” works for, execute:
*/
String query =
"SELECT dep.department_name "
+ "FROM MATCH (emp:Employee) -[:works_at]-> (dep:Department) "
+ "WHERE emp.first_name = 'Nandita' AND emp.last_name = 'Sarchand' "
+ "ORDER BY 1";
PgqlResultSet resultSet = g.queryPgql(query);
resultSet.print();
+-----------------+
| department_name |
+-----------------+
| Shipping |
+-----------------+
/**
* To get an overview of the types of vertices and their frequencies, execute:
*/
String query =
"SELECT label(n), COUNT(*) "
+ "FROM MATCH (n) "
+ "GROUP BY label(n) "
+ "ORDER BY COUNT(*) DESC";
PgqlResultSet resultSet = g.queryPgql(query);
resultSet.print();
+-----------------------+
| label(n) | COUNT(*) |
+-----------------------+
| EMPLOYEE | 107 |
| DEPARTMENT | 27 |
+-----------------------+
/**
*To get an overview of the types of edges and their frequencies, execute:
*/
String query =
"SELECT label(n) AS srcLbl, label(e) AS edgeLbl, label(m) AS dstLbl, COUNT(*) "
+ "FROM MATCH (n) -[e]-> (m) "
+ "GROUP BY srcLbl, edgeLbl, dstLbl "
+ "ORDER BY COUNT(*) DESC";
PgqlResultSet resultSet = g.queryPgql(query);
resultSet.print();
+---------------------------------------------+
| srcLbl | edgeLbl | dstLbl | COUNT(*) |
+---------------------------------------------+
| EMPLOYEE | WORKS_AT | DEPARTMENT | 106 |
+---------------------------------------------+
5.8.2 Supported PGQL Features
The In-Memory graph server (PGX) supports all PGQL features except
DROP PROPERTY GRAPH.
Few features have certain limitations that are described below.
- Limitations on Quantifiers
Although all quantifiers such as*,+, and{1,4}are supported for reachability patterns, the only quantifier that is supported for shortest and cheapest path patterns is*(zero or more). - Limitations on WHERE and COST Clauses in Quantified Patterns
5.8.2.1 Limitations on Quantifiers
Although all quantifiers such as *, +, and
{1,4} are supported for
reachability patterns, the only quantifier that is supported for
shortest and cheapest path patterns is * (zero or
more).
Parent topic: Supported PGQL Features
5.8.2.2 Limitations on WHERE and COST Clauses in Quantified Patterns
The WHERE and COST clauses in quantified patterns,
such as reachability patterns or shortest and cheapest path patterns, are limited to
referencing a single variable only.
The following are examples of queries that are not supported because the
WHERE or COST clauses reference two variables
e and x instead of zero or
one:
... PATH p AS (n) –[e]-> (m) WHERE e.prop > m.prop ...
... SHORTEST ( (n) (-[e]-> (x) WHERE e.prop + x.prop > 10)* (m) ) ...
... CHEAPEST ( (n) (-[e]-> (x) COST e.prop + x.prop )* (m) ) ...The following query is supported because the subquery only references a single
variable a from the outer scope, while the variable c does
not count since it is newly introduced in the subquery:
... PATH p AS (a) -> (b)
WHERE EXISTS ( SELECT * FROM MATCH (a) -> (c) ) ...Parent topic: Supported PGQL Features
5.8.3 Java APIs for Executing CREATE PROPERTY GRAPH Statements
The easiest way to execute a CREATE PROPERTY GRAPH statement is
through the PgxSession.executePgql(String statement) method.
Example 5-2 Executing a CREATE PROPERTY GRAPH statement
String statement =
"CREATE PROPERTY GRAPH hr_simplified "
+ " VERTEX TABLES ( "
+ " hr.employees LABEL employee "
+ " PROPERTIES ARE ALL COLUMNS EXCEPT ( job_id, manager_id, department_id ), "
+ " hr.departments LABEL department "
+ " PROPERTIES ( department_id, department_name ) "
+ " ) "
+ " EDGE TABLES ( "
+ " hr.employees AS works_at "
+ " SOURCE KEY ( employee_id ) REFERENCES employees "
+ " DESTINATION departments "
+ " PROPERTIES ( employee_id ) "
+ " )";
session.executePgql(statement);
PgxGraph g = session.getGraph("HR_SIMPLIFIED");
/**
* Alternatively, one can use the prepared statement API, for example:
*/
PgxPreparedStatement stmnt = session.preparePgql(statement);
stmnt.execute();
stmnt.close();
PgxGraph g = session.getGraph("HR_SIMPLIFIED");
5.8.4 Java APIs for Executing SELECT Queries
This section describes the APIs to execute SELECT queries
in the In-Memory graph server (PGX).
- Executing SELECT Queries Against a Graph in the In-memory Graph Server (PGX)
ThePgxGraph.queryPgql(String query)method executes the query in the session that was used to create the PgxGraph. The method returns aPgqlResultSet. - Executing SELECT Queries Against a PGX Session
ThePgxSession.queryPgql(String query)method executes the given query in the session and returns aPgqlResultSet. - Iterating Through a Result Set
There are two ways to iterate through a result set: in a JDBC-like manner or using the Java Iterator interface. - Printing a Result Set
The following methods ofPgqlResultSet (package oracle.pgx.api)are used to print a result set:
5.8.4.1 Executing SELECT Queries Against a Graph in the In-memory Graph Server (PGX)
The PgxGraph.queryPgql(String query) method executes the
query in the session that was used to create the PgxGraph. The method returns a
PgqlResultSet.
The ON clauses inside the MATCH
clauses can be omitted since the query is executed directly against a PGX
graph. For the same reason, the INTO clauses inside the
INSERT clauses can be omitted. However, if you want
to explicitly specify graph names in the ON and
INTO clauses, then those graph names have to match
the actual name of the graph (PgxGraph.getName()).
Parent topic: Java APIs for Executing SELECT Queries
5.8.4.2 Executing SELECT Queries Against a PGX Session
The PgxSession.queryPgql(String query) method executes the
given query in the session and returns a PgqlResultSet.
The ON clauses inside the MATCH
clauses, and the INTO clauses inside the
INSERT clauses, must be specified and cannot be
omitted. At this moment, all the ON and
INTO clauses of a query need to reference the same
graph since joining data from multiple graphs in a single query is not yet
supported.
Parent topic: Java APIs for Executing SELECT Queries
5.8.4.3 Iterating Through a Result Set
There are two ways to iterate through a result set: in a JDBC-like manner or using the Java Iterator interface.
For JDBC-like iterations, the methods in
PgqlResultSet (package
oracle.pgx.api) are similar to the ones in
java.sql.ResultSet. A noteworthy difference is that
PGQL's result set interface is based on the new date and time library that
was introduced in Java 8, while java.sql.ResultSet is based
on the legacy java.util.Date. To bridge the gap, PGQL's
result set provides getLegacyDate(..) for applications that
still use java.util.Date.
PgqlResultSet has a cursor that
is initially set before the first row. Then, the following methods are
available to reposition the cursor:
next() : booleanprevious() : booleanbeforeFirst()afterLast()first() : booleanlast() : booleanabsolute(long row) : booleanrelative(long rows) : boolean
Above methods are documented in more detail here.
getObject(int columnIdx) : ObjectgetObject(String columnName) : ObjectgetString(int columnIdx) : StringgetString(String columnName) : StringgetInteger(int columnIdx) : IntegergetInteger(String columnName) : IntegergetLong(int columnIdx) : LonggetLong(String columnName) : LonggetFloat(int columnIdx) : FloatgetFloat(String columnName) : FloatgetDouble(int columnIdx) : DoublegetDouble(String columnName) : DoublegetBoolean(int columnIdx) : BooleangetBoolean(String columnName) : BooleangetVertexLabels(int columnIdx) : Set<String>getVertexLabels(String columnName) : Set<String>getDate(int columnIdx) : LocalDategetDate(String columnName) : LocalDategetTime(int columnIdx) : LocalTimegetTime(String columnName) : LocalTimegetTimestamp(int columnIdx) : LocalDateTimegetTimestamp(String columnName) : LocalDateTimegetTimeWithTimezone(int columnIdx) : OffsetTimegetTimeWithTimezone(String columnName) : OffsetTimegetTimestampWithTimezone(int columnIdx) : OffsetDateTimegetTimestampWithTimezone(String columnName) : OffsetDateTimegetLegacyDate(int columnIdx) : java.util.DategetLegacyDate(String columnName) : java.util.DategetList(int columnIdx) : List<T>getList(String columnName) : List<T>
Above methods are documented in more detail here.
Finally, there is a PgqlResultSet.close() which releases the
result set’s resources, and there is a
PgqlResultSet.getMetaData() through which the
column names and column count can be retrieved.
An example for result set iteration is as follows:
PgqlResultSet resultSet = g.queryPgql(
" SELECT owner.name AS account_holder, SUM(t.amount) AS total_transacted_with_Nikita "
+ " FROM MATCH (p:Person) -[:ownerOf]-> (account1:Account) "
+ " , MATCH (account1) -[t:transaction]- (account2) "
+ " , MATCH (account2:Account) <-[:ownerOf]- (owner:Person|Company) "
+ " WHERE p.name = 'Nikita' "
+ " GROUP BY owner");
while (resultSet.next()) {
String accountHolder = resultSet.getString(1);
long totalTransacted = resultSet.getLong(2);
System.out.println(accountHolder + ": " + totalTransacted);
}
resultSet.close();
The output of the above example will look like:
Oracle: 4501
Camille: 1000
In addition, the PgqlResultSet is also
iterable via the Java Iterator interface. An example of a “for each
loop” over the result set is as follows:
for (PgxResult result : resultSet) {
String accountHolder = result.getString(1);
long totalTransacted = result.getLong(2);
System.out.println(accountHolder + ": " + totalTransacted);
}
The output of the above example will look like:
Oracle: 4501
Camille: 1000Note that the same getters that are available for
PgqlResultSet are also available for
PgxResult.
Parent topic: Java APIs for Executing SELECT Queries
5.8.4.4 Printing a Result Set
The following methods of PgqlResultSet (package
oracle.pgx.api) are used to print a result set:
print() : PgqlResultSetprint(long numResults) : PgqlResultSetprint(long numResults, int from) : PgqlResultSetprint(PrintStream printStream, long numResults, int from) : PgqlResultSet
For example:
g.queryPgql("SELECT COUNT(*) AS numPersons FROM MATCH (n:Person)").print().close()
+------------+
| numPersons |
+------------+
| 3 |
+------------+Another example:
PgqlResultSet resultSet = g.queryPgql(
" SELECT owner.name AS account_holder, SUM(t.amount) AS total_transacted_with_Nikita "
+ " FROM MATCH (p:Person) -[:ownerOf]-> (account1:Account) "
+ " , MATCH (account1) -[t:transaction]- (account2) "
+ " , MATCH (account2:Account) <-[:ownerOf]- (owner:Person|Company) "
+ " WHERE p.name = 'Nikita' "
+ " GROUP BY owner")
resultSet.print().close()
+-----------------------------------------------+
| account_holder | total_transacted_with_Nikita |
+-----------------------------------------------+
| Camille | 1000.0 |
| Oracle | 4501.0 |
+-----------------------------------------------+Parent topic: Java APIs for Executing SELECT Queries
5.8.5 Java APIs for Executing UPDATE Queries
The UPDATE queries make changes to existing graphs using
the INSERT, UPDATE, and
DELETE operations as detailed in the section
Graph Modification of the PGQL 1.3 specification.
Note that INSERT allows you to insert new vertices and edges into a
graph, UPDATE allows you to update existing
vertices and edges by setting their properties to new values,
and DELETE allows you to delete vertices and
edges from a graph.
- Executing UPDATE Queries against a Graph in the in-memory Graph Server (PGX)
To executeUPDATEqueries against a graph, use thePgxGraph.executePgql(String query)method. - Executing UPDATE Queries Against a PGX Session
For now, there is no support for executingUPDATEqueries against aPgxSessionand therefore, updates always have to be executed against a PgxGraph. To obtain a graph from a session, use thePgxSession.getGraph(String graphName)method. - Updatability of Graphs Through PGQL
Graph data that is loaded from the Oracle RDBMS or from CSV files into the PGX is not updatable through PGQL right away. - Altering the Underlying Schema of a Graph
TheINSERToperations can only insert vertices and edges with known labels and properties. Similarly,UPDATEoperations can only set values of known properties. Thus, new data must always conform to the existing schema of the graph.
5.8.5.1 Executing UPDATE Queries against a Graph in the in-memory Graph Server (PGX)
To execute UPDATE queries against a graph, use the
PgxGraph.executePgql(String query) method.
The following is an example of
INSERT query:
g.executePgql("INSERT VERTEX v " +
" LABELS ( Person ) " +
" PROPERTIES ( v.firstName = 'Camille', " +
" v.lastName = ' Mullins' ) ");
Note that the INTO clause of
the INSERT can be omitted. If you use an INTO clause, the
graph name in the INTO clause must correspond to the name of the PGX graph
(PgxGraph.getName()) that the query is executed against.
The following is an example of
UPDATE query:
// set the date of birth of Camille to 2014-11-15
g.executePgql("UPDATE v SET ( v.dob = DATE '2014-11-14' ) " +
"FROM MATCH (v:Person) " +
"WHERE v.firstName = 'Camille' AND v.lastName = ' Mullins' ");
The following is an example of
DELETE query:
// delete Camille from the graph
g.executePgql("DELETE v " +
"FROM MATCH (v:Person) " +
"WHERE v.firstName = 'Camille' AND v.lastName = 'Mullins' "); Parent topic: Java APIs for Executing UPDATE Queries
5.8.5.2 Executing UPDATE Queries Against a PGX Session
For now, there is no support for executing UPDATE queries
against a PgxSession and therefore, updates always have to
be executed against a PgxGraph. To obtain a graph from a session, use the
PgxSession.getGraph(String graphName)
method.
Parent topic: Java APIs for Executing UPDATE Queries
5.8.5.3 Updatability of Graphs Through PGQL
Graph data that is loaded from the Oracle RDBMS or from CSV files into the PGX is not updatable through PGQL right away.
First, you need to create a copy of the data through the
PgxGraph.clone() method. The resulting graph is
fully updatable.
Consider the following example:
// load a graph from the RDBMS or from CSV
PgxGraph g1 = session.readGraphWithProperties("path/to/graph_config.json");
// create an updatable copy of the graph
PgxGraph g2 = g1.clone("new_graph_name");
// insert an additional vertex into the graph
g2.executePgql("INSERT VERTEX v " +
" LABELS ( Person ) " +
" PROPERTIES ( v.firstName = 'Camille', " +
" v.lastName = ' Mullins')");
Additionally, there is also a
PgxGraph.cloneAndExecutePgql(String query, String
graphName) method that combines the last two steps
from above example into a single step:
// create an updatable copy of the graph while inserting a new vertex
PgxGraph g2_copy = g1.cloneAndExecutePgql(
"INSERT VERTEX v " +
" LABELS ( Person ) " +
" PROPERTIES ( v.firstName = 'Camille', " +
" v.lastName = ' Mullins') "
, "new_graph_name");Note that graphs that are created through
PgxGraph.clone() are local to the session.
However, they can be shared with other sessions through the
PgxGraph.publish(..) methods but then they
are no longer updatable through PGQL. Only session-local graphs are
updatable but persistent graphs are not.
Parent topic: Java APIs for Executing UPDATE Queries
5.8.5.4 Altering the Underlying Schema of a Graph
The INSERT operations can only insert vertices and edges
with known labels and properties. Similarly, UPDATE operations can
only set values of known properties. Thus, new data must always conform to the
existing schema of the graph.
However, some PGX APIs exist for updating the schema of a graph:
while no APIs exist for adding new labels, new properties can be added
through the PgxGraph.createVertexProperty(PropertyType type, String
name) and PgxGraph.createEdgeProperty(PropertyType
type, String name) methods. The new properties are attached
to each vertex/edge in the graph, irrespective of their labels. Initially
the properties are assigned a default value but then the values can be
updated through the UPDATE statements.
Consider the following example:
// load a graph from the RDBMS or from CSV
PgxGraph g = session.readGraphWithProperties("path/to/graph_config.json");
// add a new property to the graph
g.createVertexProperty(PropertyType.LOCAL_DATE, "dob");
// set the date of birth of Camille to 2014-11-15
g.executePgql("UPDATE v SET ( v.dob = DATE '2014-11-14' ) " +
"FROM MATCH (v:Person) " +
"WHERE v.firstName = 'Camille' AND v.lastName = ' Mullins' ");Parent topic: Java APIs for Executing UPDATE Queries
5.8.6 Security Tools for Executing PGQL Queries
To safeguard against query injection, bind variables can be used in place of
literals while printIdentifier(String identifier)
can be used in place of identifiers like graph names, labels, and
property names.
- Using Bind Variables
There are two reasons for using bind variables: - Using Identifiers in a Safe Manner
When you create a query through string concatenation, not only literals in queries pose a security risk, but also identifiers like graph names, labels, and property names do. The only problem is that bind variables are not supported for such identifier. Therefore, if these identifiers are variable from the application's perspective, then it is recommended to protect against query injection by passing the identifier through theoracle.pgql.lang.ir.PgqlUtils.printIdentifier(String identifier)method.
5.8.6.1 Using Bind Variables
There are two reasons for using bind variables:
- It protects against query injection.
- It speeds up query execution because the same bind variables can be set multiple times without requiring recompilation of the query.
To create a prepared statement, use one of the following two methods:
PgxGraph.preparePgql(String query) : PgxPreparedStatementPgxSession.preparePgql(String query) : PgxPreparedStatement
The PgxPreparedStatement (package
oracle.pgx.api) returned from these
methods have setter methods for binding the bind
variables to values of the designated data type.
PreparedStatement stmnt = g.preparePgql(
"SELECT v.id, v.dob " +
"FROM MATCH (v) " +
"WHERE v.firstName = ? AND v.lastName = ?");
stmnt.setString(1, "Camille");
stmnt.setString(2, "Mullins");
ResultSet rs = stmnt.executeQuery();
Each
bind variable in the query needs to be set to a
value using one of the following setters of
PgxPreparedStatement:
setBoolean(int parameterIndex, boolean x)setDouble(int parameterIndex, double x)setFloat(int parameterIndex, float x)setInt(int parameterIndex, int x)setLong(int parameterIndex, long x)setDate(int parameterIndex, LocalDate x)setTime(int parameterIndex, LocalTime x)setTimestamp(int parameterIndex, LocalDateTime x)setTimeWithTimezone(int parameterIndex, OffsetTime x)setTimestampWithTimezone(int parameterIndex, OffsetDateTime x)setArray(int parameterIndex, List<?> x)
Once all the bind variables are set, the statement can be executed through:
PgxPreparedStatement.executeQuery()- For
SELECTqueries only - Returns a ResultSet
- For
PgxPreparedStatement.execute()- For any type of statement
- Returns a Boolean to indicate the form of the
result: true in case of a
SELECTquery, false otherwise - In case of
SELECT, the ResultSet can afterwards be accessed throughPgxPreparedStatement.getResultSet()
In PGQL, bind variables can be used in place of literals of any data type, including array literals. An example query with a bind variable to is set to an instance of a String array is:
List<String> countryNames = new ArrayList<String>();
countryNames.add("Scotland");
countryNames.add("Tanzania");
countryNames.add("Serbia");
PreparedStatement stmnt = g.preparePgql(
"SELECT n.name, n.population " +
"FROM MATCH (c:Country) " +
"WHERE c.name IN ?");
ResultSet rs = stmnt.executeQuery();
Finally,
if a prepared statement is no longer needed, it is
closed through
PgxPreparedStatement.close() to
free up resources.
Parent topic: Security Tools for Executing PGQL Queries
5.8.6.2 Using Identifiers in a Safe Manner
When you create a query through string concatenation, not only literals in
queries pose a security risk, but also identifiers like graph names, labels, and
property names do. The only problem is that bind variables are not supported for
such identifier. Therefore, if these identifiers are variable from the application's
perspective, then it is recommended to protect against query injection by passing
the identifier through the
oracle.pgql.lang.ir.PgqlUtils.printIdentifier(String
identifier) method.
Given an identifier string, the method automatically adds double quotes to the start and end of the identifier and escapes the characters in the identifier appropriately.
Consider the following example:
String graphNamePrinted = printIdentifier("my graph name with \" special % characters ");
PreparedStatement stmnt = g.preparePgql(
"SELECT COUNT(*) AS numVertices FROM MATCH (v) ON " + graphNamePrinted);Parent topic: Security Tools for Executing PGQL Queries
5.8.7 Best Practices for Tuning PGQL Queries
This section describes best practices regarding memory allocation, parallelism, and query planning.
- Memory Allocation
The In-Memory Analyst (PGX) hason-heapandoff-heapmemory, the earlier being the standard JVM heap while the latter being a separate heap that is managed by PGX. Just like graph data, intermediate and final results of PGQL queries are partially stored on-heap and partially off-heap. Therefore, both heaps are needed. - Parallelism
By default, all available processor threads are used to process PGQL queries. However, if needed, the number of threads can be limited by setting the parallelism option of the In-Memory Analyst (PGX). - Query Plan Explaining
ThePgxGraph.explainPgql(String query)method is used to get insight into the query plan of the query. The method returns an instance ofOperation (package oracle.pgx.api)which has the following methods:
5.8.7.1 Memory Allocation
The In-Memory Analyst (PGX) has on-heap and
off-heap memory, the earlier being the standard JVM heap while the
latter being a separate heap that is managed by PGX. Just like graph data, intermediate and
final results of PGQL queries are partially stored on-heap and partially off-heap.
Therefore, both heaps are needed.
In case of the on-heap memory, the default maximum is chosen upon startup of
the JVM, but it can be overwritten through the -Xmx option.
In case of the off-heap, there is no maximum set by default and the off-heap memory
usage, therefore, keeps increasing automatically until it depletes the system resources,
in which case the operation is cancelled, it's memory is released, and an appropriate
exception is passed to the user. If needed, a maximum off-heap size can be configured
through the max_off_heap_size option of PGX.
A ratio of 1:1 for on-heap vs. off-heap is recommended as a good starting point to allow for the largest possible graphs to be loaded and queried. For example, if you have 256 GB of memory available on your machine, then setting the maximum on-heap to 125 GB will make sure that there is a similar amount of memory available for off-heap:
export JAVA_OPTS="-Xmx125g"
Parent topic: Best Practices for Tuning PGQL Queries
5.8.7.2 Parallelism
By default, all available processor threads are used to process PGQL queries. However, if needed, the number of threads can be limited by setting the parallelism option of the In-Memory Analyst (PGX).
Parent topic: Best Practices for Tuning PGQL Queries
5.8.7.3 Query Plan Explaining
The PgxGraph.explainPgql(String query) method is used to
get insight into the query plan of the query. The method returns an instance of
Operation (package oracle.pgx.api) which has the following
methods:
print(): for printing the operation and its child operationsgetOperationType(): for getting the type of the operationgetPatternInfo(): for getting a string representation of the operationgetCostEstimate(): for getting the cost of the operationgetTotalCostEstimate(): for getting the cost of the operations and its child operationsgetCardinatlityEstimate(): for getting the expected number of result rowsgetChildren(): for accessing the child operations
Consider the following example:
g.explainPgql("SELECT COUNT(*) FROM MATCH (n) -[e1]-> (m) -[e2]-> (o)").print()
\--- GROUP BY GroupBy {"cardinality":"42", "cost":"42", "accumulatedCost":"58.1"}
\--- (m) -[e2]-> (o) NeighborMatch {"cardinality":"3.12", "cost":"3.12", "accumulatedCost":"16.1"}
\--- (n) -[e1]-> (m) NeighborMatch {"cardinality":"5", "cost":"5", "accumulatedCost":"13"}
\--- (n) RootVertexMatch {"cardinality":"8", "cost":"8", "accumulatedCost":"8"}
In the above example, the print() method is used to
print the query plan.
If a query plan is not optimal, it is often possible to rewrite the query to improve
its performance. For example, a SELECT query may be split into an
UPDATE and a SELECT query as a way to improve
the total runtime.
Note that the In-Memory Analyst (PGX) does not provide a hint mechanism.
Parent topic: Best Practices for Tuning PGQL Queries
5.9 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 5-1 PGQL on Oracle Database (RDBMS)
Description of "Figure 5-1 PGQL on Oracle Database (RDBMS)"
The basic execution flow is:
-
The PGQL query is submitted to PGQL on RDBMS through a Java API.
-
The PGQL query is translated to SQL.
-
The translated SQL is submitted to Oracle Database by JDBC.
-
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.
- PGQL Features Supported
- Creating Property Graphs through CREATE PROPERTY GRAPH Statements
- Dropping Property Graphs through DROP PROPERTY GRAPH Statements
- Using the oracle.pg.rdbms.pgql Java Package to Execute PGQL Queries
- Modifying Property Graphs through INSERT, UPDATE, and DELETE Statements
- Performance Considerations for PGQL Queries
Parent topic: Property Graph Query Language (PGQL)
5.9.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
COSTfunctions - Case-insensitive matching of uppercased references to labels and properties
In addition, the following features of PGQL require special consideration.
Parent topic: Executing PGQL Queries Directly Against Oracle Database
5.9.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.
Parent topic: PGQL Features Supported
5.9.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 5-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)
Parent topic: PGQL Features Supported
5.9.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.
Parent topic: PGQL Features Supported
5.9.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 5-3 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 5-4 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 | +-----------------------------------------+
Parent topic: Executing PGQL Queries Directly Against Oracle Database
5.9.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 5-5 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();
}
}
}
}
Parent topic: Executing PGQL Queries Directly Against Oracle Database
5.9.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 21c, 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,
- Basic Query Execution
- Security Techniques for PGQL Queries
- Using a Text Index with PGQL Queries
- Obtaining the SQL Translation for a PGQL Query
- Additional Options for PGQL Translation and Execution
- Querying Another User’s Property Graph
- Using Query Optimizer Hints with PGQL
Parent topic: Executing PGQL Queries Directly Against Oracle Database
5.9.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 5-6 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 5-7 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 5-8 PgqlExample2.java
PgqlExample2.java shows a PGQL query with a temporal filter on an edge property.
PgqlResultSetprovides an interface for consuming the query result that is very similar to thejava.sql.ResultSetinterface.- A
next()method allows moving through the query result, and aclose()method allows releasing resources after the application is fiished reading the query result. - In addition,
PgqlResultSetprovides getters forString,Integer,Long,Float,Double,Boolean,LocalDateTime, andOffsetDateTime, and it provides a genericgetObject()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 5-9 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 5-10 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 | +--------+
5.9.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.
5.9.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 5-11 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 5-12 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 | +-----------------+
Parent topic: Security Techniques for PGQL Queries
5.9.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), "");Parent topic: Security Techniques for PGQL Queries
5.9.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 5-13 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 | +-----------------------+
5.9.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 5-14 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 5-15 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 --
5.9.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
executeQueryandtranslateQuery -
Through flags in the
optionsstring argument ofexecuteQueryandtranslateQuery -
Through Java JVM arguments.
The following table summarizes the available query arguments for PGQL translation and execution.
Table 5-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 |
5.9.4.5.1 Query Options Controlled by Explicit Arguments
Some query options are controlled by explicit arguments to methods in the Java API.
-
The
executeQuerymethod ofPgqlStatementhas explicit arguments for timeout in seconds, degree of parallelism, optimizer dynamic sampling, and maximum number of results. -
The
translateQuerymethod has explicit arguments for degree of parallelism, optimizer dynamic sampling, and maximum number of results.PgqlPreparedStatementalso provides those same additional arguments forexecuteQueryandtranslateQuery.
Example 5-16 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 | +-----------------+
Parent topic: Additional Options for PGQL Translation and Execution
5.9.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 5-17 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
Parent topic: Additional Options for PGQL Translation and Execution
5.9.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 theoptionsargument or specify-Doracle.pg.rdbms.pgql.useRW=falsein the Java command line. -
Distinct Modifier in Recursive WITH: To use the DISTINCT modifier in the recursive step, first set
"_recursive_with_control"=8in your database session, then specify'USE_DIST_RW=T' in theoptionsargument or specify-Doracle.pg.rdbms.pgql.useDistRW=truein 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 queryoptionsargument or specify-Doracle.pg.rdbms.pgql.maxPathLen=nin the Java command line.
Example 5-18 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 5-19 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 | +-----------------+
Parent topic: Additional Options for PGQL Translation and Execution
5.9.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 eachOracleVertexobject constructed from your PGQL query result set.
Example 5-20 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}
Parent topic: Additional Options for PGQL Translation and Execution
5.9.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 5-21 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 | +---------------+
5.9.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 5-22 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$ | -----------------------------------------------------------------------
5.9.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 5-23 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 5-24 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 5-25 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 5-26 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.
Parent topic: Executing PGQL Queries Directly Against Oracle Database
5.9.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 optionsstring argument ofexecute - Through Java JVM arguments.
The following table summarizes the main options for modifying PGQL statement execution.
Table 5-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 |
5.9.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 5-27 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 | +----------+
Parent topic: Additional Options for PGQL Statement Execution
5.9.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 5-28 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
Parent topic: Additional Options for PGQL Statement Execution
5.9.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.
Parent topic: Executing PGQL Queries Directly Against Oracle Database