NOLOGGING で ダイレクト・ロードしても 索引有無 や 索引付与の順序 で REDO生成量が異なると云う話
タイトルでおおよそ語ってしまったんですが、NOLOGGING属性 の 表 に
ダイレクト・ロード で データ を ローディングしても、
索引有無 や 索引付与の順序 で REDO生成量は異なるんやで彡(ﾟ)(ﾟ)
下記のサンプル表＆INSERT〜SELECT〜文で、Appendヒントや索引有無による
REDO生成量の違いを確認してみるやで。
CREATE TABLE TBL_A ( C1 NUMBER ) NOLOGGING; INSERT INTO TBL_A SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 100000; COMMIT;
結果は以下の通り、予想以上に全然違うね(ﾟεﾟ )
| ケース | REDO生成量 | |
|---|---|---|
| Case1. 索引無し・Append無し | 1,597,840バイト | |
| Case2. 索引無し・Append有り | 33,588バイト | |
| Case3. 索引有り・Append無し | 7,295,152バイト | |
| Case4. 索引有り・Append有り | 5,778,116バイト | |
| Case5. 索引を後から作成 | 83,040バイト | |
| Case6. CTASで索引を後作成 ※追記 | 91,320バイト |
検証用のスクリプトは↓に置きますた。途中とちゅうでCONNECTし直してるのは、
V$MYSTAT の統計値をクリアするためです(｀･ω･)ゞ
--検証用スクリプト SET ECHO ON; SET TIME ON; SET TIMING ON; -- Initialize DROP TABLE TBL_A PURGE; CREATE TABLE TBL_A ( C1 NUMBER ) NOLOGGING; ---------------------------------------- -- Case1. No-Append, No-Index ---------------------------------------- CONNECT xxxxxxxx/xxxxxxxx SET LINESIZE 300; SELECT M.SID, N.NAME, M.VALUE FROM V$MYSTAT M, V$STATNAME N WHERE M.STATISTIC# = N.STATISTIC# AND N.NAME LIKE 'redo size'; INSERT INTO TBL_A SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 100000; COMMIT; SET LINESIZE 300; SELECT M.SID, N.NAME, M.VALUE FROM V$MYSTAT M, V$STATNAME N WHERE M.STATISTIC# = N.STATISTIC# AND N.NAME LIKE 'redo size'; TRUNCATE TABLE TBL_A; ---------------------------------------- -- Case2. Append, No-Index ---------------------------------------- CONNECT xxxxxxxx/xxxxxxxx SET LINESIZE 300; SELECT M.SID, N.NAME, M.VALUE FROM V$MYSTAT M, V$STATNAME N WHERE M.STATISTIC# = N.STATISTIC# AND N.NAME LIKE 'redo size'; INSERT /*+ APPEND */ INTO TBL_A SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 100000; COMMIT; SET LINESIZE 300; SELECT M.SID, N.NAME, M.VALUE FROM V$MYSTAT M, V$STATNAME N WHERE M.STATISTIC# = N.STATISTIC# AND N.NAME LIKE 'redo size'; TRUNCATE TABLE TBL_A; -- Add No-Logging Index ALTER TABLE TBL_A ADD CONSTRAINT TBL_A_PK PRIMARY KEY (C1) USING INDEX NOLOGGING; ---------------------------------------- -- Case3. No-Append, Index ---------------------------------------- CONNECT xxxxxxxx/xxxxxxxx SET LINESIZE 300; SELECT M.SID, N.NAME, M.VALUE FROM V$MYSTAT M, V$STATNAME N WHERE M.STATISTIC# = N.STATISTIC# AND N.NAME LIKE 'redo size'; INSERT INTO TBL_A SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 100000; COMMIT; SET LINESIZE 300; SELECT M.SID, N.NAME, M.VALUE FROM V$MYSTAT M, V$STATNAME N WHERE M.STATISTIC# = N.STATISTIC# AND N.NAME LIKE 'redo size'; TRUNCATE TABLE TBL_A; ---------------------------------------- -- Case4. Append, Index ---------------------------------------- CONNECT xxxxxxxx/xxxxxxxx SET LINESIZE 300; SELECT M.SID, N.NAME, M.VALUE FROM V$MYSTAT M, V$STATNAME N WHERE M.STATISTIC# = N.STATISTIC# AND N.NAME LIKE 'redo size'; INSERT /*+ APPEND */ INTO TBL_A SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 100000; COMMIT; SET LINESIZE 300; SELECT M.SID, N.NAME, M.VALUE FROM V$MYSTAT M, V$STATNAME N WHERE M.STATISTIC# = N.STATISTIC# AND N.NAME LIKE 'redo size'; TRUNCATE TABLE TBL_A; -- Drop No-Logging Index ALTER TABLE TBL_A DROP CONSTRAINT TBL_A_PK; ---------------------------------------- -- Case5. Append, After Create Index ---------------------------------------- CONNECT xxxxxxxx/xxxxxxxx SET LINESIZE 300; SELECT M.SID, N.NAME, M.VALUE FROM V$MYSTAT M, V$STATNAME N WHERE M.STATISTIC# = N.STATISTIC# AND N.NAME LIKE 'redo size'; INSERT /*+ APPEND */ INTO TBL_A SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 100000; COMMIT; SET LINESIZE 300; SELECT M.SID, N.NAME, M.VALUE FROM V$MYSTAT M, V$STATNAME N WHERE M.STATISTIC# = N.STATISTIC# AND N.NAME LIKE 'redo size'; -- After Add No-Logging Index ALTER TABLE TBL_A ADD CONSTRAINT TBL_A_PK PRIMARY KEY (C1) USING INDEX NOLOGGING; SET LINESIZE 300; SELECT M.SID, N.NAME, M.VALUE FROM V$MYSTAT M, V$STATNAME N WHERE M.STATISTIC# = N.STATISTIC# AND N.NAME LIKE 'redo size'; TRUNCATE TABLE TBL_A; -- ※CTASのケースを追記 DROP TABLE TBL_A PURGE; ---------------------------------------- -- Case6. CTAS, After Create Index ---------------------------------------- CONNECT xxxxxxxx/xxxxxxxx SET LINESIZE 300; SELECT M.SID, N.NAME, M.VALUE FROM V$MYSTAT M, V$STATNAME N WHERE M.STATISTIC# = N.STATISTIC# AND N.NAME LIKE 'redo size'; CREATE TABLE TBL_A NOLOGGING AS SELECT LEVEL AS C1 FROM DUAL CONNECT BY LEVEL <= 100000; SET LINESIZE 300; SELECT M.SID, N.NAME, M.VALUE FROM V$MYSTAT M, V$STATNAME N WHERE M.STATISTIC# = N.STATISTIC# AND N.NAME LIKE 'redo size'; -- After Add No-Logging Index ALTER TABLE TBL_A ADD CONSTRAINT TBL_A_PK PRIMARY KEY (C1) USING INDEX NOLOGGING; SET LINESIZE 300; SELECT M.SID, N.NAME, M.VALUE FROM V$MYSTAT M, V$STATNAME N WHERE M.STATISTIC# = N.STATISTIC# AND N.NAME LIKE 'redo size';
検証結果ログは↓の通りです。
-- 検証結果ログ 17:58:56 SQL> @./nologging_test.sql 17:59:05 SQL> SET ECHO ON; 17:59:05 SQL> SET TIME ON; 17:59:05 SQL> SET TIMING ON; 17:59:05 SQL> 17:59:05 SQL> -- Initialize 17:59:05 SQL> DROP TABLE TBL_A PURGE; Table dropped. Elapsed: 00:00:00.03 17:59:05 SQL> CREATE TABLE TBL_A ( 17:59:05 2 C1 NUMBER 17:59:05 3 ) NOLOGGING; Table created. Elapsed: 00:00:00.01 17:59:05 SQL> 17:59:05 SQL> 17:59:05 SQL> ---------------------------------------- 17:59:05 SQL> -- Case1. No-Append, No-Index 17:59:05 SQL> ---------------------------------------- 17:59:05 SQL> CONNECT xxxxxxxx/xxxxxxxx Connected. 17:59:05 SQL> SET LINESIZE 300; 17:59:05 SQL> SELECT M.SID, N.NAME, M.VALUE 17:59:05 2 FROM V$MYSTAT M, 17:59:05 3 V$STATNAME N 17:59:05 4 WHERE M.STATISTIC# = N.STATISTIC# 17:59:05 5 AND N.NAME LIKE 'redo size'; SID NAME VALUE ---------- ---------------------------------------------------------------- ---------- 63 redo size 0 Elapsed: 00:00:00.01 17:59:05 SQL> 17:59:05 SQL> INSERT INTO TBL_A SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 100000; 100000 rows created. Elapsed: 00:00:00.09 17:59:05 SQL> COMMIT; Commit complete. Elapsed: 00:00:00.01 17:59:05 SQL> 17:59:05 SQL> SET LINESIZE 300; 17:59:05 SQL> SELECT M.SID, N.NAME, M.VALUE 17:59:05 2 FROM V$MYSTAT M, 17:59:05 3 V$STATNAME N 17:59:05 4 WHERE M.STATISTIC# = N.STATISTIC# 17:59:05 5 AND N.NAME LIKE 'redo size'; SID NAME VALUE ---------- ---------------------------------------------------------------- ---------- 63 redo size 1597840 Elapsed: 00:00:00.00 17:59:05 SQL> 17:59:05 SQL> TRUNCATE TABLE TBL_A; Table truncated. Elapsed: 00:00:00.02 17:59:06 SQL> 17:59:06 SQL> 17:59:06 SQL> 17:59:06 SQL> ---------------------------------------- 17:59:06 SQL> -- Case2. Append, No-Index 17:59:06 SQL> ---------------------------------------- 17:59:06 SQL> CONNECT xxxxxxxx/xxxxxxxx Connected. 17:59:06 SQL> SET LINESIZE 300; 17:59:06 SQL> SELECT M.SID, N.NAME, M.VALUE 17:59:06 2 FROM V$MYSTAT M, 17:59:06 3 V$STATNAME N 17:59:06 4 WHERE M.STATISTIC# = N.STATISTIC# 17:59:06 5 AND N.NAME LIKE 'redo size'; SID NAME VALUE ---------- ---------------------------------------------------------------- ---------- 63 redo size 0 Elapsed: 00:00:00.00 17:59:06 SQL> 17:59:06 SQL> INSERT /*+ APPEND */ INTO TBL_A SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 100000; 100000 rows created. Elapsed: 00:00:00.08 17:59:06 SQL> COMMIT; Commit complete. Elapsed: 00:00:00.01 17:59:06 SQL> 17:59:06 SQL> SET LINESIZE 300; 17:59:06 SQL> SELECT M.SID, N.NAME, M.VALUE 17:59:06 2 FROM V$MYSTAT M, 17:59:06 3 V$STATNAME N 17:59:06 4 WHERE M.STATISTIC# = N.STATISTIC# 17:59:06 5 AND N.NAME LIKE 'redo size'; SID NAME VALUE ---------- ---------------------------------------------------------------- ---------- 63 redo size 33588 Elapsed: 00:00:00.00 17:59:06 SQL> 17:59:06 SQL> TRUNCATE TABLE TBL_A; Table truncated. Elapsed: 00:00:00.02 17:59:06 SQL> 17:59:06 SQL> -- Add No-Logging Index 17:59:06 SQL> ALTER TABLE TBL_A ADD CONSTRAINT TBL_A_PK PRIMARY KEY (C1) USING INDEX NOLOGGING; Table altered. Elapsed: 00:00:00.02 17:59:06 SQL> 17:59:06 SQL> 17:59:06 SQL> 17:59:06 SQL> ---------------------------------------- 17:59:06 SQL> -- Case3. No-Append, Index 17:59:06 SQL> ---------------------------------------- 17:59:06 SQL> CONNECT xxxxxxxx/xxxxxxxx Connected. 17:59:06 SQL> SET LINESIZE 300; 17:59:06 SQL> SELECT M.SID, N.NAME, M.VALUE 17:59:06 2 FROM V$MYSTAT M, 17:59:06 3 V$STATNAME N 17:59:06 4 WHERE M.STATISTIC# = N.STATISTIC# 17:59:06 5 AND N.NAME LIKE 'redo size'; SID NAME VALUE ---------- ---------------------------------------------------------------- ---------- 63 redo size 0 Elapsed: 00:00:00.00 17:59:06 SQL> 17:59:06 SQL> INSERT INTO TBL_A SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 100000; 100000 rows created. Elapsed: 00:00:00.24 17:59:06 SQL> COMMIT; Commit complete. Elapsed: 00:00:00.01 17:59:06 SQL> 17:59:06 SQL> SET LINESIZE 300; 17:59:06 SQL> SELECT M.SID, N.NAME, M.VALUE 17:59:06 2 FROM V$MYSTAT M, 17:59:06 3 V$STATNAME N 17:59:06 4 WHERE M.STATISTIC# = N.STATISTIC# 17:59:06 5 AND N.NAME LIKE 'redo size'; SID NAME VALUE ---------- ---------------------------------------------------------------- ---------- 63 redo size 7295152 Elapsed: 00:00:00.00 17:59:06 SQL> 17:59:06 SQL> TRUNCATE TABLE TBL_A; Table truncated. Elapsed: 00:00:00.12 17:59:06 SQL> 17:59:06 SQL> 17:59:06 SQL> 17:59:06 SQL> ---------------------------------------- 17:59:06 SQL> -- Case4. Append, Index 17:59:06 SQL> ---------------------------------------- 17:59:06 SQL> CONNECT xxxxxxxx/xxxxxxxx Connected. 17:59:06 SQL> SET LINESIZE 300; 17:59:06 SQL> SELECT M.SID, N.NAME, M.VALUE 17:59:06 2 FROM V$MYSTAT M, 17:59:06 3 V$STATNAME N 17:59:06 4 WHERE M.STATISTIC# = N.STATISTIC# 17:59:06 5 AND N.NAME LIKE 'redo size'; SID NAME VALUE ---------- ---------------------------------------------------------------- ---------- 63 redo size 0 Elapsed: 00:00:00.00 17:59:06 SQL> 17:59:06 SQL> INSERT /*+ APPEND */ INTO TBL_A SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 100000; 100000 rows created. Elapsed: 00:00:00.35 17:59:07 SQL> COMMIT; Commit complete. Elapsed: 00:00:00.00 17:59:07 SQL> 17:59:07 SQL> SET LINESIZE 300; 17:59:07 SQL> SELECT M.SID, N.NAME, M.VALUE 17:59:07 2 FROM V$MYSTAT M, 17:59:07 3 V$STATNAME N 17:59:07 4 WHERE M.STATISTIC# = N.STATISTIC# 17:59:07 5 AND N.NAME LIKE 'redo size'; SID NAME VALUE ---------- ---------------------------------------------------------------- ---------- 63 redo size 5778116 Elapsed: 00:00:00.00 17:59:07 SQL> 17:59:07 SQL> TRUNCATE TABLE TBL_A; Table truncated. Elapsed: 00:00:00.04 17:59:07 SQL> 17:59:07 SQL> -- Drop No-Logging Index 17:59:07 SQL> ALTER TABLE TBL_A DROP CONSTRAINT TBL_A_PK; Table altered. Elapsed: 00:00:00.02 17:59:07 SQL> 17:59:07 SQL> 17:59:07 SQL> 17:59:07 SQL> ---------------------------------------- 17:59:07 SQL> -- Case5. Append, After Create Index 17:59:07 SQL> ---------------------------------------- 17:59:07 SQL> CONNECT xxxxxxxx/xxxxxxxx Connected. 17:59:07 SQL> SET LINESIZE 300; 17:59:07 SQL> SELECT M.SID, N.NAME, M.VALUE 17:59:07 2 FROM V$MYSTAT M, 17:59:07 3 V$STATNAME N 17:59:07 4 WHERE M.STATISTIC# = N.STATISTIC# 17:59:07 5 AND N.NAME LIKE 'redo size'; SID NAME VALUE ---------- ---------------------------------------------------------------- ---------- 63 redo size 0 Elapsed: 00:00:00.00 17:59:07 SQL> 17:59:07 SQL> INSERT /*+ APPEND */ INTO TBL_A SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 100000; 100000 rows created. Elapsed: 00:00:00.09 17:59:07 SQL> COMMIT; Commit complete. Elapsed: 00:00:00.00 17:59:07 SQL> 17:59:07 SQL> SET LINESIZE 300; 17:59:07 SQL> SELECT M.SID, N.NAME, M.VALUE 17:59:07 2 FROM V$MYSTAT M, 17:59:07 3 V$STATNAME N 17:59:07 4 WHERE M.STATISTIC# = N.STATISTIC# 17:59:07 5 AND N.NAME LIKE 'redo size'; SID NAME VALUE ---------- ---------------------------------------------------------------- ---------- 63 redo size 33552 Elapsed: 00:00:00.00 17:59:07 SQL> 17:59:07 SQL> -- After Add No-Logging Index 17:59:07 SQL> ALTER TABLE TBL_A ADD CONSTRAINT TBL_A_PK PRIMARY KEY (C1) USING INDEX NOLOGGING; Table altered. Elapsed: 00:00:00.12 17:59:07 SQL> 17:59:07 SQL> SET LINESIZE 300; 17:59:07 SQL> SELECT M.SID, N.NAME, M.VALUE 17:59:07 2 FROM V$MYSTAT M, 17:59:07 3 V$STATNAME N 17:59:07 4 WHERE M.STATISTIC# = N.STATISTIC# 17:59:07 5 AND N.NAME LIKE 'redo size'; SID NAME VALUE ---------- ---------------------------------------------------------------- ---------- 63 redo size 83040 Elapsed: 00:00:00.00 17:59:07 SQL> 17:59:07 SQL> TRUNCATE TABLE TBL_A; Table truncated. Elapsed: 00:00:00.02 17:59:07 SQL> -- ※CTASのケースを追記 02:29:22 SQL> DROP TABLE TBL_A PURGE; Table dropped. 02:29:22 SQL> 02:29:22 SQL> ---------------------------------------- 02:29:22 SQL> -- Case6. CTAS, After Create Index 02:29:22 SQL> ---------------------------------------- 02:29:22 SQL> CONNECT AYSHIBAT/AYSHIBAT Connected. 02:29:22 SQL> SET LINESIZE 300; 02:29:22 SQL> SELECT M.SID, N.NAME, M.VALUE 02:29:22 2 FROM V$MYSTAT M, 02:29:22 3 V$STATNAME N 02:29:23 4 WHERE M.STATISTIC# = N.STATISTIC# 02:29:23 5 AND N.NAME LIKE 'redo size'; SID NAME VALUE ---------- ---------------------------------------------------------------- ---------- 63 redo size 0 02:29:23 SQL> 02:29:23 SQL> CREATE TABLE TBL_A NOLOGGING 02:29:23 2 AS SELECT LEVEL AS C1 FROM DUAL CONNECT BY LEVEL <= 100000; Table created. 02:29:23 SQL> 02:29:23 SQL> SET LINESIZE 300; 02:29:23 SQL> SELECT M.SID, N.NAME, M.VALUE 02:29:23 2 FROM V$MYSTAT M, 02:29:24 3 V$STATNAME N 02:29:24 4 WHERE M.STATISTIC# = N.STATISTIC# 02:29:24 5 AND N.NAME LIKE 'redo size'; SID NAME VALUE ---------- ---------------------------------------------------------------- ---------- 63 redo size 39956 02:29:24 SQL> 02:29:24 SQL> -- After Add No-Logging Index 02:29:24 SQL> ALTER TABLE TBL_A ADD CONSTRAINT TBL_A_PK PRIMARY KEY (C1) USING INDEX NOLOGGING; Table altered. 02:29:24 SQL> 02:29:24 SQL> SET LINESIZE 300; 02:29:24 SQL> SELECT M.SID, N.NAME, M.VALUE 02:29:25 2 FROM V$MYSTAT M, 02:29:25 3 V$STATNAME N 02:29:25 4 WHERE M.STATISTIC# = N.STATISTIC# 02:29:25 5 AND N.NAME LIKE 'redo size'; SID NAME VALUE ---------- ---------------------------------------------------------------- ---------- 63 redo size 91320 02:29:25 SQL>
Qiitaにも書いたやで彡(^)(^)
NOLOGGING で ダイレクト・ロードしても 索引有無 や 索引付与の順序 で REDO生成量が異なると云う話 http://qiita.com/ora_gonsuke777/items/b9218a6fcd71603242c2
