6.9.1.4.6.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 6-19 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 6-20 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