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ベースのプロパティ・グラフ分析をサポートします。
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つまたは複数のコール)。
-
クリーンアップを実行します
例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 作業表の作成および分析の実行
次の文は、最初に同じ列構造と基本的な圧縮を有効にした作業表を作成してから、それを計算に渡します。
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.