Help us understand the problem. What is going on with this article?

sql oracle index trigger

More than 1 year has passed since last update.

まえがき

アート・オブ・SQLのp48ページぐらいにindexとtriggerについてcrudの処理時間について言及されていました。triggerは書いたことなかったので、いい機会だとおもい、試してみたいと思った次第であります。

参考文献

[SQL] 14. トリガー (SQL 非標準) 2 | TECHSCORE(テックスコア)
http://www.techscore.com/tech/sql/SQL14/14_02.html/

トリガーの定義 (DML Event Trigger) - オラクル・Oracleをマスターするため ...
https://www.shift-the-oracle.com/trigger/dml-trigger.html

ORACLE/TRIGGER編 - オラクルちょこっとリファレンス
https://oracle-chokotto.com/ora_trigger.html

第40回 「トリガー処理のルール(してはいけないこと)」 | eラーニング ...
https://www.istudy.co.jp/blog/plsql/040

[Oracle]データベーストリガーの作成方法 | Skill Note
http://skill-note.net/post-579/

トリガーの使用 - Otn
http://otndnld.oracle.co.jp/document/products/oracle11g/111/doc_dvd/appdev.111/E05694-03/tdddg_triggers.htm

Grant create any trigger vs grant create trigger
https://stackoverflow.com/questions/49256959/grant-create-any-trigger-vs-grant-create-trigger

ORACLE/権限編 - オラクルちょこっとリファレンス
https://oracle-chokotto.com/ora_auth.html

測定パターン

100万件データにおける更新系処理の経過時間の比率をテーブルのみの場合を基準に算出していきたいと思います。

image.png

各測定パターンのsql

pat01.sql
DROP TABLE tes_tbl;
CREATE TABLE tes_tbl(
    col1 CHAR(8)
    ,col2 CHAR(8)
    ,col3 CHAR(8)
    ,col4 CHAR(8)
    ,col5 CHAR(8)
    ,col6 CHAR(8)
)
;

alter system checkpoint;
alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;

INSERT INTO tes_tbl
SELECT
    'k' || lpad(LEVEL,7,0) AS col1
    ,'r' || lpad(LEVEL,7,0) AS col2
    ,'a' || lpad(LEVEL,7,0) AS col3
    ,'i' || lpad(LEVEL,7,0) AS col4
    ,'n' || lpad(LEVEL,7,0) AS col5
    ,'e' || lpad(LEVEL,7,0) AS col6
FROM
    dual
CONNECT BY
    LEVEL <= 1000000
;
COMMIT;


pat02.sql
DROP TABLE tes_tbl;
CREATE TABLE tes_tbl(
    col1 CHAR(8)
    ,col2 CHAR(8)
    ,col3 CHAR(8)
    ,col4 CHAR(8)
    ,col5 CHAR(8)
    ,col6 CHAR(8)
)
;

ALTER TABLE tes_tbl ADD CONSTRAINT tes_tbl_pk PRIMARY KEY(col1);

alter system checkpoint;
alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;

INSERT INTO tes_tbl
SELECT
    'k' || lpad(LEVEL,7,0) AS col1
    ,'r' || lpad(LEVEL,7,0) AS col2
    ,'a' || lpad(LEVEL,7,0) AS col3
    ,'i' || lpad(LEVEL,7,0) AS col4
    ,'n' || lpad(LEVEL,7,0) AS col5
    ,'e' || lpad(LEVEL,7,0) AS col6
FROM
    dual
CONNECT BY
    LEVEL <= 1000000
;
COMMIT;


pat03.sql
DROP TABLE tes_tbl;
CREATE TABLE tes_tbl(
    col1 CHAR(8)
    ,col2 CHAR(8)
    ,col3 CHAR(8)
    ,col4 CHAR(8)
    ,col5 CHAR(8)
    ,col6 CHAR(8)
)
;

ALTER TABLE tes_tbl ADD CONSTRAINT tes_tbl_pk PRIMARY KEY(col1);
CREATE INDEX ind1 ON tes_tbl(col1,col2);

alter system checkpoint;
alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;

INSERT INTO tes_tbl
SELECT
    'k' || lpad(LEVEL,7,0) AS col1
    ,'r' || lpad(LEVEL,7,0) AS col2
    ,'a' || lpad(LEVEL,7,0) AS col3
    ,'i' || lpad(LEVEL,7,0) AS col4
    ,'n' || lpad(LEVEL,7,0) AS col5
    ,'e' || lpad(LEVEL,7,0) AS col6
