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 レコードのみ)

結果1 ROWNUM <= 5 を指定した場合
D2, D3 は 行毎に異なるが D7_1 , D7_2 は一致する(予想通り)

D7_1D1D2D3D4D5D6D7_2
595261527189595534595710527189527189527189595261
596387596536596628596387
596890597003597093596890
597346597457597547597346
597858597973598062597858

結果2 ROWNUM <= 5 を指定していない場合 (便宜上 先頭5件だけ)
D7_1 と D7_2 の値が一致しない(予想外)

D7_1D1D2D3D4D5D6D7_2
931905847219932214932414847219847219847219932949
933356933495933597933706
933944934068934171936117
936396936524936622936734
937024937149937248937357

実験結果

予想外に SYSDATE は過去の同時刻を保持しつづけたり、リアルに実行した時間の値にもなるという 2つの顔をもつことを確認した。

D1,D4,D5 が一致するのは当然として、D7_{1,2} の値がインラインビューの記述では一致しないのは意外であった。=オプティマイザの実行計画次第で検索結果が異なるということになる。(Linux:Oracle 10.1.0.2.0:Pentium3 (^^;)

この評価の有無はプログラマの組み方で発見が困難な不具合になる可能性がある、毎回評価されるように修正されることを密かに期待する。(統一すればどちらでもいい)

厳密なレコード作成時間を設定するのに使える?

ユーザー関数の毎回評価は、一括インサート時の厳密なレコード作成(更新)時間を設定する場合に使用できそうである。 (通常のSQLだと、すべて同じ日時になる)
しかし、レコード単位に SYSTIMESTAMP を取得するということは(システムコール呼び出し)システムにかなり負荷をかけているはずであり、速度+大量データ処理を要求されるバッチ処理には使用しない方が良い。(未検証)

厳密な日付取得設定に トリガー を使用する方法もあるが、こちらはより遅くなるはず。さらにトリガー処理には並列実行不可という足かせもある。



日付処理関連 SQL 関数

日本オラクル
■ 日本オラクル 株式会社
■ オラクルマスター資格 (オラクルマスターとは
■ Oracle Web セミナー

*1 異なるコンテキストかどうかは個人的な勝手な判断ですので間違っているかもしれません。