3

I wish to do a serial, logging insert of bulk data from one table to another. This is once-of as part of a data migration so swapping partitions, etc is not an answer.

The SQL will be of the following structure:

INSERT /*+ APPEND */ ... SELECT FROM ....

What might cause Oracle to run this a convential insert rather than a direct path insert?

For example, I believe having a trigger on the table will cause Oracle to conduct a convential insert. Is there a definitive list of restrictions?

WW.
  • 23,793
  • 13
  • 94
  • 121

1 Answers1

6

A quote from http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1211797200346279484 :

"insert /*+ append */ will ignore the append hint and use conventional path loading when the table has referential integrity or a trigger..." - Tom Kyte

From https://docs.oracle.com/cd/E18283_01/server.112/e17118/statements_9014.htm#sthref6486

Direct-path INSERT is subject to a number of restrictions. If any of these restrictions is violated, then Oracle Database executes conventional INSERT serially without returning any message, unless otherwise noted:

  • You can have multiple direct-path INSERT statements in a single transaction, with or without other DML statements. However, after one DML statement alters a particular table, partition, or index, no other DML statement in the transaction can access that table, partition, or index.

  • Queries that access the same table, partition, or index are allowed before the direct-path INSERT statement, but not after it.

  • If any serial or parallel statement attempts to access a table that has already been modified by a direct-path INSERT in the same transaction, then the database returns an error and rejects the statement.

  • The target table cannot be of a cluster.

  • The target table cannot contain object type columns.

  • Direct-path INSERT is not supported for an index-organized table (IOT) if it is not partitioned, if it has a mapping table, or if it is reference by a materialized view.

  • Direct-path INSERT into a single partition of an index-organized table (IOT), or into a partitioned IOT with only one partition, will be done serially, even if the IOT was created in parallel mode or you specify the APPEND or APPEND_VALUES hint. However, direct-path INSERT operations into a partitioned IOT will honor parallel mode as long as the partition-extended name is not used and the IOT has more than one partition.

  • The target table cannot have any triggers or referential integrity constraints defined on it.

  • The target table cannot be replicated.

  • A transaction containing a direct-path INSERT statement cannot be or become distributed.

However the list in the manual is not exhaustive and is not completely accurate. For example, referential integrity constraints do not block direct-path INSERT if that constraint is part of reference partitioning.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
Daniel Haviv
  • 1,036
  • 8
  • 16
  • Tom gives those as examples so that's a good source, but I wonder if there are any other possibilities. e.g. DEFAULT SYSDATE. Of course I can experiment, but I'd rather find a documented answer. – WW. Apr 20 '12 at 12:13
  • you can find the restrictions here: http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9014.htm#SQLRF01604 under "Conventional and Direct-Path INSERT" – Daniel Haviv Apr 20 '12 at 16:48
  • Thanks - that's what I was missing. Maybe edit that into your answer as it's better than the Tom Kyte link. – WW. Apr 21 '12 at 01:41