? on nologging
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: ? on nologging

  1. #1
    Join Date
    Jan 2001
    Posts
    66
    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?

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Yes oracle would still generate basic logs.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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


  4. #4
    Join Date
    Jan 2001
    Posts
    66
    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.

  5. #5
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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
  •  



Click Here to Expand Forum to Full Width