次期 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 の記事をフォローしてください。