不要被网络或他人误导啊，学技术还是身体力行的好

〇〇

SQL> set autot trace stat;
SQL> truncate table a;

Table truncated.

Elapsed: 00:00:00.59
SQL> insert into a select level from dual connect by level<=1E6;

1000000 rows created.

Elapsed: 00:00:07.80

Statistics
----------------------------------------------------------
       3787  recursive calls
      71482  db block gets
      10799  consistent gets
          5  physical reads
   73039404  redo size
        846  bytes sent via SQL*Net to client
        811  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

SQL> truncate table a;

Table truncated.

Elapsed: 00:00:00.82
SQL> insert /*+append*/into a select level from dual connect by level<=1E6;

1000000 rows created.

Elapsed: 00:00:06.41

Statistics
----------------------------------------------------------
       3803  recursive calls
      55060  db block gets
       7744  consistent gets
          5  physical reads
   57354540  redo size
        830  bytes sent via SQL*Net to client
        822  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

SQL> truncate table a;

Table truncated.

Elapsed: 00:00:00.89

SQL>   insert /*+append*/into a nologging  select level from dual connect by level<=1E6;

1000000 rows created.

Elapsed: 00:00:07.37

Statistics
----------------------------------------------------------
       3795  recursive calls
      55088  db block gets
       7745  consistent gets
          5  physical reads
   57390256  redo size
        831  bytes sent via SQL*Net to client
        835  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

〇〇

--无论表是否logging,append都减少redo,但有索引redo会大
SQL> set autot trace stat
SQL> truncate table a;

Table truncated.

Elapsed: 00:00:00.66
SQL> alter table a nologging;

Table altered.

Elapsed: 00:00:00.14
SQL> insert /*+append*/into a nologging  select level from dual connect by level<=1E6;

1000000 rows created.

Elapsed: 00:00:04.69

Statistics
----------------------------------------------------------
       3995  recursive calls
      55077  db block gets
       7777  consistent gets
          7  physical reads
   57373584  redo size
        818  bytes sent via SQL*Net to client
        833  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
         10  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

SQL> truncate table a;

Table truncated.

Elapsed: 00:00:00.19
SQL> alter table a drop constraint ub;

Table altered.

Elapsed: 00:00:00.46
SQL> insert /*+append*/into a nologging  select level from dual connect by level<=1E6;

1000000 rows created.

Elapsed: 00:00:01.72

Statistics
----------------------------------------------------------
       1843  recursive calls
       2571  db block gets
       1791  consistent gets
          2  physical reads
      68184  redo size
        823  bytes sent via SQL*Net to client
        833  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

SQL> truncate table a;

Table truncated.

Elapsed: 00:00:00.08
SQL> insert /*+append*/into a  select level from dual connect by level<=1E6;

1000000 rows created.

Elapsed: 00:00:01.57

Statistics
----------------------------------------------------------
       1694  recursive calls
       2570  db block gets
       1773  consistent gets
          2  physical reads
      68040  redo size
        823  bytes sent via SQL*Net to client
        823  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

SQL> truncate table a;

Table truncated.

Elapsed: 00:00:00.07
SQL> alter table a logging;

Table altered.

Elapsed: 00:00:00.01
SQL> insert /*+append*/into a  select level from dual connect by level<=1E6;

1000000 rows created.

Elapsed: 00:00:01.55

Statistics
----------------------------------------------------------
       1843  recursive calls
       2569  db block gets
       1791  consistent gets
          2  physical reads
      68032  redo size
        826  bytes sent via SQL*Net to client
        823  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

SQL>

dingjun123

原帖由 〇〇 于 2011-7-21 14:11 发表
--无论表是否logging,append都减少redo,但有索引redo会大
SQL> set autot trace stat
SQL> truncate table a;

Table truncated.

Elapsed: 00:00:00.66
SQL> alter table a nologging;

Table altered.

Elapsed: 00:00:00.14
SQL> insert /*+append*/into a nologging  select level from dual connect by level truncate table a;

Table truncated.

Elapsed: 00:00:00.19
SQL> alter table a drop constraint ub;

Table altered.

Elapsed: 00:00:00.46
SQL> insert /*+append*/into a nologging  select level from dual connect by level truncate table a;

Table truncated.

Elapsed: 00:00:00.08
SQL> insert /*+append*/into a  select level from dual connect by level truncate table a;

Table truncated.

Elapsed: 00:00:00.07
SQL> alter table a logging;

Table altered.

Elapsed: 00:00:00.01
SQL> insert /*+append*/into a  select level from dual connect by level

--无论表是否logging,append都减少redo,但有索引redo会大????????

eygle都测试过说错的了

dingjun123

当然这里测试redo,undo都是测试表的，没有说索引

〇〇

我的非归档

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            +ARCH
Oldest online log sequence     5258
Current log sequence           5260

〇〇

原帖由 dingjun123 于 2011-7-21 14:12 发表


--无论表是否logging,append都减少redo,但有索引redo会大????????

eygle都测试过说错的了

难道是set autot trace stat
输出错了？

dingjun123

不是的，那个额外的是维护索引产生的，我测试是测试表的

Coast_lichao

redo包含一下的重做信息：
1.回滚段头(undo segment header)
2.撤销块(undo block)
3.数据段头(data segment header)
4.数据块(data block)
测试环境：
SQL> select*From v$version where rownum<2;

BANNER
-------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     60
Current log sequence           68


create table notemp_test(id number,name char(1000) default ' ');

case1:对于永久表的DML
--insert 10000行数据
insert into notemp_test(id)select object_id from dba_objects where rownum<=10000;

select xid,used_ublk,used_urec from v$transaction;
XID               USED_UBLK  USED_UREC
----------------  ---------- ----------
0A0015000B030000     25       2500
--USED_UREC创建了2500条撤销记录(undo record)
select*From v$sesstat where statistic#=(select statistic# from v$statname
where name='redo entries')and sid=(select sid from v$mystat where rownum=1);

       SID STATISTIC#      VALUE
---------- ---------- ----------
       130        139       8392
--创建了8392个重做条目(redo entry)
commit以上的测试。
case2:对于永久表的direct load aperation
insert/*+ append */ into notemp_test select*from notemp_test where rownum<=10000;
select xid,used_ublk,used_urec from v$transaction;

XID               USED_UBLK  USED_UREC
---------------- ---------- ----------
0300090038030000          1          1
--创建一条撤销记录
select*From v$sesstat where statistic#=(select statistic# from v$statname
where name='redo entries')and sid=(select sid from v$mystat where rownum=1);
       SID STATISTIC#      VALUE
---------- ---------- ----------
       130        139        617
--创建617个redo entry，还是挺多的，可能是要对数据段头的信息创建redo

chicheng_cn421

实践出真知！

theoffspring

建议LZ把环境变量的语言设成英文，看着中文真别扭。