次期 Oracle Database 開発者向け無料版「Oracle Database 23c Free」が 2023年4月3日 に提供開始されました。このリリースは SQL の最新仕様（SQL:2023）に含まれる Property Graph 文法をサポートしているので、早速使ってみます。
ここでは、以前の記事「銀行の送金データをグラフで分析する」のデータを用います。以前の記事では SQL と別の言語である PGQL を用いてクエリを試していますが、この記事では SQL でグラフのパターンを記述します。
詳細は Oracle Database 23c Free のドキュメント の Property Graph Release 23.2 に解説されています。SQL Property Graphs の項をご参照ください。
Oracle Database 23c の起動
Docker または Podman を使用してコンテナを起動します。このコンテナは解凍後のサイズで 10 GB 程度あります。
docker run -p 1521:1521 --name database-23c \
container-registry.oracle.com/database/free:latest
このメッセージが出てきたらデータベースの起動成功です。
...
#########################
DATABASE IS READY TO USE!
#########################
...
Ctrl + C などでコンテナを停止させた場合には再度起動します。
docker start database-23c
管理者ユーザーのパスワードを設定します。
docker exec -it database-23c ./setPassword.sh password123
データベースに管理者ユーザー（sys）でログインします。
docker exec -it database-23c sqlplus sys/password123@freepdb1 as sysdba
ユーザーの作成
通常のデータベースユーザーとして graphuser を作成します。ここでは、CONNECT ロールと RESOURCE ロールのみ与えます。グラフに関連した新しい権限の詳細についてはこちらを参照してください。
CREATE USER graphuser
IDENTIFIED BY password123
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;
GRANT CONNECT, RESOURCE TO graphuser;
ログアウトします。
exit
表データの作成
データファイルをコンテナ上にダウンロードしたいので、コンテナにログインします。
docker exec -it database-23c /bin/bash
データロード用の INSERT 文を含むファイルをダウンロードします。
curl -O https://raw.githubusercontent.com/ryotayamanaka/oracle-graph/main/bank-transaction/data/scale-100/bank_account.sql
curl -O https://raw.githubusercontent.com/ryotayamanaka/oracle-graph/main/bank-transaction/data/scale-100/bank_customer.sql
curl -O https://raw.githubusercontent.com/ryotayamanaka/oracle-graph/main/bank-transaction/data/scale-100/bank_transaction.sql
データベースに graphuser ユーザーでログインします。
sqlplus graphuser/password123@freepdb1
表を作成します。ここで、主キーや外部キーといった制約はグラフの作成に必須ではありませんが、整合性のあるグラフを管理するために役立ちます。
CREATE TABLE bank_customer (
cst_id NUMBER NOT NULL
, first_name VARCHAR2(255)
, last_name VARCHAR2(255)
, CONSTRAINT bank_customer_pk PRIMARY KEY (cst_id)
);
CREATE TABLE bank_account (
acc_id NUMBER NOT NULL
, cst_id NUMBER NOT NULL
, CONSTRAINT bank_account_pk PRIMARY KEY (acc_id)
, CONSTRAINT bank_account_cst_fk FOREIGN KEY (cst_id) REFERENCES bank_customer (cst_id)
);
CREATE TABLE bank_transaction (
acc_id_src NUMBER
, acc_id_dst NUMBER
, txn_id NUMBER
, datetime TIMESTAMP
, amount NUMBER
, CONSTRAINT bank_transaction_pk PRIMARY KEY (txn_id)
, CONSTRAINT bank_transaction_src_fk FOREIGN KEY (acc_id_src) REFERENCES bank_account (acc_id)
, CONSTRAINT bank_transaction_dst_fk FOREIGN KEY (acc_id_dst) REFERENCES bank_account (acc_id)
);
INSERT 文を実行します。
set termout off
@bank_customer.sql
@bank_account.sql
@bank_transaction.sql
commit;
Commit complete.
データがロードされたことを確認します。
set termout on
select count(*) from bank_customer;
select count(*) from bank_account;
select count(*) from bank_transaction;
COUNT(*)
----------
160
COUNT(*)
----------
200
COUNT(*)
----------
6030
グラフの作成
グラフ bank_graph を作成します。ここで使われる CREATE PROPERTY GRAPH 構文は SQL 2023 で導入されたものです。ノード（= vertex）として customer と account を、エッジとして transferred_to と owns を作成しています。
CREATE PROPERTY GRAPH bank_graph
VERTEX TABLES (
bank_customer
KEY (cst_id)
LABEL customer
PROPERTIES (cst_id, first_name, last_name)
, bank_account
KEY (acc_id)
LABEL account
PROPERTIES (acc_id)
)
EDGE TABLES (
bank_transaction
KEY (txn_id)
SOURCE KEY (acc_id_src) REFERENCES bank_account (acc_id)
DESTINATION KEY (acc_id_dst) REFERENCES bank_account (acc_id)
LABEL transferred_to
PROPERTIES (txn_id, datetime, amount)
, bank_account AS bank_account_owns
KEY (acc_id)
SOURCE KEY (cst_id) REFERENCES bank_customer (cst_id)
DESTINATION KEY (acc_id) REFERENCES bank_account (acc_id)
LABEL owns
);
Property graph created.
クエリの実行
実行結果を整形するためのおまじないです。
SET LINESIZE 200
SET PAGESIZE 5000
COLUMN first_name FORMAT a10
まずは、CST_ID = 10 の顧客が持っている口座を確認します。
SELECT * FROM GRAPH_TABLE (bank_graph
MATCH (c IS customer)-[e IS owns]->(a IS account)
WHERE c.cst_id = 10
COLUMNS (c.cst_id, c.first_name, a.acc_id)
);
この顧客の名前は Laura で 2 つの口座を持っていることがわかります。
CST_ID FIRST_NAME ACC_ID
---------- ---------- ----------
10 Laura 10
10 Laura 90
次に Laura の送金先を全て表示してみます。
SELECT DISTINCT * FROM GRAPH_TABLE (bank_graph
MATCH (c IS customer)-[e IS owns]->(a IS account)-[t IS transferred_to]->(a1 IS account)
WHERE c.cst_id = 10
COLUMNS (c.cst_id, c.first_name, a.acc_id, a1.acc_id AS acc_id_1)
);
CST_ID FIRST_NAME ACC_ID ACC_ID_1
---------- ---------- ---------- ----------
10 Laura 10 97
10 Laura 10 49
10 Laura 10 59
10 Laura 10 75
10 Laura 10 92
...
口座間の送金だけに着目して、acc_id = 10 のアカウントから 3 ホップで戻ってくるようなパターンを探してみます。
SELECT DISTINCT * FROM GRAPH_TABLE (bank_graph
MATCH (a1)-[t1 IS transferred_to]->(a2)-[t2 IS transferred_to]->(a3)
, (a3)-[t3 IS transferred_to]->(a1)
WHERE a1.acc_id = 10
AND t1.amount > 500 AND t2.amount > 500 AND t3.amount > 500
AND t1.datetime < t2.datetime AND t2.datetime < t3.datetime
COLUMNS (
a1.acc_id AS acc_id_1, t1.amount AS amount_1
, a2.acc_id AS acc_id_2, t2.amount AS amount_2
, a3.acc_id AS acc_id_3, t3.amount AS amount_3
)
);
acc_id が 10 > 75 > 77 > 10 というパターンが存在することがわかります。
ACC_ID_1 AMOUNT_1 ACC_ID_2 AMOUNT_2 ACC_ID_3 AMOUNT_3
---------- ---------- ---------- ---------- ---------- ----------
10 600 75 900 77 900
10 800 75 900 77 900
同様に 4 ホップのときも試してみます。
SELECT DISTINCT * FROM GRAPH_TABLE (bank_graph
MATCH (a1)-[t1 IS transferred_to]->(a2)-[t2 IS transferred_to]->(a3)
, (a3)-[t3 IS transferred_to]->(a4)-[t4 IS transferred_to]->(a1)
WHERE a1.acc_id = 10
AND a1.acc_id != a3.acc_id AND a2.acc_id != a4.acc_id
AND t1.amount > 500 AND t2.amount > 500
AND t3.amount > 500 AND t4.amount > 500
AND t1.datetime < t2.datetime
AND t2.datetime < t3.datetime
AND t3.datetime < t4.datetime
COLUMNS (
a1.acc_id AS acc_id_1, t1.amount AS amount_1
, a2.acc_id AS acc_id_2, t2.amount AS amount_2
, a3.acc_id AS acc_id_3, t3.amount AS amount_3
, a4.acc_id AS acc_id_4, t4.amount AS amount_4
)
);
4ホップの場合にも、このようなパターンが 1 つだけ存在することがわかります。
ACC_ID_1 AMOUNT_1 ACC_ID_2 AMOUNT_2 ACC_ID_3 AMOUNT_3 ACC_ID_4 AMOUNT_4
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
10 800 49 600 81 700 25 700
指定した期間内に 500 ドル未満の振り込みを受けている回数が多い口座をリストします。リレーショナル・データベースなので集計も得意です。
SELECT t.acc_id, COUNT(acc_id) AS num_of_txn
FROM GRAPH_TABLE (bank_graph
MATCH (a1)-[t IS transferred_to]->(a2)
WHERE t.datetime >= TIMESTAMP '2020-10-01 00:00:00'
AND t.datetime < TIMESTAMP '2020-12-01 00:00:00'
AND t.amount < 500.00
COLUMNS (
a2.acc_id
)
) t
GROUP BY t.acc_id
ORDER BY num_of_txn DESC
FETCH FIRST 10 ROWS ONLY
;
ACC_ID NUM_OF_TXN
---------- ----------
23 29
41 27
33 26
30 24
141 23
159 22
134 22
123 22
130 21
34 20
実行計画の確認
通常の SQL と同様、EXPLAIN PLAN で実行計画を取得できます。
SET LINESIZE 200
SET PAGESIZE 5000
COLUMN plan_table_output FORMAT a120
EXPLAIN PLAN FOR
SELECT DISTINCT * FROM GRAPH_TABLE (bank_graph
MATCH (a1)-[t1 IS transferred_to]->(a2)-[t2 IS transferred_to]->(a3)
, (a3)-[t3 IS transferred_to]->(a1)
WHERE a1.acc_id = 10
AND t1.amount > 500 AND t2.amount > 500 AND t3.amount > 500
AND t1.datetime < t2.datetime AND t2.datetime < t3.datetime
COLUMNS (
a1.acc_id AS acc_id_1, t1.amount AS amount_1
, a2.acc_id AS acc_id_2, t2.amount AS amount_2
, a3.acc_id AS acc_id_3, t3.amount AS amount_3
)
);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format=>'ALL'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 540988490
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 156 | 28 (4)| 00:00:01 |
| 1 | HASH UNIQUE | | 1 | 156 | 28 (4)| 00:00:01 |
|* 2 | HASH JOIN | | 1 | 156 | 27 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 7 | 728 | 18 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| BANK_TRANSACTION | 9 | 468 | 9 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| BANK_TRANSACTION | 2943 | 149K| 9 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | BANK_TRANSACTION | 19 | 988 | 9 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------...
通常の SQL チューニングの手法が使えるので、表の結合に役立ちそうな索引を作成してみましょう。
CREATE INDEX bank_account_cst_idx ON bank_account (cst_id);
CREATE INDEX bank_transaction_src_idx ON bank_transaction (acc_id_src);
CREATE INDEX bank_transaction_dst_idx ON bank_transaction (acc_id_dst);
先のクエリの実行計画を再度確認すると、索引が使われていることがわかります。
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4108979867
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 156 | 19 (6)| 00:00:01 |
| 1 | HASH UNIQUE | | 1 | 156 | 19 (6)| 00:00:01 |
|* 2 | HASH JOIN | | 1 | 156 | 18 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 7 | 728 | 16 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID BATCHED| BANK_TRANSACTION | 9 | 468 | 7 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | BANK_TRANSACTION_DST_IDX | 18 | | 1 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | BANK_TRANSACTION | 2943 | 149K| 9 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID BATCHED | BANK_TRANSACTION | 19 | 988 | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | BANK_TRANSACTION_SRC_IDX | 50 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------
グラフパターンマッチを利用したクエリは、インメモリのグラフデータベース実装である Graph Server (PGX) 上で PGQL を用いて実行することで、性能を大幅に向上できる場合があります。ドキュメントの SQL Property Graphs の項には、この SQL で作成したグラフを Graph Server (PGX) にロードする方法も記載されています。
今後もさまざまなユースケースを想定してこの機能を活用してみたいと考えています。ご興味のある方は、ぜひ OracleGraph の記事をフォローしてください。