Oracle12c Temporal Validity
Oracle 12cの新機能を試していきたいと思います。
新機能って何があるの？を網羅的に見るのはしんどいので、下記の記事を参考に、SQL関連の新機能をみていきます。
ユーザー目線でチェック！ Oracle Database 12cの知りたいところ（1）：「新機能」「廃止機能」「サポート状況」から見たユーザーにとってのOracle Database 12c (2/3) - ＠IT
最初はTemporal Validity。
Temporal Validity
レコードの有効期間を表す列を用意し、該当期間のデータを素早く取り出すことができる機能みたい。
コチラを参考に、動きをみていきます。
Implementing Temporal Validity
大事な前提
上記にも書かれていますが、Temporal ValidityはFlashback Technologyを使用しています。
そして、プラガブルデータベースは非対応です。
これを見落としていてはまりました。
Temporal Validityとは
レコードに有効期間を持たせたい場合があります。ある時点とある時点で、コードは変えたくないんだけれど名前を変えたいようなことってありますよね？部署名が変わった、とか。
そんな時、開始日、終了日みたいな感じで有効期間を持つ列を用意したりするのではないでしょうか？
create table dept( id number, name varchar2(30), available_time_start timestamp, available_time_end timestamp, primary key(id, name) )
別にこのままでもいいんですが、Temporal Validityを使うと問い合わせが楽になります。
PERIOD FORによる定義
上記のavailable_time_*が有効期間なので、これをTemporal Validityの機能に合わせて定義しなおします。
ついでに、結合するテーブルとデータも用意しておきましょう。
create table dept( id number, name varchar2(30), available_time_start timestamp, available_time_end timestamp, primary key(id, name), period for available_time(available_time_start, available_time_end) ); create table emp( id number, name varchar2(30), dept_id number, primary key(id) ); insert into dept values(100, 'deptA1','04-04-01', '05-03-31'); insert into dept values(101, 'deptB1','04-04-01', '07-03-31'); insert into dept values(102, 'deptC1','04-04-01', '05-09-30'); insert into dept values(103, 'deptD1','04-04-01', '06-12-31'); insert into dept values(104, 'deptE1','04-04-01', '05-03-31'); insert into dept values(100, 'deptA2','05-04-01', null); insert into dept values(101, 'deptB2','07-04-01', null); insert into dept values(102, 'deptC2','05-10-01', null); insert into dept values(103, 'deptD2','06-01-01', null); insert into dept values(104, 'deptE2','05-04-01', null); insert into emp values(100, 'user100', 100); insert into emp values(101, 'user101', 101); insert into emp values(102, 'user102', 102); insert into emp values(103, 'user103', 103); insert into emp values(104, 'user104', 104);
AS OFによる問い合わせ
問い合わせには、Flashback queryを使います。
Flashback queryは、AS OF句を指定して問い合わせます。
select id, name, to_char(available_time_start, 'yyyy-mm-dd') a_start, to_char(available_time_end,'yyyy-mm-dd') a_end from dept as of period for available_time '04-09-01' ID NAME A_START A_END ---------- ------------------------------ ---------- ---------- 100 deptA1 2004-04-01 2005-03-31 101 deptB1 2004-04-01 2007-03-31 102 deptC1 2004-04-01 2005-09-30 103 deptD1 2004-04-01 2006-12-31 104 deptE1 2004-04-01 2005-03-31
ちなみに普通に問い合わせるとこんな感じかな。
select id, name, to_char(available_time_start, 'yyyy-mm-dd') a_start, to_char(available_time_end,'yyyy-mm-dd') a_end from dept where available_time_start <= to_timestamp('04-09-01') and (available_time_end is null or available_time_end > to_timestamp('04-09-01')) ID NAME A_START A_END ---------- ------------------------------ ---------- ---------- 100 deptA1 2004-04-01 2005-03-31 101 deptB1 2004-04-01 2007-03-31 102 deptC1 2004-04-01 2005-09-30 103 deptD1 2004-04-01 2006-12-31 104 deptE1 2004-04-01 2005-03-31
emp表とjoinするとこんな感じ。
select e.id, e.name, d.name, to_char(available_time_start, 'yyyy-mm-dd') a_start, to_char(available_time_end,'yyyy-mm-dd') a_end from emp e, dept as of period for available_time '05-10-01' d where e.dept_id = d.id ID NAME NAME A_START A_END ---------- ---------- ---------- ---------- ---------- 101 user101 deptB1 2004-04-01 2007-03-31 103 user103 deptD1 2004-04-01 2006-12-31 100 user100 deptA2 2005-04-01 102 user102 deptC2 2005-10-01 104 user104 deptE2 2005-04-01
実行計画
どっちでも同じなら別にどっちでもいいんじゃないの？というか、今までのやり方のほうが理解しやすいし、Flashback queryとか本当にずっと運用していくうえで大丈夫なの？とか思ってしまいます。
念のため実行計画を見ておこう。
対象にするのは、上記のempとjoinしたもので、Temporal Validityを使ったものと使わないもの。
使った方
explain plan for select e.id, e.name, d.name, to_char(available_time_start, 'yyyy-mm-dd') a_start, to_char(available_time_end,'yyyy-mm-dd') a_end from emp e, dept as of period for available_time '05-10-01' d where e.dept_id = d.id 解析されました。 PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1123238657 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 495 | 6 (0)| 00:00:01 | |* 1 | HASH JOIN | | 5 | 495 | 6 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| EMP | 5 | 215 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| DEPT | 5 | 280 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("E"."DEPT_ID"="T"."ID") 3 - filter(("T"."AVAILABLE_TIME_START" IS NULL OR "T"."AVAILABLE_TIME_START"<=TO_TIMESTAMP('05-10-01')) AND ("T"."AVAILABLE_TIME_END" IS NULL OR "T"."AVAILABLE_TIME_END">TO_TIMESTAMP('05-10-01'))) Note ----- - dynamic statistics used: dynamic sampling (level=2)
使わなかった方
explain plan for select e.id, e.name, d.name, to_char(d.available_time_start, 'yyyy-mm-dd') a_start, to_char(d.available_time_end,'yyyy-mm-dd') a_end from emp e, dept d where e.dept_id = d.id and d.available_time_start <= to_timestamp('05-10-01') and (d.available_time_end is null or d.available_time_end > to_timestamp('05-10-01')) 解析されました。 PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1123238657 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 495 | 6 (0)| 00:00:01 | |* 1 | HASH JOIN | | 5 | 495 | 6 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| EMP | 5 | 215 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| DEPT | 5 | 280 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("E"."DEPT_ID"="D"."ID") 3 - filter("D"."AVAILABLE_TIME_START"<=TO_TIMESTAMP('05-10-01') AND ("D"."AVAILABLE_TIME_END" IS NULL OR "D"."AVAILABLE_TIME_END">TO_TIMESTAMP('05-10-01'))) Note ----- - dynamic statistics used: dynamic sampling (level=2)
つまり
結果はほとんど差異なし。
filterのところを見ると、Temporal Validityの場合も同じようにフィルタをかけているのが分かります。
ただ、STARTのほうもIS NULL条件が入っています。
これは、STARTにNOT NULL制約が入ってないからのようで、NOT NULL制約をつけてやると同じフィルタ条件になりました。
explain plan for select e.id, e.name, d.name, to_char(available_time_start, 'yyyy-mm-dd') a_start, to_char(available_time_end,'yyyy-mm-dd') a_end from emp e, dept as of period for available_time '05-10-01' d where e.dept_id = d.id 解析されました。 PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1123238657 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 495 | 6 (0)| 00:00:01 | |* 1 | HASH JOIN | | 5 | 495 | 6 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| EMP | 5 | 215 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| DEPT | 5 | 280 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("E"."DEPT_ID"="T"."ID") 3 - filter("T"."AVAILABLE_TIME_START"<=TO_TIMESTAMP('05-10-01') AND ("T"."AVAILABLE_TIME_END" IS NULL OR "T"."AVAILABLE_TIME_END">TO_TIMESTAMP('05-10-01'))) Note ----- - dynamic statistics used: dynamic sampling (level=2)
特にインデックスが張られるわけでもなく書き方が少し直感的になるくらいで、他に何かが有利になるものでもない印象。
結局のところあんまり積極的に使いたい機能ではないかなぁ。
