添加sql hint append需要考虑两种情况

1：归档模式

2：非归档模式

[oracle@oracle9ivm ~]$ sqlplus /nolog

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Sep 12 11:44:31 2011

SQL> conn / as sysdba
Connected.
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oradata/ora10g/arch
Oldest online log sequence     132
Current log sequence           134
SQL> shutdonw
SP2-0042: unknown command "shutdonw" - rest of line ignored.
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
[oracle@oracle9ivm ~]$ sqlplus /nolog

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Sep 12 11:45:16 2011

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oradata/ora10g/arch
Oldest online log sequence     132
Next log sequence to archive   134
Current log sequence           134
SQL> alter database open;

Database altered.

SQL> d
SP2-0042: unknown command "d" - rest of line ignored.
SQL> desc t;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER(38)
NAME                                               CHAR(10)

SQL> drop table t;

Table dropped.

SQL> set timing on
SQL> set autot on stat
SQL> create table t as select * from dba_objects where 1=0;

Table created.

Elapsed: 00:00:00.09
SQL> insert into t select * from dba_objects;

30978 rows created.

Elapsed: 00:00:00.80

Statistics
----------------------------------------------------------
        336 recursive calls
       2600 db block gets
      24918 consistent gets
        221 physical reads
    3450312 redo size
        621 bytes sent via SQL*Net to client
        539 bytes received via SQL*Net from client
          3 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
      30978 rows processed

SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.10
SQL> insert /*+ append */ into t select * from dba_objects;

30978 rows created.

Elapsed: 00:00:00.55

Statistics
----------------------------------------------------------
         34 recursive calls
         37 db block gets
      23912 consistent gets
          0 physical reads
    3498972 redo size
        607 bytes sent via SQL*Net to client
        553 bytes received via SQL*Net from client
          3 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
      30978 rows processed

SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.00
SQL> alter table t nologging;

Table altered.

Elapsed: 00:00:00.05
SQL> insert /*+ append */ into t select * from dba_objects;

30978 rows created.

Elapsed: 00:00:00.27

Statistics
----------------------------------------------------------
        169 recursive calls
          5 db block gets
      23918 consistent gets
          0 physical reads
        784 redo size
        607 bytes sent via SQL*Net to client
        553 bytes received via SQL*Net from client
          3 SQL*Net roundtrips to/from client
          5 sorts (memory)
          0 sorts (disk)
      30978 rows processed

SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.00
SQL> alter table t logging;

Table altered.

Elapsed: 00:00:00.02
SQL> insert /*+ append */ into t select * from dba_objects;

30978 rows created.

Elapsed: 00:00:00.52

Statistics
----------------------------------------------------------
        169 recursive calls
          5 db block gets
      23918 consistent gets
          0 physical reads
    3504160 redo size
        607 bytes sent via SQL*Net to client
        553 bytes received via SQL*Net from client
          3 SQL*Net roundtrips to/from client
          5 sorts (memory)
          0 sorts (disk)
      30978 rows processed

SQL>

ITPub博客

关于append sql hint的实验

最新文章