直接加载和redo！

来源：互联网发布：linux arping命令编辑：程序博客网时间：2024/06/06 03:14

首先有t1和t2两个表，t1里面没有记录，t2里面有大约40万条记录：

SQL> select count(*) from t1;  COUNT(*)----------         0SQL> select count(*) from t2;  COUNT(*)----------    402344

下面是表是否记录redo的操作：

SQL> select table_name,logging from user_tables where table_name = 'T1';TABLE_NAME                     LOG------------------------------ ---T1                             YESSQL> alter table t1 nologging;表已更改。SQL> select table_name,logging from user_tables where table_name = 'T1';TABLE_NAME                     LOG------------------------------ ---T1                             NOSQL> alter table t1 logging;表已更改。SQL> select table_name,logging from user_tables where table_name = 'T1';TABLE_NAME                     LOG------------------------------ ---T1                             YES

可以看见在logging模式下，普通加载和直接加载redo size大小差别不是很大！

SQL> alter table t1 logging;表已更改。SQL> set autotrace trace stat;SQL> insert into t1 select * from t2;已创建402344行。统计信息----------------------------------------------------------       2783  recursive calls      50954  db block gets      12904  consistent gets       5560  physical reads   45313632  redo size        670  bytes sent via SQL*Net to client        564  bytes received via SQL*Net from client          3  SQL*Net roundtrips to/from client          6  sorts (memory)          0  sorts (disk)     402344  rows processedSQL> rollback;回退已完成。SQL> insert /*+ append */ into t1 select * from t2;已创建402344行。统计信息----------------------------------------------------------       1016  recursive calls       6366  db block gets       5956  consistent gets       5608  physical reads   45712692  redo size        655  bytes sent via SQL*Net to client        578  bytes received via SQL*Net from client          3  SQL*Net roundtrips to/from client          1  sorts (memory)          0  sorts (disk)     402344  rows processed

可以看见在nologging状态下面，差别就出来了吧！

SQL> set autotrace trace stat;SQL> alter table t1 nologging;表已更改。SQL> insert into t1 select * from t2;已创建402344行。统计信息----------------------------------------------------------        180  recursive calls      41450  db block gets      16625  consistent gets       9405  physical reads   44639472  redo size        672  bytes sent via SQL*Net to client        564  bytes received via SQL*Net from client          3  SQL*Net roundtrips to/from client          6  sorts (memory)          0  sorts (disk)     402344  rows processedSQL> rollback;回退已完成。SQL> insert /*+ append */ into t1 select * from t2;已创建402344行。统计信息----------------------------------------------------------          4  recursive calls       5625  db block gets       5610  consistent gets       5632  physical reads      10572  redo size        656  bytes sent via SQL*Net to client        578  bytes received via SQL*Net from client          3  SQL*Net roundtrips to/from client          1  sorts (memory)          0  sorts (disk)     402344  rows processed