Insert without redo logs or RBS???
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Insert without redo logs or RBS???

  1. #1
    Join Date
    Oct 2000
    Posts
    26
    Is this possible? I am doind a huge data transfer from Table_1 to Table_2. Is it possible to do this without it being logged or being written to the RBS for a speed increase?

    John

  2. #2
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    RBS will be used in either case. Alter the target table to NOLOGGING, and this will not generate any redo during the inserts. When done, change it back to LOGGING. Commit regularly so that the RBS won't grow too big.

  3. #3
    Join Date
    Oct 2000
    Posts
    26
    i issued ALTER TABLE TABLENMAE NOLOGGING

    then i started the inseret, but the redo writer is still being accessed. i am the only one in the database.

    Is it active beacuse the RBS is being written to? or do I have to issue another command?

    thanks man, you are very helpful

    John

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    RBS will be used regardless of if you do periodic commit in NOLOGGING mode or not. RBS is used to present a consistent view of the data from the beginning of your transaction to the end.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Oct 2000
    Posts
    26
    But is RBS causing the log writer to stay active or am i still utilizing the log files also?

    ythansk jophn

  6. #6
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    I think its because of the RBS. Check to see if there are any frequent log switches over say 30 min to 1hr while the inserts are taking place. You are not quite using the redo log files in actual terms. Don't worry about LGWR being active.

    [Edited by Halo on 11-21-2000 at 06:24 PM]

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Yes, changes to the RBS tablespace are just like any other (more or less). They are logged to your redo log files.

    By dedicating a couple of devices to your redo logs, you can minimize the impact of LGWR. If your LGWR is spinning all the time, you may want to check to see if LGWR is checkpointing too much (Checkpoint not complete message in alert.log) or you may need to tune your redo activity.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you can only use nologging option if you do direct inserts

    first alter the table to nologging attribute
    then

    insert /*+ append */ into xxxx
    select * from yyyy;

    This wont generate redo log
    direct insert is insert.... select....
    there is no way you can avoid logging in a normal insertion, only in a direct insert.
    And there is no way you can avoid using RBS... as far as I know.

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Just to add some note, if you do direct inserts you will start inserting after High Water Mark because of APPEND hint, however APPEND is required for direct inserts. This will work better if you are doing it to a temporary truncated table

  10. #10
    Join Date
    Nov 2000
    Posts
    25
    Hi

    With nologging we cannot eliminate redo completely . we can only reduce it . i tested it on some tables and still found redo creation . What is the problem here? Any idea????


    Santosh

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