kenken0807_DBメモ

つば九郎が好きなDBAです。Oracle Standard Editionでの運用やツールとかとかの備忘録。特に記載がない場合はoracle11gR2です。時々MySQL

Oracle SEでINSERT TRIGGERとVIEWで作るパーティション

oracleパーティション機能はoracle EE+optionで使用可能です。
これをなんとかstandard editionで実現できないかと模索。。。
insertとselectのみであればtriggerとviewを使用し、
パーティション風の機能の実現可能だったのでメモ。
バージョンはoracle 11.2.0.4 SEone。

実際にこの仕組みは実装はしてません。検証段階です。

まず、要件としては

1. ログインログとして、ログインするごとに一件insertする。  
2. 月ごとにパーティションを分けて、半年分はとっておく。  
3. 以前の半年分は容量節約のためtruncateしたい。  
4. selectすることはあるが、updateやdeleteはしない。  
5. insert先もselect先も同様のテーブル名称とする。  

これを実現するには、まずselectにおいては12分割(月ごとに)したテーブルに対してunion allしたviewを作成してあげればいいかなと考えた。

あとは、insertで振る分けるための機能として、探していたところInstead ofトリガーという機能を発見。
Instead ofトリガーとは・・

ビューに対してのみ作成可能。
トリガーを起動するDML文が実行されるかわりに、INSTEAD OFトリガーが起動されます。
本質的に更新できないビューを更新する唯一の方法
Oracle® Database PL/SQL言語リファレンス

なるほど、ということはこれを先ほどのviewに実装してあげて、
代わりに分割したテーブルにインサートしてあげれば良いな、と考えた。

しかし、union allしたviewに対してinstead of トリガーを作成はできるが、
insertを行うと

ORA-01732: このビューではデータ操作が無効です

が発生するためinsertできない。
対策としてunion allしたviewに対してviewを作成し、instead of トリガーを作成することで解決した。

よって、
12分割したテーブルをunion allしたview対してviewを作成しInstead ofのInsert triggerを作成して12分割したテーブルに割り振るようにすると実現可能である。

図にすると以下のような感じ。
f:id:kenken0807:20150721154436p:plain

登場人物としては以下

login1・・分割したテーブル(1/12)
login2・・分割したテーブル(2/12)
login3・・分割したテーブル(3/12)
login4・・分割したテーブル(4/12)
login5・・分割したテーブル(5/12)
login6・・分割したテーブル(6/12)
login7・・分割したテーブル(7/12)
login8・・分割したテーブル(8/12)
login9・・分割したテーブル(9/12)
login10・・分割したテーブル(10/12)
login11・・分割したテーブル(11/12)
login12・・分割したテーブル(12/12)
login_view・・login1~12をunionしたview
login・・login_viewのview(select・insert用)
login_trigger・・loginのinsert trigger
login_seq・・インサート用シーケンス

以下はオーバヘッドを比較するための検証用
login_hikaku・・オーバヘッドを見るための検証用テーブル
login_hikaku_seq・・比較検証用シーケンス

準備する

テーブル作成

create table login1 (id int primary key,flg int ,text varchar(200),d date);
create table login2 (id int primary key,flg int ,text varchar(200),d date);
create table login3 (id int primary key,flg int ,text varchar(200),d date);
create table login4 (id int primary key,flg int ,text varchar(200),d date);
create table login5 (id int primary key,flg int ,text varchar(200),d date);
create table login6 (id int primary key,flg int ,text varchar(200),d date);
create table login7 (id int primary key,flg int ,text varchar(200),d date);
create table login8 (id int primary key,flg int ,text varchar(200),d date);
create table login9 (id int primary key,flg int ,text varchar(200),d date);
create table login10 (id int primary key,flg int ,text varchar(200),d date);
create table login11 (id int primary key,flg int ,text varchar(200),d date);
create table login12 (id int primary key,flg int ,text varchar(200),d date);
create index idx_login1 on login1(flg,d);
create index idx_login2 on login2(flg,d);
create index idx_login3 on login3(flg,d);
create index idx_login4 on login4(flg,d);
create index idx_login5 on login5(flg,d);
create index idx_login6 on login6(flg,d);
create index idx_login7 on login7(flg,d);
create index idx_login8 on login8(flg,d);
create index idx_login9 on login9(flg,d);
create index idx_login10 on login10(flg,d);
create index idx_login11 on login11(flg,d);
create index idx_login12 on login12(flg,d);
create table login_hikaku (id int primary key,flg int ,text varchar(200),d date);
create index idx_login_hikaku on login_hikaku(flg,d);

VIEW作成

create or replace view login_view as
select * from login1 union all
select * from login2 union all
select * from login3 union all
select * from login4 union all
select * from login5 union all
select * from login6 union all
select * from login7 union all
select * from login8 union all
select * from login9 union all
select * from login10 union all
select * from login11 union all
select * from login12;
create or replace view login as select * from login_view ;

trigger作成

create or replace trigger login_trigger
instead of
insert on login for each row
begin
 case extract(MONTH from :new.d)
  when 1  then insert into login1  values (:new.id,:new.flg,:new.text,:new.d);
  when 2  then insert into login2  values (:new.id,:new.flg,:new.text,:new.d);
  when 3  then insert into login3  values (:new.id,:new.flg,:new.text,:new.d);
  when 4  then insert into login4  values (:new.id,:new.flg,:new.text,:new.d);
  when 5  then insert into login5  values (:new.id,:new.flg,:new.text,:new.d);
  when 6  then insert into login6  values (:new.id,:new.flg,:new.text,:new.d);
  when 7  then insert into login7  values (:new.id,:new.flg,:new.text,:new.d);
  when 8  then insert into login8  values (:new.id,:new.flg,:new.text,:new.d);
  when 9  then insert into login9  values (:new.id,:new.flg,:new.text,:new.d);
  when 10 then insert into login10 values (:new.id,:new.flg,:new.text,:new.d);
  when 11 then insert into login11 values (:new.id,:new.flg,:new.text,:new.d);
  when 12 then insert into login12 values (:new.id,:new.flg,:new.text,:new.d);
 end case;
