6.9.1.4.8 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 6-23 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$ | -----------------------------------------------------------------------