しばちょう先生の試して納得！DBAへの道 indexページ▶▶
みなさん、こんにちは。気が付けば既に3月に突入していたことに驚いている”しばちょう”こと柴田長（しばた つかさ）です。少しずつ寒さも和らぎ始めましたし、桜の開花が待ち遠しい季節となってきましたね。
さて、前々回からパーティション化によるメリットを体験して頂いておりますが、今回も引き続きパーティション表を取り上げさせて頂きます。ただし、何の目的も無くSQLを実行するだけでは面白くないので、実際のデータベース運用にそのまま使用できるようなシナリオをベースとして、パーティション表の管理について体験して頂きたいと考えています。
そのシナリオとは、Information Lifecycle Management（以降、ILMに省略）となります。情報（Information）は時間の経過と共に用途やアクセス頻度が変化していくもので、一般的には新しい情報は多くのユーザーから頻繁に参照/更新されますが、古い情報に対してのアクセスは少なくなる傾向があります。この鮮度とアクセス頻度の傾向に合わせて情報を管理すること、またその方法をILMと呼んでいます。特に、近年の新しい政府規制やガイドラインの策定により企業は情報を長期間保管する必要が生じており、「最低限のコストで大量のデータを格納しつつ性能も維持する」という難しい課題の解決がDBAに求められていると思います。
今回は、Oracle Database 11g Release 2のパーティション表と圧縮機能を使用して、古いデータのみが格納されているパーティションだけを圧縮することで「最低限のコストで大量のデータを格納しつつ性能も維持する」を体験して頂きます。合わせてパーティション表を扱う上では避けられないグローバル索引とローカル索引の違いについても、是非この機会に学んでおきましょう。
以下の演習をOracle Database 11g Release 2 Enterprise Editionのデータベースで試してみてください。
1.次のSQLを実行し、非パーティション表TAB27とパーティション表TAB27_PART作成し、各表へ約300MB（3ヵ月分）のレコードを挿入してください。作成される パーティション表TAB27_PARTは、日付型データが格納されているCOL2列をパーティション化キーとした月単位の時間隔パーティションとなります。
$ sqlplus / as sysdba
SQL>
-- TBS27表領域と、この表領域をデフォルト表領域とするTRYユーザーの作成
create tablespace TBS27 datafile '+FRA(DATAFILE)' size 3g ;
create user TRY identified by TRY default tablespace TBS27 ;
grant CONNECT, RESOURCE, DBA to TRY ;
-- 非パーティション表と索引の作成
connect TRY/TRY
create table TAB27 (COL1 number not null, COL2 date, COL3 number, COL4 number, COL5 char(1000)) ;
create unique index PK_TAB27 on TAB27(COL1, COL2) ;
alter table TAB27 add primary key (COL1, COL2) using index ;
create index IDX_TAB27_COL3 on TAB27(COL3, COL2) ;
create index IDX_TAB27_COL4 on TAB27(COL4, COL2) ;
-- パーティション表と索引の作成
create table TAB27_PART (COL1 number not null, COL2 date, COL3 number, COL4 number, COL5 char(1000))
partition by range(COL2) interval(NUMTOYMINTERVAL(1, 'month'))
(partition P201401 values less than (to_date('2014/02/01', 'YYYY/MM/DD'))) ;
create unique index PK_TAB27_PART on TAB27_PART(COL1, COL2) local ;
alter table TAB27_PART add primary key (COL1, COL2) using index ;
create index IDX_TAB27_PART_COL3 on TAB27_PART(COL3, COL2) ;
create index IDX_TAB27_PART_COL4 on TAB27_PART(COL4, COL2) local ;
-- 約100MB（2014年1月の一ヵ月分）のレコードを保持するベース表を作成
create table TAB27_BASE as select * from TAB27 where 1=2 ;
insert /*+append */ into TAB27_BASE
select LEVEL,
to_date('2014/01/01','YYYY/MM/DD') + mod(LEVEL, 28),
dbms_random.value(1,100),
dbms_random.value(1,100),
'hoge' || mod(LEVEL, 5)
from DUAL connect by LEVEL <= 6 * 128 * 100 ;
commit;
-- TAB27表とTAB27_PART表へ3ヶ月分（1月～3月）のレコードをINSERT
insert /*+append */ all into TAB27 into TAB27_PART -- 2つの表へ同時INSERT
select * from TAB27_BASE -- 1月分
union all
select COL1, COL2 + 31, COL3, COL4, COl5 from TAB27_BASE -- 2月分
union all
select COL1, COL2 + 59, COL3, COL4, COl5 from TAB27_BASE ; -- 3月分
commit ;
さあ、張り切って解説していきますよー。
毎度お馴染みの準備運動ですが、今回の連載では演習の途中でデータ量を増幅していきたいと思うので、そのベースとなる1ヶ月分のレコードを確保しておくベース表（上記回答内ではTAB27_BASE表）を作成している点が初めての登場かもしれません。とは言え、あまり大きな意味は無いのが事実であり、その後のINSERT文が少しだけシンプルになる感じです。
ちなみに、非パーティション表とパーティション表へ全く同じレコードをINSERTする際には、INSERT + SELECT文をそれぞれの表に対して実行するのが一般的かもしれませんが、「INSERT ALL INTO <table_name1> INTO <table_name2>select …」文を使用することで一回のSELECT処理で複数表へINSERTすることが可能です。このINSERT文の実行計画は非常に面白いものとなっているので、興味がある方は確認してみてくださいね。
2.2. 作成された各表のセグメント・サイズを確認してください。
$ sqlplus TRY/TRY SQL> set linesize 150 pagesize 50000 col SEGMENT_NAME for a12 col PARTITION_NAME for a16 col TABLESPACE_NAME for a16 select SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES/1024/1024 from USER_SEGMENTS where SEGMENT_TYPE like 'TABLE%' and SEGMENT_NAME != 'TAB27_BASE'; SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES/1024/1024 ------------ ---------------- ------------------ ---------------- --------------- TAB27 TABLE TBS27 304 TAB27_PART P201401 TABLE PARTITION TBS27 104 TAB27_PART SYS_P183 TABLE PARTITION TBS27 104 TAB27_PART SYS_P184 TABLE PARTITION TBS27 104
こちらの演習は前回の復習となりますし、それほど難しくは無いと思います。
SQLの実行結果だけ解説しておくと、非パーティション表TAB27は約300MBの表セグメントが一つ存在します。一方、パーティション表TAB27_PARTは約100MBの表パーティション・セグメントが合計3つ（合計約300MB）存在していますね。パーティション表は複数セグメントの集合である点は今回の連載でも非常に重要なポイントですから復習しておいてくださいね。
3.各表に対して、次の2つの問合せを実行してください。
（問合せ1）2014年1月分の内、COL1列の値が100のレコードを問い合わせてください。
（問合せ2）2014年1月分のレコード数をカウントして下さい。ただし、count関数にはCOL5列を指定してください。
$ sqlplus TRY/TRY
SQL> set timing on
alter system flush shared_pool ;
alter system flush buffer_cache ;
-- 非パーティション表TAB27へ問合せ1を実行
select * from TAB27
where COL2 >= to_date('2014/01/01','YYYY/MM/DD')
and COL2 < to_date('2014/02/01','YYYY/MM/DD')
and COL1=100 ;
Elapsed: 00:00:00.12
-- 直前に実行したSQLの実行計画の取得
select * from table(dbms_xplan.display_cursor()) ;
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB27 | 1 | 1050 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_TAB27 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
alter system flush shared_pool ;
alter system flush buffer_cache ;
-- パーティション表TAB27_PARTへ問合せ1を実行
select * from TAB27_PART partition for (to_date('2014/01/01','YYYY/MM/DD'))
where COL1=100 ;
Elapsed: 00:00:00.25
-- 直前に実行したSQLの実行計画の取得
select * from table(dbms_xplan.display_cursor()) ;
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 1050 | 2 (0)| 00:00:01 | 1 | 1 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| TAB27_PART | 1 | 1050 | 2 (0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | PK_TAB27_PART | 1 | | 2 (0)| 00:00:01 | 1 | 1 |
--------------------------------------------------------------------------------------------------------------------
まず、「問合せ1」を各表で実行した結果が上記の回答例です。どちらの表に対しても、主キーに作成されている索引を使用したアクセス「INDEX RANGE SCAN」が実行されており、非常に高速なレスポンス・タイムですね。（実行時間については環境に依存しますので、上記の値はこれを保証するものではありません）
ちなみ、非パーティション表に対して実行したSELECT文では、2014年1月分という条件をWhere句で実現しています。これに関しては前回も解説したので問題無いかと思います。しかし、パーティション表に対して実行したSELECT文はWhere句の条件が足りないように見えますよね？しかし、心配はいりません。Where句の条件を省略する代わりにPARTITION句を追加して、参照先レコードを絞りこんでいますから、最終的に出力される結果は同じとなります。要は、「パーティション・プルーニングを手動で制御した」と表現すると理解し易いかもしれませんね。心配な方は実際に試して頂けると実行計画レベルでも同じであることが確認出来ますので試してみてくださいね。
つづきまして、「問合せ2」を各表で実行した結果が下記の回答例です。
$ sqlplus TRY/TRY
SQL> set timing on
alter system flush shared_pool ;
alter system flush buffer_cache ;
-- 非パーティション表TAB27へ問合せ2を実行
select count(COL5) from TAB27
where COL2 >= to_date('2014/01/01','YYYY/MM/DD')
and COL2 < to_date('2014/02/01','YYYY/MM/DD') ;
Elapsed: 00:00:02.81
-- 直前に実行したSQLの実行計画の取得
select * from table(dbms_xplan.display_cursor()) ;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 10483 (100)| |
| 1 | SORT AGGREGATE | | 1 | 1011 | | |
|* 2 | TABLE ACCESS FULL| TAB27 | 80991 | 78M| 10483 (1)| 00:02:06 |
----------------------------------------------------------------------------
alter system flush shared_pool ;
alter system flush buffer_cache ;
-- パーティション表TAB27_PARTへ問合せ2を実行
select count(COL5) from TAB27_PART partition for (to_date('2014/01/01','YYYY/MM/DD')) ;
Elapsed: 00:00:01.11
-- 直前に実行したSQLの実行計画の取得
select * from table(dbms_xplan.display_cursor()) ;
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3599 (100)| | | |
| 1 | SORT AGGREGATE | | 1 | 1011 | | | | |
| 2 | PARTITION RANGE SINGLE| | 82557 | 79M| 3599 (3)| 00:00:44 | 1 | 1 |
|* 3 | TABLE ACCESS FULL | TAB27_PART | 82557 | 79M| 3599 (3)| 00:00:44 | 1 | 1 |
------------------------------------------------------------------------------------------------------
問合せ2のSELECT文は、どちらの表に対しても、全表検索「TABLE ACCESS FULL」が実行されていることを確認出来ます。
ただし、パーティション表TAB27_PARTへ対するSELECT文の実行計画の出力に注目して下さい。「Pstart」と「Pstop」列が追加されていて、「TABLE ACCESS FULL」の行にはそれぞれ「1」という値が埋め込まれています。これは1番目のパーティションから1番目のパーティションまで（つまり、1番目のパーティションだけ）「TABLE ACCESS FULL」しました。と読み取ることが出来ますから、パーティション・プルーニングによって、表全体では無く1月分のレコードが格納されているパーティションだけに絞ったFull Scanが実現できていることが理解できます。これ、実は第25回の連載で紹介するべきだった気がしますね。失礼しました！
さて、次の演習4ではILMのシナリオに沿う形として、各表に格納されるレコードが日々蓄積されていった場合の影響を確認してみましょう。
4.各表のデータ量が6ヶ月分、9ヶ月分、12ケ月分へ増加した場合、演習3の問合せ1と問合せ2の実行時間がどのように変化するのかを調査して下さい。
$ sqlplus TRY/TRY
SQL>
-- TAB27表とTAB27_PART表へ3ヶ月分（4月～6月）のレコードをINSERT
insert /*+append */ all into TAB27 into TAB27_PART -- 2つの表へ同時INSERT
select COL1, COL2 + 90, COL3, COL4, COl5 from TAB27_BASE -- 4月分
union all
select COL1, COL2 + 120, COL3, COL4, COl5 from TAB27_BASE -- 5月分
union all
select COL1, COL2 + 151, COL3, COL4, COl5 from TAB27_BASE ; -- 6月分
commit ;
SQL> -- 6ヶ月分のデータ量が格納されている状態で演習3のSQLを実行
@query3.sql
$ sqlplus TRY/TRY
SQL>
-- TAB27表とTAB27_PART表へ3ヶ月分（7月～9月）のレコードをINSERT
insert /*+append */ all into TAB27 into TAB27_PART -- 2つの表へ同時INSERT
select COL1, COL2 + 181, COL3, COL4, COl5 from TAB27_BASE -- 7月分
union all
select COL1, COL2 + 212, COL3, COL4, COl5 from TAB27_BASE -- 8月分
union all
select COL1, COL2 + 243, COL3, COL4, COl5 from TAB27_BASE ; -- 9月分
commit ;
SQL> -- 9ヶ月分のデータ量が格納されている状態で演習3のSQLを実行
@query3.sql
$ sqlplus TRY/TRY
SQL>
-- TAB27表とTAB27_PART表へ3ヶ月分（10月～12月）のレコードをINSERT
insert /*+append */ all into TAB27 into TAB27_PART -- 2つの表へ同時INSERT
select COL1, COL2 + 273, COL3, COL4, COl5 from TAB27_BASE -- 10月分
union all
select COL1, COL2 + 304, COL3, COL4, COl5 from TAB27_BASE -- 11月分
union all
select COL1, COL2 + 334, COL3, COL4, COl5 from TAB27_BASE ; -- 12月分
commit ;
SQL> -- 12ヶ月分のデータ量が格納されている状態で演習3のSQLを実行
@query3.sql
上記3つの回答例を参考に、是非皆さんの検証環境でも試して頂ければ納得感が圧倒的に違うと思います。
演習3での実行時間は格納データ量が3ヶ月時点のものあったので、この演習4の実行結果と合わせて問合せ2（TABLE ACCESS FULLを使用して1月分のレコード件数をカウントするSELECT文）の結果を整理してみると次の表の通りとなります。
| 格納データ量 | 非パーティション | パーティション |
| 3ヶ月 | 00:00:02.81 | 00:00:01.11 |
| 9ヶ月 | 00:00:07.89 | 00:00:01.13 |
| 12ケ月 | 00:00:10.35 | 00:00:01.17 |
いかがでしょうか？非パーティション表、パーティション表ともに非常に解り易い特徴が表れていますよね。
非パーティション表の方は格納データ量と比例して実行時間が遅延していきますが、パーティション表の場合は同じパフォーマンスを維持し続けることを確認することが出来ます。この理由はそれほど難しくは無いですね。非パーティション表の場合は格納データ量が多くなれば、それだけアクセスする必要があるデータ量が増加するわけですから、CPU時間やIO時間に跳ね返ってくるでしょう。一方、パーティション表は常に1月分のパーティションに限定してアクセスするパーティション・プルーニングが機能しますから、その他の月のデータ量が増加する影響を全く受けないと言う事ですね。パーティション・プルーニング恐るべしです！！
つまり整理すると、パーティション化をすることにより、SQLのパフォーマンスに影響なく大量データを保持することが可能になりそうですよね。はい。それは正解です。しかし、ストレージ側の空き容量やコストを忘れてはいけませんよね。と言う事で、まずは表セグメントのサイズを改めて確認してみましょう。
5.演習2と同様に、各表のセグメント・サイズを確認してください。
$ sqlplus TRY/TRY SQL> set linesize 150 pagesize 50000 col SEGMENT_NAME for a12 col PARTITION_NAME for a16 col TABLESPACE_NAME for a16 select SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES/1024/1024 from USER_SEGMENTS where SEGMENT_TYPE like 'TABLE%' and SEGMENT_NAME != 'TAB27_BASE'; SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES/1024/1024 ------------ ---------------- ------------------ ---------------- --------------- TAB27 TABLE TBS27 1216 TAB27_PART P201401 TABLE PARTITION TBS27 104 TAB27_PART SYS_P183 TABLE PARTITION TBS27 104 TAB27_PART SYS_P184 TABLE PARTITION TBS27 104 TAB27_PART SYS_P201 TABLE PARTITION TBS27 104 TAB27_PART SYS_P202 TABLE PARTITION TBS27 104 TAB27_PART SYS_P203 TABLE PARTITION TBS27 104 TAB27_PART SYS_P221 TABLE PARTITION TBS27 104 TAB27_PART SYS_P222 TABLE PARTITION TBS27 104 TAB27_PART SYS_P223 TABLE PARTITION TBS27 104 TAB27_PART SYS_P241 TABLE PARTITION TBS27 104 TAB27_PART SYS_P242 TABLE PARTITION TBS27 104 TAB27_PART SYS_P243 TABLE PARTITION TBS27 104
はい。演習開始時点では3ヶ月分のデータでしたが、ここまで演習を進めて頂いた結果、12ケ月分のデータ量を保持していることを確認出来ますね。特に、パーティション表側では約100MBのセグメントが一ヵ月毎に合計12個作成されていますね。
ここで、冒頭にご紹介したILMを思い出して下さいね。
情報（Information）は時間の経過と共に用途やアクセス頻度が変化していくもので、一般的には新しい情報は多くのユーザーから頻繁に参照/更新されますが、古い情報に対してのアクセスは少なくなる傾向があります。この鮮度とアクセス頻度の傾向に合わせて情報を管理すること、またその方法をILMと呼んでいます。
これを今回の演習で作成してきたパーティション表TAB27_PARTに置き換えて考えてみると、最新の12月分のデータはユーザーから頻繁に参照/更新されるが、古い1月分のデータに対してのアクセスは少なくなる。と言う事です。もちろん、全ての表において同じことが言い切れるとは思っていませんが、時系列で注文データを格納しているトランザクション表は該当し易いと思います。
そして、このアクセス頻度が低い古いデータを高価な高速ストレージ上に配置しておく必要性はありますかね？ほとんどの方の答えは「No」になると思います。つまり、「古いデータは、より価格の安い高速とまでは言えないストレージへ移動して良い」という発想について納得されていると言う事になります。
今回は、さらに圧縮機能を組み合わせてみましょう。と言うのは、上記のデータを移動のタイミングで圧縮することで、移動先のストレージの消費容量を節約することが可能となり、より長期間のデータ保管が実現できそうな気がしますよね？
6.一番古い側から4カ月分（1月～4月）のレコードをAdvanced Compression OptionのOLTP表圧縮機能で圧縮することで、セグメント・サイズが小さくなることを確認してください。
$ sqlplus TRY/TRY SQL> $ sqlplus TRY/TRY SQL> -- 4ヶ月分のパーティションをそれぞれ圧縮 alter table TAB27_PART move partition P201401 compress for oltp ; alter table TAB27_PART move partition SYS_P183 compress for oltp ; alter table TAB27_PART move partition SYS_P184 compress for oltp ; alter table TAB27_PART move partition SYS_P201 compress for oltp ; -- パーティション・セグメント毎の圧縮属性とサイズを確認 set linesize 150 pagesize 50000 col TABLE_NAME for a12 col PARTITION_NAME for a16 col TABLESPACE_NAME for a16 select A.TABLE_NAME, A.PARTITION_NAME, A.COMPRESSION, A.COMPRESS_FOR, B.BYTES/1024/1024 "MB" from USER_TAB_PARTITIONS A, USER_SEGMENTS B where A.TABLE_NAME = B.SEGMENT_NAME and A.PARTITION_NAME = B.PARTITION_NAME and B.SEGMENT_TYPE = 'TABLE PARTITION' order by 2 ; TABLE_NAME PARTITION_NAME COMPRESS COMPRESS_FOR MB ------------ ---------------- -------- ------------ ---------- TAB27_PART P201401 ENABLED OLTP 24 TAB27_PART SYS_P183 ENABLED OLTP 24 TAB27_PART SYS_P184 ENABLED OLTP 24 TAB27_PART SYS_P201 ENABLED OLTP 24 TAB27_PART SYS_P202 DISABLED 104 TAB27_PART SYS_P203 DISABLED 104 TAB27_PART SYS_P221 DISABLED 104 TAB27_PART SYS_P222 DISABLED 104 TAB27_PART SYS_P223 DISABLED 104 TAB27_PART SYS_P241 DISABLED 104 TAB27_PART SYS_P242 DISABLED 104 TAB27_PART SYS_P243 DISABLED 104
以前、第15回と第16回で圧縮表への変更方法を解説させて頂きましたが、その際表全体を丸ごと圧縮表へ変更していました。もちろん、今回のパーティション表でも同じオペレーションで圧縮表へ変更することは可能ですが、それではパーティション表の面白さが全くありません。と言う事で、この演習では特定のパーティションのみを圧縮する方法を体験して頂きます。つまり、「あるパーティションは圧縮されているが、あるパーティションは非圧縮」というパーティション表になります。
とは言え、それほど難しい作業では無く、ALTER TABLE文でパーティション名と圧縮属性を指定するだけで済みます。このオペレーションはパーティション化によってパーティション単位でメンテナンス可能になると言う大きなメリットなのですが、若干当たり前過ぎて見逃されがちですね。もし、パーティション化していなければ表全体でしか圧縮表に変更出来ないですし、表全体を圧縮表へ変更するには非常に長時間のメンテナンス時間が必要となるでしょう。パーティション化でメンテナンス時間の短縮が可能な例の一つですね。また、補足となりますが、「move」句を付けるが否かで挙動が変わってくる点はご注意くださいね。「move」句を付けた場合はそのパーティション内の既存レコードが圧縮されますが、「move」句を付けなかった場合はそのパーティションの圧縮属性が有効化するだけで、既存レコードは非圧縮状態となります。この辺りに関しては、第15回でご紹介していますので、そちらも参考にしてみてください。
さて、上記の回答例では、1月から4月までの4つのパーティションのデータをOLTP表圧縮で圧縮することで、104MB から24MBまで対象のパーティション・セグメントが小さくなっていることを確認することが出来ます。約四分の一まで圧縮できたと言う事は、例えば、これまで1年間分のデータしか保管出来なかったストレージ容量であっても、圧縮を適用することで4年分のデータを保管できることを意味します。もちろん圧縮率はデータ特性によって異なりますので、本番採用を検討される際は、DBMS_COMPRESSIONパッケージを使用した圧縮率の見積もりを実施してみてください。
ちなみに、私のようにパーティション名を調べるのが面倒な方は、前回も紹介したPARTITION FOR句を代用することでもALTER TABLE文を実行することが可能です。さらに、この圧縮のタイミングで格納先の表領域を移動させることも可能ですので、その例を以下に載せておきますね。
$ sqlplus TRY/TRY
SQL>
-- 2014年1月のパーティションを圧縮しつつ、別表領域を移動する例（partition for句を使用）
alter table TAB27_PART
move partition for (to_date('2014/01/01', 'YYYY/MM/DD'))
compress for oltp
tablespace TBS27_LOW ;
7.一部のパーティションを圧縮したパーティション表TAB27_PARTに対して、演習3で実行した問合せ1（INDEX RANGE SCANを使用していたSQL）を再実行してください。また、実行計画も取得して下さい。
$ sqlplus TRY/TRY
SQL>
$ sqlplus TRY/TRY
SQL> set timing on
alter system flush shared_pool ;
alter system flush buffer_cache ;
select * from TAB27_PART partition for (to_date('2014/01/01','YYYY/MM/DD'))
where COL1=100 ;
Elapsed: 00:00:00.44
-- 直前に実行したSQLの実行計画の取得
select * from table(dbms_xplan.display_cursor()) ;
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 642 (100)| | | |
| 1 | PARTITION RANGE SINGLE| | 2 | 2100 | 642 (3)| 00:00:08 | 1 | 1 |
|* 2 | TABLE ACCESS FULL | TAB27_PART | 2 | 2100 | 642 (3)| 00:00:08 | 1 | 1 |
-----------------------------------------------------------------------------------------------------
ここで「問合せ1」を再実行してみましょう。何を今更という感は否めませんが、一部のパーティションを圧縮した後の重要な注意点を浮き彫りにしてくれますので、ものは試しで実行してみてください。とは言え、今回のテスト環境はデータ量が非常に少ないので何も問題無く実行出来てしまうかもしれません。しかし、そのSELECT文の実行計画を確認することで明確になります。
演習3において「パーティション表TAB27_PARTへ問合せ1を実行」を実行した際の実行計画では「INDEX RANGE SCAN」を使用していましたが、この演習7では「TABLE ACCESS FULL」に変化（索引を使用しない）しているではないですかー！！と、少しだけ驚いて見てください。では、この理由を探っていきましょう。
8.パーティション表TAB27_PARTの索引のステータスを確認してください。
$ sqlplus TRY/TRY SQL> col INDEX_NAME for a20 col INDEX_TYPE for a10 col PARTITIONED for a12 select INDEX_NAME, INDEX_TYPE, PARTITIONED, STATUS from USER_INDEXES where TABLE_NAME='TAB27_PART' ; INDEX_NAME INDEX_TYPE PARTITIONED STATUS -------------------- ---------- ------------ -------- PK_TAB27_PART NORMAL YES N/A IDX_TAB27_PART_COL3 NORMAL NO UNUSABLE IDX_TAB27_PART_COL4 NORMAL YES N/A
索引を使用しなくなった理由の候補としては、「索引が無い」、「索引は有るが使用不可」、「索引は使用可能だが使用しない方が効率的とオプティマイザが判断した」、のいずれかになります。
まずはUSER_INDEXESビューで確認してみる限り、元々使用していた索引「PK_TAB27_PART」は存在していることが確認できます。しかしながら、STATUS列が「N/A」と表示されているので使用可能か否かは判断出来ません。このように「N/A」と表示されている理由は、この索引がパーティション化されているからなのです。PARTITIONED列に「YES」と表示されている索引はパーティション化されていることを意味しますので、その場合はUSER_IND_PARTITIONSビューに問合せする必要があります。
ちなみに、索引IDX_TAB27_PART_COL3のPARTITIONED列は「NO」と表示されている為、パーティション化されていないことが理解できます。よって、USER_INDEXESビューのSTATUS列においてステータスを確認することが可能ですが、上記の回答例では「UNUSABLE」と表示されているので使用不可能な状況になっていることが理解できます。
$ sqlplus TRY/TRY SQL> col INDEX_NAME for a20 col INDEX_TYPE for a10 col PARTITIONED for a12 select INDEX_NAME, PARTITION_NAME, STATUS from USER_IND_PARTITIONS order by 1,2 ; INDEX_NAME PARTITION_NAME STATUS ------------------------------ ---------------- -------- PK_TAB27_PART P201401 UNUSABLE PK_TAB27_PART SYS_P183 UNUSABLE PK_TAB27_PART SYS_P184 UNUSABLE PK_TAB27_PART SYS_P201 UNUSABLE PK_TAB27_PART SYS_P202 USABLE PK_TAB27_PART SYS_P203 USABLE PK_TAB27_PART SYS_P221 USABLE PK_TAB27_PART SYS_P222 USABLE PK_TAB27_PART SYS_P223 USABLE PK_TAB27_PART SYS_P241 USABLE PK_TAB27_PART SYS_P242 USABLE PK_TAB27_PART SYS_P243 USABLE IDX_TAB27_PART_COL4 P201401 UNUSABLE IDX_TAB27_PART_COL4 SYS_P183 UNUSABLE IDX_TAB27_PART_COL4 SYS_P184 UNUSABLE IDX_TAB27_PART_COL4 SYS_P201 UNUSABLE IDX_TAB27_PART_COL4 SYS_P202 USABLE IDX_TAB27_PART_COL4 SYS_P203 USABLE IDX_TAB27_PART_COL4 SYS_P221 USABLE IDX_TAB27_PART_COL4 SYS_P222 USABLE IDX_TAB27_PART_COL4 SYS_P223 USABLE IDX_TAB27_PART_COL4 SYS_P241 USABLE IDX_TAB27_PART_COL4 SYS_P242 USABLE IDX_TAB27_PART_COL4 SYS_P243 USABLE
次に、USER_IND_PARTITIONSビューへ問い合わせて、パーティション化されている索引のステータスを確認してみましょう。
おっ？全部ではないにしてもUNUSABLE（使用不可）となっているパーティションが存在していることが確認出来ましたね。これには実は傾向があるのですが・・・。気付いて頂けましたかね？そうです。演習6においてデータ圧縮を実行したパーティションに作成されている索引だけがUNUSABLEに変化しているのですね。これが「MOVE」句による影響であることがパッと思い浮かんだ方は非常に勉強熱心な方ですね。第15回 圧縮表への変更方法と注意点で解説させて頂いているので、そのコピーを載せておきます。
「MOVE」は一般的に対象表が格納されている表領域を別の表領域上へ移動する際に使用されるケースが多く、そういった目的で使用されたことがある方はいらっしゃるのではないでしょうか。その場合をイメージして頂くと理解し易いのですが、表領域間を移動するということは内部的には別のセグメントへレコードを移し替えている（INSERT + SELECT）ことになりますよね。よって、「alter table <表名> MOVE compress for oltp;」では、既存レコードを圧縮表へINSERT + SELECTし直すような動作となるため、既存レコードも圧縮されて格納されることになります。
注意すべき点としては、索引のRebuildが必要となります。「レコードを入れ直す」=「各レコードのROWIDが変更される」のでROWIDを保持しているB*Tree索引が無効化する為です。
ちなみに、ALTER TABLE MOVE文に「UPDATE INDEXES」句を付け加えることで、表セグメントの移動後に索引のRebuildを自動的に実行させることが可能だったりしますが、個人的にはあまり使用しないです。ALTER TABLE MOVE文は対象の表、もしくはパーティションを排他ロックしますので、その途中で別セッションからレコードのUPDATE文が実行された場合には、そのUPDATE文は「表セグメントの移動が完了するまで待機」させられます。ここで疑問が生まれますよね？「UPDATE INDEXES」句も「表セグメントの移動後」に索引のRebuildを実行しますが、待機中のUPDATE文と索引のRebuildはどちらが優先されるのでしょうか？実は、待機中のUPDATE文が先に実行されます。これにより未コミットのレコードが存在する為、索引Rebuildの処理がORAエラーで失敗することがあります。なので、完全にDML文が実行されないと保証されていれば、UPDATE INDEXES句を付けても良いと思いますが、私は専ら表のオンライン再定義を使用していますよ。
ちなみに（その2）ですが、一度でもMOVEやTRUNCATEした表やパーティションを識別する方法はご存知ですかね？実は簡単に確認する方法があって、次のSQL文の実行結果の通りUSER_OBJECTSビューのOBJECT_ID列とDATA_OBJECT_ID列の値が異なるオブジェクトが該当します。DATA_OBJECT_ID列の初期値はOBJECT_ID列と同じ値であり、MOVEやTRUNCATEした場合にDATA_OBJECT_ID列の値が増加していくのです。
$ sqlplus TRY/TRY SQL> set pages 5000 col OBJECT_NAME for a12 select OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID from USER_OBJECTS where OBJECT_NAME='TAB27_PART' order by 3 ; OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID ------------ ------------------------------ ---------- -------------- TAB27_PART 14741 TAB27_PART P201401 14742 14782 TAB27_PART SYS_P183 14749 14783 TAB27_PART SYS_P184 14752 14784 TAB27_PART SYS_P201 14755 14785 TAB27_PART SYS_P202 14758 14758 TAB27_PART SYS_P203 14761 14761 TAB27_PART SYS_P221 14764 14764 TAB27_PART SYS_P222 14767 14767 TAB27_PART SYS_P223 14770 14770 TAB27_PART SYS_P241 14773 14773 TAB27_PART SYS_P242 14776 14776 TAB27_PART SYS_P243 14779 14779
9.非パーティション索引IDX_TAB27_PART_COL3とローカル索引IDX_TAB27_PART_COL4の再構成に要する時間を比較して下さい。
$ sqlplus TRY/TRY SQL> set timing on -- 非パーティション索引の再構成（パーティション指定は不可） alter system flush buffer_cache ; alter index IDX_TAB27_PART_COL3 rebuild ; Elapsed: 00:00:12.44 -- ローカル索引の再構成（パーティション指定が可能） alter system flush buffer_cache ; alter index IDX_TAB27_PART_COL4 rebuild partition P201401 ; alter index IDX_TAB27_PART_COL4 rebuild partition SYS_P183 ; alter index IDX_TAB27_PART_COL4 rebuild partition SYS_P184 ; alter index IDX_TAB27_PART_COL4 rebuild partition SYS_P201 ; Elapsed: 00:00:00.54 Elapsed: 00:00:00.47 Elapsed: 00:00:00.52 Elapsed: 00:00:00.47
ローカル索引について簡単に紹介しておきますね。
表をパーティション化した場合は複数の表パーティション・セグメントに分割されますよね。この表パーティション・セグメントに合わせて、索引セグメントもパーティション化している索引をローカル索引と呼びます。CREATE INDEX文に「LOCAL」句を指定することでこのような索引を作成することが可能であり、表パーティションと一致している為、メンテナンスが非常に簡単である特徴があります。例としては、一部の表パーティションをDROP PARTITIONした場合、その表パーティションに紐づいていた索引パーティションだけが削除されるだけで、その他の索引パーティションには影響を及ぼしません。
もう一つのパーティション化されている索引の種類があります。それはグローバル索引なのですが、表のパーティション化と全く関係なくパーティション化されている索引を呼びます。パーティション化されていない索引をグローバル索引と呼んでいるケースを目にする機会がありますが、それは非パーティション索引と呼ぶべきなのかと思います。グローバル索引、非パーティション索引とも言えることですが、一部の表パーティションをDROP PARTITIONすると、その表パーティション内に含まれていたレコードのROWIDが消えることになるので索引のRebuildが必要となるのは理解し易いと思います。
今回の演習の中でこれらを分類してみると、索引PK_TAB27_PARTと索引IDX_TAB27_PART_COL4がローカル索引であり、MOVE PARTITIONの影響を受けた索引パーティションが一部となっています。索引IDX_TAB27_PART_COL3が非パーティション索引であり、一部の表パーティションをMOVE PARTIITONした影響で索引全体が使用不可能となっていますよね。
と言う事で、まったく同じ値が格納されたCOL3列とCOL4列を使用して、索引全体をRebuildする実行時間と一部のローカル索引のパーティションのみをRebuildする実行時間を比較した結果が上記の回答例です。索引全体のRebuildには約12秒を要していますが、ローカル索引の場合は4つの索引パーティションをRebuildしても約2秒で完了しています。検証環境なのでデータ量は少ないですが、本番データベースのデータ量で考えてみれば、この実行時間の差はメンテナンス時間へ大きく影響してくることが想像できると思います。
さて、いかがでしたでしょうか？
少し無理やりな感じは否めませんが、ILMソリューションを実現するパーティション表の管理オペレーションについて紹介させて頂きました。パーティション表は非パーティション表と比較して考慮ポイントや確認するV$ビューが増えたりはしますが、それ以上にパフォーマンスの維持や向上、さらにはメンテナンス時間の削減へ寄与する部分が多いという点を実際に体験して納得して頂けたのであれば幸いです。
ちなみに、現時点（2014年3月）での最新リリースであるOracle Database 12c Release 1 においてはILMソリューションの容易な実装を可能とする新機能「Automatic Data Optimization」が追加されています。じゃあ、そっちを紹介してくれれば良いのでは？と思う方もいらっしゃると思いますが、自動管理のメリットを理解する為には手動管理による課題を把握している必要があると私は思っています。なので、まずは、手動によるパーティション管理を体験して頂いた次第です。とは言え、「Automatic Data Optimization」を私自身がまだまだ使いこなせていませんから、連載でご紹介する機会は少し先になりそうです。皆さんに負けないように頑張りますよー！！
いつも以上の長編記事となってしまいましたが、今回も最後まで体験して頂きましてありがとうございました。次回以降もどうぞよろしくお願い致します。