OracleDatabase

---

有識者との問い合わせのやり取りの中で発覚した、結論からいうとOracleのバグらしいのだが、
俺自身がよくわかっていないので情報整理する意味で書いてみる。
内容としては、厄介なことに同じSQLでも結果が異なるケースをもたらすようだ。

---

 

---

---

OracleのRANK関数－いわゆる「順位」をつけるための関数－の使い方の個人的なメモ


基本的な使い方はSELECTの項目内に以下のように記述する↓

rank() over(order by COLUMN_NAME [ASC|DESC] )  

という感じ。
・何か指定できるみたいだが、この使い方だと同値は同順になり、次の値から重複した分を加味した順位になっていく。
・検索結果は、over()内に指定した順序で並び替えられるので、通常順序付けにあたって最後部につけるORDER BYは不要。

---

 

---

マテビューのもとになるオブジェクト情報が「USER_MVIEW_DETAIL_RELATIONS」というシステム表？で見れる 例えばマテビュー

---

Oracleの「FOR UPDATE OF TABLE.COLUMN」は、指定した項目を基準としてレコードの排他をかける構文だと思ってたが、

SELECTの対象が複数存在する場合にどのテーブルをロックするかを指定するもとの情報らしくて、

SELECT対象が１テーブルだけだとあまり意味がないらしい。

また、仮に複数あったとしても、「そのテーブルを（ロックする）」という意味しか持たず、

項目を指定することによる、その項目に関連する排他の意味合いはないようだ。（じゃあ書かせんなよ…FOR UPDATE OF TABLEだけ（COLUMN書く必要ない）でいいやんけ）

要するに項目Aと項目Bがあったら「FOR UPDATE OF 項目A」でも「FOR UPDATE OF 項目B」でも効果は同じ、ということらしい。



なお、前提として、ここでいう「そのテーブルを」というのは、FOR UPDATEを付けているSELECT側の条件（WHERE句等）に左右される。

つまり「テーブル全体をロックする」という意味ではなくて指定された条件に該当するレコードのみがロックされる。



まあでも確かに冷静に考えると、

指定した項目によっては「（その項目を指定したからって）だから何？」っていう気になるのもわかる。

これは、自身のプロジェクトにおいて、「FOR UPDATE OF TABLE.COLUMN」の「COLUMN」に指定される項目が、

得てしてKEY項目であることが多いことに由来するのだろう。

つまり「そのKEYのレコード全体をロックする」という意味合いとして使っている（書いている）と、

結果的には勝手に”勘違いしていた”ことになるのだが、

そもそも、もともとFOR UPDATE OF TABLE.COLUMNの".COLUMN"には、

Oracle的には、項目自体に対する排他等の意味や目的はなくて、

「そのテーブルを」の判断基準にしか使ってないんだから、

指定されたからといって、例えばフラグ的意味合いの項目だったとすると”値が1のレコードだけロックするの？”とか、

様々疑問が出てくることは確かである。

それに、KEY以外の項目を指定したとしたら（あるいはKEYなしテーブルだったとしたら）、

排他ができない無意味な構文になるかというと、それはそれで本末転倒であるし、

そういうことも考えると「よく考えればわかるだろ」と言われるとそうでもある。



結合してデータを取得する際に同じ名前の項目が一度のSELECT内で複数存在する場合に、

「どのテーブルの」項目かを区別する指定を明記しないと実行できずに怒られる仕様がある。

このFOR UPDATEの項目指定もそれとよく似ていて、その理由というか、経緯も多分同じなんだろう。

「Aテーブル.項目B」と「Cテーブル.項目B」が存在するとき、

単に「FOR UPDATE OF 項目B」とだけ書いても「どっちのだよ？」という気になるから、テーブル名から書かせるようになっている…

そんなところなんだろう。

だから「テーブルどころか項目名まで書かせている」というよりは「項目を書かせるためにテーブルまで指定させている」というほうが正しい気がする。

でも項目を書くことの意味がない（指定する項目によって排他の効果は変わらない）なら出発点がまず間違っていると思うのだ。

