㈠ 直接路径加载和buffer cache
然后由普通的Oracle Server Process进程把数据块写入数据文件
因为不经过buffer cache，所以不需要DBWn介入
假如有表a，现要将a中的数据插入表b，在普通的插入下，需先将a的数据块I/O到buffer cache
在buffer cache中从a的块中读出行，插进b的块中
此时，b的块就都变成了脏块，再等待DBWn把他们flush到数据文件
因此，普通插入后，a表和b表的块都会在buffer cache中出现
而直接路径插入，将a表的数据块I/O到buffer cache，读出行，直接写进b表所在的数据文件
插入完成后，除了表头块外，b表的数据块并未在buffer cache中出现过
测试：
hr@ORCL> create table a (id number,name varchar2(10));
Table created.
hr@ORCL> create table b (id number,name varchar2(10));
Table created.
hr@ORCL> insert into a values(1,'aa');
1 row created.
hr@ORCL> insert into a values(2,'bb');
1 row created.
hr@ORCL> insert into a values(3,'cc');
1 row created.
hr@ORCL> insert into a values(4,'dd');
1 row created.
hr@ORCL> commit;
Commit complete.
hr@ORCL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from a;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
4 508
4 508
4 508
4 508
--现在a表有4行，占用块508，而目前b表还木有数据
--将buffer cache清空
hr@ORCL> alter system flush buffer_cache;
System altered.
--先用直接路径插入，从a表向b表插入数据
hr@ORCL> insert /*+ append */ into b select * from a;
4 rows created.
hr@ORCL> commit;
Commit complete.
--使用v$bh查看buffer cache中的块
hr@ORCL> select file#,block# from v$bh where objd=(select data_object_id from user_objects where object_name='A');
FILE# BLOCK#
---------- ----------
4 508 ←←当前包含数据的块
4 508 ←←当前包含数据的块
4 511
4 511
4 506
4 509
4 509
4 512
4 512
4 507
4 507
4 510
4 510
4 505
14 rows selected.
--这是因为对a表进行全表扫，a表中低高水位点下所有的块都被读进buffer cache，这其中当然也包括508了
hr@ORCL> select file#,block# from v$bh where objd=(select data_object_id from user_objects where object_name='B');
FILE# BLOCK#
---------- ----------
4 2571
4 2569
4 2570
hr@ORCL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from b;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
4 2572
4 2572
4 2572
4 2572
--上面两个查询可以看到，b表中的数据占用地2572块，但是，直接路径插入后，2572并没有被调入buffer cache
--buffer cache中只有2569 2570 2571
--其中2571是段头块(select header_file,header_block from dba_segments where segment_name='B')
--2570 2569则是L1 L2这两个位图块
--接下来使用普通插入
hr@ORCL> alter system flush buffer_cache;
System altered.
hr@ORCL> insert into b select * from a;
4 rows created.
hr@ORCL> commit;
Commit complete.
hr@ORCL> select file#,block# from v$bh where objd=(select data_object_id from user_objects where object_name='B');
FILE# BLOCK#
---------- ----------
4 2571
4 2574
4 2569
4 2575
4 2570
4 2570
4 2573
4 2576 ←←本次普通插入的数据所在的块
8 rows selected.
hr@ORCL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from b;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
----------