SQL 2023 のグラフ構文を試す



![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/410176/41bfa035-85c1-49bd-72a6-29c893909f02.png)

次期 Oracle Database 開発者向け無料版「Oracle Database 23c Free」が 2023年4月3日 に提供開始されました。このリリースは SQL の最新仕様（SQL:2023）に含まれる Property Graph 文法をサポートしているので、早速使ってみます。

ここでは、以前の記事[「銀行の送金データをグラフで分析する」](https://qiita.com/ryotayamanaka/items/99a64b10f8f1f6456175)のデータを用います。以前の記事では SQL と別の言語である PGQL を用いてクエリを試していますが、この記事では SQL でグラフのパターンを記述します。

詳細は [Oracle Database 23c Free のドキュメント](https://docs.oracle.com/en/database/oracle/oracle-database/23/graph.html) の [Property Graph Release 23.2](https://docs.oracle.com/en/database/oracle/property-graph/23.2/books.html) に解説されています。[SQL Property Graphs](https://docs.oracle.com/en/database/oracle/property-graph/23.2/spgdg/sql-property-graphs.html) の項をご参照ください。

## 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 ロールのみ与えます。グラフに関連した新しい権限の詳細については[こちら](https://docs.oracle.com/en/database/oracle/property-graph/23.1/spgdg/sql-ddl-statements-property-graphs.html#GUID-8BF72892-12C6-4508-A5CB-7F22B4DDC2B7)を参照してください。 

```sql
CREATE USER graphuser
IDENTIFIED BY password123
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;

GRANT CONNECT, RESOURCE TO graphuser;
```

ログアウトします。

```sql
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
```

表を作成します。ここで、主キーや外部キーといった制約はグラフの作成に必須ではありませんが、整合性のあるグラフを管理するために役立ちます。

```sql
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 を作成しています。

```sql
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 の顧客が持っている口座を確認します。

```sql
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 の送金先を全て表示してみます。

```sql
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 ホップで戻ってくるようなパターンを探してみます。

```sql
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 ホップのときも試してみます。

```sql
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 ドル未満の振り込みを受けている回数が多い口座をリストします。リレーショナル・データベースなので集計も得意です。

```sql
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
```
```sql
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 チューニングの手法が使えるので、表の結合に役立ちそうな索引を作成してみましょう。

```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](https://docs.oracle.com/en/database/oracle/property-graph/23.2/spgdg/sql-property-graphs.html) の項には、この SQL で作成したグラフを Graph Server (PGX) にロードする方法も記載されています。

今後もさまざまなユースケースを想定してこの機能を活用してみたいと考えています。ご興味のある方は、ぜひ [OracleGraph](https://qiita.com/tags/oraclegraph) の記事をフォローしてください。