しばちょう先生の試して納得！DBAへの道 indexページ▶▶
みなさん、こんにちは。”しばちょう”こと柴田長（しばた つかさ）です。
先日（9/5）、「Oracle Database Technology Night ～集え！オラクルの力（チカラ）～」で改めてOracle Automatic Storage Managementについてご説明させて頂きました。月曜日の18:45開始にも関わらず約100名の方にご来場頂きまして、本当にありがとうございました。早いもので、Oracle Database Technology Nightは3回目を終え、リピートで受講して頂いている方も多くなってきております。益々、皆さんとDatabase Technologyを盛り上げていきたいと思いますので、是非とも引き続きご参加のほどよろしくお願い致します！！
Oracle Database Technology Nightは7/1（金）を皮切りに、ほぼ毎月一回のペースで日本オラクルが誇る技術者陣が現場で今すぐ使える技術情報を皆様にお届けさせて頂いております。また、皆様からのご質問にダイレクトに回答させて頂いたり、ディスカッションさせて頂ければと考えております。次回は11/7（月）18:45~20:30で、データベース・セキュリティに精通したエンジニアが具体的なデモを交えながら解説させて頂く予定です。前回の最後に10分程度お時間を頂いて実際にデータベースが攻撃されるデモを簡単にご覧頂いたのですが、皆さん大変興味を持って頂けたようでした。是非、ご来場お待ちしております。
さらに、10月27日（木）には、Oracle DBA & Developer Dayも予定されております。私も一コマ頂きましたのでお話させて頂く予定です。
さて宣伝が長くなってしまいましたが、今回は、Oracle Database 11g Release 1 で実装された時間隔パーティション表を扱ってみたいと思います。これはレンジ・パーティション表を拡張した機能であり、事前定義したインターバル期間（時間隔）に従って、レコードの挿入時に必要に応じて自動的にパーティションが追加される機能です。はい、間違いなく「11g Release 1」です。何故、このタイミングで？と思う方もいらっしゃるかもしれませんが、理由は、12c Release 1 において「時間隔参照パーティション化」と呼ばれる新機能が追加されているからですね。そのベース機能となる時間隔パーティションは非常に便利な機能ではありますが、少々クセが強いので、今回はそこを体験して頂きたいと思います。最後に、12cの新機能である、時間隔参照パーティション化も少し触ってみましょう！以下の演習をOracle Database 12c Release 12.1.0.2 のデータベースで試してみてください。
なお、Oracle Database 12c Release 1 (12.1.0.2) の単一インスタンス・データベースのインストレーション・ガイド 及び、Oracle VM VirtualBoxを用いたOracle Database 12c Release 1 環境の構築ガイドが、Oracle Technology Networkのこちらのページに公開されておりますので、参考にしてみてください。
- 【今回ご紹介するネタ一覧（逆引き）】
- ✓ 通常のレンジ・パーティションの作成方法（演習1）
- ✓ 時間隔パーティションの作成方法（演習1
- ✓ 時間隔パーティションによるパーティション追加の自動化（演習3, 4）
- ✓ 時間隔パーティションの最小パーティションの削除方法（演習5, 6）
- ✓ 時間隔パーティションの無効化と再有効化（演習6）
- ✓ パーティション名を指定せずにパーティションを指定するPARTITION FOR句（演習7）
- ✓ レコードが格納されているパーティション名の特定方法（演習7）
- ✓ 時間隔 – 参照パーティション表の作成（演習8）
1. 2016年1月～3月の月次で区切られた通常のレンジ・パーティション表TAB48NPと、2016年1月用のパーティションを一つだけ持ち、かつ、1ヶ月間隔でパーティションが自動的に追加される時間隔パーティション表TAB48IPの2つのパーティション表を作成して下さい。
$ sqlplus / as sysdba
SQL>
-- TBS48表領域と、この表領域をデフォルト表領域とするTRYユーザーの作成
create tablespace TBS48 datafile '＋DATA(DATAFILE)' size 512m ;
create user TRY identified by TRY default tablespace TBS48 ;
grant connect, resource, dba to TRY ;
connect TRY/TRY
-- TAB48NP表の作成
create table TAB48NP (COL1 number not null, COL2 date, COL3 number, COL4 char(1000))
partition by range(COL2)
(partition P201601 values less than (to_date('2016/02/01', 'YYYY/MM/DD')),
partition P201602 values less than (to_date('2016/03/01', 'YYYY/MM/DD')),
partition P201603 values less than (to_date('2016/04/01', 'YYYY/MM/DD'))) ;
-- TAB48IP表の作成
create table TAB48IP (COL1 number not null, COL2 date, COL3 number, COL4 char(1000))
partition by range(COL2) interval(NUMTOYMINTERVAL(1, 'month'))
(partition P201601 values less than (to_date('2016/02/01', 'YYYY/MM/DD'))) ;
-- 各パーティション表のパーティション定義を確認
set linesize 150 pagesize 50000
col TABLE_NAME for a10
col PARTITION_NAME for a10
col INTERVAL for a32
select TABLE_NAME, PARTITIONING_TYPE, PARTITION_COUNT, INTERVAL
from USER_PART_TABLES order by 1 DESC ;
TABLE_NAME PARTITION PARTITION_COUNT INTERVAL
---------- --------- --------------- --------------------------------
TAB48NP RANGE 3
TAB48IP RANGE 1048575 NUMTOYMINTERVAL(1, 'MONTH')
select TABLE_NAME, PARTITION_NAME, HIGH_VALUE
from USER_TAB_PARTITIONS order by 1 DESC, 2 ASC ;
TABLE_NAME PARTITIO HIGH_VALUE
---------- -------- ---------------------------------------------------------
TAB48NP P201601 TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
TAB48NP P201602 TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
TAB48NP P201603 TO_DATE(' 2016-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
TAB48IP P201601 TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
さあ、今回も張り切って行きましょう！と言う事で、早速、通常のレンジ・パーティション表と時間隔（インターバル）パーティション表を作成して頂きましたが、通常のレンジ・パーティション表は問題無いとしても、時間隔パーティション表は少々難しいかなと思います。なぜならば、SQL言語リファレンスの「CREATE TABLE」の章の記載だけではこの演習問題のSQLは書けなかったりします。「一ヵ月単位」という要件をどのように記載するのか？この章には書かれていないですよね？すいません。
時間隔パーティションを設定する際の特徴は、「INTERVAL句」を使用する事です。この句の後ろに記述する数値の範囲、又は日時期間に基づいて、自動的にパーティションが追加されていくのが時間隔パーティションのメリットです。「自動的に追加される」部分については、以降の演習で確認して行くとして、ここで抑えておいて頂きたいのはINTERVAL句の後ろの書き方です。
簡単な記述例としては、「PARTITION BY RANGE(COL2) INTERVAL(100) 」のように括弧内に数字を入力します。この場合、COL2列がNUMBER型であることが前提ですが、COL2の値が100単位でパーティションが自動的に追加される事になります。しかし、今回は「一ヵ月単位」というDATE型の列でパーティション化をするので、この書き方では対応出来ませんね。さて、どうしましょう？一つ覚えておいて損が無いことがあります。それは、「パーティション表」に関する情報が詳しく書かれているマニュアルは「VLDBおよびパーティショニング・ガイド」と言う事です。早速開いてみると、目次にありますね。「時間隔パーティション表の作成」というピッタリな章が！？
はい、もうお分かりですね（って、上記の回答例にバッチリ書いてあるので、ここまで長引かせる必要は無かった気がしますが）、「NUMTOYMINTERVAL」ファンクションを使用して、「PARTITION BY RANGE(COL2) INTERVAL(NUMTOYMINTERVAL(1, ‘MONTH’)) 」のように記載します。これで、「一ヵ月単位」という要件をクリア出来ます。ちなみに、「一年単位」は「INTERVAL(1, ‘YEAR’)」となります。そんなこと、何処に書いてあるのか？と言うと、SQL言語リファレンスの一つのファンクションとして記載されています。少々、探しづらいですね。はい、マニュアルの修正依頼を出しておきます。
さらに、「NUMTODSINTERVAL」ファンクションも存在していて、こちらを使用すれば「日単位、時単位、分単位、秒単位」を指定出来ますが、あまり細かくパーティションを区切ると、合計パーティション数が多くなり過ぎてしまうためにお薦め出来ません。特に、コンポジット・パーティション表を採用される場合には顕著ですので、一番細かい単位でも「日単位」に留めておいて頂ければと思います。
演習の回答例では、USER_PART_TABLESビューで各パーティション化のルールを確認しています。時間隔パーティション表の場合はINTERVAL列に設定条件が表示されることと、PARTITION_COUNT列に非常に大きな値が表示される点が特徴になります。その下でアクセスしているUSER_TAB_PARTITIONSビューで、各パーティションのHIGH_VALUEを確認する事は知られていると思いますが、通常のレンジ・パーティション表TAB48NPは3つのパーティション（1月～3月分）が作成されているのに対して、時間隔パーティション表TAB48IPでは1月分の一つのパーティションしか作成されていませんね。これは時間隔パーティション表を作成するCREATE TABLE文では必ず一つのパーティションを指定する必要があり、そのパーティションだけが作成されている状態になります。以降のパーティションは、INSERT処理で必要になった際に作成されていきます。
2. パーティション表TAB48NPに対して、1月～4月分のレコードを挿入して下さい。
$ sqlplus /nolog
SQL> connect TRY/TRY
-- 1月分のレコードをINSERT
insert into TAB48NP values (1, to_date('2016/01/15', 'YYYY/MM/DD'), 1, 'JAN') ;
1 row created.
-- 2月分のレコードをINSERT
insert into TAB48NP values (2, to_date('2016/02/15', 'YYYY/MM/DD'), 2, 'FEB') ;
1 row created.
-- 3月分のレコードをINSERT
insert into TAB48NP values (3, to_date('2016/03/15', 'YYYY/MM/DD'), 3, 'MAR') ;
1 row created.
-- 4月分のレコードをINSERT insert into TAB48NP values (4, to_date('2016/04/15', 'YYYY/MM/DD'), 4, 'APR') ; ERROR at line 1: ORA-14400: inserted partition key does not map to any partition
-- 4月分のレコードを格納するパーティションを追加 alter table TAB48NP add partition P201605 values less than (to_date('2016/05/01', 'YYYY/MM/DD')) ;
-- 再び、4月分のレコードをINSERT
insert into TAB48NP values (4, to_date('2016/04/15', 'YYYY/MM/DD'), 4, 'APR') ;
1 row created.
commit ;
はい、こちらの演習は、通常のレンジ・パーティション表に対してレコードを挿入するだけなので簡単ですよね？と気を抜いて頂いて、でもやっぱりORA-14400が発生します。と演じてみて下さい（笑）
あくまで正確にご理解頂きたいので、少々冗長な説明になりますが、ご了承ください。
通常のレンジ・パーティション表TAB48NPには、1月～3月分の3つのパーティションしか用意していませんでしたので、1月～3月分のレコードは問題無く挿入出来ますが、4月分のレコードは挿入するパーティションが存在しない為にORA-14400が発生します。これは通常のデータベース管理の一つのタスク（月次ジョブのスケジューリングで対応が多いでしょう）として、新たな月次用のパーティションを追加（ADD）することで回避していると思います。
この面倒なタスクを減らすことが出来るのが、時間隔パーティション表ですよ！というのが、次の演習3でございます。
3. 時間隔パーティション表TAB48IPに対して、演習2と同様に1月～4月分のレコードを挿入して下さい。
$ sqlplus /nolog
SQL> connect TRY/TRY
-- 1月分のレコードをINSERT
insert into TAB48IP values (1, to_date('2016/01/15', 'YYYY/MM/DD'), 1, 'JAN') ;
1 row created.
-- 2月分のレコードをINSERT
insert into TAB48IP values (2, to_date('2016/02/15', 'YYYY/MM/DD'), 2, 'FEB') ;
1 row created.
-- 3月分のレコードをINSERT
insert into TAB48IP values (3, to_date('2016/03/15', 'YYYY/MM/DD'), 3, 'MAR') ;
1 row created.
-- 4月分のレコードをINSERT
insert into TAB48IP values (4, to_date('2016/04/15', 'YYYY/MM/DD'), 4, 'APR') ;
1 row created.
commit ;
はい、何も言う事はありませんね。演習１で時間隔パーティション表を作成した際には1月分のパーティションしか明示的に作成していませんでしたが、2月～3月のレコードを挿入することに成功しています。
と言う事は？2月～3月分のパーティションが自動的に追加されたのでしょうか？次の演習で確認してみましょう！
4. 今一度、各パーティション表のパーティション定義を確認して下さい。
$ sqlplus /nolog
SQL> connect TRY/TRY
set linesize 150 pagesize 50000
col TABLE_NAME for a10
col PARTITION_NAME for a10
select TABLE_NAME, PARTITION_NAME, HIGH_VALUE
from USER_TAB_PARTITIONS
order by 1 DESC, 2 ASC ;
TABLE_NAME PARTITION_ HIGH_VALUE
---------- ---------- ---------------------------------------------------------
TAB48NP P201601 TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
TAB48NP P201602 TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
TAB48NP P201603 TO_DATE(' 2016-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
TAB48NP P201605 TO_DATE(' 2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
TAB48IP P201601 TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
TAB48IP SYS_P1274 TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', TAB48IP SYS_P1275 TO_DATE(' 2016-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', TAB48IP SYS_P1276 TO_DATE(' 2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
お見事！時間隔パーティション表TAB48IPに、2月~4月分のパーティションが追加されていますね。特徴的なのは、PARTITION_NAME列の値が「SYS_Pxxxx」という名称になっています。これは自動的に追加されたパーティションであることを物語っていて、Oracleが内部的に割り当てた名前になっています。
と、まあ、普通な感じですよね。つまらない？そうですね。ごめんなさい。もうちょっとお付き合い下さいね。
5. 各パーティション表の1月分のパーティションを削除して下さい。
$ sqlplus /nolog SQL> connect TRY/TRY -- パーティション表TAB48NP alter table TAB48NP drop partition P201601 ; -- 時間隔パーティション表TAB48IP alter table TAB48IP drop partition P201601 ; ERROR at line 1: ORA-14758: Last partition in the range section cannot be dropped
「また、つまらない演習問題を」と思われた方は、シメシメです。「あ、これだよね」と思われた方は、Oracle Databaseを触ってますねー！！ と言うのは、通常のレンジ・パーティション表では削除できるのですが、時間隔パーティション表の場合は今回の1月分のパーティションは「Last Partition」という特殊なパーティションである為に削除出来ないのです。Last Partitionという表現は難しいのですが、私の理解している言葉で表すと、「時間隔パーティションの基礎となる一番古いパーティション」です。
もちろん、時間隔パーティション表でも、Last Partition以外のパーティションは削除することは可能ですが、「古いレコードをまとめて削除したい」という要件があった場合、「一番古いパーティションだけは消せません」ではちょっとカッコ悪いですよね。これを解決する方法が次の演習です。
6. 時間隔パーティションを一時的に通常のレンジ・パーティションへ変更して、1月分のパーティションの削除を試行して下さい。その後、再び時間隔パーティション化を実行して下さい。
$ sqlplus /nolog
SQL> connect TRY/TRY
-- 時間隔パーティションの無効化
alter table TAB48IP set interval() ;
-- 1月分のパーティションの削除
alter table TAB48IP drop partition P201601 ;
-- 再び、時間隔パーティション化
alter table TAB48IP set interval(NUMTOYMINTERVAL(1, 'month')) ;
-- パーティション定義を確認
set linesize 150 pagesize 50000
col TABLE_NAME for a10
col PARTITION_NAME for a10
select TABLE_NAME, PARTITION_NAME, HIGH_VALUE
from USER_TAB_PARTITIONS
where TABLE_NAME = 'TAB48IP' ;
TABLE_NAME PARTITION_ HIGH_VALUE
---------- ---------- ---------------------------------------------------------
TAB48IP SYS_P1274 TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
TAB48IP SYS_P1275 TO_DATE(' 2016-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
TAB48IP SYS_P1276 TO_DATE(' 2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
はい、Last Partitionを削除できない理由は、時間隔パーティション表だからですね。それならば、時間隔パーティション表と通常のレンジ・パーティション表にすれば、Last Partitionを削除できるのでは？と言う発想です。でもって、上記の回答例の通り、見事に消してやりましたよ。
時間隔パーティション表を通常のレンジ・パーティション表に戻す為には、「SET INTERVAL ()」を使用して、時間隔パーティションを無効にしますが、この記載をマニュアルから見つけるのはかなりタフです。SQL言語リファレンスの「ALTER TABLE」の章の「alter_interval_partitioning」にシレっと記載されています。「時間隔パーティション表をレンジ・パーティション表に戻す場合。SET INTERVAL ()を使用して、時間隔パーティションを無効にします。データベースは、作成されるレンジ・パーティションの上限として、作成済の時間隔パーティションの上限を使用し、既存の時間隔パーティションをレンジ・パーティションに変換します。その表に子の時間隔参照パーティション表が存在する場合、その子表は通常の子の参照パーティション表に変換されます。」
おい！これまた分かりづらい・・・
7. 時間隔パーティション表TAB48IPの3月分のパーティションを削除した後、3月分のレコードをINSERTして下さい。
$ sqlplus /nolog
SQL> connect TRY/TRY
-- 時間隔パーティション表TAB48IPの3月分のパーティションを削除
alter table TAB48IP drop partition for (to_date('2016/03/15', 'YYYY/MM/DD')) ;
-- 3月分のレコードをINSERT
insert into TAB48IP values (3, to_date('2016/03/15', 'YYYY/MM/DD'), 3, 'MAR') ;
commit ;
1 row created.
-- 3月分のレコードが挿入されたパーティション名の確認
select COL1, DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID),
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
from TAB48IP where COL1 = 3 ;
COL1 DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
---------- ------------------------------------ ------------------------------------
3 3 8173
connect / as sysdba
col SEGMENT_NAME for a12
select SEGMENT_NAME, PARTITION_NAME
from DBA_EXTENTS
where FILE_ID = 3
and 8173 between BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
SEGMENT_NAME PARTITION_
------------ ----------
TAB48IP SYS_P1276
問題の意図が分かりづらい演習になってしまいましたが、3月分のパーティションを削除したにも関わらず、3月分のレコードの挿入に成功してしまいましたね。その理由は何でしょうか？と言うのが出題の意図です。上記の回答例には幾つかのテクニックも含まれていますので、ゆっくりと紹介して行きましょう。
まず一つ目。3月分のパーティションを削除するALTER TABLE文を再度確認してみて下さい。如何でしょう？パーティション名を指定していないではありませんか！そうなのです。パーティション名を指定する代わりにPARTITION FOR句を使用することで目的のパーティションを削除することが出来るのです。FOR句の後ろの括弧内にはその目的のパーティション内に含まれるパーティション・キーの値（今回の場合は3月分のパーティションに含まれる2016年3月15日）を指定するだけで良いのです。この機能は特に時間隔パーティション表を管理する上で、非常に便利なのですね。と言うもの、思い出して下さい。時間隔パーティション表のパーティション名は、自動的に良く分からない名前（SYS_Pxxxx）に命名されていましたよね？このパーティション名を知るには、USER_TAB_PARTITIONSビューをイチイチ参照しなければなりませんが、面倒臭がり屋の私のようなDBAにとっては、そのビューを参照しなくても、このPARTITION FOR句を使えば済んでしまいます。
次に二つ目。3月分のパーティションを削除した後に、3月分のレコードの挿入に成功しています。この理由は、「時間隔パーティション表だから当たり前でしょ！自動的に必要とされるパーティションが追加されたのだ」と思った方、残念！大間違いですよ。
と言う事で3点目。その挿入に成功した3月分のレコードがどのパーティションに格納されたのかをDBMS_ROWIDパッケージとDBA_EXTENTSビューを使用して確認しています。まず、DBMS_ROWIDパッケージの二つのファンクションを使用して、対象レコードが格納された「データファイル番号」と「データブロックのアドレス」を抽出しています。その情報をDBA_EXTENTSビューに対して投入してみると・・・なんと！時間隔パーティション表TAB48IPの「SYS_P1276」と言う名前のパーティション・セグメント（= パーティション）に格納されていることが分かるのです。この「SYS_P1276」は演習6のUSER_TAB_PARTITIONビューを参照した結果から、「4月分のパーティション」であることも判明します。
さて、大間違いでしたよね？時間隔パーティション表であっても、このケースでは自動的にパーティションは追加されず、既存のパーティションに格納されるようになるのですね。これは非常に注意が必要です。不具合でも何でもありません。そういう仕様なのです。と言うのも、演習6のUSER_TAB_PARTITIONビューを参照した結果を見て頂きたいのですが、
TABLE_NAME PARTITION_ HIGH_VALUE
---------- ---------- ---------------------------------------------------------
TAB48IP SYS_P1274 TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
TAB48IP SYS_P1275 TO_DATE(' 2016-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
TAB48IP SYS_P1276 TO_DATE(' 2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
・SYS_P1274は、2016年3月1日0時0分0秒よりも前の日時のレコードが格納されます。
・SYS_P1275は、2016年4月1日0時0分0秒よりも前の日時のレコードが格納されます。
・SYS_P1275は、2016年5月1日0時0分0秒よりも前の日時のレコードが格納されます。
この状態からSYS_P1275のパーティションを削除したわけですから、次のように変化しています。
TABLE_NAME PARTITION_ HIGH_VALUE
---------- ---------- ---------------------------------------------------------
TAB48IP SYS_P1274 TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
TAB48IP SYS_P1276 TO_DATE(' 2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
・SYS_P1274は、2016年3月1日0時0分0秒よりも前の日時のレコードが格納されます。
・SYS_P1275は、2016年5月1日0時0分0秒よりも前の日時のレコードが格納されます。
如何でしょう？そう、別に何月分という表現は私が都合良く使っていただけであり、正確には「HIGH_VALUE」の情報でしか管理されていませんので、上記の状態では、2016年3月15日のレコードはSYS_P1275パーティションに格納されますよね。とは言え、この状態で6月分のレコードの挿入を試みた場合、HIGH_VALUEの値が「TO_DATE(‘ 2016-06-01 00:00:00’」である新たなパーティションが追加されます。
つまり、時間隔パーティション表は、現状存在するいずれかのパーティションに格納することが不可能なレコードの挿入が試行された際にだけ、新たなパーティションが自動追加されると言うことなのですね。この辺りのクセはご理解の上で活用してみて下さい。
8. 以下のSQL文を実行して、時間隔パーティション表TAB48IPを参照（リファレンス）パーティション化の親表とする子表TAB48IP_CHILDを作成して下さい。
$ sqlplus /nolog
SQL> connect TRY/TRY
-- TAB48IP表に主キーを作成
alter table TAB48IP add primary key (COL1) ;
-- TAB48IP_CHILD表の作成
create table TAB48IP_CHILD
(COL1 number not null, COL9 number,
constraint FK_TAB48IP_CHILD foreign key (COL1) references TAB48IP(COL1))
partition by reference (FK_TAB48IP_CHILD) ;
set linesize 150 pagesize 50000
col TABLE_NAME for a14
col PARTITION_NAME for a10
col INTERVAL for a32
select TABLE_NAME, PARTITIONING_TYPE, PARTITION_COUNT, INTERVAL
from USER_PART_TABLES
where TABLE_NAME in ('TAB48IP', 'TAB48IP_CHILD') ;
TABLE_NAME PARTITION PARTITION_COUNT INTERVAL
-------------- --------- --------------- --------------------------------
TAB48IP RANGE 1048575 NUMTOYMINTERVAL(1, 'MONTH')
TAB48IP_CHILD REFERENCE 1048575 YES
set linesize 150 pagesize 50000
col TABLE_NAME for a14
col PARTITION_NAME for a10
select TABLE_NAME, PARTITION_NAME, HIGH_VALUE
from USER_TAB_PARTITIONS
where TABLE_NAME in ('TAB48IP', 'TAB48IP_CHILD') ;
TABLE_NAME PARTITION_ HIGH_VALUE
-------------- ---------- ---------------------------------------------------------
TAB48IP SYS_P1274 TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
TAB48IP SYS_P1276 TO_DATE(' 2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
TAB48IP_CHILD SYS_P1280
TAB48IP_CHILD SYS_P1281
ようやく、Oracle Database 12c Release 1の新機能に辿りつきました。時間隔パーティション表と参照（リファレンス）パーティションは、Oracle Database 11g Release 1の新機能でしたが、これらを組み合わせて使う事が出来ない事を当時、非常に残念に思っていました。それがようやく実現したのが、Oracle Database 12c Release 1なのですね。パチパチパチ！
と言う事で、ここまで使用してきた時間隔パーティション表TAB48IPを親表として、その主キーであるCOL1の値を外部参照制約キーとする子表TAB48IP_CHILDを作成して頂きました。通常の外部参照制約を作成する方法と同じで、最後に「PARTITION BY REFERENCE」句を付けておくだけですね。これだけ。非常に簡単ですね。
何が嬉しいのか？はい、これだけで良い点です。実際に、USER_TAB_PARTIIONSビューで子表TAB48IP_CHILDのパーティションを確認してみると既に2つのパーティションが自動的に作成されているではありませんか。そうです。子表側は親表のパーティション化のルールに従って、自動的にパーティション化されてしまうのです。しかも、親表のパーティション・キー（今回は、COL2列（DATE型））を子表が持っていなくてもOKなのです。凄いでしょ！ちなみに、以前のバーションで上記の子表を作成するDDL文を実行すると、「ORA-14659: 親表のPartition化メソッドはサポートされていません」というORAエラーが発生してしまっていましたね。
9. 親表である、時間隔パーティション表TAB48IPにレコードを挿入して5月分のパーティションを自動追加させた際に、子表TAB48IP_CHILDのパーティションも追加されるか否かを確認して下さい。
$ sqlplus /nolog
SQL> connect TRY/TRY
-- 5月分のレコードを親表TAB48IPに挿入
insert into TAB48IP values (5, to_date('2016/05/15', 'YYYY/MM/DD'), 5, 'MAY') ;
commit ;
set linesize 150 pagesize 50000
col TABLE_NAME for a14
col PARTITION_NAME for a10
select TABLE_NAME, PARTITION_NAME, HIGH_VALUE
from USER_TAB_PARTITIONS
where TABLE_NAME in ('TAB48IP', 'TAB48IP_CHILD') ;
TABLE_NAME PARTITION_ HIGH_VALUE
-------------- ---------- ---------------------------------------------------------
TAB48IP SYS_P1274 TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
TAB48IP SYS_P1276 TO_DATE(' 2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
TAB48IP SYS_P1282 TO_DATE(' 2016-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
TAB48IP_CHILD SYS_P1280
TAB48IP_CHILD SYS_P1281
時間隔 – 参照パーティション表の動作を見て行きましょう。
まずは、親表の時間隔パーティション表TAB48IPに、5月分のレコードを挿入して自動的に5月分のパーティションを追加させてみましたが、この時、子表のパーティションは自動的に増えているのかを確認してみました。結果は増えていません。これはこれで良いですよね？子表には何も挿入していないわけですからね。では、この5月分のレコードを親レコードとする子レコードを子表TAB48IP_CHILDに挿入したらどうなるのか？次の演習を見てみましょう。
10. 子表TAB48IP_CHILDに対して、演習9で挿入したレコード（COL1=5）の子レコードを挿入した後、再度、子表TAB48IP_CHILDのパーティションの構成を確認して下さい。
$ sqlplus /nolog SQL> connect TRY/TRY -- 演習9で挿入したレコード（COL1=5）の子レコードを子表へ挿入 insert into TAB48IP_CHILD values (5, 999) ; commit ; set linesize 150 pagesize 50000 col TABLE_NAME for a14 col PARTITION_NAME for a10 select TABLE_NAME, PARTITION_NAME, HIGH_VALUE from USER_TAB_PARTITIONS where TABLE_NAME = 'TAB48IP_CHILD' ; TABLE_NAME PARTITION_ HIGH_VALUE -------------- ---------- ---------- TAB48IP_CHILD SYS_P1280 TAB48IP_CHILD SYS_P1281 TAB48IP_CHILD SYS_P1282
じゃーんですね。見事に、新たなパーティションが子表TAB48IP_CHILD側にも追加されることが確認出来ました。親表のパーティション・キーであるCOL2列を持っていない子表にも関わらず、親表と同じルールでパーティションが自動的に追加されていく。これはDBAにとっては本当に嬉しい機能ですよね！！素晴らしい。これ以外にも様々なパーティションの操作にも対応していますので、是非是非試して頂ければ幸いです。（原稿提出の締め切り上、今回はここまで。また機会があれば・・・）
さて、Oracle Database 12c Release 1の新機能である「時間隔参照パーティション化」と言うよりも、Oracle Database 11g Release 1からの機能である「時間隔（インターバル）パーティション」のクセを体験することがメインとなってしまいましたが、如何でしたでしょうか？従来からのパーティション表の管理方法でも充分ですが、少々面倒だと感じられていたその管理性を向上させてくれる機能であるので、知っておいて損は無いかと思います。ただし、そのクセを理解しておかなければ痛い目に会う事もあると思いますので、今回はそのあたりにフォーカスをさせて頂いた次第です。少しでも参考になったのであれば幸いでございます。
今回も最後までお付き合い頂きましてありがとうございました。是非、感想や質問をお待ちしておりますね。次回以降もどうぞよろしくお願い致します。