FROM
    dual
CONNECT BY
    LEVEL <= 1000000
;
COMMIT;

pat04.sql
DROP TABLE tes_tbl;
CREATE TABLE tes_tbl(
    col1 CHAR(8)
    ,col2 CHAR(8)
    ,col3 CHAR(8)
    ,col4 CHAR(8)
    ,col5 CHAR(8)
    ,col6 CHAR(8)
)
;

ALTER TABLE tes_tbl ADD CONSTRAINT tes_tbl_pk PRIMARY KEY(col1);
CREATE INDEX ind1 ON tes_tbl(col1,col2);
CREATE INDEX ind2 ON tes_tbl(col1,col2,col3);

alter system checkpoint;
alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;

INSERT INTO tes_tbl
SELECT
    'k' || lpad(LEVEL,7,0) AS col1
    ,'r' || lpad(LEVEL,7,0) AS col2
    ,'a' || lpad(LEVEL,7,0) AS col3
    ,'i' || lpad(LEVEL,7,0) AS col4
    ,'n' || lpad(LEVEL,7,0) AS col5
    ,'e' || lpad(LEVEL,7,0) AS col6
FROM
    dual
CONNECT BY
    LEVEL <= 1000000
;
COMMIT;

pat05.sql
DROP TABLE tes_tbl;
CREATE TABLE tes_tbl(
    col1 CHAR(8)
    ,col2 CHAR(8)
    ,col3 CHAR(8)
    ,col4 CHAR(8)
    ,col5 CHAR(8)
    ,col6 CHAR(8)
)
;

ALTER TABLE tes_tbl ADD CONSTRAINT tes_tbl_pk PRIMARY KEY(col1);
CREATE INDEX ind1 ON tes_tbl(col1,col2);
CREATE INDEX ind2 ON tes_tbl(col1,col2,col3);
CREATE INDEX ind3 ON tes_tbl(col1,col2,col3,col4);

alter system checkpoint;
alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;

INSERT INTO tes_tbl
SELECT
    'k' || lpad(LEVEL,7,0) AS col1
    ,'r' || lpad(LEVEL,7,0) AS col2
    ,'a' || lpad(LEVEL,7,0) AS col3
    ,'i' || lpad(LEVEL,7,0) AS col4
    ,'n' || lpad(LEVEL,7,0) AS col5
    ,'e' || lpad(LEVEL,7,0) AS col6
FROM
    dual
CONNECT BY
    LEVEL <= 1000000
;
COMMIT;

pat06.sql
DROP TABLE tes_tbl;
CREATE TABLE tes_tbl(
    col1 CHAR(8)
    ,col2 CHAR(8)
    ,col3 CHAR(8)
    ,col4 CHAR(8)
    ,col5 CHAR(8)
    ,col6 CHAR(8)
)
;

ALTER TABLE tes_tbl ADD CONSTRAINT tes_tbl_pk PRIMARY KEY(col1);
CREATE INDEX ind1 ON tes_tbl(col1,col2);
CREATE INDEX ind2 ON tes_tbl(col1,col2,col3);
CREATE INDEX ind3 ON tes_tbl(col1,col2,col3,col4);
CREATE INDEX ind4 ON tes_tbl(col1,col2,col3,col4,col5);

alter system checkpoint;
alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;

INSERT INTO tes_tbl
SELECT
    'k' || lpad(LEVEL,7,0) AS col1
    ,'r' || lpad(LEVEL,7,0) AS col2
    ,'a' || lpad(LEVEL,7,0) AS col3
    ,'i' || lpad(LEVEL,7,0) AS col4
    ,'n' || lpad(LEVEL,7,0) AS col5
    ,'e' || lpad(LEVEL,7,0) AS col6
FROM
    dual
CONNECT BY
    LEVEL <= 1000000
;
COMMIT;

pat07.sql
DROP TABLE tes_tbl;
CREATE TABLE tes_tbl(
    col1 CHAR(8)
    ,col2 CHAR(8)
    ,col3 CHAR(8)
    ,col4 CHAR(8)
    ,col5 CHAR(8)
    ,col6 CHAR(8)
)
;

