Oracle Scratchpad

March 8, 2019

Append hint

Filed under: Hints,Ignoring Hints,Infrastructure,Oracle — Jonathan Lewis @ 1:13 pm GMT Mar 8,2019

One of the questions that came up on the CBO Panel Session at the UKOUG Tech2018 conference was about the /*+ append */ hint – specifically how to make sure it was ignored when it came from a 3rd party tool that was used to load data into the database. The presence of the hint resulted in increasing amounts of space in the table being “lost” as older data was deleted by the application which then didn’t reuse the space the inserts always went above the table’s highwater mark; and it wasn’t possible to change the application code.

The first suggestion aired was to create an SQL Patch to associate the hint /*+ ignore_optim_embedded_hints */ with the SQL in the hope that this would make Oracle ignore the append hint. This won’t work, of course, because the append hint is not an optimizer hint, it’s a “behaviour” hint.

There are, however, various things that will make the append hint invalid – sometimes to the great surprise of the people using it. The three things I can think of at present are:

  • row-level triggers
  • enabled foreign key constraints
  • non-unique indexes enforcing unique constraints

It’s been some time since I last ran a detailed check so I’m not going to guarantee that the following claims are still true – if it matters to you then it’s easy enough to create a little test of (say) 10,000 rows inserted into a non-empty, indexed table.

  • Row level triggers turn array processing into single row processing, so there’s a significant increase in redo generation.
  • Non-unique indexes enforcing unique constraints have (small but) potentially undesirable effects on the optimizer and on run-time and may turn array processing into single row processing.
  • Foreign key constraint require checking which may have some impact, but doesn’t turn array processing into single row processing.

Of the three options the foreign key constraint seemed to me to be the best strategy to disable the hint with minimal side effects, so my answer was:

“Create a new table with no data and a primary key constraint; add an invisible column to the target table, index it (online) and add a foreign key constraint from the column to the new table”.

My thinking on this was that the foreign key will disable the append hint. The column will always be null which means it will always satisfy the foreign key constraint without being checked and it won’t introduce any index maintenance overheads. Net effect: /*+ append */ is disabled with no extra overheads.


The append hint is also ignored if the table is an Index Organized Table (IOT), but changing a heap table to an IOT is probably not a sensible choice if all you want to do is disable the hint – the potential for unexpected client side anomalies is too high, and if the table is heavily indexed the processing overhead for the secondary indexes could be fairly significant.

If I recall correctly the person asking the question said that the “do nothing” trigger option sounded like something they would be most comfortable with and they’d live with the overheads. I understand the feeling – an invisible column with an invisible index and extra foreign key constraint sounds too close to the boundary where mixing and matching simple features ends up hitting some unexpected behaviour (i.e. a bug).

Update 14th March 2019

A note on twitter has reminded me that distributed transactions introduce another limitation.  The append hint will work across a database link only if the insert is into a local table; the first insert below will accept the  hint, the second will not.

insert /*+ append */ into local_table select * from table@remote_location;

insert /*+ append */ into table@remote_location select * from local_table;

There are a couple more limitations given in the comments, viz: disable table locking, and adding a security policy (VPD / RLS / FGAC / OLS).


  1. Hi Jonathan,
    As direct path requires an exclusive lock, an alternative can be to ALTER TABLE DISABLE TABLE LOCK:
    (being careful to disable on all referential integrity related tables to avoid the lock horror you described in

    Comment by @FranckPachot — March 8, 2019 @ 5:56 pm GMT Mar 8,2019 | Reply

    • Franck,
      Thanks for that. Did we mention it at the event – I don’t think we did, or maybe we did and then warned about the locking error and put the questionner off the idea.

      Comment by Jonathan Lewis — March 12, 2019 @ 9:23 am GMT Mar 12,2019 | Reply

  2. VPD policy on the table for INSERT also disables the APPEND behavior.

    Comment by Jan S. — March 12, 2019 @ 9:19 am GMT Mar 12,2019 | Reply

  3. Jan S.
    Thanks for that.
    Always useful to know about the restrictions before you run into them on a live system

    Comment by Jonathan Lewis — March 12, 2019 @ 9:24 am GMT Mar 12,2019 | Reply

  4. […] is an interesting example because it highlights a case where you might want to ignore the normal performance penalties associated with row-level triggers. I’ll start by building some […]

    Pingback by Distributed Trap | Oracle Scratchpad — October 8, 2019 @ 10:59 am BST Oct 8,2019 | Reply

  5. […] Avoiding APPEND (March 2019) – ideas for making Oracle ignore an APPEND hint in a 3rd party application. […]

    Pingback by “Ignoring Hints” Catalogue | Oracle Scratchpad — February 21, 2022 @ 9:44 am GMT Feb 21,2022 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: