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分割したテーブルに割り振るようにすると実現可能である。
図にすると以下のような感じ。
登場人物としては以下
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でそれぞれにインデックスが効いてくれるようになる。