Re: Hard disk configuration for fast write (Insert)

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 16 Nov 2006 10:24:10 -0800
Message-ID: <1163701450.291280.78330@m7g2000cwm.googlegroups.com>

Steve Howard wrote:
> Charles Hooper wrote:
> > The NOLOGGING clause applies to the initial creation of the objects
> > only, not any DML that may take place on the tables, indexes, etc.
> > after creation.
> > http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:9549144668497
> >
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.
>
> It also applies to INSERTs when the rows are SELECTed from another
> object...
>
> SQL> create table t1116(c number) nologging;
>
> Table created.
>
> SQL> begin
> 2 for i in 1..100000 loop
> 3 insert /*+ append */ into t1116 values(i);
> 4 end loop;
> 5 end;
> 6 /
>
> PL/SQL procedure successfully completed.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> select value from v$mystat where statistic#=(select statistic#
> from v$statname where name = 'redo size')
> 2 /
>
> VALUE
> ----------
> 24503040
>
> SQL> insert /*+ append */ into t1116 select * from t1116;
>
> 100000 rows created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> select value from v$mystat where statistic#=(select statistic#
> from v$statname where name = 'redo size')
> 2 /
>
> VALUE
> ----------
> 24514412
>
> SQL>
>
> Regards,
>
> Steve

Wait a minute Steve, you aren't supported to use syntax that I am not familar with. :-) The append hint is briefly mentioned in Tom Kyte's "Expert Oracle Database Architecture", but no example is provided. Thanks for bringing this to my attention.

A little test to create a table and populate the table, and the amount of redo generated (note that dropped tables are being added to the Recycle Bin, so the number of rows will increase by 1 each time).

TEST 1:
select value from v$mystat where statistic#=(select statistic# from v$statname where name = 'redo size');

     VALUE

---

         0

CREATE TABLE T2 AS
SELECT
  *
FROM
  DBA_OBJECTS
WHERE
  1=0;

TABLE CREATED select value from v$mystat where statistic#=(select statistic# from v$statname where name = 'redo size');

     VALUE

---

     19292

INSERT INTO T2
SELECT
  *
FROM
  DBA_OBJECTS
WHERE
  ROWNUM<100000;

14,356 ROWS INSERTED select value from v$mystat where statistic#=(select statistic# from v$statname where name = 'redo size');

     VALUE

---

   1563164

---

TEST 2:
select value from v$mystat where statistic#=(select statistic# from v$statname where name = 'redo size');

     VALUE

---

         0

CREATE TABLE T2 NOLOGGING AS
SELECT
  *
FROM
  DBA_OBJECTS
WHERE
  1=0;

TABLE CREATED select value from v$mystat where statistic#=(select statistic# from v$statname where name = 'redo size');

     VALUE

---

     19324

INSERT INTO T2
SELECT
  *
FROM
  DBA_OBJECTS
WHERE
  ROWNUM<100000;

14,357 ROWS INSERTED select value from v$mystat where statistic#=(select statistic# from v$statname where name = 'redo size');

     VALUE

---

   1563296

---

TEST 3:
select value from v$mystat where statistic#=(select statistic# from v$statname where name = 'redo size');

     VALUE

---

         0

CREATE TABLE T2 NOLOGGING AS
SELECT
  *
FROM
  DBA_OBJECTS
WHERE
  1=0;

TABLE CREATED select value from v$mystat where statistic#=(select statistic# from v$statname where name = 'redo size');

     VALUE

---

     19432

INSERT /*+ append */ INTO T2
SELECT
  *
FROM
  DBA_OBJECTS
WHERE
  ROWNUM<100000;

14,358 ROWS INSERTED select value from v$mystat where statistic#=(select statistic# from v$statname where name = 'redo size');

     VALUE

---

     63316

---

TEST 4:
select value from v$mystat where statistic#=(select statistic# from v$statname where name = 'redo size');

     VALUE

---

         0

CREATE TABLE T2 NOLOGGING AS
SELECT
  *
FROM
  DBA_OBJECTS
WHERE
  ROWNUM<100000;

TABLE CREATED select value from v$mystat where statistic#=(select statistic# from v$statname where name = 'redo size');

     VALUE

---

     68716

---

TEST 5:
select value from v$mystat where statistic#=(select statistic# from v$statname where name = 'redo size');

     VALUE

---

         0

CREATE TABLE T2 AS
SELECT
  *
FROM
  DBA_OBJECTS
WHERE
  ROWNUM<100000;

select value from v$mystat where statistic#=(select statistic# from v$statname where name = 'redo size');

     VALUE

---

     66276

---

Based on the above tests, the following would generate the least amount of redo to create a table and add rows to the table: CREATE TABLE T2 AS
SELECT
  *
FROM
  DBA_OBJECTS
WHERE
  ROWNUM<100000;

The above tests were not intended as a way to understand what is happening in the system, and should not be seen as supporting or refuting what was stated previously.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Nov 16 2006 - 12:24:10 CST