ROWID

今日、先輩と話していて初めて知ったこと。

OracleデータベースにはROWIDという値が内部的に作成されて保持されているらしい。どんな値かというと、行のアドレスを示す一意の値らしい。Oracleのリファレンスガイドによれば、この値が単一の行にアクセスするための最速の方法だという。へー。

ただ、このROWIDは疑似列なので、内部的というか、隠しデータ的に保持されている。なので、例えば

SELECT *
FROM nantoka_table
WHERE nantoka_num = 1001;

みたいに全列項目を取得するSELECT文を叩いてみてもROWIDは結果に表示されない。故に俺も今日まで存在を知らなかった。でも、

SELECT rowid
FROM nantoka_table
WHERE nantoka_num = 1001;

みたいに指定してやればちゃんと’AAABBBNTKAAA’みたいな値が返ってくる。

これが存在すると分かったらけっこう便利！

テーブルには主（プライマリ）キーと呼ばれる列項目が設定されていることが多い。主キーというのはそのテーブルの中で一つの行を特定できる項目のこと。

主キーにかかる制約は２つ。
・一意であること（値が重複しないこと）
・Nullにならない（必ず値が入っていること）

※ちなみに、一意ではあるけどNullを許容する列項目はユニークキーと言う。

例えばある一つのクラス（3年B組）の生徒名簿のテーブルがあったとしたら、出席番号が主キーに使えるだろう。みんな持ってるし、ダブらないから、出席番号で生徒が特定できる。

しかし、3年B組のテスト結果をまとめた下のようなテーブルだったらどうか？

出席番号	生徒名	教科	テスト点数	再テスト点数	処理フラグ
1	市村篤	国語	60	70	0
2	入船力也	国語	15	10	0
3	兼末健次郎	国語	28	35	0
4	坂本幸作	国語	78	82	0
1	市村篤	数学	40	30	0
3	兼末健次郎	数学	82	75	0
・・・

全部の教科の成績を格納している仕様上、同じ生徒の出席番号と名前が繰り返し出てくる。よってこのテーブルでは出席番号は主キーにはできない。このテーブルで一つの行を特定するとしたら・・・１人の生徒の行は１教科につき１つという前提で、出席番号＋教科の組み合わせで判断することになるだろう。いわゆる複合主キー。

じゃあ、実際に、さっきの成績テーブルを使った処理を考える。

・成績テーブル上で、「再テスト点数」が50点未満かつ「再テスト点数＜テスト点数」の行を抽出し、その行の「生徒名」、「教科」、さらにシーケンスで取得した「ID」を、別表である「腐ったみかんテーブル」に登録する。
・腐ったみかんテーブルに登録対象となった成績テーブル上の行については、処理フラグを「1」に更新する。

単に生徒名と教科だけデータ登録するならシンプルなSELECT INSERTでもいいんだけど、シーケンスで個別なIDを割り振りたいので、カーソル使うのが便利。

DECLARE

 seiseki_rec seiseki_cur%rowtype;
 kusatta_mikan_id number default NULL;

 CURSOR seiseki_cur
 IS
  SELECT
   syusseki_bango
  ,seito_mei
  ,kyouka
 FROM
  seiseki_table
 WHERE
     sai_test_tensu < 50
 AND sai_test_tensu < test_tensu;

こんな感じで成績テーブルの行を抽出するカーソルを作って

BEGIN

 OPEN seiseki_cur;
  LOOP                                      
   FETCH seiseki_cur INTO seiseki_rec;
   EXIT WHEN seiseki_cur%NOTFOUND;

   SELECT seq_id.nextval
   INTO kusatta_mikan_id
   FROM dual;                               
   
   INSERT INTO
   kusatta_mikan_table
    (id
    ,seito_mei
    ,kyouka
    )
   VALUES
    (kusatta_mikan_id
    ,seiseki_rec.seito_mei
    ,seiseki_rec.kyouka
    );

ここまでで腐ったみかんテーブルへの行登録はできる。が、問題は元の成績テーブルに対する更新。処理フラグを1にするという処理も、このループ内の処理に組み込みたい。今、カーソルが抽出して、変数seiseki_recにフェッチされている行が、成績テーブル上のどの行なのかを特定してUPDATE文を書く必要がある。

先に書いた通り、成績テーブルの行は最低でも「出席番号」と「教科」の列を見ないと一意に特定できない（生徒名だと同姓同名がいる可能性で好ましくない）。

そういうわけで、

   UPDATE
   seiseki_table
   SET
   syori_flag = 1
   WHERE
       syusseki_bango = seiseki_rec.syusseki_bango 
   AND kyouka         = seiseki_rec.kyouka;         

と、こんな感じでWHERE句以下に２つの列項目を条件として書くことになる。

が、これはROWIDの存在を知らなかった昨日までの俺の話！！！

ROWIDの存在を知っていれば、まず初めのカーソル定義の時にROWIDも取得しておいて・・・

 CURSOR seiseki_cur
 IS
  SELECT
   rowid
  ,seito_mei
  ,kyouka
 FROM
  seiseki_table
 WHERE
     sai_test_tensu < 50
 AND sai_test_tensu < test_tensu;

UPDATEをする時は、ROWIDだけで行を特定できる。

   UPDATE
   seiseki_table
   SET
   syori_flag = 1
   WHERE
    rowid = seiseki_rec.rowid 

まぁ、今回の場合、２行書いてたのが１行になったって話なので恩恵が少ないが、実際仕事してると３つ４つの列を組み合わせないと一意にならないテーブルというのもよく出会うわけで、とても良いことを知った気分。打つコードの量はなるべく減らしたいよねぇ、俺の指の関節だって消耗品だもの。

ただ、ROWIDには注意点もあって、１つはあくまでも内部的に自動作成されるものなので、この値を任意に挿入したり、更新したり、または削除したりすることはできないということ。

２つ目に、Oracleのリファレンスガイドによれば、このROWIDを主キーとして扱うなと言っている。これはインポート・ユーティリティとエクスポート・ユーティリティで行を削除してから再挿入する場合、ROWIDが変わる場合があるからとのこと。一意ではあるけど不変とは限らないないわけか。

つまり、例えば他のテーブルに外部キーとしてROWIDを渡して結合条件なんかにしたら、値が変わってやばいことになるでってことだと思う。

今回、上に挙げた例では、同じ成績テーブル同士の行を比較するのに使っているだけで、トランザクション間に行削除や再挿入の操作も挟まないので問題ないと思われる。というか、こんな風に一度アクセスした行にすぐ再アクセスするくらいしか、今のところ使い道が思いつかないが。