? on nologging

[thomgreen]

I have used the nologging option on particular tables that have a large amount of inserts. I am still getting archive logs roughly every 2 minutes from the insert jobs. There are no indexes on these tables.

Any ideas or advice?

[sambavan]

Yes oracle would still generate basic logs.

Sam

[ales]

You cannot avoid logging for ordinary inserts regardless of logging/nologging state of a table. To minimize amount of redo you have to set table nologging and use direct-load insert: INSERT /*+ APPEND */ INTO my_table SELECT ... FROM ...
Direct-load insert doesn't insert into existing extents, it always creates a new one and fills it with inserted data, hence APPEND. That implies that using INSERT /*+ APPEND */ INTO my_table VALUES (...) creates a new extent for each inserted row. I don't think it's a good idea to do that .
Ales

[thomgreen]

Ales,

Thanks for the info. Friday I was looking more into the issue and found that the APPEND hint is a solution. Although I also read that my database must be running in serial mode. We are using Enterprise Edition which makes me believe that i am not in serial mode. I decided to try the hint anyway and it did not work - archive logs are still generated.

I am going to look up 'serial mode' and try and figure out if I can change mode on EE and if I really should. Do you have any further suggestions or helpfull information.

[ales]

Originally posted by thomgreen
Although I also read that my database must be running in serial mode.

I've never heard about serial mode of the Oracle database.

You can use direct-load insert in serial or parallel mode. Parallel means that the execution of insert is performed by more servers thus performs better. Parallel works only in EE.
Both serial and parallel direct-load inserts don't generate redo/undo.

Conventional INSERT:

scott@oracle> create table o as select * from all_objects where 1=0;

Table created.

scott@oracle> @rs

NAME                      VALUE
-------------------- ----------
redo size                 59440

scott@oracle> insert into o select * from all_objects;

13472 rows created.

scott@oracle> @rs

NAME                      VALUE
-------------------- ----------
redo size               1896536

redo generated: 1,75 MB

scott@oracle> drop table o;

Table dropped.

Direct-load INSERT:

scott@oracle>  create table o NOLOGGING as select * from all_objects where 1=0;

Table created.

scott@oracle> @rs

NAME                      VALUE
-------------------- ----------
redo size               1979404

scott@oracle> insert /*+ APPEND */ into o select * from all_objects;

13472 rows created.

scott@oracle> @rs

NAME                      VALUE
-------------------- ----------
redo size               2019428

redo generated: 39kB

The right way could be:
1. drop all indexes and triggers on the table
2. ALTER TABLE the_table NOLOGGING
3. INSERT /*+ APPEND */ INTO the_table SELECT ...
4. COMMIT
5. ALTER TABLE the_table LOGGING
7. recreate triggers and indexes (possible in nologging as well)
8. backup database

Direct-load insert is suitable for irregular, one-shot operations as copying data from production to test database since subsequent backup is a must.
For regular inserts in a production database better use conventional insert because of possible recovery.
In a production database, redo makes sense.

Ales