6.9.1.4.8 PGQLでの問合せオプティマイザ・ヒントの使用
Java APIでは、PGQL問合せを実行する際の結合タイプに影響を与える問合せオプティマイザ・ヒントが許可されます。PgqlStatementおよびPgqlPreparedStatement内のexecuteQueryメソッドおよびtranslateQueryメソッドは、対応するSQL問合せの問合せ計画に影響を与えるために、options引数で次の文字列を受け入れます。
-
ALL_EDGE_NL – $GE表と$GT表に関連するすべての結合にネステッド・ループ結合を使用します。
-
ALL_EDGE_HASH – $GE表と$GT表に関連するすべての結合にHASH結合を使用します。
-
ALL_VERTEX_NL – $VT表に関連するすべての結合にネステッド・ループ結合を使用します。
-
ALL_VERTEX_HASH – $VT表に関連するすべての結合にHASH結合を使用します。
例6-23 PgqlExample16.java
PgqlExample16.javaは、オプティマイザ・ヒントを使用して、グラフ・トラバースに使用される結合に影響を与える方法を示しています。
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();
}
}
}
}
test_graphのPgqlExample16.javaの出力(GraphLoaderExample.javaコードを使用してロードできます)は、次のとおりです。
-- 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)
ALL_EDGE_HASHを使用する最初の問合せの問合せ計画は次のようになります。
----------------------------------------------- | 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$ | -----------------------------------------------
ALL_EDGE_NLを使用する2番目の問合せの問合せ計画は次のようになります。
----------------------------------------------------------------------- | 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$ | -----------------------------------------------------------------------