Oracle direct path operation

Summary:
Direct path insertion directly allocates space outside the high waterline, bypasses the data buffer, and directly inserts data into the data file where the table is located.
Direct path insertion does not generate Redo and Undo logs and relies on high water points for rollback.
At the end of direct path insertion, Oracle maintains the index. To avoid the impact of maintaining the index performance, you can delete the index and recreate it after the insertion is complete.
Direct path insertion will cause table-level locks to be applied to the inserted Table. Before submission, other sessions cannot perform insert, update, delete, or other operations on the table.

1. direct path insertion and indirect path insertion are different
I believe many people already know this question. I will review it again to help beginners.
Create table 1 as select column 1, column 2,... select table 2
Insert/* + append */into Table 1 select column 1, column 2,... select table 2
Insert in the preceding format is called direct path insert. Of course, there is also a direct path insertion form in SQL * Loader.
The so-called direct path insertion means to bypass the Buffer cache and directly insert data into the data file where the table is located.
For example, if there is a table AA, You need to insert the data in AA into the table BB. Under normal indirect insertion, the AA data block is first transmitted to the Buffer cache, and then the BB block is also transmitted to the Buffer cache, read and travel from AA blocks in the Buffer cache and insert them into BB blocks. BB blocks become dirty blocks, and then wait for DBWn to write them into the data file. Therefore, after the indirect path is inserted, both the block of the AA table and the block of the BB table will appear in the Buffer cache.
Insert the data block of the AA table into the Buffer cache, read the data block of the AA table, and directly write it into the data file of the BB table. After the data is inserted, the data blocks of table BB will not appear in the Buffer cache.
The following is a test:
Step 1: Prepare the test table:
SQL> create table aa (id number (4), name varchar2 (5 ));
The table has been created.
SQL> create table bb (id number (4), name varchar2 (5 ));
The table has been created.
SQL> insert into aa values (1, 'A ');
One row has been created.
SQL> insert into aa values (2, 'bb ');
One row has been created.
SQL> insert into aa values (3, 'cc ');
One row has been created.
SQL> insert into aa values (4, 'dd ');
One row has been created.
SQL> commit;
Submitted.
SQL> select dbms_rowid.rowid_relative_fno (rowid), dbms_rowid.rowid_block_number (rowid) from aa;
DBMS_ROWID.ROWID_RELATIVE_FNO (ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID)
------------------------------------------------------------------------
4 18493
4 18493
4 18493
4 18493
Currently, there are four rows in the AA table, occupying 18493 blocks. The BB table has no data.
Step 2: Clear the buffer cache. Here I use the method of restarting the database:
SQL> shutdown immediate
SQL> startup
Step 3: Use a direct path to insert data from the AA table to the BB table:
SQL> insert/* + append */into bb select * from aa;
Four rows have been created.
SQL> commit;
Submitted.
Step 4: Use V $ bh to view the blocks in the Buffer cache:
SQL> select file #, block # from v $ bh where objd = (select data_object_id from user_objects where object_name = 'A ');
FILE # BLOCK #
--------------------
4 18491
4 18491
4 18494
4 18492
4 18495
4 18493 <---- the block currently containing data
4 18496
You have selected 7 rows.
A full table scan is performed on the AA table. Therefore, all the blocks in the medium and high water points in the AA table are read into the Buffer cache. Of course, this includes 18493 blocks containing data.
SQL> select file #, block # from v $ bh where objd = (select data_object_id from user_objects where object_name = 'bb ');
FILE # BLOCK #
--------------------
4 18499
4 18499
4 18497
SQL> select dbms_rowid.rowid_relative_fno (rowid), dbms_rowid.rowid_block_number (rowid) from bb;
DBMS_ROWID.ROWID_RELATIVE_FNO (ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID)
------------------------------------------------------------------------
4 18500
4 18500
4 18500
4 18500
The preceding Two Queries show that the BB table occupies 18,500th data blocks. However, after the direct path is inserted, the 18500 data blocks are not transferred to the Buffer cache. Buffer cache contains only 18499 and 18497. Among them, 18499 is the segment header block, and 18497 is the L1 block. After direct path insertion, You need to modify the usage of the data block in the L1 block.
Step 5: Try indirect path insertion again:
SQL> insert into bb select * from aa;
Four rows have been created.
SQL> commit;
Submitted.
SQL> select file #, block # from v $ bh where objd = (select data_object_id from user_objects where object_name = 'bb ');
FILE # BLOCK #
--------------------
4 18504 <---- data block inserted in this indirect path
4 18499
4 18499
4 18502
4 18497
4 18500
4 18503
4 18498
4 18501
Nine rows have been selected.
SQL> select dbms_rowid.rowid_relative_fno (rowid), dbms_rowid.rowid_block_number (rowid) from bb;
DBMS_ROWID.ROWID_RELATIVE_FNO (ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID)
------------------------------------------------------------------------
4 18500
4 18500
4 18500
4 18500
4 18504
4 18504
4 18504
4 18504
Eight rows have been selected.
As shown in the above experiment, indirect path insertion requires data blocks to be first transmitted to the Buffer cache. This is a common way for Oracle to modify data. Instead of directly modifying data files, the data files are modified in the memory, which is then protected by logs. For the modification of a small amount of data, the performance of this method is still very good. However, with the modification of a large amount of data, direct path insertion can provide better performance.
In addition to dropping blocks from the BB table into the Buffer cache, direct path insertion does not generate rollback information. The following is a further experiment:

1 2 Next Page