Databases Reference
In-Depth Information
Another issue is serialization, there can be only one direct path load on a table, with no other
concurrent updates, deletes, or inserts. This is a scalability issue in a concurrent environment.
The table cannot be queried when the load operation is in progress, until a commit (or a
rollback) is executed.
The last peculiarity of direct path load is related to the High-Water Mark. It loads data above
the High-Water Mark, even if there is room in the blocks below it. We have analyzed the
effects of the High-Water Mark on performance in this chapter in
Avoiding full table scans
.
Now that we have a better comprehension of the direct path load, you will know when to use
it best—in data loading batches, populating staging, data warehouses, and so on. But also
when not to use it—in transaction processing and concurrent environments.
However, the considerations made when commenting our example are still valid. We can
use the single SQL statement without the
APPEND
hint, as follows:
INSERT INTO sh.MY_SALES (cust_id, prod_id)
SELECT cust_id, prod_id FROM sh.sales
WHERE time_id between
TO_DATE('20010701', 'YYYYMMDD')
AND TO_DATE('20011231', 'YYYYMMDD');
In this case, we obtain results similar to the previous execution with the
APPEND
hint, as
shown in the following screenshot:
See also
F
There's more on arrays and
BULK
COLLECT
in
Array processing and bulk collect
in
Chapter 6
,
Optimizing PL/SQL Code
F
You can find more details on optimizer hints in
Exploring the optimizer hints
in
Chapter 7
,
Improving the Oracle Optimizer
F
Using create table as select
in
Chapter 8
,
Other Optimizations
shows more
information on this kind of operation
F
Direct path load from external files is explored in more detail in
Chapter 9
,
Tuning memory
in the
Direct path inserting
recipe
