木構造を持つテーブルの深さを求めるSQL
例えばこの図のような木構造を持つテーブルがあるとする。
この木はレコードとして、一意のIDと親となるIDを表すPARENT_IDをセットで持つ。
テーブルとしてはこのようになる。
| ID | PARENT_ID |
|---|---|
| 0 | (null) |
| 1 | 0 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 4 |
| 6 | 4 |
| 7 | 4 |
| 8 | 7 |
| 9 | 7 |
| 10 | 8 |
| 11 | 8 |
| 12 | 4 |
このとき、この木における各IDの深さは下記のようなSQLで求めることができる。
SELECT ID, PARENT_ID, LEVEL
FROM IDTABLE
START WITH PARENT_ID IS NULL
CONNECT BY PRIOR ID = PARENT_ID;
出力結果はこうなる
| ID | PARENT_ID | LEVEL |
|---|---|---|
| 0 | (null) | 1 |
| 1 | 0 | 2 |
| 2 | 1 | 3 |
| 3 | 1 | 3 |
| 4 | 1 | 3 |
| 5 | 4 | 4 |
| 6 | 4 | 4 |
| 7 | 4 | 4 |
| 8 | 7 | 5 |
| 9 | 7 | 6 |
| 10 | 8 | 6 |
| 11 | 8 | 6 |
| 12 | 4 | 4 |
どんなときに使うのか
自分が使った例としてはArcSDEの更新履歴ツリーの深さ管理がある。
ArcSDEはユーザが接続したバージョン（更新履歴のタグ）に対して木の根からの更新履歴をたどるようにして画面にそのバージョンの状態を描画する。
このとき更新履歴ツリーが深いと読み込みの回数が増え、描画や追加の更新が遅くなる。
そのようなときはマージ処理をして途中の更新履歴をタグに集約し、木を浅くする。
目安として「木の深さがいくつ以上になったらマージする」という判断に使える。
Oracle公式技術記事
こちらも参照すると良い。