Databases Reference
In-Depth Information
The following screenshot, instead, represents the execution plan for the
INSERT
in step 5,
obtained with the statement in step 7:
We can see the change in the execution plan as a result of using the
LOAD
AS
SELECT
step to
populate the data in the
MY_SALES
table; this is responsible for the speed-up measured above.
In step 8 we drop the
MY_SALES
table to clean the database schema.
There's more...
Direct path loading can be easily integrated in our
INSERT
statement using a simple hint.
Let's see the magic behind this hint, which gave us an improvement in performance of about
50 percent.
The main difference between conventional and direct path loading is in the way the statement
is executed. Conventional path loading uses the standard transactional SQL engine, so the
rows are inserted in database blocks with free space in the buffer cache and the block is then
asynchronously written to the disk by the database writer process. The available blocks below
the High-Water Mark are used, using the space available in the table free-lists.
Direct path loading completely bypasses the transactional SQL engine. Data is composed in
database blocks, which are directly written on the disk above the High-Water Mark. Free-lists
and buffer cache are not used, and the transaction isn't recoverable, because only minimal
redo log entries are generated.
However, there are some issues in using direct path loading; the table isn't accessible by other
SQL within the same transaction. Other sessions can access the table on which an exclusive
lock is held—DML activities are queued during the execution of the direct path loading.
