Database Reference
In-Depth Information
•
The modified table is index organized.
•
The modified table is stored in a cluster.
•
The modified table contains object type columns.
•
The modified table has a primary (or unique) key that is policed via a nonunique index. From
version 11.1, this limitation no longer exists.
When to Use It
You should use direct-path inserts whenever you have to load a large amount of data and the restrictions that apply to
direct-path inserts aren't a concern for you.
If performance is your primary goal, you might also consider using minimal logging (
nologging
). As previously
explained, however, you should use this possibility only if you fully understand and accept the implications of
doing so and if you take necessary measures to not lose data in the process.
Pitfalls and Fallacies
Even if minimal logging is
not
used, a database running in noarchivelog mode doesn't generate redo for direct-path
inserts.
It isn't possible to use minimal logging for segments stored in a database or a tablespace in
force logging
mode. In
fact, force logging overrides the
nologging
parameter. Note that force logging is particularly useful when replication
features like standby databases and Streams are used. To successfully use them, redo logs need to contain information
about all modifications.
During a direct-path insert, the high watermark isn't increased. This operation is performed only when the
transaction is committed. Therefore, the session executing a direct-path insert (and only that session—for other
sessions, the uncommitted data above the high watermark isn't even visible) can't access the modified table after the
load without committing (or rolling back) the transaction. SQL statements executed before committing (or rolling
back) terminate with an
ORA-12838: cannot read/modify an object after modifying it in parallel
error.
Here's an example:
SQL> INSERT /*+ append */ INTO t SELECT * FROM t;
SQL> SELECT count(*) FROM t;
SELECT count(*) FROM t
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
SQL> COMMIT;
SQL> SELECT count(*) FROM t;
COUNT(*)
----------
10000
The text associated with the 0RA-12938 error may be confusing also because it's generated even if no parallel
processing is used.
