LoginSignup
3
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@ora_gonsuke777in日本オラクル株式会社

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

Last updated at Posted at 2016-04-20

タイトルでおおよそ語ってしまったんですが、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 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';

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

-- 検証結果ログ
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>
3
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?