NOLOGGING で ダイレクト・ロードしても 索引有無 や 索引付与の順序 で REDO生成量が異なると云う話

タイトルでおおよそ語ってしまったんですが、NOLOGGING属性 の 表 に
ダイレクト・ロード で データ を ローディングしても、
索引有無 や 索引付与の順序 で REDO生成量は異なるんやで彡(ﾟ)(ﾟ)

下記のサンプル表＆INSERT～SELECT～文で、Appendヒントや索引有無による
REDO生成量の違いを確認してみるやで。

```sql
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 の統計値をクリアするためです(｀･ω･)ゞ

```sql
--検証用スクリプト
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 AYSHIBAT/AYSHIBAT
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';
```

検証結果ログは↓の通りです。

```sql
-- 検証結果ログ
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>
```