ALTER TABLE tes_tbl ADD CONSTRAINT tes_tbl_pk PRIMARY KEY(col1);
CREATE INDEX ind1 ON tes_tbl(col1,col2);
CREATE INDEX ind2 ON tes_tbl(col1,col2,col3);
CREATE INDEX ind3 ON tes_tbl(col1,col2,col3,col4);
CREATE INDEX ind4 ON tes_tbl(col1,col2,col3,col4,col5);
CREATE INDEX ind5 ON tes_tbl(col1,col2,col3,col4,col5,col6);

alter system checkpoint;
alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;

INSERT INTO tes_tbl
SELECT
    'k' || lpad(LEVEL,7,0) AS col1
    ,'r' || lpad(LEVEL,7,0) AS col2
    ,'a' || lpad(LEVEL,7,0) AS col3
    ,'i' || lpad(LEVEL,7,0) AS col4
    ,'n' || lpad(LEVEL,7,0) AS col5
    ,'e' || lpad(LEVEL,7,0) AS col6
FROM
    dual
CONNECT BY
    LEVEL <= 1000000
;
COMMIT;

pat08.sql
DROP TABLE tes_tbl;
CREATE TABLE tes_tbl(
    col1 CHAR(8)
    ,col2 CHAR(8)
    ,col3 CHAR(8)
    ,col4 CHAR(8)
    ,col5 CHAR(8)
    ,col6 CHAR(8)
)
;

ALTER TABLE tes_tbl ADD CONSTRAINT tes_tbl_pk PRIMARY KEY(col1);
CREATE UNIQUE INDEX ind1 ON tes_tbl(col1,col2);

alter system checkpoint;
alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;

INSERT INTO tes_tbl
SELECT
    'k' || lpad(LEVEL,7,0) AS col1
    ,'r' || lpad(LEVEL,7,0) AS col2
    ,'a' || lpad(LEVEL,7,0) AS col3
    ,'i' || lpad(LEVEL,7,0) AS col4
    ,'n' || lpad(LEVEL,7,0) AS col5
    ,'e' || lpad(LEVEL,7,0) AS col6
FROM
    dual
CONNECT BY
    LEVEL <= 1000000
;
COMMIT;

pat09.sql
DROP TABLE tes_tbl;
CREATE TABLE tes_tbl(
    col1 CHAR(8)
    ,col2 CHAR(8)
    ,col3 CHAR(8)
    ,col4 CHAR(8)
    ,col5 CHAR(8)
    ,col6 CHAR(8)
)
;

ALTER TABLE tes_tbl ADD CONSTRAINT tes_tbl_pk PRIMARY KEY(col1);
CREATE UNIQUE INDEX ind1 ON tes_tbl(col1,col2);
CREATE UNIQUE INDEX ind2 ON tes_tbl(col1,col2,col3);

alter system checkpoint;
alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;

INSERT INTO tes_tbl
SELECT
    'k' || lpad(LEVEL,7,0) AS col1
    ,'r' || lpad(LEVEL,7,0) AS col2
    ,'a' || lpad(LEVEL,7,0) AS col3
    ,'i' || lpad(LEVEL,7,0) AS col4
    ,'n' || lpad(LEVEL,7,0) AS col5
    ,'e' || lpad(LEVEL,7,0) AS col6
FROM
    dual
CONNECT BY
    LEVEL <= 1000000
;
COMMIT;

pat10.sql
DROP TABLE tes_tbl;
CREATE TABLE tes_tbl(
    col1 CHAR(8)
    ,col2 CHAR(8)
    ,col3 CHAR(8)
    ,col4 CHAR(8)
    ,col5 CHAR(8)
    ,col6 CHAR(8)
)
;

ALTER TABLE tes_tbl ADD CONSTRAINT tes_tbl_pk PRIMARY KEY(col1);
CREATE UNIQUE INDEX ind1 ON tes_tbl(col1,col2);
CREATE UNIQUE INDEX ind2 ON tes_tbl(col1,col2,col3);
CREATE UNIQUE INDEX ind3 ON tes_tbl(col1,col2,col3,col4);

alter system checkpoint;
alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;

INSERT INTO tes_tbl
SELECT
    'k' || lpad(LEVEL,7,0) AS col1
    ,'r' || lpad(LEVEL,7,0) AS col2
    ,'a' || lpad(LEVEL,7,0) AS col3
    ,'i' || lpad(LEVEL,7,0) AS col4
    ,'n' || lpad(LEVEL,7,0) AS col5
    ,'e' || lpad(LEVEL,7,0) AS col6
