Database Reference
In-Depth Information
Direct-Path Insert
Oracle Database provides two ways for loading data into a table (provided it's not stored in a cluster): conventional
inserts and direct-path inserts.
Conventional inserts
, as the name suggests, are the ones that are generally used.
Direct-
path inserts
are used only when the database engine is explicitly instructed to do so. The aim of direct-path inserts
is to efficiently load large amounts of data (they can have poorer performance than conventional inserts for small
amounts of data). They're able to achieve this goal because their implementation is optimized for performance at the
expense of functionality. For this reason, they're subject to more requirements and restrictions than conventional
inserts. In this section, I discuss how direct-path inserts work, when they should be used, and some common pitfalls
and fallacies related to them.
■
Note
To load data CTaS statements, use a direct-path insert.
How It Works
You can enable direct-path inserts either by specifying a hint or by using a specific feature. The following
possibilities exist:
append
hint in
INSERT INTO ... SELECT ...
statements (including multitable
inserts) and
MERGE
statements (for the part inserting data):
•
Specify the
INSERT /*+
append
*/ INTO ... SELECT ...
append
hint in “regular”
INSERT
statements that use the
VALUES
clause
(works only in version 11.1):
•
Specify the
INSERT /*+
append
*/ INTO ... VALUES (...)
append_values
hint in “regular”
INSERT
statements that use the
VALUES
clause
(works only from version 11.2 onward):
•
Specify the
INSERT /*+
append_values
*/ INTO ... VALUES (...)
INSERT INTO ... SELECT ...
statements in parallel. Note that in this case, both the
INSERT
and the
SELECT
can be parallelized independently. To take advantage of direct-path
inserts, at least the
INSERT
part must be parallelized.
•
Execute
•
Use the OCI direct-path interface either directly or via an application that uses it (for example,
the SQL*Loader utility).
If you need to disable a direct-path insert for a SQL statement that automatically enables it (for example, an
INSERT INTO ... SELECT ...
statements executed in parallel), you can specify the
noappend
hint.
