6.9.1.4.6.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 6-18 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