FROM
    dual
CONNECT BY
    LEVEL <= 1000000
;
COMMIT;

pat11.sql
DROP TABLE tes_tbl;
CREATE TABLE tes_tbl(
    col1 CHAR(8)
    ,col2 CHAR(8)
    ,col3 CHAR(8)
    ,col4 CHAR(8)
    ,col5 CHAR(8)
    ,col6 CHAR(8)
)
;

ALTER TABLE tes_tbl ADD CONSTRAINT tes_tbl_pk PRIMARY KEY(col1);
CREATE UNIQUE INDEX ind1 ON tes_tbl(col1,col2);
CREATE UNIQUE INDEX ind2 ON tes_tbl(col1,col2,col3);
CREATE UNIQUE INDEX ind3 ON tes_tbl(col1,col2,col3,col4);
CREATE UNIQUE INDEX ind4 ON tes_tbl(col1,col2,col3,col4,col5);

alter system checkpoint;
alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;

INSERT INTO tes_tbl
SELECT
    'k' || lpad(LEVEL,7,0) AS col1
    ,'r' || lpad(LEVEL,7,0) AS col2
    ,'a' || lpad(LEVEL,7,0) AS col3
    ,'i' || lpad(LEVEL,7,0) AS col4
    ,'n' || lpad(LEVEL,7,0) AS col5
    ,'e' || lpad(LEVEL,7,0) AS col6
FROM
    dual
CONNECT BY
    LEVEL <= 1000000
;
COMMIT;

pat12.sql
DROP TABLE tes_tbl;
CREATE TABLE tes_tbl(
    col1 CHAR(8)
    ,col2 CHAR(8)
    ,col3 CHAR(8)
    ,col4 CHAR(8)
    ,col5 CHAR(8)
    ,col6 CHAR(8)
)
;

ALTER TABLE tes_tbl ADD CONSTRAINT tes_tbl_pk PRIMARY KEY(col1);
CREATE UNIQUE INDEX ind1 ON tes_tbl(col1,col2);
CREATE UNIQUE INDEX ind2 ON tes_tbl(col1,col2,col3);
CREATE UNIQUE INDEX ind3 ON tes_tbl(col1,col2,col3,col4);
CREATE UNIQUE INDEX ind4 ON tes_tbl(col1,col2,col3,col4,col5);
CREATE UNIQUE INDEX ind5 ON tes_tbl(col1,col2,col3,col4,col5,col6);

alter system checkpoint;
alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;

INSERT INTO tes_tbl
SELECT
    'k' || lpad(LEVEL,7,0) AS col1
    ,'r' || lpad(LEVEL,7,0) AS col2
    ,'a' || lpad(LEVEL,7,0) AS col3
    ,'i' || lpad(LEVEL,7,0) AS col4
    ,'n' || lpad(LEVEL,7,0) AS col5
    ,'e' || lpad(LEVEL,7,0) AS col6
FROM
    dual
CONNECT BY
    LEVEL <= 1000000
;
COMMIT;

pat13.sql
DROP SEQUENCE tes_tbl_hist_seq;
CREATE SEQUENCE tes_tbl_hist_seq START WITH 1 INCREMENT BY 1;

DROP TABLE tes_tbl_hist;
CREATE TABLE tes_tbl_hist(
    seq NUMBER
    ,old_col1 CHAR(8)
    ,old_col2 CHAR(8)
    ,old_col3 CHAR(8)
    ,old_col4 CHAR(8)
    ,old_col5 CHAR(8)
    ,old_col6 CHAR(8)
    ,new_col1 CHAR(8)
    ,new_col2 CHAR(8)
    ,new_col3 CHAR(8)
    ,new_col4 CHAR(8)
    ,new_col5 CHAR(8)
    ,new_col6 CHAR(8)
    ,crud CHAR(6)
    ,upd_time CHAR(14)
)
;

DROP TABLE tes_tbl;
CREATE TABLE tes_tbl(
    col1 CHAR(8)
    ,col2 CHAR(8)
    ,col3 CHAR(8)
    ,col4 CHAR(8)
    ,col5 CHAR(8)
    ,col6 CHAR(8)
)
;

alter system checkpoint;
alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;

