Results 1 to 4 of 4

Thread: Insert Hints

  1. #1
    Join Date
    Jan 2006

    Question Unanswered: Insert Hints

    i like to insert some Data into my tablespace without generate redo log or archive log. The databse is in archive mode. On the web i found the following

    c - APPEND into tables - By using the APPEND hint, you ensure that Oracle always grabs "fresh" data blocks by raising the high-water-mark for the table. If you are doing parallel insert DML, the Append mode is the default and you don't need to specify an APPEND hint. Mark Bobak notes "Also, if you're going w/ APPEND, consider putting the table into NOLOGGING mode, which will allow Oracle to avoid almost all redo logging."

    insert /*+ append */ into customer values ('hello',';there');
    insert /*+ append */ into customer values ('hello',';there');

    is not valid. APPEND only works with Insert into table1 select * from table2;
    Is there any option to do a insert wihtout generate logs?
    --> insert /*+ ???? */ into customer values ('hello',';there');

  2. #2
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 5
    "Ordinary" INSERT statements (such as the one you need) always generates redo, no matter if the underlying table is specified with nologging or not. "+ APPEND" hint works with a direct load (in SQL*Loader) and a direct load insert. Redo can not be totally eliminated because direct load inserts generate undo which, in turn, generates redo.

    Nologging can be achieved on tablespace level (which has no effect if the database is in force logging mode, so you'd need to alter both database AND tablespace).

  3. #3
    Join Date
    Oct 2002
    Except in few (very few) circumstances, not logging redo is a pretty bad idea.

  4. #4
    Join Date
    May 2004
    Dominican Republic
    I wonder why would you want to do this for row-by-row inserts anyways ?? !! ?? They will, no matter in what mode the table is, generelate undo and therefore redo (especially if you're commiting after each one; whereas if you're not, lots of undo).

    The second quote is NOT valid.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts