4 SQLベースのプロパティ・グラフの問合せおよび分析
SQLを使用して、Oracle Spatial and Graphのプロパティ・グラフ・データを問合せることができます。
Oracle Spatial and Graphのプロパティ・グラフ・サポートのために、すべての頂点とエッジはリレーショナル形式でOracleデータベースに保存されます。Oracle Spatial and Graphプロパティ・グラフ・スキーマ・オブジェクトの詳細は、「Oracle Databaseのプロパティ・グラフ・スキーマ・オブジェクト」を参照してください。
この章では、SQLを使用して実装された一般的なグラフの問合せの例を示します。対象者には、DBAと、SQL構文およびプロパティ・グラフ・スキーマ・オブジェクトを理解しているアプリケーション開発者が含まれます。
SQLを使用してプロパティ・グラフを直接問合せするメリットには次のものがあります。
-
Oracleデータベースの外にデータを持ち出す必要がありません。
-
Oracleデータベースにより提供される業界で実績のあるSQLエンジンを活用できます。
-
別のデータ・タイプ(リレーショナル、JSON、XMLなど)のプロパティ・グラフ・データを容易に結合または統合できます。
-
既存のOracle SQLチューニングおよびデータベース管理ツールおよびユーザー・インタフェースを活用できます。
この例では、現在のスキーマにconnectionsという名前のプロパティ・グラフがあると想定しています。このSQL問合せと出力例は、説明のためだけのものであり、実際の出力は、connectionsグラフのデータにより異なる可能性があります。いくつかの例では、出力は、読みやすくするために変更が加えられています。
- 単純なプロパティ・グラフ問合せ
このトピックの例では、グラフの頂点、エッジ、およびプロパティを問合せます。 - プロパティ・グラフのテキスト問合せ
プロパティの値(頂点プロパティまたはエッジ・プロパティ)にフリー・テキストが含まれている場合、V列にOracle Text索引を作成するのを手助けできます。 - ナビゲーションおよびグラフ・パターン一致
グラフ・データ・モデルの使用の主なメリットは、リンクやエッジとしてモデル化された関係を使用して、頂点としてモデル化されたエンティティ(人、ムービー、製品、サービス、イベントなど)間を容易に移動できることです。さらに、パターンの検出、個人の集計、傾向の分析などを実行するために、グラフ一致テンプレートを定義することができます。 - ナビゲーション・オプション: CONNECT BYおよび並列反復
CONNECT BY句および並列反復は、高度なナビゲーションおよび問合せのオプションを提供します。 - Pivot
PIVOT句により、動的に列を表に追加して、新しい表を作成できます。 - SQLベース・プロパティ・グラフ分析
インメモリー・アナリストで提供される分析機能に加えて、Oracle Spatial and Graphのプロパティ・グラフ機能は、いくつかのネイティブな、SQLベースのプロパティ・グラフ分析をサポートします。 - プロパティ・グラフ問合せ言語(PGQL)
PGQLは、ノードからなり、エッジにより別のノードに接続され、それぞれが関連付けられたキー値ペア(プロパティ)を持つことができる、プロパティ・グラフ・データ構造のためのSQLライクな問合せ言語です。 - Oracle Databaseに対して直接PGQL問合せを実行
このトピックでは、(インメモリーと対照的に)Oracle Database内のグラフに対して直接PGQL問合せを実行する方法について説明します。
4.1 単純なプロパティ・グラフ問合せ
このトピックの例では、グラフの頂点、エッジ、およびプロパティを問合せます。
例4-1 指定した頂点IDの頂点の検索
この例では、connectionsグラフの頂点IDが1の頂点を検索します。
SQL> select vid, k, v, vn, vt
from connectionsVT$
where vid=1;
出力は次のようになります。
1 country United States
1 name Barack Obama
1 occupation 44th president of United States of America
...
例4-2 指定したエッジIDのエッジの検索
この例では、connectionsグラフのエッジIDが100のエッジを検索します。
SQL> select eid,svid,dvid,k,t,v,vn,vt
from connectionsGE$
where eid=1000;
出力は次のようになります。
1000 1 2 weight 3 1 1
前の出力で、エッジ・プロパティのKは"weight"で、値のタイプIDは3で、浮動小数点の値を示しています。
例4-3 単純なカウントの実行
この例では、connectionsグラフで単純なカウントを実行します。
SQL> -- Get the total number of K/V pairs of all the vertices
SQL> select /*+ parallel */ count(1)
from connectionsVT$;
299
SQL> -- Get the total number of K/V pairs of all the edges
SQL> select /*+ parallel(8) */ count(1)
from connectionsGE$;
164
SQL> -- Get the total number of vertices
SQL> select /*+ parallel */ count(distinct vid)
from connectionsVT$;
78
SQL> -- Get the total number of edges
SQL> select /*+ parallel */ count(distinct eid)
from connectionsGE$;
164
例4-4 使用しているプロパティ・キーのセットの取得
この例では、connectionsグラフの頂点で使用されているプロパティ・キーのセットを取得します。
SQL> select /*+ parallel */ distinct k
from connectionsVT$;
company
show
occupation
type
team
religion
criminal charge
music genre
genre
name
role
political party
country
13 rows selected.
SQL> -- get the set of property keys used for edges
SQL> select /*+ parallel */ distinct k
from connectionsGE$;
weight
例4-5 値のある頂点の検索
この例では、connectionsグラフで、文字列型の値(任意のプロパティ)を持つ頂点、大文字小文字にかかわらずa、e、i、o、uが隣接して2つ含まれる値とその場所を検索します。
SQL> select vid, t, k, v
from connectionsVT$
where t=1
and regexp_like(v, '([aeiou])\1', 'i');
6 1 name Jordan Peele
6 1 show Key and Peele
54 1 name John Green
...
前述のような種類の問合せでは、どの正規表現が使用されるかを事前に知ることは困難なので、B-Tree索引を活用するのは通常困難です。上のような問合せの場合、次の実行計画を入手できます。表の完全スキャンはオプティマイザーにより選択されることに注意してください。
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 795 | 28 (0)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 15 | 795 | 28 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 15 | 795 | 28 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| CONNECTIONSVT$ | 15 | 795 | 28 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(INTERNAL_FUNCTION("V") AND REGEXP_LIKE ("V",U'([aeiou])\005C1','i') AND "T"=1 AND INTERNAL_FUNCTION("K"))
Note
-----
- Degree of Parallelism is 2 because of table property
Oracleデータベースのインメモリー・オプションが利用可能でメモリーも十分にある場合、これにより表(表全体、または関係する列のセット)をメモリーに配置するのを手助けできます。これを実行する方法の1つを次に示します。
SQL> alter table connectionsVT$ inmemory;
Table altered.
正規表現を含む同じSQLを入力すると、"TABLE ACCESS INMEMORY FULL"を実行する計画が表示されます。
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 795 | 28 (0)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 15 | 795 | 28 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 15 | 795 | 28 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS INMEMORY FULL| CONNECTIONSVT$ | 15 | 795 | 28 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(INTERNAL_FUNCTION("V") AND REGEXP_LIKE ("V",U'([aeiou])\005C1','i') AND "T"=1 AND INTERNAL_FUNCTION("K"))
Note
-----
- Degree of Parallelism is 2 because of table property
4.2 プロパティ・グラフのテキスト問合せ
プロパティの値(頂点プロパティまたはエッジ・プロパティ)にフリー・テキストが含まれている場合、V列にOracle Text索引を作成するのを手助けできます。
Oracle Textではデータベースに直接格納されたテキストを処理できます。テキストは短い文字列(名前やアドレスなど)でも、ドキュメント全体でもかまいません。ドキュメントは様々なテキスト形式をとることができます。
テキストは数多くの様々な言語でもかまいません。Oracle Textはスペースで区切られた言語(GreekやCyrillicなどのキャラクタ・セットを含む)を処理できます。さらに、Oracle Textは中国語、日本語および韓国語の絵文字的言語も処理できます。
プロパティ・グラフ機能はUnicodeをより適切にサポートするためにNVARCHARタイプの列を使用するので、データベースのキャラクタ・セットとしてUTF8 (AL32UTF8)を使用することを強くお薦めします。
頂点表(またはエッジ表)にOracle Text索引を作成するには、ALTER SESSION権限が必要です。次に例を示します。
SQL> grant alter session to <YOUR_USER_SCHEMA_HERE>;カスタマイズが必要な場合、CTX_DDLにEXECUTE権限も付与します。
SQL> grant execute on ctx_ddl to <YOUR_USER_SCHEMA_HERE>;次に、SCOTTにこれらの権限を付与する文の例をいくつか示します。
SQL> conn / as sysdba
Connected.
SQL> -- This is a PDB setup --
SQL> alter session set container=orcl;
Session altered.
SQL> grant execute on ctx_ddl to scott;
Grant succeeded.
SQL> grant alter session to scott;
Grant succeeded.
例4-6 テキスト索引の作成
この例では、SCOTTスキーマのconnectionsグラフの頂点表(V列)にOracle Text索引を作成します。Apache LuceneまたはApache SolrCloudより提供されるテキスト索引機能と違い、ここで作成されるOracle Text索引は、プロパティ・キー1つだけまたはそのサブセットに対してではなく、すべてのプロパティ・キーに対するものであることに注意してください。さらに、新しいプロパティがグラフに追加され、プロパティ値が文字列データ型である場合、それは同じテキスト索引に自動的に含まれます。
この例はMDSYSが所有するOPG_AUTO_LEXERレクサーを使用します。
SQL> execute opg_apis.create_vertices_text_idx('scott', 'connections', pref_owner=>'MDSYS', lexer=>'OPG_AUTO_LEXER', dop=>2);カスタマイズが必要な場合、ctx_ddl.create_preference APIを使用できます。次に例を示します。
SQL> -- The following requires access privilege to CTX_DDL
SQL> exec ctx_ddl.create_preference('SCOTT.OPG_AUTO_LEXER', 'AUTO_LEXER');
PL/SQL procedure successfully completed.
SQL> execute opg_apis.create_vertices_text_idx('scott', 'connections', pref_owner=>'scott', lexer=>'OPG_AUTO_LEXER', dop=>2);
PL/SQL procedure successfully completed.
Oracle Textが提供する豊富な関数のセットを使用して、グラフ要素に対する問合せを実行できるようになりました。
注意:
Oracle Text索引が必要なくなった場合、drop_vertices_text_idxまたはopg_apis.drop_edges_text_idx APIを使用して削除することができます。次の文は、SCOTTが所有するconnectionsという名前のグラフの頂点およびエッジのテキスト索引を削除します。
SQL> exec opg_apis.drop_vertices_text_Idx('scott', 'connections');
SQL> exec opg_apis.drop_edges_text_Idx('scott', 'connections');例4-7 プロパティ値を持つ頂点の検索
次の例は、キーワード"Obama"を含むプロパティ値(文字列型)を持つ頂点を検索します。
SQL> select vid, k, t, v
from connectionsVT$
where t=1
and contains(v, 'Obama', 1) > 0
order by score(1) desc
;
前の文からの出力およびSQL実行計画は次のように表示されます。DOMAIN INDEXは実行計画で操作として表示されることに注意してください。
1 name 1 Barack Obama
Execution Plan
----------------------------------------------------------
Plan hash value: 1619508090
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 56 | 5 (20) | 00:00:01 | | |
| 1 | SORT ORDER BY | | 1 | 56 | 5 (20) | 00:00:01 | | |
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| CONNECTIONSVT$ | 1 | 56 | 4 (0)| 00:00:01 | ROWID | ROWID |
|* 3 | DOMAIN INDEX | CONNECTIONSXTV$ | | | 4 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T"=1 AND INTERNAL_FUNCTION("K") AND INTERNAL_FUNCTION("V"))
3 - access("CTXSYS"."CONTAINS"("V",'Obama',1)>0)
例4-8 ファジー・マッチ
次の例は、ファジー・マッチが使用されたバリアント"ameriian"(この例のための故意のスペルミス)を含むプロパティ値(文字列型)を持つ頂点を検索します。
SQL> select vid, k, t, v
from connectionsVT$
where contains(v, 'fuzzy(ameriian,,,weight)', 1) > 0
order by score(1) desc;
前の文からの出力およびSQL実行計画は次のように表示されます。
8 role 1 american business man
9 role 1 american business man
4 role 1 american economist
6 role 1 american comedian actor
7 role 1 american comedian actor
1 occupation 1 44th president of United States of America
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1619508090
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 56 | 5 (20)| 00:00:01 | | |
| 1 | SORT ORDER BY | | 1 | 56 | 5 (20)| 00:00:01 | | |
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| CONNECTIONSVT$ | 1 | 56 | 4 (0)| 00:00:01 | ROWID | ROWID |
|* 3 | DOMAIN INDEX | CONNECTIONSXTV$ | | | 4 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(INTERNAL_FUNCTION("K") AND INTERNAL_FUNCTION("V"))
例4-9 問合せ緩和
次の例は、問合せ緩和を実装した高度なOracle Text問合せで、これを使用すると、最も制限されたバージョンの問合せを最初に実行して、必要な一致数を得るまで、問合せを徐々に緩和できます。問合せ緩和を複数の文字列を含む問合せとともに使用すると、他の可能性のある一致よりも先に結果に出現するように、“最適な”一致を決定するための指針を与えることができます。
この例は、"american actor"を問合せ緩和のシーケンスを使用して検索します。
SQL> select vid, k, t, v
from connectionsVT$
where CONTAINS (v,
'<query>
<textquery lang="ENGLISH" grammar="CONTEXT">
<progression>
<seq>{american} {actor}</seq>
<seq>{american} NEAR {actor}</seq>
<seq>{american} AND {actor}</seq>
<seq>{american} ACCUM {actor}</seq>
</progression>
</textquery>
<score datatype="INTEGER" algorithm="COUNT"/>
</query>') > 0;
前の文からの出力およびSQL実行計画は次のように表示されます。
7 role 1 american comedian actor
6 role 1 american comedian actor
44 occupation 1 actor
8 role 1 american business man
53 occupation 1 actor film producer
52 occupation 1 actor
4 role 1 american economist
47 occupation 1 actor
9 role 1 american business man
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2158361449
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 56 | 4 (0)| 00:00:01 | | |
|* 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| CONNECTIONSVT$ | 1 | 56 | 4 (0)| 00:00:01 | ROWID | ROWID |
|* 2 | DOMAIN INDEX | CONNECTIONSXTV$ | | | 4 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(INTERNAL_FUNCTION("K") AND INTERNAL_FUNCTION("V"))
2 - access("CTXSYS"."CONTAINS"("V",'<query> <textquery lang="ENGLISH" grammar="CONTEXT">
<progression> <seq>{american} {actor}</seq> <seq>{american} NEAR {actor}</seq>
<seq>{american} AND {actor}</seq> <seq>{american} ACCUM {actor}</seq> </progression>
</textquery> <score datatype="INTEGER" algorithm="COUNT"/> </query>')>0)
例4-10 エッジの検索
頂点と同じように、プロパティ・グラフのエッジ表(GE$)のV列にOracle Text索引を作成できます。次の例はMDSYSが所有するOPG_AUTO_LEXERレクサーを使用します。
SQL> exec opg_apis.create_edges_text_idx('scott', 'connections', pref_owner=>'mdsys', lexer=>'OPG_AUTO_LEXER', dop=>4);カスタマイズが必要な場合、ctx_ddl.create_preference APIを使用できます。
4.3 ナビゲーションおよびグラフ・パターン一致
グラフ・データ・モデルの使用の主なメリットは、リンクやエッジとしてモデル化された関係を使用して、頂点としてモデル化されたエンティティ(人、ムービー、製品、サービス、イベントなど)間を容易に移動できることです。さらに、パターンの検出、個人の集計、傾向の分析などを実行するために、グラフ一致テンプレートを定義することができます。
このトピックは、connectionsという名前のプロパティ・グラフの例を使用して、グラフのナビゲーションおよびパターン一致の例を示します。ほとんどのSQL文は比較的単純ですが、より高度な要件を実装するためのブロックの構築としても使用できます。単純なものから始めて、次第に複雑さを増していくのが一般的には最適です。
例4-11 人の協力者は誰ですか
次のSQL文は、ID 1の頂点が協力するすべてのエンティティを検索します。単純化するために、出力の関係のみ考えます。
SQL> select dvid, el, k, vn, v
from connectionsGE$
where svid=1
and el='collaborates';
注意:
対象となる特定の頂点IDを検索するには、キーワードまたはファジー・マッチを使用して、プロパティ・グラフでテキスト問合せを実行できます。(詳細と例については、「プロパティ・グラフのテキスト問合せ」を参照してください)
前の例の出力および実行計画は次のようになります。
2 collaborates weight 1 1
21 collaborates weight 1 1
22 collaborates weight 1 1
....
26 collaborates weight 1 1
10 rows selected.
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 460 | 2 (0)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 10 | 460 | 2 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 3 | PX PARTITION HASH ALL | | 10 | 460 | 2 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| CONNECTIONSGE$ | 10 | 460 | 2 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWP | |
|* 5 | INDEX RANGE SCAN | CONNECTIONSXSE$ | 20 | | 1 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(INTERNAL_FUNCTION("EL") AND "EL"=U'collaborates' AND INTERNAL_FUNCTION("K") AND INTERNAL_FUNCTION("V"))
5 - access("SVID"=1)
例4-12 人の協力者は誰でその職業は何ですか
次のSQL文はID 1の頂点の協力者と、各協力者の職業を検索します。頂点表(VT$)との結合が必要です。
SQL> select dvid, vertices.v
from connectionsGE$, connectionsVT$ vertices
where svid=1
and el='collaborates'
and dvid=vertices.vid
and vertices.k='occupation';
前の例の出力および実行計画は次のようになります。
21 67th United States Secretary of State
22 68th United States Secretary of State
23 chancellor
28 7th president of Iran
19 junior United States Senator from New York
...
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 525 | 7 (0)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 7 | 525 | 7 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 3 | NESTED LOOPS | | 7 | 525 | 7 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 4 | PX PARTITION HASH ALL | | 10 | 250 | 2 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| CONNECTIONSGE$ | 10 | 250 | 2 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWP | |
|* 6 | INDEX RANGE SCAN | CONNECTIONSXSE$ | 20 | | 1 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWP | |
| 7 | PARTITION HASH ITERATOR | | 1 | | 0 (0)| 00:00:01 | KEY | KEY | Q1,00 | PCWP | |
|* 8 | TABLE ACCESS BY LOCAL INDEX ROWID | CONNECTIONSVT$ | | | | | KEY | KEY | Q1,00 | PCWP | |
|* 9 | INDEX UNIQUE SCAN | CONNECTIONSXQV$ | 1 | | 0 (0)| 00:00:01 | KEY | KEY | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(INTERNAL_FUNCTION("EL") AND "EL"=U'collaborates')
6 - access("SVID"=1)
8 - filter(INTERNAL_FUNCTION("VERTICES"."V"))
9 - access("DVID"="VERTICES"."VID" AND "VERTICES"."K"=U'occupation')
filter(INTERNAL_FUNCTION("VERTICES"."K"))
例4-13 人の敵対者の検索と国別の集計
次のSQL文はID 1の頂点の敵対者(つまり、feuds関係)を検索し、それらを国別に集計します。頂点表(VT$)との結合が必要です。
SQL> select vertices.v, count(1)
from connectionsGE$, connectionsVT$ vertices
where svid=1
and el='feuds'
and dvid=vertices.vid
and vertices.k='country'
group by vertices.v;
例の出力および実行計画は次のようになります。この場合、ID 1の頂点には米国に3人、ロシアに1人敵対者がいます。
United States 3
Russia 1
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 375 | 5 (20)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 5 | 375 | 5 (20)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 5 | 375 | 5 (20)| 00:00:01 | | | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 5 | 375 | 5 (20)| 00:00:01 | | | Q1,01 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 5 | 375 | 5 (20)| 00:00:01 | | | Q1,00 | P->P | HASH |
| 6 | HASH GROUP BY | | 5 | 375 | 5 (20)| 00:00:01 | | | Q1,00 | PCWP | |
| 7 | NESTED LOOPS | | 5 | 375 | 4 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 8 | PX PARTITION HASH ALL | | 5 | 125 | 2 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWC | |
|* 9 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| CONNECTIONSGE$ | 5 | 125 | 2 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWP | |
|* 10 | INDEX RANGE SCAN | CONNECTIONSXSE$ | 20 | | 1 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWP | |
| 11 | PARTITION HASH ITERATOR | | 1 | | 0 (0)| 00:00:01 | KEY | KEY | Q1,00 | PCWP | |
|* 12 | TABLE ACCESS BY LOCAL INDEX ROWID | CONNECTIONSVT$ | | | | | KEY | KEY | Q1,00 | PCWP | |
|* 13 | INDEX UNIQUE SCAN | CONNECTIONSXQV$ | 1 | | 0 (0)| 00:00:01 | KEY | KEY | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - filter(INTERNAL_FUNCTION("EL") AND "EL"=U'feuds')
10 - access("SVID"=1)
12 - filter(INTERNAL_FUNCTION("VERTICES"."V"))
13 - access("DVID"="VERTICES"."VID" AND "VERTICES"."K"=U'country')
filter(INTERNAL_FUNCTION("VERTICES"."K"))
例4-14 人の協力者の検索と集計およびソート
次のSQL文はID 1の頂点の協力者を検索し、国別に集計し、昇順にソートします。
SQL> select vertices.v, count(1)
from connectionsGE$, connectionsVT$ vertices
where svid=1
and el='collaborates'
and dvid=vertices.vid
and vertices.k='country'
group by vertices.v
order by count(1) asc;
例の出力および実行計画は次のようになります。この場合、ID 1の頂点には米国に最も多くの協力者がいます。
Germany 1
Japan 1
Iran 1
United States 7
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 750 | 9 (23)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10002 | 10 | 750 | 9 (23)| 00:00:01 | | | Q1,02 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 10 | 750 | 9 (23)| 00:00:01 | | | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 10 | 750 | 9 (23)| 00:00:01 | | | Q1,02 | PCWP | |
| 5 | PX SEND RANGE | :TQ10001 | 10 | 750 | 9 (23)| 00:00:01 | | | Q1,01 | P->P | RANGE |
| 6 | HASH GROUP BY | | 10 | 750 | 9 (23)| 00:00:01 | | | Q1,01 | PCWP | |
| 7 | PX RECEIVE | | 10 | 750 | 9 (23)| 00:00:01 | | | Q1,01 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 10 | 750 | 9 (23)| 00:00:01 | | | Q1,00 | P->P | HASH |
| 9 | HASH GROUP BY | | 10 | 750 | 9 (23)| 00:00:01 | | | Q1,00 | PCWP | |
| 10 | NESTED LOOPS | | 10 | 750 | 7 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 11 | PX PARTITION HASH ALL | | 10 | 250 | 2 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWC | |
|* 12 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | CONNECTIONSGE$ | 10 | 250 | 2 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWP | |
|* 13 | INDEX RANGE SCAN | CONNECTIONSXSE$ | 20 | | 1 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWP | |
| 14 | PARTITION HASH ITERATOR | | 1 | | 0 (0)| 00:00:01 | KEY | KEY | Q1,00 | PCWP | |
|* 15 | TABLE ACCESS BY LOCAL INDEX ROWID | CONNECTIONSVT$ | | | | | KEY | KEY | Q1,00 | PCWP | |
|* 16 | INDEX UNIQUE SCAN | CONNECTIONSXQV$ | 1 | | 0 (0)| 00:00:01 | KEY | KEY | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
12 - filter(INTERNAL_FUNCTION("EL") AND "EL"=U'collaborates')
13 - access("SVID"=1)
15 - filter(INTERNAL_FUNCTION("VERTICES"."V"))
16 - access("DVID"="VERTICES"."VID" AND "VERTICES"."K"=U'country')
filter(INTERNAL_FUNCTION("VERTICES"."K"))
4.4 ナビゲーション・オプション: CONNECT BYおよび並列反復
CONNECT BY句および並列反復は、高度なナビゲーションおよび問合せのオプションを提供します。
-
CONNECT BY句により、一致を階層的な順序でナビゲートおよび検索することができます。出力エッジを追うために、前のdvid = svidを使用してナビゲーションの手順を示すことができます。
-
並列反復により、指定したホップ数までナビゲーションを実行できます。
例ではconnectionsという名前のプロパティ・グラフを使用します。
例4-15 CONNECT WITH
次のSQL文は出力エッジを1ホップだけ進みます。
SQL> select G.dvid
from connectionsGE$ G
start with svid = 1
connect by nocycle prior dvid = svid and level <= 1;
前の例の出力および実行計画は次のようになります。
2
3
4
5
6
7
8
9
10
...
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 273 | 3 (67)| 00:00:01 | | | | | |
|* 1 | CONNECT BY WITH FILTERING| | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 2 | 12 | 0 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 4 | PX PARTITION HASH ALL | | 2 | 12 | 0 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWC | |
|* 5 | INDEX RANGE SCAN | CONNECTIONSXSE$ | 2 | 12 | 0 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWP | |
|* 6 | FILTER | | | | | | | | | | |
| 7 | NESTED LOOPS | | 5 | 95 | 1 (0)| 00:00:01 | | | | | |
| 8 | CONNECT BY PUMP | | | | | | | | | | |
| 9 | PARTITION HASH ALL | | 2 | 12 | 0 (0)| 00:00:01 | 1 | 8 | | | |
|* 10 | INDEX RANGE SCAN | CONNECTIONSXSE$ | 2 | 12 | 0 (0)| 00:00:01 | 1 | 8 | | | |
------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("SVID"=PRIOR "DVID")
filter(LEVEL<=2)
5 - access("SVID"=1)
6 - filter(LEVEL<=2)
10 - access("connect$_by$_pump$_002"."prior dvid "="SVID")
1ホップから複数ホップに拡張するには、前の例の1を別の整数に変更します。たとえば、2ホップに変更するには、次のように指定します。level <= 2
例4-16 並列反復
次のSQL文はWITH句内で反復を使用して、4ホップまでのナビゲーションを実行します。反復的に定義されたグラフ拡張: g_expの使用は、問合せ内のg_expを参照し、それが反復を定義します。この例はまた、パラレル実行のためのPARALLELオプティマイザ・ヒントも使用します。
SQL> WITH g_exp(svid, dvid, depth) as
(
select svid as svid, dvid as dvid, 0 as depth
from connectionsGE$
where svid=1
union all
select g2.svid, g1.dvid, g2.depth + 1
from g_exp g2, connectionsGE$ g1
where g2.dvid=g1.svid
and g2.depth <= 3
)
select /*+ parallel(4) */ dvid, depth
from g_exp
where svid=1
;
例の出力および実行計画は次のようになります。CURSOR DURATION MEMORYが実行中に選択され、グラフ拡張がメモリーに中間データを格納することを示します。
22 4
25 4
24 4
1 4
23 4
33 4
22 4
22 4
... ...
Execution Plan
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 801 | 31239 | 147 (0)| 00:00:01 | | | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6614_11CB2D2 | | | | | | | | | |
| 3 | UNION ALL (RECURSIVE WITH) BREADTH FIRST | | | | | | | | | | |
| 4 | PX COORDINATOR | | | | | | | | | | |
| 5 | PX SEND QC (RANDOM) | :TQ20000 | 2 | 12 | 0 (0)| 00:00:01 | | | Q2,00 | P->S | QC (RAND) |
| 6 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6614_11CB2D2 | | | | | | | Q2,00 | PCWP | |
| 7 | PX PARTITION HASH ALL | | 2 | 12 | 0 (0)| 00:00:01 | 1 | 8 | Q2,00 | PCWC | |
|* 8 | INDEX RANGE SCAN | CONNECTIONSXSE$ | 2 | 12 | 0 (0)| 00:00:01 | 1 | 8 | Q2,00 | PCWP | |
| 9 | PX COORDINATOR | | | | | | | | | | |
| 10 | PX SEND QC (RANDOM) | :TQ10000 | 799 | 12M| 12 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 11 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6614_11CB2D2 | | | | | | | Q1,00 | PCWP | |
|* 12 | HASH JOIN | | 799 | 12M| 12 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 13 | BUFFER SORT (REUSE) | | | | | | | | Q1,00 | PCWP | |
| 14 | PARTITION HASH ALL | | 164 | 984 | 2 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWC | |
| 15 | INDEX FAST FULL SCAN | CONNECTIONSXDE$ | 164 | 984 | 2 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWP | |
| 16 | PX BLOCK ITERATOR | | | | | | | | Q1,00 | PCWC | |
|* 17 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6614_11CB2D2 | | | | | | | Q1,00 | PCWP | |
| 18 | PX COORDINATOR | | | | | | | | | | |
| 19 | PX SEND QC (RANDOM) | :TQ30000 | 801 | 31239 | 135 (0)| 00:00:01 | | | Q3,00 | P->S | QC (RAND) |
|* 20 | VIEW | | 801 | 31239 | 135 (0)| 00:00:01 | | | Q3,00 | PCWP | |
| 21 | PX BLOCK ITERATOR | | 801 | 12M| 135 (0)| 00:00:01 | | | Q3,00 | PCWC | |
| 22 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6614_11CB2D2 | 801 | 12M| 135 (0)| 00:00:01 | | | Q3,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("SVID"=1)
12 - access("G2"."DVID"="G1"."SVID")
17 - filter("G2"."INTERNAL_ITERS$"=LEVEL AND "G2"."DEPTH"<=3)
20 - filter("SVID"=1)
4.5 ピボット
PIVOT句により、動的に列を表に追加して、新しい表を作成できます。
プロパティ・グラフのスキーマ設計(VT$ and GE$)は、広い("fat")ではなく狭い("skinny")です。つまり、頂点またはエッジに複数のプロパティがある場合、これらのプロパティ・キー、値、データ型などは、複数の列ではなく複数の行を使用して格納されます。このような設計は、追加する列が多すぎる、または表の物理的な列数の制限に達することを心配する必要なく、プロパティを動的に追加できるという意味で、非常に柔軟です。しかし、アプリケーションによっては、プロパティが多少均一ならば、広い表の方が好まれることもあります。
例4-17 ピボット
次のCREATE TABLE ... AS SELECT文はPIVOTを使用して4つの列、‘company’、’occupation’、’name’および‘religion’を追加します。
SQL> CREATE TABLE table pg_wide
as
with G AS (select vid, k, t, v
from connectionsVT$
)
select *
from G
pivot (
min(v) for k in ('company', 'occupation', 'name', 'religion')
);
Table created.
次のDESCRIBE文は、追加した4つの列を含む新しい表の定義を表示します。(出力は、読みやすくするために変更が加えられています。)
SQL> DESCRIBE pg_wide;
Name Null? Type
--------------------------------------------------- -------- --------------------
VID NOT NULL NUMBER
T NUMBER(38)
'company' NVARCHAR2(15000)
'occupation' NVARCHAR2(15000)
'name' NVARCHAR2(15000)
'religion' NVARCHAR2(15000)
4.6 SQLベース・プロパティ・グラフ分析
インメモリー・アナリストで提供される分析機能に加えて、Oracle Spatial and Graphのプロパティ・グラフ機能は、いくつかのネイティブな、SQLベースのプロパティ・グラフ分析をサポートします。
SQLベースの分析の利点は次のとおりです。
-
物理メモリー内に収まらない大規模なグラフの分析が容易になる
-
データベースの外部にグラフ・データが転送されないため分析コストが低くなる
-
プロパティ・グラフ・データベースの現在の状態を使用してより的確な分析を行う
-
インメモリー・グラフとグラフ・データベースの最新更新との同期手順をなくして分析をシンプルにする
ただし、グラフ(またはサブグラフ)がメモリーに収まっている場合、インメモリー・アナリストにより提供された実行中の分析は、SQLベースの分析を使用するよりも、通常、パフォーマンスは上回ります。
多くの分析の実装には中間データ構造が必要なため、ほとんどのSQL (およびPL/SQL)ベースの分析APIには作業表(wt)用のパラメータがあります。典型的なフローには、次の手順が含まれます。
-
作業表を準備します。
-
分析を実行します(1つまたは複数のコール)。
-
クリーンアップを実行します
次のサブトピックでは、一般的なタイプのプロパティ・グラフ分析の、SQLベースの例を示します。
4.6.1 最短パスの例
次の例は、SQLベースの最短パス分析を示しています。
例4-18 最短パスの設定および計算
たとえば、最短パスについて考えます。内部的に、Oracle Databaseは双方向Dijkstraアルゴリズムを使用します。次のコード・スニペットは、準備、実行、およびクリーンアップのワークフロー全体を示しています。
set serveroutput on
DECLARE
wt1 varchar2(100); -- intermediate working tables
n number;
path varchar2(1000);
weights varchar2(1000);
BEGIN
-- prepare
opg_apis.find_sp_prep('connectionsGE$', wt1);
dbms_output.put_line('working table name ' || wt1);
-- compute
opg_apis.find_sp(
'connectionsGE$',
1, -- start vertex ID
53, -- destination vertex ID
wt1, -- working table (for Dijkstra expansion)
dop => 1, -- degree of parallelism
stats_freq=>1000, -- frequency to collect statistics
path_output => path, -- shortest path (a sequence of vertices)
weights_output => weights, -- edge weights
options => null
);
dbms_output.put_line('path ' || path);
dbms_output.put_line('weights ' || weights);
-- cleanup (commented out here; see text after the example)
-- opg_apis.find_sp_cleanup('connectionsGE$', wt1);
END;
/
この例では、次のような出力が生成されます。作業表の名前が指定されていない場合、準備手順で、自動的に一時表の名前が生成され作成されます。一時作業表の名前にはセッションIDが使用されるので、出力はおそらく異なります。
working table name "CONNECTIONSGE$$TWFS12" path 1 3 52 53 weights 4 3 1 1 1 PL/SQL procedure successfully completed.
作用表の定義を知りたい場合は、クリーンアップ・フェーズをスキップします(find_sp_cleanupへのコールをコメントアウトしている前の例を参照)。計算が実行された後で、作業表を記述できます。
SQL> describe "CONNECTIONSGE$$TWFS12"
Name Null? Type
--------- -------- ----------------------------
NID NUMBER
D2S NUMBER
P2S NUMBER
D2T NUMBER
P2T NUMBER
F NUMBER(38)
B NUMBER(38)
インメモリーや高度な圧縮の使用などの様々な表作成オプションの試行を希望する上級ユーザーの場合、前の作業表を事前に作成して名前を渡すことができます。
例4-19 最短パス:作業表の作成および分析の実行
次の文は、最初に同じ列構造と基本的圧縮を有効にして作業表を作成してからそれをSQLベースの計算に渡す、いくつかの高度なオプションを示しています。このコードは、CREATE TABLE圧縮とインメモリーのオプションを使用して計算用の中間表を最適化します。
create table connections$MY_EXP(
NID NUMBER,
D2S NUMBER,
P2S NUMBER,
D2T NUMBER,
P2T NUMBER,
F NUMBER(38),
B NUMBER(38)
) compress nologging;
DECLARE
wt1 varchar2(100) := 'connections$MY_EXP';
n number;
path varchar2(1000);
weights varchar2(1000);
BEGIN
dbms_output.put_line('working table name ' || wt1);
-- compute
opg_apis.find_sp(
'connectionsGE$',
1,
53,
wt1,
dop => 1,
stats_freq=>1000,
path_output => path,
weights_output => weights,
options => null
);
dbms_output.put_line('path ' || path);
dbms_output.put_line('weights ' || weights);
-- cleanup
-- opg_apis.find_sp_cleanup('connectionsGE$', wt1);
END;
/
計算の終了時に、作業表が削除または切り捨てられていない場合は、次のように作業表の内容をチェックできます。作業表の構造はリリース間で異なる可能性があることに注意してください。
SQL> select * from connections$MY_EXP;
NID D2S P2S D2T P2T F B
---------- ---------- ---------- ---------- ---------- ---------- ----------
1 0 1.000E+100 1 -1
53 1.000E+100 0 -1 1
54 1.000E+100 1 53 -1 1
52 1.000E+100 1 53 -1 1
5 1 1 1.000E+100 0 -1
26 1 1 1.000E+100 0 -1
8 1000 1 1.000E+100 0 -1
3 1 1 2 52 0 0
15 1 1 1.000E+100 0 -1
21 1 1 1.000E+100 0 -1
19 1 1 1.000E+100 0 -1
...
例4-20 最短パス:同じグラフへの複数コールの実行
同じグラフへの複数のコールを実行するには、準備手順への1つのコールだけが必要です。次に、同じグラフ内で複数の頂点のペアの最短パスを計算する例を示します。
DECLARE
wt1 varchar2(100); -- intermediate working tables
n number;
path varchar2(1000);
weights varchar2(1000);
BEGIN
-- prepare
opg_apis.find_sp_prep('connectionsGE$', wt1);
dbms_output.put_line('working table name ' || wt1);
-- find shortest path from vertex 1 to vertex 53
opg_apis.find_sp( 'connectionsGE$', 1, 53,
wt1, dop => 1, stats_freq=>1000, path_output => path, weights_output => weights, options => null);
dbms_output.put_line('path ' || path);
dbms_output.put_line('weights ' || weights);
-- find shortest path from vertex 2 to vertex 36
opg_apis.find_sp( 'connectionsGE$', 2, 36,
wt1, dop => 1, stats_freq=>1000, path_output => path, weights_output => weights, options => null);
dbms_output.put_line('path ' || path);
dbms_output.put_line('weights ' || weights);
-- find shortest path from vertex 30 to vertex 4
opg_apis.find_sp( 'connectionsGE$', 30, 4,
wt1, dop => 1, stats_freq=>1000, path_output => path, weights_output => weights, options => null);
dbms_output.put_line('path ' || path);
dbms_output.put_line('weights ' || weights);
-- cleanup
opg_apis.find_sp_cleanup('connectionsGE$', wt1);
END;
/
この例の出力は次のようになります。指定した頂点の複数のペアに対し、3つの最短パスが見つかりました。
working table name "CONNECTIONSGE$$TWFS12" path 1 3 52 53 weights 4 3 1 1 1 path 2 36 weights 2 1 1 path 30 21 1 4 weights 4 3 1 1 1 PL/SQL procedure successfully completed.
4.6.2 協調フィルタリングの概要および例
協調フィルタリング(ソーシャル・フィルタとも呼ばれる)は、他の人の推奨を使用して情報をフィルタリングします。協調フィルタリングは、似た好みを持つ他の人の購入内容に基づいて購入を推奨するシステムで広く使用されます。
次の例では、SQLベースの協調フィルタリング分析を示します。
例4-21 協調フィルタリングの設定および計算
この例では、SQLベースの協調フィルタリングの使用方法、具体的には、行列因子分解を使用した顧客への電話ブランドのお薦め方法を示します。この例では、データベース内に「PHONES」というグラフが存在すると想定しています。このグラフ例には、顧客頂点と品目頂点、および一部の顧客頂点を他の一部の品目頂点にリンクする「評価」ラベルを持つエッジが含まれています。評価ラベルには、特定の顧客(エッジOUT頂点)が、指定された商品(エッジIN頂点)に割り当てた評価に対応する数値があります。
次の図は、このグラフを示しています。
次のコードは、行列因子分解アルゴリズムを内部で使用する、SQLベースの協調フィルタリング・アルゴリズムを実行するためのエンドツーエンド・フローを示しています。
set serveroutput on
DECLARE
wt_l varchar2(32); -- working tables
wt_r varchar2(32);
wt_l1 varchar2(32);
wt_r1 varchar2(32);
wt_i varchar2(32);
wt_ld varchar2(32);
wt_rd varchar2(32);
edge_tab_name varchar2(32) := 'phonesge$';
edge_label varchar2(32) := 'rating';
rating_property varchar2(32) := '';
iterations integer := 100;
min_error number := 0.001;
k integer := 5;
learning_rate number := 0.001;
decrease_rate number := 0.95;
regularization number := 0.02;
dop number := 2;
tablespace varchar2(32) := null;
options varchar2(32) := null;
BEGIN
-- prepare
opg_apis.cf_prep(edge_tab_name,wt_l,wt_r,wt_l1,wt_r1,wt_i,wt_ld,wt_rd);
dbms_output.put_line('working table wt_l ' || wt_l);
dbms_output.put_line('working table wt_r ' || wt_r);
dbms_output.put_line('working table wt_l1 ' || wt_l1);
dbms_output.put_line('working table wt_r1 ' || wt_r1);
dbms_output.put_line('working table wt_i ' || wt_i);
dbms_output.put_line('working table wt_ld ' || wt_ld);
dbms_output.put_line('working table wt_rd ' || wt_rd);
-- compute
opg_apis.cf(edge_tab_name,edge_label,rating_property,iterations,
min_error,k,learning_rate,decrease_rate,regularization,dop,
wt_l,wt_r,wt_l1,wt_r1,wt_i,wt_ld,wt_rd,tablespace,options);
END;
/
working table wt_l "PHONESGE$$CFL57" working table wt_r "PHONESGE$$CFR57" working table wt_l1 "PHONESGE$$CFL157" working table wt_r1 "PHONESGE$$CFR157" working table wt_i "PHONESGE$$CFI57" working table wt_ld "PHONESGE$$CFLD57" working table wt_rd "PHONESGE$$CFRD57" PL/SQL procedure successfully completed.
例4-22 協調フィルタリング:中間エラーの検証
作業表内のデータがまだ削除されていないかぎり、すべての計算の最後に次の問合せを使用してアルゴリズムの現在のエラーを確認できます。次のSQL問合せは、協調フィルタリング・アルゴリズムの現在の実行の中間エラーを取得する方法を示しています。
SELECT /*+ parallel(48) */ SQRT(SUM((w1-w2)*(w1-w2) +
<regularization>/2 * (err_reg_l+err_reg_r))) AS err
FROM <wt_i>;
正則化パラメータと作業表名(パラメータwt_i)は、OPG_APIS.CFアルゴリズムを実行しているときに使用される値に応じて置き換える必要があることに注意してください。前述の例では、次のように、<regularization>を0.02に置き換え、<wt_i>を「PHONESGE$$CFI149」に置き換えます。
SELECT /*+ parallel(48) */ SQRT(SUM((w1-w2)*(w1-w2) + 0.02/2 * (err_reg_l+err_reg_r))) AS err
FROM "PHONESGE$$CFI149";
この問合せでは、次の出力が生成される可能性があります。
ERR ---------- 4.82163662
現在のエラーの値が大きすぎるか、協調フィルタリングの行列因子分解の結果から得た予想がまだ有用でない場合は、作業表とこれまでの進展を再利用して、アルゴリズムのさらなる反復を実行できます。次の例は、SQLベースの協調フィルタリングを使用した予測の実行方法を示しています。
例4-23 協調フィルタリング:予測の実行
協調フィルタリング・アルゴリズムの結果が、行列積の2つの因子であるwt_l表とwt_r表に格納されます。協調フィルタリングの予測をする場合、これらの行列因子を使用する必要があります。
このアルゴリズムの一般的なフローでは、OPG_APIS.CF_CLEANUPプロシージャをコールする前に、2つの行列因子を使用して予測を行うことができます。または、後で使用するために、それらを他の表にコピーして永続的なものにすることができます。次の例は、後者の場合を示しています。
DECLARE
wt_l varchar2(32); -- working tables
wt_r varchar2(32);
wt_l1 varchar2(32);
wt_r1 varchar2(32);
wt_i varchar2(32);
wt_ld varchar2(32);
wt_rd varchar2(32);
edge_tab_name varchar2(32) := 'phonesge$';
edge_label varchar2(32) := 'rating';
rating_property varchar2(32) := '';
iterations integer := 100;
min_error number := 0.001;
k integer := 5;
learning_rate number := 0.001;
decrease_rate number := 0.95;
regularization number := 0.02;
dop number := 2;
tablespace varchar2(32) := null;
options varchar2(32) := null;
BEGIN
-- prepare
opg_apis.cf_prep(edge_tab_name,wt_l,wt_r,wt_l1,wt_r1,wt_i,wt_ld,wt_rd);
-- compute
opg_apis.cf(edge_tab_name,edge_label,rating_property,iterations,
min_error,k,learning_rate,decrease_rate,regularization,dop,
wt_l,wt_r,wt_l1,wt_r1,wt_i,wt_ld,wt_rd,tablespace,options);
-- save only these two tables for later predictions
EXECUTE IMMEDIATE 'CREATE TABLE customer_mat AS SELECT * FROM ' || wt_l;
EXECUTE IMMEDIATE 'CREATE TABLE item_mat AS SELECT * FROM ' || wt_r;
-- cleanup
opg_apis.cf_cleanup('phonesge$',wt_l,wt_r,wt_l1,wt_r1,wt_i,wt_ld,wt_rd);
END;
/
この例は、次の出力のみを生成します。
PL/SQL procedure successfully completed.
これで行列因子がcustomer_mat表とitem_mat表に保存されたので、次の問合せを使用して、実際の値(以前、「評価」としてグラフに存在していた値)と見積予想(特定の顧客行と品目列での行列の乗算の結果)の間の「エラー」(差分)を確認できます。
次の問合せは、数値IDではなく、頂点のNVARCHARプロパティ(たとえば、nameプロパティ)を返すために、頂点表で結合によってカスタマイズされていることに注意してください。この問合せは、グラフ内のすべての品目頂点に対するすべての顧客頂点のすべての予測を返します。
SELECT /*+ parallel(48) */ MIN(vertex1.v) AS customer,
MIN(vertex2.v) AS item,
MIN(edges.vn) AS real,
SUM(l.v * r.v) AS predicted
FROM PHONESGE$ edges,
CUSTOMER_MAT l,
ITEM_MAT r,
PHONESVT$ vertex1,
PHONESVT$ vertex2
WHERE l.k = r.k
AND l.c = edges.svid(+)
AND r.p = edges.dvid(+)
AND l.c = vertex1.vid
AND r.p = vertex2.vid
GROUP BY l.c, r.p
ORDER BY l.c, r.p -- This order by clause is optional
;
この問合せでは、次のような出力が生成される可能性があります(簡略化するために、一部の行は省略されています)。
CUSTOMER ITEM REAL PREDICTED ------------------------------------------------ Adam Apple 5 3.67375703 Adam Blackberry 3.66079652 Adam Danger 2.77049596 Adam Ericsson 4.21764858 Adam Figo 3.10631337 Adam Google 4 4.42429022 Adam Huawei 3 3.4289115 Ben Apple 2.82127589 Ben Blackberry 2 2.81132282 Ben Danger 3 2.12761307 Ben Ericsson 3 3.2389595 Ben Figo 2.38550534 Ben Google 3.39765075 Ben Huawei 2.63324582 ... Don Apple 1.3777496 Don Blackberry 1 1.37288909 Don Danger 1 1.03900439 Don Ericsson 1.58172236 Don Figo 1 1.16494421 Don Google 1.65921807 Don Huawei 1 1.28592648 Erik Apple 3 2.80809351 Erik Blackberry 3 2.79818695 Erik Danger 2.11767182 Erik Ericsson 3 3.2238255 Erik Figo 2.3743591 Erik Google 3 3.38177526 Erik Huawei 3 2.62094201
予測結果が準備できたか、アルゴリズムの反復をさらに実行する必要があるかどうかを判断するために一部の行のみを確認する場合は、外部問合せの中に前の問合せをラップできます。次の例では、最初の11個の結果のみが選択されます。
SELECT /*+ parallel(48) */ * FROM (
SELECT /*+ parallel(48) */ MIN(vertex1.v) AS customer,
MIN(vertex2.v) AS item,
MIN(edges.vn) AS real,
SUM(l.v * r.v) AS predicted
FROM PHONESGE$ edges,
CUSTOMER_MAT l,
ITEM_MAT r,
PHONESVT$ vertex1,
PHONESVT$ vertex2
WHERE l.k = r.k
AND l.c = edges.svid(+)
AND r.p = edges.dvid(+)
AND l.c = vertex1.vid
AND r.p = vertex2.vid
GROUP BY l.c, r.p
ORDER BY l.c, r.p
) WHERE rownum <= 11;
この問合せでは、次のような出力が生成される可能性があります。
CUSTOMER ITEM REAL PREDICTED ------------------------------------------------ Adam Apple 5 3.67375703 Adam Blackberry 3.66079652 Adam Danger 2.77049596 Adam Ericsson 4.21764858 Adam Figo 3.10631337 Adam Google 4 4.42429022 Adam Huawei 3 3.4289115 Ben Apple 2.82127589 Ben Blackberry 2 2.81132282 Ben Danger 3 2.12761307 Ben Ericsson 3 3.2389595
特定の頂点(顧客、品目、またはその両方)の予測を取得するには、希望するID値によって問合せを制限できます。たとえば、頂点1(顧客)の予測値と頂点105(品目)の予測値を取得するには、次の問合せを使用できます。
SELECT /*+ parallel(48) */ MIN(vertex1.v) AS customer,
MIN(vertex2.v) AS item,
MIN(edges.vn) AS real,
SUM(l.v * r.v) AS predicted
FROM PHONESGE$ edges,
CUSTOMER_MAT l,
ITEM_MAT r,
PHONESVT$ vertex1,
PHONESVT$ vertex2
WHERE l.k = r.k
AND l.c = edges.svid(+)
AND r.p = edges.dvid(+)
AND l.c = vertex1.vid
AND vertex1.vid = 1 /* Remove to get all predictions for item 105 */
AND r.p = vertex2.vid
AND vertex2.vid = 105 /* Remove to get all predictions for customer 1 */
/* Remove both lines to get all predictions */
GROUP BY l.c, r.p
ORDER BY l.c, r.p;
この問合せでは、次のような出力が生成される可能性があります。
CUSTOMER ITEM REAL PREDICTED ------------------------------------------------ Adam Ericsson 4.21764858
4.7 プロパティ・グラフ問合せ言語(PGQL)
PGQLは、ノードからなり、エッジにより別のノードに接続され、それぞれが関連付けられたキー値ペア(プロパティ)を持つことができる、プロパティ・グラフ・データ構造のためのSQLライクな問合せ言語です。
この言語はグラフ・パターン一致の概念に基づき、データ・グラフの頂点およびエッジに対し一致するパターンを指定することができます。
Oracle Spatial and Graphプロパティ・グラフ・サポートでは、Java APIを介してプロパティ・グラフ問合せ言語(PGQL)の問合せを実行するための2つの方法が提供されています。
-
「インメモリー・アナリスト(PGX)の使用」で説明されているように、
oracle.pgx.apiJavaパッケージを使用して、インメモリー・アナリスト(PGX)にロードされているグラフのインメモリー・スナップショットを問い合せます。 -
「Oracle Databaseに対して直接PGQL問合せを実行」で説明されているように、
oracle.pg.rdbmsJavaパッケージを使用して、Oracle Databaseに格納されているグラフ・データを直接問合せます。
4.7.1 PGQLによるトポロジの制約
パターン一致はトポロジ制約を使用して実行されます。これはグラフ内のノード間の接続のパターンを記述するもので、値制約(SQL相当に類似)により、それらの接続およびノードが持つ必要のあるプロパティを指定することで、さらに制約を一致させることができます。
たとえば、コンピュータ・ネットワーク上のTCP/IP接続のグラフを想定し、誰かが1つのマシンにログインし、そこから別のマシンへ、そこからさらに別のマシンへという場合を検出するとします。次のようなパターンを問合せます。
SELECT host1.id(), host2.id(), host3.id()
WHERE /* choose what to return */
(host1) -[c1 WITH toPort = 22 and opened = true]-> (host2) /* topology must match this pattern */
-[connection2 WITH toPort = 22 and opened = true]-> (host3),
connection1.bytes > 300, /* meaningful amount of data was exchanged */
connection2.bytes > 300,
connection1.start < connection2.start, /* second connection within time-frame of first */
connection2.start + connection2.duration < connection1.start + connection1.duration
GROUP BY host1.id(), host2.id(), host3.id() /* aggregate multiple matching connections */
ORDER BY DESC(connection1.when) /* reverse sort chronologically */
4.7.2 PGQLによる制約は方向性あり
トポロジの制約には、グラフのエッジと同じく、方向性があります。そのため、(a) <-[]- (b)は、bにはaに向けられたエッジがある場合を指定するのに対し、(a) -[]-> (b)は、逆の方向のエッジを探します。
次の例は、AprilとChrisの共通の友人で両者よりも年上の人を探します。
SELECT friend.name, friend.dob
WHERE /* note the arrow directions below */
(p1:person WITH name = 'April') -[:likes]-> (friend) <-[:likes]- (p2:person WITH name = 'Chris'),
friend.dob > p1.dob AND friend.dob > p2.dob
ORDER BY friend.dob DESC
4.7.3 PGQLによる頂点およびエッジ・ラベル
ラベルは、グラフのエッジおよびノードにタイプ情報を添付する方法で、すべてのノードが同じものを表しているわけではないグラフの制約に使用することができます。次に例を示します。
SELECT p WHERE (p:person) -[e:likes]-> (m:movie WITH title='Star Wars'),
(p) -[e:likes]-> (m:movie WITH title='Avatar')
4.7.4 PGQLによる標準パス問合せ
標準パス問合せによりパターンの再利用が可能になります。次の例は、MarioとLuigiの共通の祖先をすべて検索します。
PATH has_parent := () -[:has_father|has_mother]-> ()
SELECT ancestor.name
WHERE
(:Person WITH name = 'Mario') -/:has_parent*/-> (ancestor:Person),
(:Person WITH name = 'Luigi') -/:has_parent*/-> (ancestor)
追加の制約または問合せ結果では使用されない、中間エッジまたはノードの名前は定義する必要がないため、前のパス仕様では、匿名制約の使用も示しています。匿名要素は、[:has_father|has_mother]などの制約を持つことができます。エッジは変数名を取得できません(他の場所で参照されないため)が、制約はされます。
4.8 Oracle Databaseに対して直接PGQL問合せを実行
このトピックでは、(インメモリーと対照的に)Oracle Database内のグラフに対して直接PGQL問合せを実行する方法について説明します。
プロパティ・グラフ問合せ言語(PGQL)の問合せは、Oracle Databaseに格納されているディスク常駐プロパティ・グラフ・データに対して実行できます。グラフ・データ・アクセス・レイヤー(DAL)には、PGQL問合せを実行するためのJava APIが用意されています。DAL内のロジックは発行されたPGQL問合せを同等のSQL問合せに変換し、結果として作成されたSQLがデータベース・サーバーで実行されます。次に、DALは、便利なPGQL結果セットAPIでSQL問合せの結果をラップします。このPGQL問合せの実行フローは次の図のようになります。
基本的な実行フローは次のとおりです。
-
PGQL問合せはJava APIを介してDALに送信されます。
-
PGQL問合せは、DALでSQLに変換されます。
-
変換されたSQLは、JDBCによってOracle Databaseに送信されます。
-
SQL結果セットは、PGQL結果セットとしてラップされてコール元に返されます。
Oracle Databaseに格納されたプロパティ・グラフ・データに対してPGQL問合せを直接実行できることにより、いくつかの利点があります。
-
PGQLでは、VT$、GE$、およびGT$を含めた問合せスキーマ表に手動で書き込まれるSQLよりも、より自然なグラフ問合せの表現方法が提供されています。
-
PGQL問合せは、グラフ・データのスナップショットをPGXにロードせずに実行できます。そのため、頻繁に更新されるグラフ・データの失効について心配する必要がありません。
-
PGQL問合せは、大きすぎてメモリー内に収まらないグラフ・データに対して実行できます。
-
堅牢でスケーラブルなOracle SQLエンジンを、PGQL問合せの実行に使用できます。
-
Oracle Databaseの管理、監視、およびチューニングを行うための成熟したツールを、PGQL問合せのチューニングと監視に使用できます。
4.8.1 サポートされるPGQL機能
PGQLは、プロパティ・グラフ・データの問合せを行うための、SQLに似た問合せ言語です。これは、グラフのパターン一致の概念に基づいており、これを使用して、特にトポロジ制約、パス、フィルタ、ソート、および集計を指定できます。
oracle.pg.rdbmsパッケージに定義されているPGQL用のJava APIは、PGQL 1.0仕様といくつかの拡張機能をサポートしています。PGQL 1.0仕様については、http://pgql-lang.org/spec/1.0/を参照してください。PGQL 1.0に対する次の拡張機能がサポートされています。
4.8.1.1 Temporal型
Temporal型のDATE、TIMESTAMP、およびTIMESTAMP WITH TIMEZONEがPGQL問合せでサポートされています。
これらのすべての値型は、Oracle SQLのTIMESTAMP WITH TIME ZONE型を使用して内部で表されます。DATEの値は、UTC+0のタイムゾーンで最も早い時刻と想定して、自動的にTIMESTAMP WITH TIME ZONEに変換されます(たとえば、2000-01-01は2000-01-01 00:00:00.00+00:00になります)。TIMESTAMPの値は、UTC+0タイムゾーンと想定して、自動的にTIMESTAMP WITH TIME ZONEに変換されます(たとえば、2000-01-01 12:00:00.00は2000-01-01 12:00:00.00+00:00)。
Temporalの定数は、PGQL問合せに次のように書き込まれます。
-
DATE 'YYYY-MM-DD'
-
TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF'
-
TIMESTAMP WITH TIMEZONE 'YYYY-MM-DD HH24:MI:SS.FFTZH:TZM'
例としては、DATE '2000-01-01'、TIMESTAMP '2000-01-01 14:01:45.23'、TIMESTAMP WITH TIMEZONE '2000-01-01 13:00:00.00-05:00'、およびTIMESTAMP WITH TIMEZONE '2000-01-01 13:00:00.00+01:00'などがあります。
また、Temporal値は、String値をTemporal型にキャストすることで取得できます。サポートされている文字列形式は次のとおりです。
-
DATE 'YYYY-MM-DD'
-
TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF'および'YYYY-MM-DD"T"HH24:MI:SS.FF'
-
TIMESTAMP WITH TIMEZONE 'YYYY-MM-DD HH24:MI:SS.FFTZH:TZM'および'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM'
例としては、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)などがあります。
OraclePgqlResultオブジェクトの結果を使用している場合、getValueは、Temporal型に対してjava.sql.Timestampオブジェクトを返します。
バインド変数は、PGQLではTIMESTAMP WITH TIMEZONEのTemporal型にのみ使用でき、java.sql.Timestampオブジェクトを入力として取るsetTimestampメソッドがバインド値を設定するために使用されます。より単純な別の方法として、CAST文で文字列のバインド変数を使用してTemporal値(たとえば、CAST (? AS TIMESTAMP WITH TIMEZONE)とそれに続くsetString(1, "1985-01-01T14:05:05.00-08:00"))をバインドできます。バインド変数の詳細は、「PGQL問合せでのバインド変数の使用」も参照してください。
4.8.1.2 型キャスト
型キャストは、SQLスタイルCAST (VALUE AS DATATYPE)構文を使用するPGQLでサポートされます。たとえば、CAST ('25' AS INT)、CAST (10 AS STRING)、CAST ('2005-02-04' AS DATE)、CAST (e.weight AS STRING)などです。サポートされているキャスト操作を次の表にまとめます。Yは、変換がサポートされることを示し、Nは、変換がサポートされないことを示します。無効な値に対するキャスト操作(たとえば、CAST ('xyz' AS INT))またはサポートされない変換(たとえば、CAST (10 AS TIMESTAMP))では、SQL例外ではなく、NULLが返されます。
表4-1 PGQLでの型キャストのサポート(変換元の型および変換先の型)
| 「変換先」の型 | STRINGから | INTから | LONGから | FLOATから | DOUBLEから | BOOLEANから | DATEから | TIMESTAMPから | TIMESTAMP WITH TIMEZONEから |
|---|---|---|---|---|---|---|---|---|---|
|
STRINGへ |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
|
INTへ |
Y |
Y |
Y |
Y |
Y |
Y |
N |
N |
N |
|
LONGへ |
Y |
Y |
Y |
Y |
Y |
Y |
N |
N |
N |
|
FLOATへ |
Y |
Y |
Y |
Y |
Y |
Y |
N |
N |
N |
|
DOUBLEへ |
Y |
Y |
Y |
Y |
Y |
Y |
N |
N |
N |
|
BOOLEANへ |
Y |
Y |
Y |
Y |
Y |
Y |
N |
N |
N |
|
DATEへ |
Y |
N |
N |
N |
N |
N |
Y |
Y |
Y |
|
TIMESTAMPへ |
Y |
N |
N |
N |
N |
N |
Y |
Y |
Y |
|
TIMESTAMP WITH TIMEZONEへ |
Y |
N |
N |
N |
N |
N |
Y |
Y |
Y |
型キャストを使用する問合せ例を次に示します。
SELECT e.name, CAST (e.birthDate AS STRING) AS dob
WHERE (e), e.birthDate < CAST ('1980-01-01' AS DATE)
4.8.1.3 ALL_DIFFERENT組込み関数
ALL_DIFFERENT組込み関数はサポートされています。このBoolean関数は、一連の値がすべてお互いに異なるかどうかをテストします。値がすべて異なる場合はtrueが返され、そうでない場合はfalseが返されます。一般的なユースケースでは、グラフ・パターンが、グラフの一致についてデフォルトの準同型セマンティクスではなく同型セマンティクスを達成するように、すべての頂点が各ソリューション内で異なるものになることが強制されます。
問合せ例は次のとおりです。
SELECT a, b, c
WHERE (a)->(b)<-(c), ALL_DIFFERENT(a, b, c)
4.8.1.4 CONTAINS組込み関数
CONTAINS組込み関数がサポートされています。これは、Oracle Text索引とともに、頂点プロパティとエッジ・プロパティに対して使用されます。CONTAINSは、値がOracle Text検索文字列に一致するとtrueを返し、一致しないとfalseを返します。
問合せ例は次のとおりです。
SELECT v.name
WHERE (v), CONTAINS(v.abstract, 'Oracle')
PGQLでの全文索引の使用の詳細は、「PGQL問合せでのテキスト索引の使用」も参照してください。
4.8.1.5 1つ以上のパス演算子(+)
パス・セグメントのゼロ以上の反復を指定するクリーネ・スター演算子* (アスタリスク)に加え、パス・セグメントの1つ以上の反復を指定する+ (プラス記号)演算子がサポートされています。パス問合せを評価するとき、各頂点は、長さ0 (ゼロ)のパスによって自身に接続されているとみなされます。+演算子を使用して、これらのゼロ長さのパスの一致を排除できます。
問合せ例は次のとおりです。
PATH fof := ()-[:friendOf]->()
SELECT x.name, y.name
WHERE (x)-/:fof+/->(y)
パスの問合せの詳細は、「パス問合せのオプション」も参照してください。
4.8.2 oracle.pg.rdbms Javaパッケージを使用したPGQL問合せの実行
Oracle Spatial and Graphプロパティ・グラフ・サポートでは、Oracle Databaseに対してPGQL問合せを実行するためにoracle.pg.rdbmsパッケージ内にJava APIを用意しています。この項では、一連の例を使用してJava APIの使用方法について説明します。
PGQL問合せの機能を使用するには、次のクラスをJavaプログラムにインポートします。
import oracle.pg.rdbms.*;次に示す、Oracleフラット・ファイル形式のtest_graphデータ・セットは、この後のサブトピック内の例で使用されます。このデータ・セットには、1つの頂点ファイル(test_graph.opv)と1つのエッジ・ファイル(test_graph.ope)が含まれています
test_graph.opv:
2,fname,1,Ray,, 2,lname,1,Green,, 2,mval,5,,,1985-01-01T12:00:00.000Z 2,age,2,,41, 0,bval,6,Y,, 0,fname,1,Bill,, 0,lname,1,Brown,, 0,mval,1,y,, 0,age,2,,40, 1,bval,6,Y,, 1,fname,1,John,, 1,lname,1,Black,, 1,mval,2,,27, 1,age,2,,30, 3,bval,6,N,, 3,fname,1,Susan,, 3,lname,1,Blue,, 3,mval,6,N,, 3,age,2,,35,
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,
4.8.2.1 基本的な問合せの実行
2つの主要なJavaインタフェース、OraclePgqlStatementとOraclePgqlResultSetがPGQLの実行に使用されます。このトピックには、基本的な問合せ実行のいくつかの例が含まれています。
例4-24 PgqlExample1.java
PgqlExample1.javaは、PGQL問合せの実行と、問合せの結果を使用した反復の例を示しています。OraclePgqlExecutionFactoryは、OraclePropertyGraphオブジェクトからOraclePgqlStatementを取得するために使用されます。次に、OraclePgqlStatementのexecuteQueryメソッドがコールされ、それによりOraclePgqlResultSetオブジェクトが返されます。OraclePgqlResultSetには、問合せ結果を利用するためのIteratorインタフェースが用意されており、OraclePgqlResultSetMetadataインタフェースは、問合せ結果内の列の位置、名前、および型を判別するために使用できます。各結果は、1つのOraclePgqlResultオブジェクト内にラップされます。
結果列は、OraclePgqlColumnDescriptor.Type.VERTEX、OraclePgqlColumnDescriptor.Type.EDGE、およびOraclePgqlColumnDescriptor.Type.VALUEの3つのタイプのいずれかになります。列値に対して返される、対応するJavaクラスは、それぞれOracleVertex、OracleEdge、およびObjectです。ノードとエッジの値は汎用Javaオブジェクトとして返され、OraclePgqlResultのgetValueTypeメソッドを使用して、オブジェクトのより特定のタイプを判別でき、それを適切なJavaの型にキャストできます。getValueTypeによって返される値は、oracle.pg.common.OraclePropertyGraphBaseに定義されている次の定数フィールドのいずれかになります。TYPE_DT_BOOL, TYPE_DT_BYTE, TYPE_DT_CHAR, TYPE_DT_DATE, TYPE_DT_DOUBLE, TYPE_DT_EMPTY, TYPE_DT_FLOAT, TYPE_DT_INTEGER, TYPE_DT_JSON, TYPE_DT_LONG, TYPE_DT_SERI, TYPE_DT_SHORT, TYPE_DT_SPATIAL, TYPE_DT_STRING, TYPE_DT_URI
OraclePgqlResultSetオブジェクトとOraclePgqlStatementオブジェクトは、問合せ結果の使用後にクローズする必要があります。
import oracle.pg.rdbms.*;
import oracle.pg.common.*;
import java.util.*;
import java.text.*;
/**
* 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[] szArgs) throws Exception
{
int iArgIdx=0;
String szHost = szArgs[iArgIdx++];
String szPort = szArgs[iArgIdx++];
String szSID = szArgs[iArgIdx++];
String szUser = szArgs[iArgIdx++];
String szPassword = szArgs[iArgIdx++];
String szGraph = szArgs[iArgIdx++];
String szVertexFile = szArgs[iArgIdx++];
String szEdgeFile = szArgs[iArgIdx++];
Oracle oracle = null;
OraclePropertyGraph opg = null;
OraclePgqlStatement ops = null;
OraclePgqlResultSet oprs= null;
try {
// Create a connection to Oracle
oracle =
new Oracle("jdbc:oracle:thin:@"+szHost+":"+szPort +":"+szSID, szUser, szPassword);
// Create a property graph
opg = OraclePropertyGraph.getInstance(oracle, szGraph);
// Clear any existing data
opg.clearRepository();
// Load data
OraclePropertyGraphDataLoader opgLoader = OraclePropertyGraphDataLoader.getInstance();
opgLoader.loadData(opg, szVertexFile, szEdgeFile, 1);
// Create an OraclePgqlStatement
ops = OraclePgqlExecutionFactory.createStatement(opg);
// Execute query to get an OraclePgqlResultSet object
String pgql =
"SELECT v.fname AS fname, v.lname AS lname, v.mval AS mval "+
"WHERE (v)";
oprs = ops.executeQuery(pgql, /* query string */
"" /* options */);
// Use OraclePgqlResultSetMetaData object to determine number of result columns
OraclePgqlResultSetMetaData rsmd = oprs.getMetaData();
int colCount = rsmd.getColumnCount();
// Use an iterator to consume the result set
Iterator<OraclePgqlResult> itr = oprs.getResults().iterator();
while (itr.hasNext()) {
OraclePgqlResult opr = itr.next();
StringBuffer buff = new StringBuffer("[");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSXXX");
for (int i = 1; i <= colCount; i++) {
buff.append(rsmd.getColumnName(i)).append("=");
OraclePgqlColumnDescriptor.Type t = opr.getColumnType(i);
switch (t) {
case VERTEX:
// process vertex
OracleVertex v = opr.getVertex(i);
buff.append(v.getId()+" ");
break;
case EDGE:
// process edge
OracleEdge e = opr.getEdge(i);
buff.append(e.getId()+":"+e.getLabel()+" ");
break;
case VALUE:
// process value
int valueType = opr.getValueType(i);
Object obj = opr.getValue(i);
switch(valueType) {
case OraclePropertyGraphBase.TYPE_DT_BOOL:
buff.append("BOOLEAN:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_DATE:
buff.append("DATE:"+sdf.format((Date)obj)+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_DOUBLE:
buff.append("DOUBLE:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_FLOAT:
buff.append("FLOAT:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_INTEGER:
buff.append("INTEGER:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_LONG:
buff.append("LONG:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_STRING:
buff.append("STRING:"+obj.toString()+" ");
break;
}
break;
}
}
buff.append("]");
System.out.println(buff.toString());
}
}
finally {
// close the result set
if (oprs != null) {
oprs.close();
}
// close the statement
if (ops != null) {
ops.close();
}
// close the property graph
if (opg != null) {
opg.shutdown();
}
// close oracle
if (oracle != null) {
oracle.dispose();
}
}
}
}
PgqlExample1.javaは、次に示すtest_graphの出力を生成します。
[fname=STRING:Susan lname=STRING:Blue mval=BOOLEAN:false ] [fname=STRING:Bill lname=STRING:Brown mval=STRING:y ] [fname=STRING:Ray lname=STRING:Green mval=DATE:1985-01-01T04:00:00.000-08:00 ] [fname=STRING:John lname=STRING:Black mval=INTEGER:27 ]
例4-25 PgqlExample2.java
PgqlExample2.javaは、エッジ・プロパティに対するTemporalフィルタを持つPGQL問合せを示しています。
import oracle.pg.rdbms.*;
import oracle.pg.common.*;
import java.util.*;
import java.text.*;
/**
* 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[] szArgs) throws Exception
{
int iArgIdx=0;
String szHost = szArgs[iArgIdx++];
String szPort = szArgs[iArgIdx++];
String szSID = szArgs[iArgIdx++];
String szUser = szArgs[iArgIdx++];
String szPassword = szArgs[iArgIdx++];
String szGraph = szArgs[iArgIdx++];
String szVertexFile = szArgs[iArgIdx++];
String szEdgeFile = szArgs[iArgIdx++];
Oracle oracle = null;
OraclePropertyGraph opg = null;
OraclePgqlStatement ops = null;
OraclePgqlResultSet oprs= null;
try {
// Create a connection to Oracle
oracle =
new Oracle("jdbc:oracle:thin:@"+szHost+":"+szPort +":"+szSID, szUser, szPassword);
// Create a property graph
opg = OraclePropertyGraph.getInstance(oracle, szGraph);
// Clear any existing data
opg.clearRepository();
// Load data
OraclePropertyGraphDataLoader opgLoader = OraclePropertyGraphDataLoader.getInstance();
opgLoader.loadData(opg, szVertexFile, szEdgeFile, 1);
// Create an OraclePgqlStatement
ops = OraclePgqlExecutionFactory.createStatement(opg);
// Execute query to get an OraclePgqlResultSet object
String pgql =
"SELECT v.fname AS n1, v2.fname AS n2, e.firstMetIn AS loc "+
"WHERE (v)-[e:knows]->(v2), "+
" e.since > TIMESTAMP '2000-01-01 00:00:00.00+00:00'";
oprs = ops.executeQuery(pgql, "");
// Print results
printResults(oprs);
}
finally {
// close the result set
if (oprs != null) {
oprs.close();
}
// close the statement
if (ops != null) {
ops.close();
}
// close the property graph
if (opg != null) {
opg.shutdown();
}
// close oracle
if (oracle != null) {
oracle.dispose();
}
}
}
/**
* Prints an OraclePgqlResultSet
*/
static void printResults(OraclePgqlResultSet oprs) throws Exception
{
// Use OraclePgqlResultSetMetaData object to determine number of result columns
OraclePgqlResultSetMetaData rsmd = oprs.getMetaData();
int colCount = rsmd.getColumnCount();
// Use an iterator to consume the result set
Iterator<OraclePgqlResult> itr = oprs.getResults().iterator();
while (itr.hasNext()) {
OraclePgqlResult opr = itr.next();
StringBuffer buff = new StringBuffer("[");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSXXX");
for (int i = 1; i <= colCount; i++) {
buff.append(rsmd.getColumnName(i)).append("=");
OraclePgqlColumnDescriptor.Type t = opr.getColumnType(i);
switch (t) {
case VERTEX:
// process vertex
OracleVertex v = opr.getVertex(i);
buff.append(v.getId()+" ");
break;
case EDGE:
// process edge
OracleEdge e = opr.getEdge(i);
buff.append(e.getId()+":"+e.getLabel()+" ");
break;
case VALUE:
// process value
int valueType = opr.getValueType(i);
Object obj = opr.getValue(i);
switch(valueType) {
case OraclePropertyGraphBase.TYPE_DT_BOOL:
buff.append("BOOLEAN:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_DATE:
buff.append("DATE:"+sdf.format((Date)obj)+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_DOUBLE:
buff.append("DOUBLE:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_FLOAT:
buff.append("FLOAT:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_INTEGER:
buff.append("INTEGER:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_LONG:
buff.append("LONG:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_STRING:
buff.append("STRING:"+obj.toString()+" ");
break;
}
break;
}
}
buff.append("]");
System.out.println(buff.toString());
}
}
}
PgqlExample2.javaは、次に示すtest_graphの出力を生成します。
[n1=STRING:Ray n2=STRING:John loc=STRING:OK ] [n1=STRING:Bill n2=STRING:Ray loc=STRING:OH ] [n1=STRING:Susan n2=STRING:Bill loc=STRING:CA ] [n1=STRING:John n2=STRING:Susan loc=STRING:SC ] [n1=STRING:Ray n2=STRING:Susan loc=STRING:TX ] [n1=STRING:John n2=STRING:Bill loc=STRING:GA ] [n1=STRING:Susan n2=STRING:John loc=STRING:CA ] [n1=STRING:Susan n2=STRING:Ray loc=STRING:CA ]
例4-26 PgqlExample3.java
PgqlExample3.javaは、グループ化と集計を含むPGQL問合せを示しています。
import oracle.pg.rdbms.*;
import oracle.pg.common.*;
import java.util.*;
import java.text.*;
/**
* 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[] szArgs) throws Exception
{
int iArgIdx=0;
String szHost = szArgs[iArgIdx++];
String szPort = szArgs[iArgIdx++];
String szSID = szArgs[iArgIdx++];
String szUser = szArgs[iArgIdx++];
String szPassword = szArgs[iArgIdx++];
String szGraph = szArgs[iArgIdx++];
String szVertexFile = szArgs[iArgIdx++];
String szEdgeFile = szArgs[iArgIdx++];
Oracle oracle = null;
OraclePropertyGraph opg = null;
OraclePgqlStatement ops = null;
OraclePgqlResultSet oprs= null;
try {
// Create a connection to Oracle
oracle =
new Oracle("jdbc:oracle:thin:@"+szHost+":"+szPort +":"+szSID, szUser, szPassword);
// Create a property graph
opg = OraclePropertyGraph.getInstance(oracle, szGraph);
// Clear any existing data
opg.clearRepository();
// Load data
OraclePropertyGraphDataLoader opgLoader = OraclePropertyGraphDataLoader.getInstance();
opgLoader.loadData(opg, szVertexFile, szEdgeFile, 1);
// Create an OraclePgqlStatement
ops = OraclePgqlExecutionFactory.createStatement(opg);
// Execute query to get an OraclePgqlResultSet object
String pgql =
"SELECT v.fname AS fname, COUNT(v2) AS friendCnt "+
"WHERE (v)-[e:friendOf]->(v2) "+
"GROUP BY v "+
"ORDER BY friendCnt DESC";
oprs = ops.executeQuery(pgql, "");
// Print results
printResults(oprs);
}
finally {
// close the result set
if (oprs != null) {
oprs.close();
}
// close the statement
if (ops != null) {
ops.close();
}
// close the property graph
if (opg != null) {
opg.shutdown();
}
// close oracle
if (oracle != null) {
oracle.dispose();
}
}
}
/**
* Prints an OraclePgqlResultSet
*/
static void printResults(OraclePgqlResultSet oprs) throws Exception
{
// Use OraclePgqlResultSetMetaData object to determine number of result columns
OraclePgqlResultSetMetaData rsmd = oprs.getMetaData();
int colCount = rsmd.getColumnCount();
// Use an iterator to consume the result set
Iterator<OraclePgqlResult> itr = oprs.getResults().iterator();
while (itr.hasNext()) {
OraclePgqlResult opr = itr.next();
StringBuffer buff = new StringBuffer("[");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSXXX");
for (int i = 1; i <= colCount; i++) {
buff.append(rsmd.getColumnName(i)).append("=");
OraclePgqlColumnDescriptor.Type t = opr.getColumnType(i);
switch (t) {
case VERTEX:
// process vertex
OracleVertex v = opr.getVertex(i);
buff.append(v.getId()+" ");
break;
case EDGE:
// process edge
OracleEdge e = opr.getEdge(i);
buff.append(e.getId()+":"+e.getLabel()+" ");
break;
case VALUE:
// process value
int valueType = opr.getValueType(i);
Object obj = opr.getValue(i);
switch(valueType) {
case OraclePropertyGraphBase.TYPE_DT_BOOL:
buff.append("BOOLEAN:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_DATE:
buff.append("DATE:"+sdf.format((Date)obj)+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_DOUBLE:
buff.append("DOUBLE:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_FLOAT:
buff.append("FLOAT:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_INTEGER:
buff.append("INTEGER:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_LONG:
buff.append("LONG:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_STRING:
buff.append("STRING:"+obj.toString()+" ");
break;
}
break;
}
}
buff.append("]");
System.out.println(buff.toString());
}
}
}
PgqlExample3.javaは、次に示すtest_graphの出力を生成します。
[fname=STRING:John friendCnt=LONG:2 ] [fname=STRING:Bill friendCnt=LONG:1 ] [fname=STRING:Ray friendCnt=LONG:1 ] [fname=STRING:Susan friendCnt=LONG:1 ]
例4-27 PgqlExample4.java
PgqlExample4.javaは、PGQLパス問合せを示しています。
import oracle.pg.rdbms.*;
import oracle.pg.common.*;
import java.util.*;
import java.text.*;
/**
* 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[] szArgs) throws Exception
{
int iArgIdx=0;
String szHost = szArgs[iArgIdx++];
String szPort = szArgs[iArgIdx++];
String szSID = szArgs[iArgIdx++];
String szUser = szArgs[iArgIdx++];
String szPassword = szArgs[iArgIdx++];
String szGraph = szArgs[iArgIdx++];
String szVertexFile = szArgs[iArgIdx++];
String szEdgeFile = szArgs[iArgIdx++];
Oracle oracle = null;
OraclePropertyGraph opg = null;
OraclePgqlStatement ops = null;
OraclePgqlResultSet oprs= null;
try {
// Create a connection to Oracle
oracle =
new Oracle("jdbc:oracle:thin:@"+szHost+":"+szPort +":"+szSID, szUser, szPassword);
// Create a property graph
opg = OraclePropertyGraph.getInstance(oracle, szGraph);
// Clear any existing data
opg.clearRepository();
// Load data
OraclePropertyGraphDataLoader opgLoader = OraclePropertyGraphDataLoader.getInstance();
opgLoader.loadData(opg, szVertexFile, szEdgeFile, 1);
// Create an OraclePgqlStatement
ops = OraclePgqlExecutionFactory.createStatement(opg);
// Execute query to get an OraclePgqlResultSet object
String pgql =
"PATH fof := ()-[:friendOf|knows]->() "+
"SELECT v2.fname AS friend "+
"WHERE (v WITH fname = 'John')-/:fof*/->(v2), "+
" v != v2";
oprs = ops.executeQuery(pgql, "");
// Print results
printResults(oprs);
}
finally {
// close the result set
if (oprs != null) {
oprs.close();
}
// close the statement
if (ops != null) {
ops.close();
}
// close the property graph
if (opg != null) {
opg.shutdown();
}
// close oracle
if (oracle != null) {
oracle.dispose();
}
}
}
/**
* Prints an OraclePgqlResultSet
*/
static void printResults(OraclePgqlResultSet oprs) throws Exception
{
// Use OraclePgqlResultSetMetaData object to determine number of result columns
OraclePgqlResultSetMetaData rsmd = oprs.getMetaData();
int colCount = rsmd.getColumnCount();
// Use an iterator to consume the result set
Iterator<OraclePgqlResult> itr = oprs.getResults().iterator();
while (itr.hasNext()) {
OraclePgqlResult opr = itr.next();
StringBuffer buff = new StringBuffer("[");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSXXX");
for (int i = 1; i <= colCount; i++) {
buff.append(rsmd.getColumnName(i)).append("=");
OraclePgqlColumnDescriptor.Type t = opr.getColumnType(i);
switch (t) {
case VERTEX:
// process vertex
OracleVertex v = opr.getVertex(i);
buff.append(v.getId()+" ");
break;
case EDGE:
// process edge
OracleEdge e = opr.getEdge(i);
buff.append(e.getId()+":"+e.getLabel()+" ");
break;
case VALUE:
// process value
int valueType = opr.getValueType(i);
Object obj = opr.getValue(i);
switch(valueType) {
case OraclePropertyGraphBase.TYPE_DT_BOOL:
buff.append("BOOLEAN:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_DATE:
buff.append("DATE:"+sdf.format((Date)obj)+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_DOUBLE:
buff.append("DOUBLE:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_FLOAT:
buff.append("FLOAT:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_INTEGER:
buff.append("INTEGER:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_LONG:
buff.append("LONG:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_STRING:
buff.append("STRING:"+obj.toString()+" ");
break;
}
break;
}
}
buff.append("]");
System.out.println(buff.toString());
}
}
}
PgqlExample4.javaは、次に示すtest_graphの出力を生成します。
[friend=STRING:Susan ] [friend=STRING:Bill ] [friend=STRING:Ray ]
4.8.2.2 PGQL問合せでのバインド変数の使用
パフォーマンス改善とセキュリティ向上のためにPGQL問合せでバインド変数を使用できます。PGQL問合せの定数スカラー値は、バインド変数に置き換えることができます。バインド変数は、'?' (疑問符)で示されます。一定の年齢値よりも上の年齢の人を選択する、次の2つの問合せについて検討します。
// people older than 30
SELECT v.fname AS fname, v.lname AS lname, v.age AS age
WHERE (v), v.age > 30
// people older than 40
SELECT v.fname AS fname, v.lname AS lname, v.age AS age
WHERE (v), v.age > 40
これらの問合せのSQL変換は、年齢フィルタに同様の方法で定数30および40を使用します。データベースは、これらの各問合せのハード解析を実行します。このハード解析時間は、単純な問合せの実行時間を超過することがよくあります。
次のように、各問合せ内の定数をバインド変数に置き換えることができます。
SELECT v.fname AS fname, v.lname AS lname, v.age AS age
WHERE (v), v.age > ?
これにより、SQLエンジンは、様々な年齢値に再利用できる、この問合せの汎用カーソルを作成できます。その結果、ハード解析が様々な年齢値に対してこの問合せを実行する必要がなくなり、各問合せの解析時間が大幅に短縮されます。
さらに、PGQL問合せでバインド変数を使用するアプリケーションでは、文字列連結を使用して定数値をPGQL問合せに埋め込むものに比べ、インジェクション攻撃に対する脆弱性が軽減されます。
カーソル共有とバインド変数の詳細は、『Oracle Database SQLチューニング・ガイド』も参照してください。
PgqlExample5.javaに示すように、OraclePgqlPreparedStatementインタフェースは、バインド変数を使用する問合せを実行するために使用できます。OraclePgqlPreparedStatementには、問合せ実行のための値の設定に使用できる様々な値タイプ用の複数の設定メソッドが用意されています。
PGQLではバインド変数にいくつかの制限があります。バインド変数は、定数プロパティ値にのみを使用できます。つまり、頂点とエッジをバインド変数に置き換えることはできません。また、一旦特定のバインド変数をある型に設定したら、それを別の型に設定することはできません。たとえば、OraclePgqlPreparedStatementに対してsetInt(1, 30)が実行される場合、その同じOraclePgqlPreparedStatementでsetString(1, "abc")をコールすることはできません。
例4-28 PgqlExample5.java
PgqlExample5.javaは、PGQL問合せでのバインド変数の使用方法を示しています。
import oracle.pg.rdbms.*;
import oracle.pg.common.*;
import java.util.*;
import java.text.*;
/**
* This example shows how to use bind variables with a PGQL query.
*/
public class PgqlExample5
{
public static void main(String[] szArgs) throws Exception
{
int iArgIdx=0;
String szHost = szArgs[iArgIdx++];
String szPort = szArgs[iArgIdx++];
String szSID = szArgs[iArgIdx++];
String szUser = szArgs[iArgIdx++];
String szPassword = szArgs[iArgIdx++];
String szGraph = szArgs[iArgIdx++];
String szVertexFile = szArgs[iArgIdx++];
String szEdgeFile = szArgs[iArgIdx++];
Oracle oracle = null;
OraclePropertyGraph opg = null;
OraclePgqlPreparedStatement opps = null;
OraclePgqlResultSet oprs= null;
try {
// Create a connection to Oracle
oracle =
new Oracle("jdbc:oracle:thin:@"+szHost+":"+szPort +":"+szSID, szUser, szPassword);
// Create a property graph
opg = OraclePropertyGraph.getInstance(oracle, szGraph);
// Clear any existing data
opg.clearRepository();
// Load data
OraclePropertyGraphDataLoader opgLoader = OraclePropertyGraphDataLoader.getInstance();
opgLoader.loadData(opg, szVertexFile, szEdgeFile, 1);
// Query string with a bind variable (denoted by ?)
String pgql =
"SELECT v.fname AS fname, v.lname AS lname, v.age AS age "+
"WHERE (v), "+
" v.age > ?";
// Create an OraclePgqlPreparedStatement
opps = OraclePgqlExecutionFactory.prepareStatement(opg, pgql);
// Set filter value to 30
opps.setInt(1, 30);
// execute query
oprs = opps.executeQuery("");
// Print query results
System.out.println("-- Values for v.age > 30 ------------------");
printResults(oprs);
// close result set
oprs.close();
// set filter value to 40
opps.setInt(1, 40);
// execute query
oprs = opps.executeQuery("");
// Print query results
System.out.println("-- Values for v.age > 40 ------------------");
printResults(oprs);
// close result set
oprs.close();
}
finally {
// close the result set
if (oprs != null) {
oprs.close();
}
// close the statement
if (opps != null) {
opps.close();
}
// close the property graph
if (opg != null) {
opg.shutdown();
}
// close oracle
if (oracle != null) {
oracle.dispose();
}
}
}
/**
* Prints an OraclePgqlResultSet
*/
static void printResults(OraclePgqlResultSet oprs) throws Exception
{
// Use OraclePgqlResultSetMetaData object to determine number of result columns
OraclePgqlResultSetMetaData rsmd = oprs.getMetaData();
int colCount = rsmd.getColumnCount();
// Use an iterator to consume the result set
Iterator<OraclePgqlResult> itr = oprs.getResults().iterator();
while (itr.hasNext()) {
OraclePgqlResult opr = itr.next();
StringBuffer buff = new StringBuffer("[");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSXXX");
for (int i = 1; i <= colCount; i++) {
buff.append(rsmd.getColumnName(i)).append("=");
OraclePgqlColumnDescriptor.Type t = opr.getColumnType(i);
switch (t) {
case VERTEX:
// process vertex
OracleVertex v = opr.getVertex(i);
buff.append(v.getId()+" ");
break;
case EDGE:
// process edge
OracleEdge e = opr.getEdge(i);
buff.append(e.getId()+":"+e.getLabel()+" ");
break;
case VALUE:
// process value
int valueType = opr.getValueType(i);
Object obj = opr.getValue(i);
switch(valueType) {
case OraclePropertyGraphBase.TYPE_DT_BOOL:
buff.append("BOOLEAN:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_DATE:
buff.append("DATE:"+sdf.format((Date)obj)+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_DOUBLE:
buff.append("DOUBLE:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_FLOAT:
buff.append("FLOAT:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_INTEGER:
buff.append("INTEGER:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_LONG:
buff.append("LONG:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_STRING:
buff.append("STRING:"+obj.toString()+" ");
break;
}
break;
}
}
buff.append("]");
System.out.println(buff.toString());
}
}
}
PgqlExample5.javaは、次に示すtest_graphの出力を生成します。
-- Values for v.age > 30 ------------------ [fname=STRING:Susan lname=STRING:Blue age=INTEGER:35 ] [fname=STRING:Bill lname=STRING:Brown age=INTEGER:40 ] [fname=STRING:Ray lname=STRING:Green age=INTEGER:41 ] -- Values for v.age > 40 ------------------ [fname=STRING:Ray lname=STRING:Green age=INTEGER:41 ]
例4-29 PgqlExample6.java
PgqlExample6.javaは、2つのバインド変数、1つのString変数と1つのTimestamp変数を含む問合せを示しています。
import oracle.pg.rdbms.*;
import oracle.pg.common.*;
import java.util.*;
import java.text.*;
import java.sql.*;
import java.time.*;
import java.time.format.*;
/**
* This example shows how to use multiple bind variables with a PGQL query.
*/
public class PgqlExample6
{
public static void main(String[] szArgs) throws Exception
{
int iArgIdx=0;
String szHost = szArgs[iArgIdx++];
String szPort = szArgs[iArgIdx++];
String szSID = szArgs[iArgIdx++];
String szUser = szArgs[iArgIdx++];
String szPassword = szArgs[iArgIdx++];
String szGraph = szArgs[iArgIdx++];
String szVertexFile = szArgs[iArgIdx++];
String szEdgeFile = szArgs[iArgIdx++];
Oracle oracle = null;
OraclePropertyGraph opg = null;
OraclePgqlPreparedStatement opps = null;
OraclePgqlResultSet oprs= null;
try {
// Create a connection to Oracle
oracle =
new Oracle("jdbc:oracle:thin:@"+szHost+":"+szPort +":"+szSID, szUser, szPassword);
// Create a property graph
opg = OraclePropertyGraph.getInstance(oracle, szGraph);
// Clear any existing data
opg.clearRepository();
// Load data
OraclePropertyGraphDataLoader opgLoader = OraclePropertyGraphDataLoader.getInstance();
opgLoader.loadData(opg, szVertexFile, szEdgeFile, 1);
// Query string with multiple bind variables
String pgql =
"SELECT v1.fname AS fname1, v2.fname AS fname2 "+
"WHERE (v1)-[e:knows]->(v2), "+
" e.since < ? AND e.firstMetIn = ?";
// Create an OraclePgqlPreparedStatement
opps = OraclePgqlExecutionFactory.prepareStatement(opg, 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());
opps.setTimestamp(1, t);
// Set e.firstMetIn = 'CA'
opps.setString(2, "CA");
// execute query
oprs = opps.executeQuery("");
// Print query results
System.out.println("-- Values for e.since < 2006-01-01T12:00:01.00Z AND e.firstMetIn = 'CA' --");
printResults(oprs);
// close result set
oprs.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());
opps.setTimestamp(1, t);
// Set e.firstMetIn = 'TX'
opps.setString(2, "TX");
// execute query
oprs = opps.executeQuery("");
// Print query results
System.out.println("-- Values for e.since < 2000-01-01T12:00:00.00Z AND e.firstMetIn = 'TX' --");
printResults(oprs);
// close result set
oprs.close();
}
finally {
// close the result set
if (oprs != null) {
oprs.close();
}
// close the statement
if (opps != null) {
opps.close();
}
// close the property graph
if (opg != null) {
opg.shutdown();
}
// close oracle
if (oracle != null) {
oracle.dispose();
}
}
}
/**
* Prints an OraclePgqlResultSet
*/
static void printResults(OraclePgqlResultSet oprs) throws Exception
{
// Use OraclePgqlResultSetMetaData object to determine number of result columns
OraclePgqlResultSetMetaData rsmd = oprs.getMetaData();
int colCount = rsmd.getColumnCount();
// Use an iterator to consume the result set
Iterator<OraclePgqlResult> itr = oprs.getResults().iterator();
while (itr.hasNext()) {
OraclePgqlResult opr = itr.next();
StringBuffer buff = new StringBuffer("[");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSXXX");
for (int i = 1; i <= colCount; i++) {
buff.append(rsmd.getColumnName(i)).append("=");
OraclePgqlColumnDescriptor.Type t = opr.getColumnType(i);
switch (t) {
case VERTEX:
// process vertex
OracleVertex v = opr.getVertex(i);
buff.append(v.getId()+" ");
break;
case EDGE:
// process edge
OracleEdge e = opr.getEdge(i);
buff.append(e.getId()+":"+e.getLabel()+" ");
break;
case VALUE:
// process value
int valueType = opr.getValueType(i);
Object obj = opr.getValue(i);
switch(valueType) {
case OraclePropertyGraphBase.TYPE_DT_BOOL:
buff.append("BOOLEAN:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_DATE:
buff.append("DATE:"+sdf.format((java.util.Date)obj)+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_DOUBLE:
buff.append("DOUBLE:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_FLOAT:
buff.append("FLOAT:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_INTEGER:
buff.append("INTEGER:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_LONG:
buff.append("LONG:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_STRING:
buff.append("STRING:"+obj.toString()+" ");
break;
}
break;
}
}
buff.append("]");
System.out.println(buff.toString());
}
}
}
PgqlExample6.javaは、次に示すtest_graphの出力を生成します。
-- Values for e.since < 2006-01-01T12:00:01.00Z AND e.firstMetIn = 'CA' -- [fname1=STRING:Susan fname2=STRING:Bill ] [fname1=STRING:Susan fname2=STRING:Ray ] -- Values for e.since < 2000-01-01T12:00:00.00Z AND e.firstMetIn = 'TX' -- [fname1=STRING:Ray fname2=STRING:Bill ]
4.8.2.3 PGQL問合せでのテキスト索引の使用
Oracle Databaseに対して実行されたPGQL問合せは、頂点プロパティとエッジ・プロパティ用に作成されたOracle Text索引を使用できます。テキスト索引を作成した後は、CONTAINS演算子を使用して全文検索を実行できます。CONTAINSには、頂点プロパティまたはエッジ・プロパティとOracle Text検索文字列という2つの引数があります。ワイルドカード、Stemming、およびSoundexなどの高度な機能を含め、すべての有効なOracle Text検索文字列を使用できます。
例4-30 PgqlExample7.java
PgqlExample7.javaは、CONTAINS問合せの実行方法を示しています。
import oracle.pg.rdbms.*;
import oracle.pg.common.*;
import java.util.*;
import java.text.*;
import java.sql.*;
import java.time.*;
import java.time.format.*;
/**
* This example shows how to use an Oracle Text index with a PGQL query.
*/
public class PgqlExample7
{
public static void main(String[] szArgs) throws Exception
{
int iArgIdx=0;
String szHost = szArgs[iArgIdx++];
String szPort = szArgs[iArgIdx++];
String szSID = szArgs[iArgIdx++];
String szUser = szArgs[iArgIdx++];
String szPassword = szArgs[iArgIdx++];
String szGraph = szArgs[iArgIdx++];
String szVertexFile = szArgs[iArgIdx++];
String szEdgeFile = szArgs[iArgIdx++];
Oracle oracle = null;
OraclePropertyGraph opg = null;
OraclePgqlPreparedStatement opps = null;
OraclePgqlResultSet oprs= null;
try {
// Create a connection to Oracle
oracle =
new Oracle("jdbc:oracle:thin:@"+szHost+":"+szPort +":"+szSID, szUser, szPassword);
// Create a property graph
// try to drop property graph first
try {
OraclePropertyGraphUtils.dropPropertyGraph(oracle, szGraph);
}
catch (SQLException ex) { /*do nothing*/; }
// Get property graph instance
opg = OraclePropertyGraph.getInstance(oracle, szGraph);
// Load data
OraclePropertyGraphDataLoader opgLoader = OraclePropertyGraphDataLoader.getInstance();
opgLoader.loadData(opg, szVertexFile, szEdgeFile, 1);
// Create text index with SQL API
CallableStatement cs = null;
// text index on vertices
cs = oracle.getConnection().prepareCall(
"begin opg_apis.create_vertices_text_idx(:1,:2); end;"
);
cs.setString(1,szUser);
cs.setString(2,szGraph);
cs.execute();
cs.close();
// text index on edges
cs = oracle.getConnection().prepareCall(
"begin opg_apis.create_edges_text_idx(:1,:2); end;"
);
cs.setString(1,szUser);
cs.setString(2,szGraph);
cs.execute();
cs.close();
// 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 "+
"WHERE (v), "+
" CONTAINS(v.lname,'B%')";
// Create an OraclePgqlStatement
opps = OraclePgqlExecutionFactory.prepareStatement(opg, pgql);
// execute query
oprs = opps.executeQuery("");
// print results
System.out.println("-- Vertex Property Query ---------------");
printResults(oprs);
// close result set and prepared statement
oprs.close();
opps.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 "+
"WHERE (v1)-[e:knows]->(v2), "+
" CONTAINS(e.firstMetIn,'%A')";
// Create an OraclePgqlStatement
opps = OraclePgqlExecutionFactory.prepareStatement(opg, pgql);
// execute query
oprs = opps.executeQuery("");
// print results
System.out.println("-- Edge Property Query -----------------");
printResults(oprs);
// close result set and statement
oprs.close();
opps.close();
}
finally {
// close the result set
if (oprs != null) {
oprs.close();
}
// close the statement
if (opps != null) {
opps.close();
}
// close the property graph
if (opg != null) {
opg.shutdown();
}
// close oracle
if (oracle != null) {
oracle.dispose();
}
}
}
/**
* Prints an OraclePgqlResultSet
*/
static void printResults(OraclePgqlResultSet oprs) throws Exception
{
// Use OraclePgqlResultSetMetaData object to determine number of result columns
OraclePgqlResultSetMetaData rsmd = oprs.getMetaData();
int colCount = rsmd.getColumnCount();
// Use an iterator to consume the result set
Iterator<OraclePgqlResult> itr = oprs.getResults().iterator();
while (itr.hasNext()) {
OraclePgqlResult opr = itr.next();
StringBuffer buff = new StringBuffer("[");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSXXX");
for (int i = 1; i <= colCount; i++) {
buff.append(rsmd.getColumnName(i)).append("=");
OraclePgqlColumnDescriptor.Type t = opr.getColumnType(i);
switch (t) {
case VERTEX:
// process vertex
OracleVertex v = opr.getVertex(i);
buff.append(v.getId()+" ");
break;
case EDGE:
// process edge
OracleEdge e = opr.getEdge(i);
buff.append(e.getId()+":"+e.getLabel()+" ");
break;
case VALUE:
// process value
int valueType = opr.getValueType(i);
Object obj = opr.getValue(i);
switch(valueType) {
case OraclePropertyGraphBase.TYPE_DT_BOOL:
buff.append("BOOLEAN:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_DATE:
buff.append("DATE:"+sdf.format((java.util.Date)obj)+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_DOUBLE:
buff.append("DOUBLE:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_FLOAT:
buff.append("FLOAT:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_INTEGER:
buff.append("INTEGER:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_LONG:
buff.append("LONG:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_STRING:
buff.append("STRING:"+obj.toString()+" ");
break;
}
break;
}
}
buff.append("]");
System.out.println(buff.toString());
}
}
}
PgqlExample7.javaは、次に示すtest_graphの出力を生成します。
-- Vertex Property Query --------------- [fname=STRING:Susan lname=STRING:Blue ] [fname=STRING:Bill lname=STRING:Brown ] [fname=STRING:John lname=STRING:Black ] -- Edge Property Query ----------------- [fname1=STRING:Susan fname2=STRING:Bill loc=STRING:CA ] [fname1=STRING:John fname2=STRING:Bill loc=STRING:GA ] [fname1=STRING:Susan fname2=STRING:John loc=STRING:CA ] [fname1=STRING:Susan fname2=STRING:Ray loc=STRING:CA ]
4.8.2.4 PGQL問合せのSQL変換の取得
OraclePgqlStatementメソッドとOraclePgqlPreparedStatementメソッドを介してPGQL問合せのSQL変換を取得できます。PGQL問合せの未加工のSQLは、次のいくつかの理由で役立ちます。
-
他のSQLベースのツールまたはインタフェース(たとえば、SQL PlusやSQL Developer)を使用してデータベースに対してSQLを直接実行できます。
-
生成されたSQLをカスタマイズおよびチューニングして、パフォーマンスの最適化やアプリケーションの特定要件の達成を実行できます。
-
PGQL副問合せを、Oracle Databaseに格納されている他のデータ(リレーショナル表、空間データ、およびJSONデータなど)と結合する大規模なSQL問合せを作成できます。
例4-31 PgqlExample8.java
PgqlExample8.javaは、PGQL問合せの未加工のSQL変換の取得方法を示しています。OraclePgqlStatementのtranslateQueryメソッドは、問合せからの戻り列とSQL変換自体に関する情報を含むOraclePgqlSqlTransオブジェクトを返します。
変換されたSQLは、PGQL問合せから射影される「論理」オブジェクトまたは値の型に応じて異なる列を返します。PGQLに射影される頂点またはエッジには、変換されたSQLに射影される、2つの対応する列があります。
-
$IT : ID型– NVARCHAR(1): 「V」は頂点、「E」はエッジ。
-
$ID :頂点IDまたはエッジID – NUMBER: VT$表とGE$表内のVID列またはEID例と同じ内容
PGQLに射影されているプロパティ値または定数スカラー値には、変換されたSQLに射影される4つの対応する列があります。
-
$T :値の型– NUMBER: VT$表とGE$表内のT列と同じ内容
-
$V:値– NVARCHAR2(15000): VT$表とGE$表内のV列と同じ内容
-
$VN:数値の値– NUMBER: VT$表とGE$表内のVN列と同じ内容
-
$VT: Temporal値– TIMESTAMP WITH TIME ZONE: VT$表とGE$表内のVT列と同じ内容
import oracle.pg.rdbms.*;
import oracle.pg.common.*;
import java.util.*;
import java.text.*;
import java.sql.*;
import java.time.*;
import java.time.format.*;
/**
* This example shows how to obtain the SQL translation for a PGQL query.
*/
public class PgqlExample8
{
public static void main(String[] szArgs) throws Exception
{
int iArgIdx=0;
String szHost = szArgs[iArgIdx++];
String szPort = szArgs[iArgIdx++];
String szSID = szArgs[iArgIdx++];
String szUser = szArgs[iArgIdx++];
String szPassword = szArgs[iArgIdx++];
String szGraph = szArgs[iArgIdx++];
String szVertexFile = szArgs[iArgIdx++];
String szEdgeFile = szArgs[iArgIdx++];
Oracle oracle = null;
OraclePropertyGraph opg = null;
OraclePgqlStatement ops = null;
try {
// Create a connection to Oracle
oracle =
new Oracle("jdbc:oracle:thin:@"+szHost+":"+szPort +":"+szSID, szUser, szPassword);
// Create a property graph
opg = OraclePropertyGraph.getInstance(oracle, szGraph);
// Clear any existing data
opg.clearRepository();
// Load data
OraclePropertyGraphDataLoader opgLoader = OraclePropertyGraphDataLoader.getInstance();
opgLoader.loadData(opg, szVertexFile, szEdgeFile, 1);
// Execute query to get an OraclePgqlResultSet object
String pgql =
"SELECT v1, v1.fname AS fname1, e, e.since AS since "+
"WHERE (v1)-[e:knows]->(v2)";
// Create an OraclePgqlStatement
ops = OraclePgqlExecutionFactory.createStatement(opg);
// Get the SQL translation
OraclePgqlSqlTrans sqlTrans = ops.translateQuery(pgql,"");
// Get the return column descriptions
OraclePgqlColumnDescriptor[] 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 (ops != null) {
ops.close();
}
// close the property graph
if (opg != null) {
opg.shutdown();
}
// close oracle
if (oracle != null) {
oracle.dispose();
}
}
}
/**
* Prints return columns for a SQL translation
*/
static void printReturnCols(OraclePgqlColumnDescriptor[] cols) throws Exception
{
StringBuffer buff = new StringBuffer("");
for (int i = 0; i < cols.length; i++) {
String colName = cols[i].getColName();
OraclePgqlColumnDescriptor.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は、次に示すtest_graphの出力を生成します。
-- 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.SVID AS "v1$ID", T1.T AS "fname1$T", T1.V AS "fname1$V", T1.VN AS "fname1$VN", T1.VT AS "fname1$VT", n'E' AS "e$IT", T0.EID AS "e$ID", T0.T AS "since$T", T0.V AS "since$V", T0.VN AS "since$VN", T0.VT AS "since$VT" FROM "SCOTT".graph1GE$ T0, "SCOTT".graph1VT$ T1 WHERE T0.K=n'since' AND T1.K=n'fname' AND T0.SVID=T1.VID AND (T0.EL = n'knows')
例4-32 PgqlExample9.java
また、バインド変数を含むPGQL問合せのSQL変換も取得できます。この場合、対応するSQL変換にもバインド変数が含まれます。OraclePgqlSqlTransインタフェースには、SQL問合せにバインドされている必要があるJavaオブジェクトの順序付きリスト(リストの最初のオブジェクトは1の位置に設定され、2番目は2の位置に設定されている必要がある)を返すgetSqlBvListメソッドがあります。
PgqlExample9.javaは、バインド変数を使用したPGQL問合せのSQLの取得および実行方法を示しています。
import oracle.pg.rdbms.*;
import oracle.pg.common.*;
import java.util.*;
import java.text.*;
import java.sql.*;
import java.time.*;
import java.time.format.*;
/**
* 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[] szArgs) throws Exception
{
int iArgIdx=0;
String szHost = szArgs[iArgIdx++];
String szPort = szArgs[iArgIdx++];
String szSID = szArgs[iArgIdx++];
String szUser = szArgs[iArgIdx++];
String szPassword = szArgs[iArgIdx++];
String szGraph = szArgs[iArgIdx++];
String szVertexFile = szArgs[iArgIdx++];
String szEdgeFile = szArgs[iArgIdx++];
Oracle oracle = null;
OraclePropertyGraph opg = null;
OraclePgqlPreparedStatement opps = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// Create a connection to Oracle
oracle =
new Oracle("jdbc:oracle:thin:@"+szHost+":"+szPort +":"+szSID, szUser, szPassword);
// Create a property graph
opg = OraclePropertyGraph.getInstance(oracle, szGraph);
// Clear any existing data
opg.clearRepository();
// Load data
OraclePropertyGraphDataLoader opgLoader = OraclePropertyGraphDataLoader.getInstance();
opgLoader.loadData(opg, szVertexFile, szEdgeFile, 1);
// Execute query to get an OraclePgqlResultSet object
String pgql =
"SELECT v1, v1.fname AS fname1, v1.age AS age, ? as constVal "+
"WHERE (v1), v1.fname = ? OR v1.age < ?";
// Create an OraclePgqlStatement
opps = OraclePgqlExecutionFactory.prepareStatement(opg, pgql);
// set bind values
opps.setDouble(1, 2.05d);
opps.setString(2, "Bill");
opps.setInt(3, 35);
// Get the SQL translation
OraclePgqlSqlTrans sqlTrans = opps.translateQuery("");
// Get the SQL String
String sqlStr = sqlTrans.getSqlTranslation();
// Get the return column descriptions
OraclePgqlColumnDescriptor[] 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
ps = oracle.getConnection().prepareStatement("SELECT COUNT(*) FROM ("+sqlStr+")");
// 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();
}
// close the SQL statement
if (ps != null) {
ps.close();
}
// close the statement
if (opps != null) {
opps.close();
}
// close the property graph
if (opg != null) {
opg.shutdown();
}
// close oracle
if (oracle != null) {
oracle.dispose();
}
}
}
/**
* Prints return columns for a SQL translation
*/
static void printReturnCols(OraclePgqlColumnDescriptor[] cols) throws Exception
{
StringBuffer buff = new StringBuffer("");
for (int i = 0; i < cols.length; i++) {
String colName = cols[i].getColName();
OraclePgqlColumnDescriptor.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は、次に示すtest_graphの出力を生成します。
-- 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.VID AS "v1$ID", T0.T AS "fname1$T", T0.V AS "fname1$V", T0.VN AS "fname1$VN", T0.VT AS "fname1$VT", T1.T AS "age$T", T1.V AS "age$V", T1.VN AS "age$VN", T1.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 "SCOTT".graph1VT$ T0, "SCOTT".graph1VT$ T1 WHERE T0.K=n'fname' AND T1.K=n'age' AND T0.VID=T1.VID AND ((T0.T = 1 AND T0.V = ?) OR T1.VN < ?) -- Bind Values -------------------------- 2.05 2.05 Bill 35 -- Execute Query: Result has 2 rows --
4.8.2.5 PGQL変換および実行の追加オプション
PGQL問合せの変換および実行に影響を与える複数のオプションがあります。問合せオプションを設定する主な方法は次のとおりです。
-
executeQueryおよびtranslateQueryに対して明示的引数を使用する -
executeQueryおよびtranslateQueryのoptions文字列引数でフラグを使用する -
Java JVM引数を使用する
次の表に、PGQLの変換と実行に使用できる問合せ引数をまとめています。
表4-2 PGQLの変換および実行のオプション
| オプション | デフォルト | 明示的引数 | オプション・フラグ | JVM引数 |
|---|---|---|---|---|
|
並列度 |
1 |
parallel |
なし |
なし |
|
タイムアウト |
unlimited |
timeout |
なし |
なし |
|
動的サンプリング |
2 |
dynamicSampling |
なし |
なし |
|
結果の最大数 |
unlimited |
maxResults |
なし |
なし |
|
GT$表の使用 |
off |
なし |
USE_GT_TAB=T |
-Doracle.pg.rdbms.pgql.useGtTab=true |
|
CONNECT BYの使用 |
off |
なし |
USE_RW=F |
-Doracle.pg.rdbms.pgql.useRW=false |
|
明確な再帰的WITHの使用 |
off |
なし |
USE_DIST_RW=T |
-Doracle.pg.rdbms.pgql.useDistRW=true |
|
パスの最大長 |
unlimited |
なし |
MAX_PATH_LEN=n |
-Doracle.pg.rdbms.pgql.maxPathLen=n |
|
partialの設定 |
false |
なし |
EDGE_SET_PARTIAL=T |
-Doracle.pg.rdbms.pgql.edgeSetPartial=true |
4.8.2.5.1 明示的引数によって制御される問合せオプション
一部の問合せオプションは、Java APIのメソッドに対する明示的引数によって制御されます。
-
OraclePgqlStatementのexecuteQueryメソッドには、タイムアウト(秒単位)、並列度、問合せ識別子(将来の使用のために予約されている)、オプティマイザ動的サンプリング、および最大結果数の明示的引数があります。 -
translateQueryメソッドには、並列性、オプティマイザ動的サンプリング、および最大結果数の明示的引数があります。また、OraclePgqlPreparedStatementには、executeQueryおよびtranslateQuery用にこれらの同じ追加引数が用意されています。
例4-33 PgqlExample10.java
PgqlExample10.javaは、明示的引数によって制御される追加オプションを指定したPGQL問合せの実行を示しています。
import oracle.pg.rdbms.*;
import oracle.pg.common.*;
import java.util.*;
import java.text.*;
/**
* This example shows how to execute a PGQL query with various options.
*/
public class PgqlExample10
{
public static void main(String[] szArgs) throws Exception
{
int iArgIdx=0;
String szHost = szArgs[iArgIdx++];
String szPort = szArgs[iArgIdx++];
String szSID = szArgs[iArgIdx++];
String szUser = szArgs[iArgIdx++];
String szPassword = szArgs[iArgIdx++];
String szGraph = szArgs[iArgIdx++];
String szVertexFile = szArgs[iArgIdx++];
String szEdgeFile = szArgs[iArgIdx++];
Oracle oracle = null;
OraclePropertyGraph opg = null;
OraclePgqlStatement ops = null;
OraclePgqlResultSet oprs= null;
try {
// Create a connection to Oracle
oracle =
new Oracle("jdbc:oracle:thin:@"+szHost+":"+szPort +":"+szSID, szUser, szPassword);
// Create a property graph
opg = OraclePropertyGraph.getInstance(oracle, szGraph);
// Clear any existing data
opg.clearRepository();
// Load data
OraclePropertyGraphDataLoader opgLoader = OraclePropertyGraphDataLoader.getInstance();
opgLoader.loadData(opg, szVertexFile, szEdgeFile, 1);
// Create an OraclePgqlStatement
ops = OraclePgqlExecutionFactory.createStatement(opg);
// Execute query to get an OraclePgqlResultSet object
String pgql =
"SELECT v1.fname AS fname1, v2.fname AS fname2 "+
"WHERE (v1)-[:friendOf]->(v2)";
oprs = ops.executeQuery(pgql /* query string */,
100 /* timeout (sec): 0 is default and implies no timeout */,
2 /* parallel: 1 is default */,
1001 /* query id: 0 is default */,
6 /* dynamic sampling: 2 is default */,
50 /* max results: -1 is default and implies no limit */,
"" /* options */);
// Print query results
printResults(oprs);
}
finally {
// close the result set
if (oprs != null) {
oprs.close();
}
// close the statement
if (ops != null) {
ops.close();
}
// close the property graph
if (opg != null) {
opg.shutdown();
}
// close oracle
if (oracle != null) {
oracle.dispose();
}
}
}
/**
* Prints an OraclePgqlResultSet
*/
static void printResults(OraclePgqlResultSet oprs) throws Exception
{
// Use OraclePgqlResultSetMetaData object to determine number of result columns
OraclePgqlResultSetMetaData rsmd = oprs.getMetaData();
int colCount = rsmd.getColumnCount();
// Use an iterator to consume the result set
Iterator<OraclePgqlResult> itr = oprs.getResults().iterator();
while (itr.hasNext()) {
OraclePgqlResult opr = itr.next();
StringBuffer buff = new StringBuffer("[");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSXXX");
for (int i = 1; i <= colCount; i++) {
buff.append(rsmd.getColumnName(i)).append("=");
OraclePgqlColumnDescriptor.Type t = opr.getColumnType(i);
switch (t) {
case VERTEX:
// process vertex
OracleVertex v = opr.getVertex(i);
buff.append(v.getId()+" ");
break;
case EDGE:
// process edge
OracleEdge e = opr.getEdge(i);
buff.append(e.getId()+":"+e.getLabel()+" ");
break;
case VALUE:
// process value
int valueType = opr.getValueType(i);
Object obj = opr.getValue(i);
switch(valueType) {
case OraclePropertyGraphBase.TYPE_DT_BOOL:
buff.append("BOOLEAN:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_DATE:
buff.append("DATE:"+sdf.format((Date)obj)+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_DOUBLE:
buff.append("DOUBLE:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_FLOAT:
buff.append("FLOAT:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_INTEGER:
buff.append("INTEGER:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_LONG:
buff.append("LONG:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_STRING:
buff.append("STRING:"+obj.toString()+" ");
break;
}
break;
}
}
buff.append("]");
System.out.println(buff.toString());
}
}
}
PgqlExample10.javaは、次に示すtest_graphの出力を生成します。
[fname1=STRING:Bill fname2=STRING:John ] [fname1=STRING:Susan fname2=STRING:John ] [fname1=STRING:Ray fname2=STRING:Susan ] [fname1=STRING:John fname2=STRING:Bill ] [fname1=STRING:John fname2=STRING:Susan ]
4.8.2.5.2 GT$スケルトン表の使用
プロパティ・グラフ・リレーショナル・スキーマは、エッジが持っているプロパティの数に関係なく、グラフ内のエッジごとに1行を格納するGT$スケルトン表を定義します。デフォルトで、このスケルトン表にはデータは移入されていませんが、移入されている場合、PGQL問合せ実行は、多くの場合、GT$表を利用して、GE$表でのソート操作を回避できます。それにより、大幅にパフォーマンスが改善されます。
executeQueryおよびtranslateQueryのoptions引数に"USE_GT_TAB=T"を追加するか、Javaコマンドラインで-Doracle.pg.rdbms.pgql.useGtTab=trueを使用してGT$表の使用をオンにできます。
例4-34 PgqlExample11.java
PgqlExample11.javaは、GT$スケルトン表を使用する問合せを示します。
import oracle.pg.rdbms.*;
import oracle.pg.common.*;
import java.util.*;
import java.text.*;
import java.sql.*;
import java.time.*;
import java.time.format.*;
/**
* This example shows how to use the GT$ skeleton table for faster
* PGQL query execution.
*/
public class PgqlExample11
{
public static void main(String[] szArgs) throws Exception
{
int iArgIdx=0;
String szHost = szArgs[iArgIdx++];
String szPort = szArgs[iArgIdx++];
String szSID = szArgs[iArgIdx++];
String szUser = szArgs[iArgIdx++];
String szPassword = szArgs[iArgIdx++];
String szGraph = szArgs[iArgIdx++];
String szVertexFile = szArgs[iArgIdx++];
String szEdgeFile = szArgs[iArgIdx++];
Oracle oracle = null;
OraclePropertyGraph opg = null;
OraclePgqlStatement ops = null;
try {
// Create a connection to Oracle
oracle =
new Oracle("jdbc:oracle:thin:@"+szHost+":"+szPort +":"+szSID, szUser, szPassword);
// Create a property graph
opg = OraclePropertyGraph.getInstance(oracle, szGraph);
// Clear any existing data
opg.clearRepository();
// Load data
OraclePropertyGraphDataLoader opgLoader = OraclePropertyGraphDataLoader.getInstance();
opgLoader.loadData(opg, szVertexFile, szEdgeFile, 1);
// populate GT$ skeleton table with distinct edges
CallableStatement cs = null;
cs = oracle.getConnection().prepareCall(
"begin opg_graphop.populate_skeleton_tab(:1,:2); end;"
);
cs.setString(1,szGraph);
cs.setInt(2,1);
cs.execute();
cs.close();
// Execute query to get an OraclePgqlResultSet object
String pgql =
"SELECT v1, v2 "+
"WHERE (v1)-[knows]->(v2)";
// Create an OraclePgqlStatement
ops = OraclePgqlExecutionFactory.createStatement(opg);
// Get the SQL translation without GT table
OraclePgqlSqlTrans sqlTrans = ops.translateQuery(pgql,"");
// Print SQL translation
System.out.println("-- SQL Translation without GT Table ----------------------");
System.out.println(sqlTrans.getSqlTranslation());
// Get the SQL translation with GT table
sqlTrans = ops.translateQuery(pgql,"USE_GT_TAB=T");
// Print SQL translation
System.out.println("-- SQL Translation with GT Table -------------------------");
System.out.println(sqlTrans.getSqlTranslation());
}
finally {
// close the statement
if (ops != null) {
ops.close();
}
// close the property graph
if (opg != null) {
opg.shutdown();
}
// close oracle
if (oracle != null) {
oracle.dispose();
}
}
}
}
PgqlExample11.javaは、次に示すtest_graphの出力を生成します。
-- SQL Translation without GT Table ---------------------- SELECT n'V' AS "v1$IT", T0.SVID AS "v1$ID", n'V' AS "v2$IT", T0.DVID AS "v2$ID" FROM (SELECT DISTINCT EID, SVID, DVID,EL FROM "SCOTT".graph1GE$) T0 -- SQL Translation with GT Table ------------------------- SELECT n'V' AS "v1$IT", T0.SVID AS "v1$ID", n'V' AS "v2$IT", T0.DVID AS "v2$ID" FROM "SCOTT".graph1GT$ T0
4.8.2.5.3 パス問合せのオプション
PGQLでパス問合せを実行するためのオプションがいくつかあります。Oracle SQLには、CONNECT BY句または再帰的WITH句という2つの基本的評価メソッドがあります。再帰的WITHがデフォルトの評価メソッドになります。さらに、再帰的WITH評価メソッドをさらに変更して、問合せ評価の再帰的ステップ中にDISTINCT修飾子を含めることができます。各ステップで個別の頂点を計算することにより、連結度が高いグラフでの組合せの激増を防止できます。DISTINCT修飾子は、データベース内に特定のパラメータ("_recursive_with_control"=8)を設定する必要があるため、デフォルトでは追加されません。
検索するパスの最大長も制御できます。この場合のパスの長さは、*演算子と+演算子を評価する際に許可される反復数として定義されます。デフォルトの最大長は無制限です。
パスの評価オプションを要約すると次のようになります。
-
CONNECT BY: CONNECT BY句を使用するには、
options引数に'USE_RW=F'を指定するか、Javaコマンドラインに-Doracle.pg.rdbms.pgql.useRW=falseを指定します。 -
再帰的WITHでのDistinct修飾子:再帰的ステップでDISTINCT修飾子を使用するには、まずデータベース・セッションで
"_recursive_with_control"=8を設定してから、options引数に'USE_DIST_RW=T'を指定するか、Javaコマンドラインに-Doracle.pg.rdbms.pgql.useDistRW=trueを指定します。セッションで"_recursive_with_control"が8に設定されていない場合、「ORA-32486:再帰的WITH句の再帰的ブランチでのサポートされていない操作です」が発生します。 -
パス長の制限:nに対する*および+を評価する際の最大反復数を制限するには、問合せの
options引数に'MAX_PATH_LEN=n'を指定するか、Javaコマンドラインに-Doracle.pg.rdbms.pgql.maxPathLen=nを指定します。
例4-35 PgqlExample12.java
PgqlExample12.javaは、様々なオプションでのパス問合せの変換を示しています。
import oracle.pg.rdbms.*;
import oracle.pg.common.*;
import java.util.*;
import java.text.*;
import java.sql.*;
import java.time.*;
import java.time.format.*;
/**
* This example shows how to use various options with PGQL path queries.
*/
public class PgqlExample12
{
public static void main(String[] szArgs) throws Exception
{
int iArgIdx=0;
String szHost = szArgs[iArgIdx++];
String szPort = szArgs[iArgIdx++];
String szSID = szArgs[iArgIdx++];
String szUser = szArgs[iArgIdx++];
String szPassword = szArgs[iArgIdx++];
String szGraph = szArgs[iArgIdx++];
String szVertexFile = szArgs[iArgIdx++];
String szEdgeFile = szArgs[iArgIdx++];
Oracle oracle = null;
OraclePropertyGraph opg = null;
OraclePgqlStatement ops = null;
try {
// Create a connection to Oracle
oracle =
new Oracle("jdbc:oracle:thin:@"+szHost+":"+szPort +":"+szSID, szUser, szPassword);
// Create a property graph
opg = OraclePropertyGraph.getInstance(oracle, szGraph);
// Clear any existing data
opg.clearRepository();
// Load data
OraclePropertyGraphDataLoader opgLoader = OraclePropertyGraphDataLoader.getInstance();
opgLoader.loadData(opg, szVertexFile, szEdgeFile, 1);
// Set "_recursive_with_control"=8 to enable distinct optimization
// optimization for recursive with
Statement stmt = oracle.getConnection().createStatement();
stmt.executeUpdate("alter session set \"_recursive_with_control\"=8");
stmt.close();
// populate GT$ skeleton table with distinct edges
CallableStatement cs = null;
cs = oracle.getConnection().prepareCall(
"begin opg_graphop.populate_skeleton_tab(:1,:2); end;"
);
cs.setString(1,szGraph);
cs.setInt(2,1);
cs.execute();
cs.close();
// Path Query to illustrate options
String pgql =
"PATH fof := ()-[:friendOf]->() "+
"SELECT v1, v2 "+
"WHERE (v1 WITH ID() = 2)-/:fof*/->(v2)";
// Create an OraclePgqlStatement
ops = OraclePgqlExecutionFactory.createStatement(opg);
// get SQL translation with defaults - Non-distinct Recursive WITH
OraclePgqlSqlTrans sqlTrans =
ops.translateQuery(pgql /* query string */,
2 /* parallel: default is 1 */,
2 /* dynamic sampling: default is 2 */,
-1 /* max results: -1 implies no limit */,
" USE_GT_TAB=T " /* options */);
System.out.println("-- Default Path Translation --------------------");
System.out.println(sqlTrans.getSqlTranslation()+"\n");
// get SQL translation with DISTINCT reachability optimization
sqlTrans =
ops.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 USE_GT_TAB=T " /* options */);
System.out.println("-- DISTINCT RW Path Translation --------------------");
System.out.println(sqlTrans.getSqlTranslation()+"\n");
// get SQL translation with CONNECT BY
sqlTrans =
ops.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 USE_GT_TAB=T " /* options */);
System.out.println("-- CONNECT BY Path Translation --------------------");
System.out.println(sqlTrans.getSqlTranslation()+"\n");
}
finally {
// close the statement
if (ops != null) {
ops.close();
}
// close the property graph
if (opg != null) {
opg.shutdown();
}
// close oracle
if (oracle != null) {
oracle.dispose();
}
}
}
}
PgqlExample12.javaは、次に示すtest_graphの出力を生成します。
-- Default Path Translation -------------------- SELECT /*+ PARALLEL(2) */ * FROM(SELECT n'V' AS "v1$IT", T0.SVID AS "v1$ID", n'V' AS "v2$IT", T0.DVID AS "v2$ID" FROM (/*Path[*/SELECT DISTINCT SVID, DVID FROM ( SELECT 2 AS SVID, 2 AS DVID FROM SYS.DUAL UNION ALL SELECT SVID,DVID FROM (WITH RW (ROOT, SVID, DVID, LVL) AS ( SELECT ROOT, SVID, DVID, LVL FROM (SELECT SVID ROOT, SVID, DVID, 1 LVL FROM (SELECT T0.SVID AS SVID, T0.DVID AS DVID FROM "SCOTT".graph1GT$ T0 WHERE (T0.EL = n'friendOf')) WHERE SVID = 2 ) UNION ALL SELECT RW.ROOT, R.SVID, R.DVID, RW.LVL+1 FROM (SELECT T0.SVID AS SVID, T0.DVID AS DVID FROM "SCOTT".graph1GT$ T0 WHERE (T0.EL = n'friendOf')) R, RW WHERE RW.DVID = R.SVID ) CYCLE SVID SET cycle_col TO 1 DEFAULT 0 SELECT ROOT SVID, DVID FROM RW))/*]Path*/) T0 WHERE T0.SVID = 2) -- DISTINCT RW Path Translation -------------------- SELECT /*+ PARALLEL(2) */ * FROM(SELECT n'V' AS "v1$IT", T0.SVID AS "v1$ID", n'V' AS "v2$IT", T0.DVID AS "v2$ID" FROM (/*Path[*/SELECT DISTINCT SVID, DVID FROM ( SELECT 2 AS SVID, 2 AS DVID FROM SYS.DUAL UNION ALL SELECT SVID,DVID FROM (WITH RW (ROOT, SVID, DVID, LVL) AS ( SELECT ROOT, SVID, DVID, LVL FROM (SELECT SVID ROOT, SVID, DVID, 1 LVL FROM (SELECT T0.SVID AS SVID, T0.DVID AS DVID FROM "SCOTT".graph1GT$ T0 WHERE (T0.EL = n'friendOf')) WHERE SVID = 2 ) UNION ALL SELECT DISTINCT RW.ROOT, R.SVID, R.DVID, RW.LVL+1 FROM (SELECT T0.SVID AS SVID, T0.DVID AS DVID FROM "SCOTT".graph1GT$ T0 WHERE (T0.EL = n'friendOf')) R, RW WHERE RW.DVID = R.SVID ) CYCLE SVID SET cycle_col TO 1 DEFAULT 0 SELECT ROOT SVID, DVID FROM RW))/*]Path*/) T0 WHERE T0.SVID = 2) -- CONNECT BY Path Translation -------------------- SELECT /*+ PARALLEL(2) */ * FROM(SELECT n'V' AS "v1$IT", T0.SVID AS "v1$ID", n'V' AS "v2$IT", T0.DVID AS "v2$ID" FROM (/*Path[*/SELECT DISTINCT SVID, DVID FROM ( SELECT 2 AS SVID, 2 AS DVID FROM SYS.DUAL UNION ALL SELECT SVID, DVID FROM (SELECT CONNECT_BY_ROOT T0.SVID AS SVID, T0.DVID AS DVID FROM( SELECT T0.SVID AS SVID, T0.DVID AS DVID FROM "SCOTT".graph1GT$ T0 WHERE (T0.EL = n'friendOf')) T0 START WITH T0.SVID = 2 CONNECT BY NOCYCLE PRIOR DVID = SVID))/*]Path*/) T0 WHERE T0.SVID = 2)
デフォルトの再帰的WITH戦略を使用する最初の問合せの問合せ計画は、次のようになります。
-- default RW --------------------------------------------------------------------------------------- | Id | Operation | Name | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TEMP TABLE TRANSFORMATION | | | 2 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D66E7_135E00 | | 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_0FD9D66E7_135E00 | | 7 | PX BLOCK ITERATOR | | |* 8 | TABLE ACCESS FULL | GRAPH1GT$ | | 9 | PX COORDINATOR | | | 10 | PX SEND QC (RANDOM) | :TQ10000 | | 11 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D66E7_135E00 | | 12 | NESTED LOOPS | | | 13 | PX BLOCK ITERATOR | | |* 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66E7_135E00 | | 15 | PARTITION HASH ALL | | |* 16 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| GRAPH1GT$ | |* 17 | INDEX RANGE SCAN | GRAPH1XSG$ | | 18 | PX COORDINATOR | | | 19 | PX SEND QC (RANDOM) | :TQ30001 | | 20 | VIEW | | | 21 | HASH UNIQUE | | | 22 | PX RECEIVE | | | 23 | PX SEND HASH | :TQ30000 | | 24 | HASH UNIQUE | | | 25 | VIEW | | | 26 | UNION-ALL | | | 27 | PX SELECTOR | | | 28 | FAST DUAL | | | 29 | VIEW | | |* 30 | VIEW | | | 31 | PX BLOCK ITERATOR | | | 32 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66E7_135E00 | ---------------------------------------------------------------------------------------
再帰的ステップにDISTINCT修飾子を追加する2番目の問合せの問合せ計画は、次のようになります。
------------------------------------------------------------------------------------------- | Id | Operation | Name | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TEMP TABLE TRANSFORMATION | | | 2 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D66E2_135E00 | | 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_0FD9D66E2_135E00 | | 7 | PX BLOCK ITERATOR | | |* 8 | TABLE ACCESS FULL | GRAPH1GT$ | | 9 | PX COORDINATOR | | | 10 | PX SEND QC (RANDOM) | :TQ10001 | | 11 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D66E2_135E00 | | 12 | SORT GROUP BY | | | 13 | PX RECEIVE | | | 14 | PX SEND HASH | :TQ10000 | | 15 | SORT GROUP BY | | | 16 | NESTED LOOPS | | | 17 | PX BLOCK ITERATOR | | |* 18 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66E2_135E00 | | 19 | PARTITION HASH ALL | | |* 20 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| GRAPH1GT$ | |* 21 | INDEX RANGE SCAN | GRAPH1XSG$ | | 22 | PX COORDINATOR | | | 23 | PX SEND QC (RANDOM) | :TQ30001 | | 24 | VIEW | | | 25 | HASH UNIQUE | | | 26 | PX RECEIVE | | | 27 | PX SEND HASH | :TQ30000 | | 28 | HASH UNIQUE | | | 29 | VIEW | | | 30 | UNION-ALL | | | 31 | PX SELECTOR | | | 32 | FAST DUAL | | | 33 | VIEW | | |* 34 | VIEW | | | 35 | PX BLOCK ITERATOR | | | 36 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66E2_135E00 | -------------------------------------------------------------------------------------------
CONNECTY BYを使用する3番目の問合せの問合せ計画は、次のようになります。
------------------------------------------------------------------ | Id | Operation | Name | ------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | 1 | VIEW | | | 2 | HASH UNIQUE | | | 3 | VIEW | | | 4 | UNION-ALL | | | 5 | FAST DUAL | | |* 6 | VIEW | | |* 7 | CONNECT BY NO FILTERING WITH START-WITH| | | 8 | PX COORDINATOR | | | 9 | PX SEND QC (RANDOM) | :TQ10000 | | 10 | PX BLOCK ITERATOR | | |* 11 | TABLE ACCESS FULL | GRAPH1GT$ | ------------------------------------------------------------------
例4-36 PgqlExample13.java
PgqlExample13.javaは、パス問合せ評価中に長さ制限を設定する方法を示しています。
import oracle.pg.rdbms.*;
import oracle.pg.common.*;
import java.util.*;
import java.text.*;
import java.sql.*;
import java.time.*;
import java.time.format.*;
/**
* This example shows how to use the maximum path length option for
* PGQL path queries.
*/
public class PgqlExample13
{
public static void main(String[] szArgs) throws Exception
{
int iArgIdx=0;
String szHost = szArgs[iArgIdx++];
String szPort = szArgs[iArgIdx++];
String szSID = szArgs[iArgIdx++];
String szUser = szArgs[iArgIdx++];
String szPassword = szArgs[iArgIdx++];
String szGraph = szArgs[iArgIdx++];
String szVertexFile = szArgs[iArgIdx++];
String szEdgeFile = szArgs[iArgIdx++];
Oracle oracle = null;
OraclePropertyGraph opg = null;
OraclePgqlStatement ops = null;
OraclePgqlResultSet oprs= null;
try {
// Create a connection to Oracle
oracle =
new Oracle("jdbc:oracle:thin:@"+szHost+":"+szPort +":"+szSID, szUser, szPassword);
// Create a property graph
opg = OraclePropertyGraph.getInstance(oracle, szGraph);
// Clear any existing data
opg.clearRepository();
// Load data
OraclePropertyGraphDataLoader opgLoader = OraclePropertyGraphDataLoader.getInstance();
opgLoader.loadData(opg, szVertexFile, szEdgeFile, 1);
// Path Query to illustrate options
String pgql =
"PATH fof := ()-[:friendOf]->() "+
"SELECT v1.fname AS fname1, v2.fname AS fname2 "+
"WHERE (v1 WITH fname = 'Ray')-/:fof*/->(v2)";
// Create an OraclePgqlStatement
ops = OraclePgqlExecutionFactory.createStatement(opg);
// execute query for 1-hop
oprs = ops.executeQuery(pgql, " MAX_PATH_LEN=1 ");
// print results
System.out.println("-- Results for 1-hop ----------------");
printResults(oprs);
// close result set
oprs.close();
// execute query for 2-hop
oprs = ops.executeQuery(pgql, " MAX_PATH_LEN=2 ");
// print results
System.out.println("-- Results for 2-hop ----------------");
printResults(oprs);
// close result set
oprs.close();
// execute query for 3-hop
oprs = ops.executeQuery(pgql, " MAX_PATH_LEN=3 ");
// print results
System.out.println("-- Results for 3-hop ----------------");
printResults(oprs);
// close result set
oprs.close();
}
finally {
// close the result set
if (oprs != null) {
oprs.close();
}
// close the statement
if (ops != null) {
ops.close();
}
// close the property graph
if (opg != null) {
opg.shutdown();
}
// close oracle
if (oracle != null) {
oracle.dispose();
}
}
}
/**
* Prints an OraclePgqlResultSet
*/
static void printResults(OraclePgqlResultSet oprs) throws Exception
{
// Use OraclePgqlResultSetMetaData object to determine number of result columns
OraclePgqlResultSetMetaData rsmd = oprs.getMetaData();
int colCount = rsmd.getColumnCount();
// Use an iterator to consume the result set
Iterator<OraclePgqlResult> itr = oprs.getResults().iterator();
while (itr.hasNext()) {
OraclePgqlResult opr = itr.next();
StringBuffer buff = new StringBuffer("[");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSXXX");
for (int i = 1; i <= colCount; i++) {
buff.append(rsmd.getColumnName(i)).append("=");
OraclePgqlColumnDescriptor.Type t = opr.getColumnType(i);
switch (t) {
case VERTEX:
// process vertex
OracleVertex v = opr.getVertex(i);
buff.append(v.getId()+" ");
break;
case EDGE:
// process edge
OracleEdge e = opr.getEdge(i);
buff.append(e.getId()+":"+e.getLabel()+" ");
break;
case VALUE:
// process value
int valueType = opr.getValueType(i);
Object obj = opr.getValue(i);
switch(valueType) {
case OraclePropertyGraphBase.TYPE_DT_BOOL:
buff.append("BOOLEAN:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_DATE:
buff.append("DATE:"+sdf.format((java.util.Date)obj)+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_DOUBLE:
buff.append("DOUBLE:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_FLOAT:
buff.append("FLOAT:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_INTEGER:
buff.append("INTEGER:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_LONG:
buff.append("LONG:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_STRING:
buff.append("STRING:"+obj.toString()+" ");
break;
}
break;
}
}
buff.append("]");
System.out.println(buff.toString());
}
}
}
PgqlExample13.javaは、次に示すtest_graphの出力を生成します。
-- Results for 1-hop ---------------- [fname1=STRING:Ray fname2=STRING:Susan ] [fname1=STRING:Ray fname2=STRING:Ray ] -- Results for 2-hop ---------------- [fname1=STRING:Ray fname2=STRING:Susan ] [fname1=STRING:Ray fname2=STRING:Ray ] [fname1=STRING:Ray fname2=STRING:John ] -- Results for 3-hop ---------------- [fname1=STRING:Ray fname2=STRING:Susan ] [fname1=STRING:Ray fname2=STRING:Bill ] [fname1=STRING:Ray fname2=STRING:Ray ] [fname1=STRING:Ray fname2=STRING:John ]
4.8.2.5.4 Partialオブジェクトの構築のオプション
問合せ結果からエッジを読み込んでいるとき、開始および終了頂点をローカル・キャッシュに追加する際に、次の2つ動作が可能です。
-
エッジ自体から入手可能な頂点IDのみを追加します。このオプションは、効率化のためのデフォルトです。
-
項点IDを追加し、開始および終了頂点のすべてのプロパティを取得します。このオプションの場合、
options引数に'EDGE_SET_PARTIAL=T'を指定するか、Javaコマンドラインに'-Doracle.pg.rdbms.pgql.edgeSetPartial=true'を指定できます。
例4-37 PgqlExample14.java
PgqlExample14.javaは、この動作の違いを示しています。このプログラムは、まずすべてのエッジを取得するための問合せを実行します。それにより、インシデントの頂点がローカル・キャッシュに追加されます。2番目の問合せはすべての頂点を取得します。次に、プログラムは、各OracleVertexオブジェクトを表示して、どのプロパティがロードされているかを示します。
import oracle.pg.rdbms.*;
import oracle.pg.common.*;
import java.util.*;
import java.text.*;
import java.sql.*;
import java.time.*;
import java.time.format.*;
/**
* This example shows the behavior of the EDGE_SET_PARTIAL option with
* PGQL queries.
*/
public class PgqlExample14
{
public static void main(String[] szArgs) throws Exception
{
int iArgIdx=0;
String szHost = szArgs[iArgIdx++];
String szPort = szArgs[iArgIdx++];
String szSID = szArgs[iArgIdx++];
String szUser = szArgs[iArgIdx++];
String szPassword = szArgs[iArgIdx++];
String szGraph = szArgs[iArgIdx++];
String szVertexFile = szArgs[iArgIdx++];
String szEdgeFile = szArgs[iArgIdx++];
Oracle oracle = null;
OraclePropertyGraph opg = null;
OraclePgqlStatement ops = null;
OraclePgqlResultSet oprs= null;
try {
// Create a connection to Oracle
oracle =
new Oracle("jdbc:oracle:thin:@"+szHost+":"+szPort +":"+szSID, szUser, szPassword);
// Create a property graph
opg = OraclePropertyGraph.getInstance(oracle, szGraph);
// Clear any existing data
opg.clearRepository();
// Load data
OraclePropertyGraphDataLoader opgLoader = OraclePropertyGraphDataLoader.getInstance();
opgLoader.loadData(opg, szVertexFile, szEdgeFile, 1);
// Query to illustrate set partial
String pgql =
"SELECT e "+
"WHERE (v1)-[e:knows]->(v2)";
// Create an OraclePgqlStatement
ops = OraclePgqlExecutionFactory.createStatement(opg);
// execute query for 1-hop
oprs = ops.executeQuery(pgql, " ");
// print results
System.out.println("-- Results for edge query -----------------");
printResults(oprs);
// close result set
oprs.close();
// Query to retrieve vertices
pgql =
"SELECT v "+
"WHERE (v)";
// Get each vertex object in result and print with toString()
oprs = ops.executeQuery(pgql, " ");
// iterate through result
System.out.println("-- Vertex objects retrieved from vertex query --");
Iterator<OraclePgqlResult> itr = oprs.getResults().iterator();
while (itr.hasNext()) {
OraclePgqlResult opr = itr.next();
OracleVertex v = opr.getVertex(1);
System.out.println(v.toString());
}
// close result set
oprs.close();
デフォルトの動作でのPgqlExample14.javaの出力は次のようになります。
-- Results for edge query -----------------
[e=11:knows ]
[e=6:knows ]
[e=10:knows ]
[e=5:knows ]
[e=4:knows ]
[e=13:knows ]
[e=12:knows ]
[e=9:knows ]
[e=8:knows ]
[e=15:knows ]
[e=7:knows ]
[e=14:knows ]
-- Vertex objects retrieved from vertex query --
Vertex ID 3 {}
Vertex ID 0 {}
Vertex ID 2 {}
Vertex ID 1 {}
-Doracle.pg.rdbms.pgql.edgeSetPartial=trueを使用したPgqlExample14.javaの出力は次のようになります。
-- Results for edge query -----------------
[e=11:knows ]
[e=6:knows ]
[e=10:knows ]
[e=5:knows ]
[e=4:knows ]
[e=13:knows ]
[e=12:knows ]
[e=9:knows ]
[e=8:knows ]
[e=15:knows ]
[e=7:knows ]
[e=14:knows ]
-- Vertex objects retrieved from vertex query --
Vertex ID 3 {bval:bol:false, fname:str:Susan, lname:str:Blue, mval:bol:false, age:int:35}
Vertex ID 0 {bval:bol:true, fname:str:Bill, lname:str:Brown, mval:str:y, age:int:40}
Vertex ID 2 {fname:str:Ray, lname:str:Green, mval:dat:1985-01-01 04:00:00.0, age:int:41}
Vertex ID 1 {bval:bol:true, fname:str:John, lname:str:Black, mval:int:27, age:int:30}
4.8.2.6 別のユーザーのプロパティ・グラフの問合せ
データベースの適切な権限を付与されている場合は、別のユーザーのプロパティ・グラフのデータを問い合せることができます。たとえば、SCOTTのスキーマ内のGRAPH1を問い合せるには、SCOTT.GRAPH1GE$およびSCOTT.GRAPH1VT$に対するREAD権限を持っている必要があります。また、USE_GT_TAB=Tオプションを指定して問い合せる場合は、SCOTT.GRAPH1GT$に対するREAD権限を持っている必要があります。
例4-38 PgqlExample15.java
PgqlExample15.javaは、他のユーザーがSCOTTのスキーマ内のグラフを問い合せる方法を示しています。
import oracle.pg.rdbms.*;
import oracle.pg.common.*;
import java.util.*;
import java.text.*;
/**
* This example shows how to query a property graph located in another user's
* schema. READ privilege on GE$, VT$ and GT$ 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[] szArgs) throws Exception
{
int iArgIdx=0;
String szHost = szArgs[iArgIdx++];
String szPort = szArgs[iArgIdx++];
String szSID = szArgs[iArgIdx++];
String szUser = szArgs[iArgIdx++];
String szPassword = szArgs[iArgIdx++];
String szGraph = szArgs[iArgIdx++];
String szVertexFile = szArgs[iArgIdx++];
String szEdgeFile = szArgs[iArgIdx++];
Oracle oracle = null;
OraclePropertyGraph opg = null;
OraclePgqlStatement ops = null;
OraclePgqlResultSet oprs= null;
try {
// Create a connection to Oracle
oracle =
new Oracle("jdbc:oracle:thin:@"+szHost+":"+szPort +":"+szSID, szUser, szPassword);
// Get a property graph instance for Scott's graph
opg = OraclePropertyGraph.getInstance(oracle, "SCOTT", szGraph, 1, 1, null, null);
// Create an OraclePgqlStatement
ops = OraclePgqlExecutionFactory.createStatement(opg);
// Execute query to get an OraclePgqlResultSet object
String pgql =
"SELECT v.fname AS fname, v.lname AS lname "+
"WHERE (v)";
oprs = ops.executeQuery(pgql, "");
// Print query results
printResults(oprs);
}
finally {
// close the result set
if (oprs != null) {
oprs.close();
}
// close the statement
if (ops != null) {
ops.close();
}
// close oracle
if (oracle != null) {
oracle.dispose();
}
}
}
/**
* Prints an OraclePgqlResultSet
*/
static void printResults(OraclePgqlResultSet oprs) throws Exception
{
// Use OraclePgqlResultSetMetaData object to determine number of result columns
OraclePgqlResultSetMetaData rsmd = oprs.getMetaData();
int colCount = rsmd.getColumnCount();
// Use an iterator to consume the result set
Iterator<OraclePgqlResult> itr = oprs.getResults().iterator();
while (itr.hasNext()) {
OraclePgqlResult opr = itr.next();
StringBuffer buff = new StringBuffer("[");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSXXX");
for (int i = 1; i <= colCount; i++) {
buff.append(rsmd.getColumnName(i)).append("=");
OraclePgqlColumnDescriptor.Type t = opr.getColumnType(i);
switch (t) {
case VERTEX:
// process vertex
OracleVertex v = opr.getVertex(i);
buff.append(v.getId()+" ");
break;
case EDGE:
// process edge
OracleEdge e = opr.getEdge(i);
buff.append(e.getId()+":"+e.getLabel()+" ");
break;
case VALUE:
// process value
int valueType = opr.getValueType(i);
Object obj = opr.getValue(i);
switch(valueType) {
case OraclePropertyGraphBase.TYPE_DT_BOOL:
buff.append("BOOLEAN:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_DATE:
buff.append("DATE:"+sdf.format((Date)obj)+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_DOUBLE:
buff.append("DOUBLE:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_FLOAT:
buff.append("FLOAT:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_INTEGER:
buff.append("INTEGER:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_LONG:
buff.append("LONG:"+obj.toString()+" ");
break;
case OraclePropertyGraphBase.TYPE_DT_STRING:
buff.append("STRING:"+obj.toString()+" ");
break;
}
break;
}
}
buff.append("]");
System.out.println(buff.toString());
}
}
}
次のSQL文は、データベース・ユーザーUSER2を作成し、必要な権限を付与します。また、OraclePropertyGraph.grantAccess Java APIを使用しても同じ効果を得ることができます。
SQL> grant connect, resource, unlimited tablespace to user2 identified by user2; Grant succeeded. SQL> grant read on scott.graph1vt$ to user2; Grant succeeded. SQL> grant read on scott.graph1ge$ to user2; Grant succeeded. SQL> grant read on scott.graph1gt$ to user2; Grant succeeded.
データベースにUSER2として接続した場合のtest_graphデータ・セットのPgqlExample15.javaの出力は次のようになります。PgqlExample15を実行する前に、test_graphはユーザーSCOTTによってGRAPH1としてすでにロードされている必要があることに注意してください。
[fname=STRING:Susan lname=STRING:Blue ] [fname=STRING:Bill lname=STRING:Brown ] [fname=STRING:Ray lname=STRING:Green ] [fname=STRING:John lname=STRING:Black ]
4.8.2.7 PGQLでの問合せオプティマイザ・ヒントの使用
Java APIでは、PGQL問合せを実行する際の結合タイプに影響を与える問合せオプティマイザ・ヒントが許可されます。OraclePgqlStatementおよびOraclePgqlPreparedStatement内のexecuteQueryメソッドおよびtranslateQueryメソッドは、対応するSQL問合せの問合せ計画に影響を与えるために、options引数で次の文字列を受け入れます。
-
ALL_EDGE_NL – $GE表と$GT表に関連するすべての結合にネステッド・ループ結合を使用します。
-
ALL_EDGE_HASH – $GE表と$GT表に関連するすべての結合にHASH結合を使用します。
-
ALL_VERTEX_NL – $VT表に関連するすべての結合にネステッド・ループ結合を使用します。
-
ALL_VERTEX_HASH – $VT表に関連するすべての結合にHASH結合を使用します。
例4-39 PgqlExample16.java
PgqlExample16.javaは、オプティマイザ・ヒントを使用して、グラフ・トラバースに使用される結合に影響を与える方法を示しています。
import oracle.pg.rdbms.*;
import oracle.pg.common.*;
import java.util.*;
import java.text.*;
import java.sql.*;
import java.time.*;
import java.time.format.*;
/**
* This example shows how to use query optimizer hints with PGQL queries.
*/
public class PgqlExample16
{
public static void main(String[] szArgs) throws Exception
{
int iArgIdx=0;
String szHost = szArgs[iArgIdx++];
String szPort = szArgs[iArgIdx++];
String szSID = szArgs[iArgIdx++];
String szUser = szArgs[iArgIdx++];
String szPassword = szArgs[iArgIdx++];
String szGraph = szArgs[iArgIdx++];
String szVertexFile = szArgs[iArgIdx++];
String szEdgeFile = szArgs[iArgIdx++];
Oracle oracle = null;
OraclePropertyGraph opg = null;
OraclePgqlStatement ops = null;
try {
// Create a connection to Oracle
oracle =
new Oracle("jdbc:oracle:thin:@"+szHost+":"+szPort +":"+szSID, szUser, szPassword);
// Create a property graph
opg = OraclePropertyGraph.getInstance(oracle, szGraph);
// Clear any existing data
opg.clearRepository();
// Load data
OraclePropertyGraphDataLoader opgLoader = OraclePropertyGraphDataLoader.getInstance();
opgLoader.loadData(opg, szVertexFile, szEdgeFile, 1);
// populate GT$ skeleton table with distinct edges
CallableStatement cs = null;
cs = oracle.getConnection().prepareCall(
"begin opg_graphop.populate_skeleton_tab(:1,:2); end;"
);
cs.setString(1,szGraph);
cs.setInt(2,1);
cs.execute();
cs.close();
// Query to illustrate join hints
String pgql =
"SELECT v1, v4 "+
"WHERE (v1)-[:friendOf]->(v2)-[:friendOf]->(v3)-[:friendOf]->(v4)";
// Create an OraclePgqlStatement
ops = OraclePgqlExecutionFactory.createStatement(opg);
// get SQL translation with hash join hint
OraclePgqlSqlTrans sqlTrans =
ops.translateQuery(pgql /* query string */,
" USE_GT_TAB=T 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 =
ops.translateQuery(pgql /* query string */,
" USE_GT_TAB=T 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 (ops != null) {
ops.close();
}
// close the property graph
if (opg != null) {
opg.shutdown();
}
// close oracle
if (oracle != null) {
oracle.dispose();
}
}
}
}
PgqlExample16.javaでのtest_graphの出力は次のようになります。
-- Query with ALL_EDGE_HASH -------------------- SELECT /*+ USE_HASH(T0 T1 T2) */ n'V' AS "v1$IT", T0.SVID AS "v1$ID", n'V' AS "v4$IT", T2.DVID AS "v4$ID" FROM "SCOTT".graph1GT$ T0, "SCOTT".graph1GT$ T1, "SCOTT".graph1GT$ T2 WHERE T0.DVID=T1.SVID AND T1.DVID=T2.SVID AND (T2.EL = n'friendOf') AND (T0.EL = n'friendOf') AND (T1.EL = n'friendOf') -- Query with ALL_EDGE_NL --------------------- SELECT /*+ USE_NL(T0 T1 T2) */ n'V' AS "v1$IT", T0.SVID AS "v1$ID", n'V' AS "v4$IT", T2.DVID AS "v4$ID" FROM "SCOTT".graph1GT$ T0, "SCOTT".graph1GT$ T1, "SCOTT".graph1GT$ T2 WHERE T0.DVID=T1.SVID AND T1.DVID=T2.SVID AND (T2.EL = n'friendOf') AND (T0.EL = n'friendOf') AND (T1.EL = n'friendOf')
ALL_EDGE_HASHを使用する最初の問合せの問合せ計画は次のようになります。
------------------------------------------ | Id | Operation | Name | ------------------------------------------ | 0 | SELECT STATEMENT | | |* 1 | HASH JOIN | | | 2 | PARTITION HASH ALL | | |* 3 | TABLE ACCESS FULL | GRAPH1GT$ | |* 4 | HASH JOIN | | | 5 | PARTITION HASH ALL| | |* 6 | TABLE ACCESS FULL| GRAPH1GT$ | | 7 | PARTITION HASH ALL| | |* 8 | TABLE ACCESS FULL| GRAPH1GT$ | ------------------------------------------
ALL_EDGE_NLを使用する2番目の問合せの問合せ計画は次のようになります。
------------------------------------------------------------------- | Id | Operation | Name | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | NESTED LOOPS | | | 2 | NESTED LOOPS | | | 3 | PARTITION HASH ALL | | |* 4 | TABLE ACCESS FULL | GRAPH1GT$ | | 5 | PARTITION HASH ALL | | |* 6 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| GRAPH1GT$ | |* 7 | INDEX RANGE SCAN | GRAPH1XSG$ | | 8 | PARTITION HASH ALL | | |* 9 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | GRAPH1GT$ | |* 10 | INDEX RANGE SCAN | GRAPH1XSG$ | -------------------------------------------------------------------
4.8.3 PGQL問合せでのパフォーマンスの考慮事項
多数の要因が、Oracle DatabaseでのPGQL問合せのパフォーマンスに影響を与えます。次に、問合せのパフォーマンスのためのお薦めの慣例をいくつか示します。
問合せオプティマイザ統計
優れた、最新の問合せオプティマイザ統計は、問合せのパフォーマンスにとって非常に重要です。プロパティ・グラフのデータを大幅に更新した後は、必ずOPG_APIS.ANALYZE_PGを実行してください。
GT$スケルトン表
移入済のGT$スケルトン表がない場合は、グラフのトラバーサルを評価するために、多くの問合せでGE$エッジ・プロパティ表のソートが複数回必要になる可能性があります。このような高価なソート操作を回避するために、可能なときは常に、PGQL問合せを実行する前にGT$スケルトン表を移入してください。
パラレル問合せの実行
パラレル問合せ実行を使用して、OracleのパラレルSQLエンジンを利用します。多くの場合、パラレル実行は、シリアル実行に対して大幅なスピードアップになります。パラレル実行は、再帰的WITH戦略を使用して評価されるパス問合せで特に重要となります。
パラレル問合せ実行の詳細は、『Oracle Database VLDBおよびパーティショニング・ガイド』も参照してください。
オプティマイザの動的サンプリング
グラフ・データ・モデル本来の柔軟性のため、静的情報では必ずしも最適な問合せ計画が生成されない場合があります。そのような場合は、よりすぐれた問合せ計画を得るために、問合せオプティマイザで動的サンプリングを使用して、実行時にデータのサンプリングを実行できます。サンプリングされるデータ量は、使用される動的サンプリング・レベルによって制御されます。動的サンプリング・レベルの範囲は0から11です。最適な使用レベルは、特定のデータセットとワークロードによって異なりますが、多くの場合、2(デフォルト)、6、または11のレベルで良好な結果を得られます。
『Oracle Database SQLチューニング・ガイド』の「補足的な動的統計」も参照してください。
バインド変数
可能な場合は常に、定数にバインド変数を使用します。バインド変数の使用により、問合せのコンパイル時間が大幅に削減されます。それにより、使用されている定数値のみが異なる問合せでの問合せワークロードのスループットが大幅に増加します。さらに、バインド変数を使用した問合せでは、インジェクション攻撃に対する脆弱性が軽減されます。
パス問合せ
+(プラス記号)演算子または*(アスタリスク)演算子を使用して任意の長さのパスを検索する、PGQLのパス問合せでは、計算が非常に複雑なため、特別な考慮が必要になります。最適なパフォーマンスを得るためには、パラレル実行を使用し、再帰的WITH(USE_DIST_RW=T)にDISTINCTオプションを使用してください。また、大規模で、連結度が高いグラフの場合は、MAX_PATH_LEN=nを使用して、再帰ステップの反復数を妥当な数に制限することをお薦めします。お薦めの方法は、小さな反復制限から開始して、その制限を繰り返し増加してより多くの結果を見つけるというものです。