Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Location
    New Delhi
    Posts
    7

    Exclamation Unanswered: Append hint in the insert

    I am using the append hint with an insert statement which inserts bunch of records into a table say A.Now if the parallel clause in the table creation script of table A is set to no parallel, will i be able to get the benefit of performance using the append insert? Won't it be the normal insert as the table i am intending to insert is not having its parallel clause set to parallel?
    Please answer it fast as i am stuck.
    Thanks and Regards,
    Sharad

  2. #2
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    Insert /*+APPEND*/ causes Oracle to post the new data above the highwater mark. It will be faster than typical insert, but may lead to wasted space.

    As a side note your HWM is moved even higher than the artificially high value it had earlier. Spend a few hours reading up on HWM for more info on the pros/cons of that.
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

  3. #3
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    Now if the parallel clause in the table creation script of table A is set to no parallel, will i be able to get the benefit of performance using the append insert? Won't it be the normal insert as the table i am intending to insert is not having its parallel clause set to parallel?
    No, you will get the benefits of APPEND hint even w/o the PARALLEL clause on the table.

    The APPEND hint has these two important points:

    It does not generate redo log information. So the only time you should use it is when you can afford to loose the data in the table. Once your next hot or cold is taken than you off course are no longer at risk.

    Also you need to be aware that when you use the append hint the data is inserted beginning at the high water mark of the table. So you have just deleted a bunch of data those blocks will not be reused. If the tables was just truncated than it will start loading at the beginning.


    Hope that helps,

    clio_usa - 8/8i/9i OCP DBA

Posting Permissions

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