スキップしてメイン コンテンツに移動

パーティション・ビューって..

Oracle EEのオプションで最も使うと思われるものがPartitioning Optionではないかと個人的に思っています。小幡さんのブログ(Storage Serverフィルタリング考察)にてPartition Viewという懐かしいモノが紹介されていました。7.2で鳴り物入りと書かれていましたが、すぐにPartitioning Tableがリリースされて陽の目を見なかったですね。。。

しかし、Partition ViewにはPartitioning Tableにない素晴らしい点があります。それは、EEじゃなくても使える。頑張って作りこめばPartitioning Optionみたいに使える(これは利点なのか...?)ことです。

というわけで、一回、Partition Viewをまとめてみます。

* 個人的には、昔、この機能を使いこなそうとかなり苦労しました。

まず、大前提です。

  1. 基本的には、Partitioning Tableのように論理的に一つのテーブルとして扱えません(あくまでも1つのビューです)
  2. なので、グローバルインデックスや、カラムの追加/削除、パーティションの追加/削除といったことは透過的に実行できません。
  3. さらに、DMLもビューに対して実行できません(union all viewなので)

もう一度書きますが、あくまでもビューです。

では、普通のビューとどう違うのか?
それは、たった1つなのですが、現在のPartitioning TableのようにPartition ViewへのQueryの実行計画をPartitionを考慮して立ててくれること。です。

事前に準備すべき事は以下となります。

  1. 各テーブルのPartition Keyとなるカラムにはチェック制約が必要
  2. 初期化パラメータ(partition_view_enabled)がTRUE(ただし、大昔から、このパラメータは無くなり_partition_view_enabledがデフォルトTRUEとなっていますので、余り気にしない)

1のチェック制約を見て、ナルホドと思いますね。単純というか何と言うか...

では、一応、やってみます。

-- 通常のビュー用
-- 2010 Q1
create table t2010q1 (term date
     , id number
     , text varchar2(4000));
-- 2010 Q2
create table t2010q2 (term date
     , id number
     , text varchar2(4000));
-- 2010 Q3
create table t2010q3 (term date
     , id number
     , text varchar2(4000));
-- 2010 Q4
create table t2010q4 (term date
     , id number
     , text varchar2(4000));

-- パーティションビュー用
-- 2010 Q1
create table p2010q1 (term date
     , id number
     , text varchar2(4000)
     , constraint p2010q1_chk
      check(term >= to_date('2010/01/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss')
      and   term <= to_date('2010/03/31 23:59:59', 'yyyy/mm/dd hh24:mi:ss'))
     );
-- 2010 Q2
create table p2010q2 (term date
     , id number
     , text varchar2(4000)
     , constraint p2010q2_chk
      check(term >= to_date('2010/04/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss')
      and   term <= to_date('2010/06/30 23:59:59', 'yyyy/mm/dd hh24:mi:ss'))
     );
-- 2010 Q3
create table p2010q3 (term date
     , id number
     , text varchar2(4000)
     , constraint p2010q3_chk
      check(term >= to_date('2010/07/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss')
      and   term <= to_date('2010/09/30 23:59:59', 'yyyy/mm/dd hh24:mi:ss'))
     );
-- 2010 Q4
create table p2010q4 (term date
     , id number
     , text varchar2(4000)
     , constraint p2010q4_chk
      check(term >= to_date('2010/10/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss')
      and   term <= to_date('2010/12/31 23:59:59', 'yyyy/mm/dd hh24:mi:ss'))
     );

-- 通常のビューの作成
create or replace view t2010 as
select * from t2010q1
union all
select * from t2010q2
union all
select * from t2010q3
union all
select * from t2010q4;

-- パーティションビューの作成
create or replace view p2010 as
select * from p2010q1
union all
select * from p2010q2
union all
select * from p2010q3
union all
select * from p2010q4;

先程、書きましたが、通常のビューとパーティションビューの違いは、メンバーとなるテーブルにチェック制約がついているか否かの違いです。
では、データを入れますが、ビュー経由で直接データのinsertはできません、やりたいなら、(Partitioning Tableのように扱いたいなら)ビューに対してinstead triggerなどを仕込む必要があります。(今回は、面倒なので、テーブルにinsertしています) 
declare
 dt date;
 tbl varchar2(30);
