-
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?
-
Yes oracle would still generate basic logs.
Sam
Thanx
Sam
Life is a journey, not a destination!
-
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
-
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.
-
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.
Code:
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|