CREATE OR REPLACE TRIGGER trg_tes_tbl
AFTER--対象テーブル変更後
INSERT OR UPDATE OR DELETE ON tes_tbl--CUDのいずれかが発生時
FOR EACH ROW--行トリガー
BEGIN--▼plsqlかける
    CASE
        WHEN inserting THEN--insertの場合をinsertingで検知
            INSERT INTO tes_tbl_hist
            VALUES(
                    tes_tbl_hist_seq.nextval
                    ,NULL
                    ,NULL
                    ,NULL
                    ,NULL
                    ,NULL
                    ,NULL
                    ,:NEW.col1--:newは変更後の値を取得する予約語ぽい
                    ,:NEW.col2
                    ,:NEW.col3
                    ,:NEW.col4
                    ,:NEW.col5
                    ,:NEW.col6
                    ,'insert'
                    ,to_char(sysdate,'yyyymmddhh24miss')
                    )
            ;
        WHEN updating THEN--updateの場合をupdatingで検知
            INSERT INTO tes_tbl_hist
            VALUES(
                    tes_tbl_hist_seq.nextval
                    ,:OLD.col1--:oldは変更前の値を取得する予約語ぽい
                    ,:OLD.col2
                    ,:OLD.col3
                    ,:OLD.col4
                    ,:OLD.col5
                    ,:OLD.col6
                    ,:NEW.col1
                    ,:NEW.col2--:newは変更後の値を取得する予約語ぽい
                    ,:NEW.col3
                    ,:NEW.col4
                    ,:NEW.col5
                    ,:NEW.col6
                    ,'update'
                    ,to_char(sysdate,'yyyymmddhh24miss')
                    )
            ;
        WHEN deleting THEN--deleteの場合をdeletingで検知
            INSERT INTO tes_tbl_hist
            VALUES(
                    tes_tbl_hist_seq.nextval
                    ,:OLD.col1--:oldは変更前の値を取得する予約語ぽい
                    ,:OLD.col2
                    ,:OLD.col3
                    ,:OLD.col4
                    ,:OLD.col5
                    ,:OLD.col6
                    ,NULL
                    ,NULL
                    ,NULL
                    ,NULL
                    ,NULL
                    ,NULL
                    ,'delete'
                    ,to_char(sysdate,'yyyymmddhh24miss')
                    )
            ;
    END CASE;
END;--▲plsqlかける
/

ALTER TABLE tes_tbl ENABLE ALL TRIGGERS;--対象テーブルのトリガー有効にしちゃう

INSERT INTO tes_tbl
SELECT
    'k' || lpad(LEVEL,7,0) AS col1
    ,'r' || lpad(LEVEL,7,0) AS col2
    ,'a' || lpad(LEVEL,7,0) AS col3
    ,'i' || lpad(LEVEL,7,0) AS col4
    ,'n' || lpad(LEVEL,7,0) AS col5
    ,'e' || lpad(LEVEL,7,0) AS col6
FROM
    dual
CONNECT BY
    LEVEL <= 1000000
;
COMMIT;

pat14.sql
alter system checkpoint;
alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;

UPDATE tes_tbl
SET
    col1 = 'upd' || 'k' 
    ,col2 = 'upd' || 'r'
    ,col3 = 'upd' || 'a'
    ,col4 = 'upd' || 'i'
    ,col5 = 'upd' || 'n'
    ,col6 = 'upd' || 'e'
;
COMMIT;

pat15.sql
alter system checkpoint;
alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;

DELETE FROM tes_tbl;
COMMIT;

インデックス確認

conf_ind.sql
SELECT
    ui.index_name
    , ui.table_name
    , uc.constraint_type
    , ui.uniqueness
    , uic.column_name
    , uic.column_position
    , uic.descend
FROM
    user_indexes ui
        INNER JOIN user_ind_columns uic
            ON
                ui.index_name = uic.index_name
            AND ui.table_name = uic.table_name
        LEFT OUTER JOIN user_constraints uc
            ON
                ui.index_name = uc.CONSTRAINT_NAME
            AND ui.table_name = uc.table_name

WHERE
    ui.table_name = UPPER('tes_tbl')
;

image.png

image.png

トリガー使用準備

