Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Question about Append hint in Insert

Re: Question about Append hint in Insert

From: Jonathan Lewis <>
Date: Fri, 18 Jun 2004 22:32:52 +0100
Message-ID: <00ff01c4557b$cfb74790$7102a8c0@Primary>


APPEND stops table UNDO, not table REDO. That point often gets hidden because the hint is used so often in conjunction with nologging tables.

Otherwise your comments are correct - the benefit applies only to the table segment, and you get the exclusive table lock.

There is some potential for benefit on the indexes because the index entries are per-sorted before inserting, and there is an optimization that reduces undo and redo if multiple rows goes into a single leaf block.

There is some downside on the table because the HWM is bumped for the append, so you could leave a trail of empty space behind you as you do lots of appends - but at 1,000 rows per insert, the percentage waste of space is likely to be small.


Jonathan Lewis The Co-operative Oracle Users' FAQ Optimising Oracle Seminar - schedule updated May 1st


1.) Should be no real disadvantage, other than an exclusive lock on the table. More on that later.
2.) Hint will only apply to table. Any index updates that result from table load will always log. If you want to, you could alter index ... unusable, then do the load, then alter index ... rebuild nologging; 3.) The problem here is that an INSERT /*+ APPEND */ takes an *exclusive* lock on the table. This prevents any other DML from running on the table till that transaction commits. The second process will wait on the exclusive mode TM enqueue that the first process is holding.

Hope that helps,


Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"Post Hoc Ergo Propter Hoc"

-----Original Message-----

[] On Behalf Of Harvinder Singh Sent: Friday, June 18, 2004 2:43 PM
Subject: Question about Append hint in Insert


We are testing insert performance in one of out tables and we are selecting from 1 table 1000 rows at a time and inserting in 2nd table. So we were getting tps of about 17000 for 1 million records. Then I added append hint to insert and tps went up to 23000. Now if I a try to insert from 2 clients to increase scalibility both insert at tps of 11000. so I have 2 questions:
1) What is the disadvantage of adding append hint during insert (we never delete or update this table in production) 2) Will the hint applicable to all the indexes or only table 3) Why with 2 clients using append result in less tps than normal 2 client inserts?



Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.

Archives are at FAQ is at
Received on Fri Jun 18 2004 - 16:29:35 CDT

Original text of this message