Databases Reference
In-Depth Information
We can see the results for this example in the following screenshot:
The timing for the operation is slightly better in this case than in the example in which we
used the arrays. However, there are some considerations to be taken care of, when using
direct path load, explained later in this recipe.
In the last step, we drop the table to clear the schema from our tests.
There's more...
Looking at the examples in this recipe, there are two lessons to be learned. The first is "never
write procedural code when you can perform the same task in a single SQL instruction" — let
the optimizer do the work it's designed for.
The second lesson is "use arrays and set operations when you can". The Oracle database
works better when the operations are expressed in sets, so the engine can use all the
resources available to satisfy the requests optimally.
When to use direct path load
In step 6, we used a direct path load to insert data in the
MY_SALES
table using the
APPEND
hint. However, there are some considerations to be taken care of when using direct path load,
to understand when to use and when not to use this type of operation. If direct path load
would be the fastest method to insert data in tables, without constraints, the optimizer would
use it by default.
The first consideration is what happens when the database engine "moves" data blocks to the
disk, without using the buffer cache. When we have to move small datasets, direct path load
would be slower than conventional path load, which uses the buffer cache.