prepare_using_trigger.sql
Last login: Sun Sep 30 15:42:46 2018
[oracle@centos ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on  9 30 17:37:13 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.



Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
に接続されました。
SQL> grant create any trigger to kuraine;--次回からはcreate triggerを与えましょう。。

権限付与が成功しました。

経過: 00:00:00.03

SELECT TABLE_OWNER,TRIGGER_NAME, TRIGGER_TYPE, STATUS FROM user_triggers;
SELECT * FROM DBA_SYS_PRIVS WHERE grantee = 'KURAINE';

image.png

image.png

測定手順(一部抜粋)

こんなかんじ。

SQL> host pwd
/home/oracle/sql

SQL> host ls -l
合計 60
-rw-r--r--. 1 oracle oinstall  572 10月  2 20:33 pat01.sql
-rw-r--r--. 1 oracle oinstall  638 10月  2 20:31 pat02.sql
-rw-r--r--. 1 oracle oinstall  679 10月  2 20:34 pat03.sql
-rw-r--r--. 1 oracle oinstall  725 10月  2 20:34 pat04.sql
-rw-r--r--. 1 oracle oinstall  776 10月  2 20:32 pat05.sql
-rw-r--r--. 1 oracle oinstall  832 10月  2 20:33 pat06.sql
-rw-r--r--. 1 oracle oinstall  893 10月  2 20:33 pat07.sql
-rw-r--r--. 1 oracle oinstall  686 10月  2 20:36 pat08.sql
-rw-r--r--. 1 oracle oinstall  739 10月  2 20:35 pat09.sql
-rw-r--r--. 1 oracle oinstall  797 10月  2 20:36 pat10.sql
-rw-r--r--. 1 oracle oinstall  860 10月  2 20:36 pat11.sql
-rw-r--r--. 1 oracle oinstall  928 10月  2 20:37 pat12.sql
-rw-r--r--. 1 oracle oinstall 3530 10月  2 21:33 pat13.sql
-rw-r--r--. 1 oracle oinstall  298 10月  2 21:42 pat14.sql
-rw-r--r--. 1 oracle oinstall  148 10月  2 21:42 pat15.sql

SQL> @pat14.sql

システムが変更されました。

経過: 00:00:17.78

システムが変更されました。

経過: 00:00:00.03

システムが変更されました。

経過: 00:00:00.43

システムが変更されました。

経過: 00:00:00.75

1000000行が更新されました。

経過: 00:02:40.88

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

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |         |  1022K|    58M|  2227   (1)| 00:00:01 |
|   1 |  UPDATE            | TES_TBL |       |       |            |          |
|   2 |   TABLE ACCESS FULL| TES_TBL |  1022K|    58M|  2227   (1)| 00:00:01 |
------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


統計
----------------------------------------------------------
    1055717  recursive calls
    2353165  db block gets
     100825  consistent gets
       9100  physical reads
  827069232  redo size
       1193  bytes sent via SQL*Net to client
       1643  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
        435  sorts (memory)
          0  sorts (disk)
    1000000  rows processed


コミットが完了しました。

経過: 00:00:00.02


トリガーの更新結果

conf_trigger_result.sql
select 'insert' as crud, count(*) from tes_tbl_hist group by 'insert' union all
select 'update' as crud, count(*) from tes_tbl_hist group by 'update' union all
select 'delete' as crud, count(*) from tes_tbl_hist group by 'delete' ;

select * from tes_tbl_hist where crud = 'insert' and rownum <= 10 union all
select * from tes_tbl_hist where crud = 'update' and rownum <= 10 union all
select * from tes_tbl_hist where crud = 'delete' and rownum <= 10 ;


image.png

image.png

サマリ

image.png

image.png

あとがき

unique indexはunique checkが入る分、non unique indexより少し遅くなる印象でした。。
triggerはunique indexの5つ分よりも遅いっぽい。。
triggerはログテーブル・履歴管理用に仕込んでおくのが一般的らしいけど、大量データだと遅いなー。。

今回の測定回数は1回のみでしたけど、あとで何回も実行できるように、測定手順(更新系)をまとめられました。今回出てきた統計用語は次回まとめたいと思います。Excelのグラフにこんなかっこいいグラフあるとは知らなかった。。

以上、ありがとうございました。

ukijumotahaneniarukenia
sqlが大好き。(oracle) WordPressはじめました。sql oracleやそれ以外について調べたこととか記していきます。 https://github.com/ukijumotahaneniarukenia
https://ukijumotahaneniarukenia.site/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした