Property Graph: In-Database Graph Algorithms
Property Graph: In-Database Graph Algorithms
Developers can use the Oracle Graph Algorithms package (DBMS_OGA) to execute popular algorithms like PageRank on graphs in the database.
Developers can now execute graph algorithms using SQL and PL/SQL. They can build graph applications using SQL development tools and frameworks.
Graph Algorithm Functions Supported by DBMS_OGAから、以下のアルゴリズムがサポートされたことが確認できます。
mkdir bank_graph
cd bank_graph
Documents % mkdir bank_graph
Documents % cd bank_graph
bank_graph %
data/BANK_ACCOUNTS.csvおよびdata/BANK_TRANSFERS.csvを手元にダウンロードします。
bank_graph % curl -OL https://raw.githubusercontent.com/oracle-samples/oracle-graph/refs/heads/master/shared/datasets/bank_graph/data/BANK_ACCOUNTS.csv
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 27756 100 27756 0 0 493k 0 --:--:-- --:--:-- --:--:-- 501k
bank_graph % curl -OL https://raw.githubusercontent.com/oracle-samples/oracle-graph/refs/heads/master/shared/datasets/bank_graph/data/BANK_TRANSFERS.csv
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 129k 100 129k 0 0 479k 0 --:--:-- --:--:-- --:--:-- 480k
bank_graph % ls
BANK_ACCOUNTS.csv BANK_TRANSFERS.csv
bank_graph %
公式では、setup/oracle-graph-26ai-sqldeveloper.sqlとしてセットアップ用のスクリプトが提供されています。グラフ・アルゴリズム・ファンクションの例とラベルの設定が整合していなかったので、セットアップ用のスクリプトを書き直しました。
SQL> @oracle-graph-setup
Property GRAPHが削除されました。
Table BANK_TRANSFERSが削除されました。
Table BANK_ACCOUNTSが削除されました。
Table BANK_ACCOUNTSは作成されました。
Table BANK_TRANSFERSは作成されました。
Table BANK_ACCOUNTSが変更されました。
Table BANK_TRANSFERSが変更されました。
Table BANK_TRANSFERSが変更されました。
Table BANK_TRANSFERSが変更されました。
csv
column_names on
delimiter ,
enclosures ""
double
encoding UTF8
row_limit off
row_terminator default
skip_rows 0
skip_after_names
データを表にロードします APEXDEV.BANK_ACCOUNTS
#INFO 処理された行数: 1,000
#INFO エラーのある行数: 0
#INFO 最後にコミットされたバッチで処理された最後の行: 1,000
成功: エラーなしで処理されました
csv
column_names on
delimiter ,
enclosures ""
double
encoding UTF8
row_limit off
row_terminator default
skip_rows 0
skip_after_names
データを表にロードします APEXDEV.BANK_TRANSFERS
#INFO 処理された行数: 5,001
#INFO エラーのある行数: 0
#INFO 最後にコミットされたバッチで処理された最後の行: 5,001
成功: エラーなしで処理されました
Property GRAPHは作成されました。
GRAPH_NAME GRAPH_MODE ALLOWS_MIXED_TYPES INMEMORY
_____________ _____________ _____________________ ___________
BANK_GRAPH TRUSTED NO NO
DBMS_METADATA.GET_DDL('PROPERTY_GRAPH','BANK_GRAPH')
_______________________________________________________
CREATE PROPERTY GRAPH "APEXDEV"."BANK_GRAPH"
VERTEX TABLES (
"APEXDEV".
GRAPH_NAME ELEMENT_NAME ELEMENT_KIND OBJECT_OWNER OBJECT_NAME
_____________ _________________ _______________ _______________ _________________
BANK_GRAPH BANK_ACCOUNTS VERTEX APEXDEV BANK_ACCOUNTS
BANK_GRAPH BANK_TRANSFERS EDGE APEXDEV BANK_TRANSFERS
GRAPH_NAME LABEL_NAME PROPERTY_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE DATA_CHAR_LENGTH CHARACTER_SET_NAME COLLATION PROPERTY_ORDER
_____________ _____________ ________________ ____________ ______________ _________________ _____________ ___________________ _____________________ _________________ _________________
BANK_GRAPH ACCOUNTS ID NUMBER 22 0 1
BANK_GRAPH ACCOUNTS NAME VARCHAR2 400 400 CHAR_CS USING_NLS_COMP 2
BANK_GRAPH ACCOUNTS BALANCE NUMBER 22 20 2 0 3
BANK_GRAPH TRANSFERS SRC_ACCT_ID NUMBER 22 0 1
BANK_GRAPH TRANSFERS DST_ACCT_ID NUMBER 22 0 2
BANK_GRAPH TRANSFERS AMOUNT NUMBER 22 0 3
6行が選択されました。
ACCT_ID NUM_TRANSFERS
__________ ________________
387 39
934 39
135 36
534 32
380 31
330 30
746 28
406 28
920 26
352 26
10行が選択されました。
ACCT_ID NUM_IN_MIDDLE
__________ ________________
387 195
934 195
135 180
534 160
[中略]
10行が選択されました。
ACCOUNT_ID NUM_CYCLES
_____________ _____________
135 37
387 34
934 30
640 28
458 27
13 27
559 25
352 23
406 23
499 22
10行が選択されました。
SQL>
select *
from graph_table(
dbms_oga.bellman_ford(
bank_graph,
json {
'GRAPH_OWNER' : sys_context('USERENV','CURRENT_SCHEMA'),
'GRAPH_NAME' : 'BANK_GRAPH',
'ELEM_TABLE' : 'BANK_ACCOUNTS',
'KEY_VALUE' : json { 'ID' : 101 }
},
property(edge input amount default on null 0),
property(vertex output cost)
)
match (a is accounts)
columns (a.id, a.cost)
)
order by cost asc
fetch first 5 rows only;SQL> select *
2 from graph_table(
3 dbms_oga.bellman_ford(
4 bank_graph,
5 json {
6 'GRAPH_OWNER' : sys_context('USERENV','CURRENT_SCHEMA'),
7 'GRAPH_NAME' : 'BANK_GRAPH',
8 'ELEM_TABLE' : 'BANK_ACCOUNTS',
9 'KEY_VALUE' : json { 'ID' : 101 }
10 },
11 property(edge input amount default on null 0),
12 property(vertex output cost)
13 )
14 match (a is accounts)
15 columns (a.id, a.cost)
16 )
17 order by cost asc
18* fetch first 5 rows only;
ID COST
______ _________
101 0.0
418 1751.0
207 3065.0
261 3888.0
794 5046.0
SQL>
PAGERANKのサンプルを実行します。
select id, rank
from graph_table(
dbms_oga.pagerank(
bank_graph,
property(vertex output rank),
10, 1.0, 0.85d, false
)
match (a is accounts)
columns (a.id, a.rank)
)
order by rank desc
fetch first 5 rows only;SQL> select id, rank
2 from graph_table(
3 dbms_oga.pagerank(
4 bank_graph,
5 property(vertex output rank),
6 10, 1.0, 0.85d, false
7 )
8 match (a is accounts)
9 columns (a.id, a.rank)
10 )
11 order by rank desc
12* fetch first 5 rows only;
ID RANK
______ _______________________
387 0.006779999999999995
934 0.006779999999999995
135 0.006269999999999996
534 0.005589999999999997
380 0.005419999999999998
SQL>
select *
from graph_table (
dbms_oga.personalized_pagerank(
bank_graph,
property(vertex output rank),
json {
'GRAPH_OWNER' : sys_context('USERENV','CURRENT_SCHEMA'),
'GRAPH_NAME' : 'BANK_GRAPH',
'ELEM_TABLE' : 'BANK_ACCOUNTS',
'KEY_VALUE' : json { 'ID' : 200 }
},
50, 0.5, 0.85d, false
)
match (a is accounts)
columns (a.id, a.rank)
)
order by rank desc
fetch first 5 rows only;SQL> select *
2 from graph_table (
3 dbms_oga.personalized_pagerank(
4 bank_graph,
5 property(vertex output rank),
6 json {
7 'GRAPH_OWNER' : sys_context('USERENV','CURRENT_SCHEMA'),
8 'GRAPH_NAME' : 'BANK_GRAPH',
9 'ELEM_TABLE' : 'BANK_ACCOUNTS',
10 'KEY_VALUE' : json { 'ID' : 200 }
11 },
12 50, 0.5, 0.85d, false
13 )
14 match (a is accounts)
15 columns (a.id, a.rank)
16 )
17 order by rank desc
18* fetch first 5 rows only;
ID RANK
______ ______________________
200 0.15056794280000008
872 0.0318331171
439 0.0290869813
264 0.0274480045
851 0.0267444629
SQL>
PERSONALIZED_PAGERANK_SETのサンプルを実行します。
select json_arrayagg(p_json)
from graph_table(
bank_graph
match (a is accounts)
where a.id in (100, 200, 300)
columns (vertex_id(a) as p_json)
);SQL> select json_arrayagg(p_json)
2 from graph_table(
3 bank_graph
4 match (a is accounts)
5 where a.id in (100, 200, 300)
6 columns (vertex_id(a) as p_json)
7* );
JSON_ARRAYAGG(P_JSON)
____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
[{"GRAPH_OWNER":"APEXDEV","GRAPH_NAME":"BANK_GRAPH","ELEM_TABLE":"BANK_ACCOUNTS","KEY_VALUE":{"ID":100}},{"GRAPH_OWNER":"APEXDEV","GRAPH_NAME":"BANK_GRAPH","ELEM_TABLE":"BANK_ACCOUNTS","KEY_VALUE":{"ID":200}},{"GRAPH_OWNER":"APEXDEV","GRAPH_NAME":"BANK_GRAPH","ELEM_TABLE":"BANK_ACCOUNTS","KEY_VALUE":{"ID":300}}]
SQL>
with
function get_start_vertices return json
is
l_arr json;
begin
select json_arrayagg(p_json returning json)
into l_arr
from graph_table(
bank_graph
match (a is accounts)
where a.id in (100, 200, 300)
columns (vertex_id(a) as p_json)
);
return l_arr;
end;
select *
from graph_table (
dbms_oga.personalized_pagerank_set(
bank_graph,
property(vertex output rank),
get_start_vertices(),
50, 0.5, 0.85d, false
)
match (a is accounts)
columns (a.id, a.rank)
)
order by rank desc
fetch first 5 rows only;
/SQL> with
2 function get_start_vertices return json
3 is
4 l_arr json;
5 begin
6 select json_arrayagg(p_json returning json)
7 into l_arr
8 from graph_table(
9 bank_graph
10 match (a is accounts)
11 where a.id in (100, 200, 300)
12 columns (vertex_id(a) as p_json)
13 );
14 return l_arr;
15 end;
16 select *
17 from graph_table (
18 dbms_oga.personalized_pagerank_set(
19 bank_graph,
20 property(vertex output rank),
21 get_start_vertices(),
22 50, 0.5, 0.85d, false
23 )
24 match (a is accounts)
25 columns (a.id, a.rank)
26 )
27 order by rank desc
28 fetch first 5 rows only;
29* /
ID RANK
______ _______________________
200 0.05056237473333334
100 0.05
300 0.05
559 0.013113229933333331
439 0.010976970833333334
SQL>
select id, comp_id
from graph_table (
dbms_oga.wcc (
bank_graph,
property(vertex output comp_id))
match (a is accounts)
columns (a.id, a.comp_id)
) fetch first 5 rows only;SQL> select id, comp_id
2 from graph_table (
3 dbms_oga.wcc (
4 bank_graph,
5 property(vertex output comp_id))
6 match (a is accounts)
7 columns (a.id, a.comp_id)
8* ) fetch first 5 rows only;
ID COMP_ID
______ __________
463 1
464 1
465 1
466 1
467 1
SQL>