end;
/

sequence作成

create SEQUENCE login_seq;
create SEQUENCE login_hikaku_seq;

動作検証

  • insert
↓を15万回
insert into login values (login_seq.nextval,1,dbms_random.string('U',200),sysdate)
↓を10万回
insert into login values (login_seq.nextval,1,dbms_random.string('U',200),TO_DATE('19500101','YYYYMMDD') + MOD(ABS(DBMS_RANDOM.RANDOM()),TO_DATE(TO_CHAR(CURRENT_DATE, 'YYYYMMDD'),'YYYYMMDD') - TO_DATE('19500101','YYYYMMDD')))
ちゃんと割り振られている
TABLE_NAME      NUM_ROWS
------------- ----------
LOGIN1              8583
LOGIN2              7700
LOGIN3              8641
LOGIN4              8296
LOGIN5              8549
LOGIN6              8224
LOGIN7            159088
LOGIN8              8402
LOGIN9              7988
LOGIN10             8439
LOGIN11             8248
LOGIN12             8442

比較用テーブルとの実行速度の違い

逐次新規接続での300件insert実施

通常テーブル
insert into login_hikaku values (login_hikaku_seq.nextval,1,dbms_random.string('U',200),sysdate)
トリガーあり
insert into login values (login_seq.nextval,1,dbms_random.string('U',200),sysdate)
回数 通常テーブル トリガーあり
一回目 0m9.516s 0m9.517s
二回目 0m9.512s 0m9.481s

300件なので大きな差はあまりなかった。逐次新規接続しているのでそちらのオーバヘッドのほうが多かったようだ。

一回に新規接続のみで50000件insert実施

通常テーブル
insert into login_hikaku values (login_hikaku_seq.nextval,1,dbms_random.string('U',200),sysdate)
トリガーあり
insert into login values (login_seq.nextval,1,dbms_random.string('U',200),sysdate)
回数 通常テーブル トリガーあり
一回目 2m4.671s 2m12.290s
二回目 2m8.502s 2m14.830s
三回目 2m9.822s 2m10.726s

トリガーありのほうが実行時間がかかっている。やはりオーバヘッドはあるのがわかる。

オーバヘッドについて

insert

SQL TRACEで確認する(比較用テーブルとの実行速度の違いの2番目のクエリを実行する)

通常テーブル
1. insert into login_hikaku values (login_hikaku_seq.nextval,1,dbms_random.string('U',200),sysdate)  
2. commit
トリガーあり
1. select text from view$ where rowid=:1  
2. insert into login values (login_seq.nextval,1,dbms_random.string('U',200),sysdate)  
3. INSERT INTO LOGIN7 VALUES (:B1 ,:B2 ,:B3 ,:B4 )  
4. commit  

通常テーブルよりも2つのクエリ(1,3)が多く実行されているのがわかる。
(viewを確認するのための再帰SQLとinstead ofトリガーで実行されたinsert文)

select

  • トリガーありの場合はunion allなのですべてのテーブルに検索してしまう。
select count(*) from login where d between to_date('2015/07/21 11:00:00','YYYY/MM/DD HH24:MI:SS') and to_date('2015/07/21 12:00:00','YYYY/MM/DD HH24:MI:SS') and flg =1;
-------------------------------------------------------------------------------------
| Id  | Operation               | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |             |     1 |    22 |   330   (2)| 00:00:04 |
|   1 |  SORT AGGREGATE         |             |     1 |    22 |            |          |
|   2 |   VIEW                  | LOGIN_VIEW  |   101K|  2189K|   330   (2)| 00:00:04 |
|   3 |    UNION-ALL            |             |       |       |            |          |
|*  4 |     INDEX RANGE SCAN    | IDX_LOGIN1  |     4 |    44 |     2   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN    | IDX_LOGIN2  |     4 |    44 |     2   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN    | IDX_LOGIN3  |     4 |    44 |     2   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN    | IDX_LOGIN4  |     4 |    44 |     2   (0)| 00:00:01 |
|*  8 |     INDEX RANGE SCAN    | IDX_LOGIN5  |     4 |    44 |     2   (0)| 00:00:01 |
|*  9 |     INDEX RANGE SCAN    | IDX_LOGIN6  |     4 |    44 |     2   (0)| 00:00:01 |
|* 10 |     INDEX FAST FULL SCAN| IDX_LOGIN7  |   101K|  1094K|   308   (2)| 00:00:04 |
|* 11 |     INDEX RANGE SCAN    | IDX_LOGIN8  |     4 |    44 |     2   (0)| 00:00:01 |
|* 12 |     INDEX RANGE SCAN    | IDX_LOGIN9  |     4 |    44 |     2   (0)| 00:00:01 |
|* 13 |     INDEX RANGE SCAN    | IDX_LOGIN10 |     4 |    44 |     2   (0)| 00:00:01 |
|* 14 |     INDEX RANGE SCAN    | IDX_LOGIN11 |     4 |    44 |     2   (0)| 00:00:01 |
|* 15 |     INDEX RANGE SCAN    | IDX_LOGIN12 |     4 |    44 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

的確にインデックスを張っていればすべてのテーブル検索してしまうが、viewの述語pushでそれぞれにインデックスが効いてくれるようになる。

まとめ

  • oracle standard editionでもパーティションっぽいことはできる。
  • 多少のオーバヘッドはあるがOLTP環境では許容の範囲内だと思う。