SYSDATE で色々やってみる
プログラミング作業をしていると システム日付関数 SYSDATE を 特に気を使うことなく多用している。
しかし、調べてみるとデータベースサーバーのリソースを一番消費している処理だったということが少なくない。
ユーザーSQLではもちろん、内部のSQLでも頻繁に呼び出される。オラクルで最も使用回数が多い関数と思う。
そんな誰でも使っている関数なのに、未だに良く分からない事が多い。(＝仕様{振る舞い}を知っているだけ)
SYSDATE および SYSTIMESTAMP は、ファンクションの位置付けになっている。
SYSDATE, SYSTIMESTAMP 関数・・・動きを観察して考えると、実に不思議でユニークな関数である。
通常 SQL 文の中では、いくつ記述しても 1回の評価しかされない。(1つのバインド変数のようにも見える)
しかし、異なるコンテキスト*1においては SYSDATE, SYSTIMESTAMP は異なる複数の時刻をあらわすことがある。パラレルクエリーにした場合はどうなるのだろう？
SYSTIMESTAMP で実験
SYSTIMESTAMP のユニーク具合を観察するための SQL 文を書いてみた。
(前知識) SQL 文内のユーザー定義関数は、記述した数だけ評価(毎回実行)される(※)。
(※) 但し、この内容は相当過去の資料を見たという不確かな記憶のため、その記述がどこかにあるのかも
特定できていない。
DETERMINISTIC 付きの場合の呼び出しの振る舞いについても、毎回評価されているようにみえていたのであるが、
正しく検証できていない可能性を否定できない。
これはソースから WNDS, RNDS, WNPS, RNPS がチェックされて毎回評価するように実行計画されているかもしれないという
点からである。（良いアイディアが出たら検証してみようと思う）
Oracle 10g での実験のため Oracle 11g の EE 版のみで使用できるファンクションの結果をキャッシュするという新機能で違う結果になるのだろうと思う。
ポイント
- D1 , D4 は同時刻か否か (当初の予想…同時刻)
- D2 , D3 は同時刻か否か (当初の予想…異なる時刻)
- D7 の 2つ(D7_1, D7_2)は同時刻か否か…
(当初の予想…同時刻?、いや関数は毎回評価であるから…異なる時刻か?)
CREATE OR REPLACE FUNCTION FUNC_GET_TIMESTAMP
RETURN TIMESTAMP
IS
BEGIN
RETURN SYSTIMESTAMP;
END;
/
SELECT
D7 D7_1
,TO_CHAR(SYSTIMESTAMP, 'FF6') D1
,TO_CHAR(FUNC_GET_TIMESTAMP(), 'FF6') D2
,TO_CHAR(FUNC_GET_TIMESTAMP(), 'FF6') D3
,TO_CHAR(SYSTIMESTAMP, 'FF6') D4
,( SELECT TO_CHAR(SYSTIMESTAMP, 'FF6') FROM MY_DUAL ) D5
, D6
, D7 D7_2
FROM (
SELECT
'DUMMY'
,TO_CHAR(SYSTIMESTAMP, 'FF6') D6
,TO_CHAR(FUNC_GET_TIMESTAMP(), 'FF6') D7
FROM
USER_CATALOG
WHERE ROWNUM <= 5 --- これの有り無しで結果が異なった。
);
実行計画 ROWNUM <= 5 を使用した場合
|8|SELECT STATEMENT|
|1| └TEST.MY_DUAL TABLE ACCESS [FULL]|
|7| TEST. VIEW|
|6| └COUNT [STOPKEY]|
|5| FILTER|
|2| └SYS.OBJ$ TABLE ACCESS [FULL]|
|4| └SYS.TAB$ TABLE ACCESS [CLUSTER]|
|3| └SYS.I_OBJ# INDEX [UNIQUE SCAN]|
実行計画 ROWNUM <= 5 を指定していない場合
|6|SELECT STATEMENT
|1|TEST.MY_DUAL TABLE ACCESS [FULL]
|5|FILTER
|2|└SYS.OBJ$ TABLE ACCESS [FULL]
|4|└SYS.TAB$ TABLE ACCESS [CLUSTER]
|3| └SYS.I_OBJ# INDEX [UNIQUE SCAN]
DUAL 表は使用せず(DUALを使うと特別な実行計画になってしまう)、わざと自前の MY_DUAL にしている。
(内容は 'X' の 1 レコードのみ)
結果１ ROWNUM <= 5 を指定した場合
D2, D3 は 行毎に異なるが D7_1 , D7_2 は一致する(予想通り)
| D7_1 | D1 | D2 | D3 | D4 | D5 | D6 | D7_2 |
| 595261 | 527189 | 595534 | 595710 | 527189 | 527189 | 527189 | 595261 |
| 596387 | 596536 | 596628 | 596387 |
| 596890 | 597003 | 597093 | 596890 |
| 597346 | 597457 | 597547 | 597346 |
| 597858 | 597973 | 598062 | 597858 |
結果２ ROWNUM <= 5 を指定していない場合 (便宜上 先頭5件だけ)
D7_1 と D7_2 の値が一致しない(予想外)
| D7_1 | D1 | D2 | D3 | D4 | D5 | D6 | D7_2 |
| 931905 | 847219 | 932214 | 932414 | 847219 | 847219 | 847219 | 932949 |
| 933356 | 933495 | 933597 | 933706 |
| 933944 | 934068 | 934171 | 936117 |
| 936396 | 936524 | 936622 | 936734 |
| 937024 | 937149 | 937248 | 937357 |
実験結果
予想外に SYSDATE は過去の同時刻を保持しつづけたり、リアルに実行した時間の値にもなるという 2つの顔をもつことを確認した。
D1,D4,D5 が一致するのは当然として、D7_{1,2} の値がインラインビューの記述では一致しないのは意外であった。＝オプティマイザの実行計画次第で検索結果が異なるということになる。(Linux:Oracle 10.1.0.2.0:Pentium3 
この評価の有無はプログラマの組み方で発見が困難な不具合になる可能性がある、毎回評価されるように修正されることを密かに期待する。(統一すればどちらでもいい)
厳密なレコード作成時間を設定するのに使える?
ユーザー関数の毎回評価は、一括インサート時の厳密なレコード作成(更新)時間を設定する場合に使用できそうである。
(通常のSQLだと、すべて同じ日時になる)
しかし、レコード単位に SYSTIMESTAMP を取得するということは(システムコール呼び出し)システムにかなり負荷をかけているはずであり、速度＋大量データ処理を要求されるバッチ処理には使用しない方が良い。(未検証)
厳密な日付取得設定に トリガー を使用する方法もあるが、こちらはより遅くなるはず。さらにトリガー処理には並列実行不可という足かせもある。
日付処理関連 SQL 関数
