Which nologging way is better for performance?
I want to use a procedure P to insert the data into table T repeatedly.
The application does not care if the data is lost or not after the data is inserted into the table because all the data is temp data. So I choose nologging fashion to insert data. Which way is beter for performance?
1. Using nologging command in in the definition of table T .
2. Using nologging command in the procedure P.(or /*+ append */ hint. )
3. using direct insert.

It is a spacial application and it don't care the data lost.
Another question: Is the indexes of this table updated correspondingly using these nologging insert methods?

What exactly do you mean by this?

If the application uses ONLY TEMP DATA, you should define the table as a temporary table to effectively achieve your point while minimizing side steps.

I think that's just what Justin asked/explained up there.

NO, we should keep the table definition and the table structure. This table is only a Transit point.

Thank you ... that good question

TABLE=LOGGING
SQL> create table TEST01 as select * from all_objects;

Table created.

INDEX=NOLOGGING

SQL> create index TEST01_IDX01 on TEST01(object_id) nologging;

Index created.

INSERT INTO TEST01 NOLOGGING SELECT * FROM ALL_OBJECTS;

SQL> INSERT INTO TEST01 NOLOGGING SELECT * FROM ALL_OBJECTS;

10914 rows created.

REDO_VALUE
----------
1931708

INSERT /*+ APPEND */ INTO TEST01 SELECT * FROM ALL_OBJECTS;

SQL> INSERT /*+ APPEND */ INTO TEST01 SELECT * FROM ALL_OBJECTS;

10914 rows created.

REDO_VALUE
----------
2099212

INSERT /*+ APPEND */ INTO TEST01 NOLOGGING SELECT * FROM ALL_OBJECTS;

SQL> INSERT /*+ APPEND */ INTO TEST01 NOLOGGING SELECT * FROM ALL_OBJECTS;

10914 rows created.

REDO_VALUE
----------
1616992

After test: "INSERT /*+ APPEND */ INTO TEST01" made least redo size on INDEX(nologging)

By the way, I test with INDEX(logging), "INSERT /*+ APPEND */ INTO TEST01" made least redo size as well.

I think... that'a little difference redo size. But if you "alter table ... nologging" and use SQL with /*+ append*/ that'll not generate redo log(generate only data dictionary).... Be CareFul to use NOLOGGING

If you need to check about TABLE http://surachartopun.com/2009/06/insertupdatedelete-will-generate-redo.html

Edited by: Surachart Opun (HunterX) on Jul 9, 2009 8:42 AM

Edited by: Surachart Opun (HunterX) on Jul 9, 2009 8:47 AM

Edited by: Surachart Opun (HunterX) on Jul 9, 2009 10:01 AM

thank you ...SomeoneElse

about append hint OTN forums change /*+ APPEND / to / APPEND */
when i use + xxx +

And to make things even more complicated: Even when using global temporary tables (GTT) there is still the need to differentiate between REDO and UNDO generation:

1. A GTT doesn't generate REDO for the data written to the GTT

2. But it DOES generate UNDO for the data written to the GTT, and for the UNDO data REDO will be generated

3. The UNDO generation (and its accompanying REDO generation) for the data can be avoided by using a direct-path insert into the GTT (INSERT /*+ APPEND */)

4. Any indexes on the GTT will always generate REDO for the index maintenance, UNDO and REDO for the UNDO, regardless of the insert mode used.

To the OP: There is no such thing like "NOLOGGING command in the procedure". NOLOGGING is an attribute of an object and as already mentioned only applies to very specific operations. It's about REDO generation, not UNDO.

All conventional DML operations (insert/update/delete) generate REDO and UNDO, regardless of the NOLOGGING attribute of the object. Only direct-path inserts and certain DDL commands make use of the NOLOGGING attribute.

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/