（これは俺の予想に過ぎないから事実がどうかは知らない…）

---

---

テーブルにdefault指定付きの項目を追加（alter table tabne_name add column_name column_type default default_value）すると、

”テーブル項目追加中（alter文実行中）”はそのテーブルに対するSELECTが待たされるらしい。

alterが終われば解放されてSELECTも通るようになる。



テーブルに項目を追加するわけだから、

追加される前（alter文実行中も「追加」が完了する前という意味では「追加される前」）に発行するselect文には

当然追加対象のテーブル項目を含めることはできないので、

追加項目を見ないselectと、これからテーブルに項目を追加するalterの両者は、一見すると無関係に見える。

だけどどうも影響を受けてしまうようだ。

---

---

OracleのFunctionの実行時間はSQLそのものの完了よりはフェッチの部分に大部分が含まれるように感じる。

Functionを呼び出したタイミングではその中身まで深く計算せず、

フェッチしている中で詳細な計算をしていくようなイメージのようである。



SELECT文の問い合わせの”結果”には、

①Oracleからの検索結果返却開始

②全検索結果のフェッチ完了

の２段階のフェーズがあるが、

SELECTする項目の一つ、ないしそのうちの一部として同じ並びで記述するFunctionは、

基本的には前者「①Oracleからの検索結果返却開始」までの間に全て寄っていて、

検索結果が返却される段階ではもう既に計算済みだと思っていた。

だがどうやらそうではない。

実際にはOracleから検索結果が返却されてから、「②全検索結果のフェッチ完了」までの間に

その計算を行っているようである。

「①Oracleからの検索結果返却開始」が開始した段階では、

実際に発行したSELECTの中に含まれるFunctionの計算は行われていないということなのだな。



仕事のとある案件においてプロジェクトで自作した業務Functionの修正を行った際、

修正前後のスピード比較をしたときにふと疑問が浮かび、簡単な実験をして知った。

Oracle的には常識なのかもしれないが自分用のメモ。

---

---

Oracleの公式のマニュアルみたいのを見ると

「V$SESSION.BLOCKING_SESSIONにはブロックしているセッションの情報が出力される」

といった内容のことが書かれているが、

主語がないので自セッションから見て

の判断がつかない。
そしてなんとなく俺は"ブロックしている"という単語だけ見て
「そのセッションは他セッションをブロックしているよ（そのセッションのSIDはこれだよ）」という内容が
V$SESSION.BLOCKING_SESSIONに出てくるのかと思っていたが、どうやら違うようだ。
正しくは「俺はそのセッションにブロックされているよ」のSIDが出力されるようである。

つまり自セッションをブロックしているセッションの情報が出力される
という意味になるようだ。
（わかりづれえ…これOracle的にはいちいち書くまでもない常識なのかな？）

---

---

DBA_TABLESを基準にしてFUNCTIONやPROCEDUREのソースを調査し

そのテーブルが使用されているかどうかを検査するSQL。

このSQLの返却結果の中でOBJ_USING_COUNTが1以上のテーブルは

少なくともどこかで使われています。

⇒そのテーブルをDROPして削除する／ALTERして項目を削除する等すると

　該当FUNCTION、PROCEFDUREがINVALID（コンパイルエラーの意）になる

 

---

---

V$SESSION.MACHINEにマシン名で検索をかけて、

あとはLOGON_TIMEやV$SQL.SQLTEXT等を見ればある程度わかるけど、

そのあたりがはっきりしない場合に接続元マシンから探り当てる方法。

※例によって経験則なので確実かどうかわかりません

※Linuxを前提にしていますがWindowsでも似た考えでできると思います

---

---

ここに挙げたやり方に則り
実際によく使ってる簡易的にセッションの情報を取得するＳＱＬ。
リンク先で挙げたようなV$SESSIONとV$SESSION_LONGOPSに加えて
V$SQLを結合してSQL_FULLTEXTを取得します
（そのセッションがどんなSQL投げてるのか調べる）。

---

 

---