begin
 for dy in 0 .. 364 loop
  dt := to_date('2010/01/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss') + dy;

  if dt >= to_date('2010/01/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss') and
     dt <= to_date('2010/03/31 23:59:59', 'yyyy/mm/dd hh24:mi:ss') then
   tbl := '2010q1';
  elsif dt >= to_date('2010/04/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss') and
     dt <= to_date('2010/06/30 23:59:59', 'yyyy/mm/dd hh24:mi:ss') then
   tbl := '2010q2';
  elsif dt >= to_date('2010/07/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss') and
     dt <= to_date('2010/09/30 23:59:59', 'yyyy/mm/dd hh24:mi:ss') then
   tbl := '2010q3';
  elsif dt >= to_date('2010/10/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss') and
     dt <= to_date('2010/12/31 23:59:59', 'yyyy/mm/dd hh24:mi:ss') then
   tbl := '2010q4';
  else
   tbl := '';
  end if;

  if tbl is not null then
   for h in 0 .. 23 loop
    for m in 0 .. 60 loop
     begin
      execute immediate 'insert all ' ||
           'into ' || 't' || tbl || ' values (:1, :2, :3) ' ||
           'into ' || 'p' || tbl || ' values (:4, :5, :6) ' ||
           'select * from dual'
         using dt + h/24 + m/24/60
          , dy + h/100 + m/10000
          ,'sample'
          , dt + h/24 + m/24/60
          , dy + h/100 + m/10000
          ,'sample';
     exception
      when others then
       null;
     end;
    end loop;
    commit;
   end loop;
   commit;
  end if;
 end loop;
 commit;
end;
/

では、一番大事な、SELECTの実行計画を見てみます。 
SQL> select count(*) from t2010 where term = to_date('2010/01/01','yyyy/mm/dd');

  COUNT(*)
----------
         1


実行計画
----------------------------------------------------------
Plan hash value: 709745821

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     1 |     9 |   548   (1)| 00:00:07 |
|   1 |  SORT AGGREGATE      |         |     1 |     9 |            |          |
|   2 |   VIEW               | T2010   |    20 |   180 |   548   (1)| 00:00:07 |
|   3 |    UNION-ALL         |         |       |       |            |          |
|*  4 |     TABLE ACCESS FULL| T2010Q1 |     5 |    45 |   137   (1)| 00:00:02 |
|*  5 |     TABLE ACCESS FULL| T2010Q2 |     5 |    45 |   137   (1)| 00:00:02 |
|*  6 |     TABLE ACCESS FULL| T2010Q3 |     5 |    45 |   137   (1)| 00:00:02 |
|*  7 |     TABLE ACCESS FULL| T2010Q4 |     5 |    45 |   137   (1)| 00:00:02 |
--------------------------------------------------------------------------------

全てのパーティション(あえてパーティションと呼びます)に適切なインデックスは作成していないので、T2010Q1パーティションのフルスキャンは許せますが、全パーティションにフルスキャンが発生しています。何とかしたいですよね。
続いてパーティションビューの実行計画を見てみます。 
SQL> select count(*) from p2010 where term = to_date('2010/01/01','yyyy/mm/dd');

  COUNT(*)
----------
         1


実行計画
----------------------------------------------------------
Plan hash value: 3622780762

---------------------------------------------------------------------------------

| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |     9 |   137   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE       |         |     1 |     9 |            |          |
|   2 |   VIEW                | P2010   |     8 |    72 |   137   (1)| 00:00:02 |
|   3 |    UNION-ALL          |         |       |       |            |          |
|*  4 |     TABLE ACCESS FULL | P2010Q1 |     5 |    45 |   137   (1)| 00:00:02 |
|*  5 |     FILTER            |         |       |       |            |          |
|*  6 |      TABLE ACCESS FULL| P2010Q2 |     5 |    45 |   137   (1)| 00:00:02 |
|*  7 |     FILTER            |         |       |       |            |          |
|*  8 |      TABLE ACCESS FULL| P2010Q3 |     5 |    45 |   137   (1)| 00:00:02 |
|*  9 |     FILTER            |         |       |       |            |          |
|* 10 |      TABLE ACCESS FULL| P2010Q4 |     5 |    45 |   137   (1)| 00:00:02 |
---------------------------------------------------------------------------------

先程と違いFILTERオペレーションが追加されました。これは、FILTERの結果FALSEなら、後続のオペレーションを実行する。という意味なので。
P2010Q1パーティションは必ずフルスキャンを実行しますが、その他のパーティションには、FILTERがかかり、フルスキャンを実行しない(というかテーブルへのアクセスもしない)ということになります。

かなり、かなり限定的(もしくは、相当いじり倒せば)Partition Viewも活躍できる場がありそうです。皆様も困った時に思い出してみてください。
ラベル:

コメント

コメントを投稿