【sql调优之执行计划】使用hint(六) append and noappend

看过eygle的一个关于nologging的测试，也看了alantany的append测试，测试了一下，没有发现有很大差别，不清楚是哪里出了问题，先放着。等alan的回复。

数据库版本：

SQL> select * from v$version;

 

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 – Production

 

非归档模式下：

SQL> create table t_test_append1 as select policy_id from t_policy where 1=2 ;

 

表已创建。

SQL> alter table t_test_append1 nologging;

 

表已更改。

SQL> insert /*+ noappend */ into t_test_append1 select policy_id from t_policy a;

 

已创建9999行。

 

 

统计信息

----------------------------------------------------------

          1  recursive calls

        170  db block gets

         56  consistent gets

          0  physical reads

     147812  redo size

        677  bytes sent via SQL*Net to client

        614  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

       9999  rows processed

 

SQL> insert /*+ append */ into t_test_append1 select policy_id from t_policy a

  2  ;

 

已创建9999行。

 

 

统计信息

----------------------------------------------------------

          1  recursive calls

         27  db block gets

         25  consistent gets

          0  physical reads

     131716  redo size

        661  bytes sent via SQL*Net to client

        613  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

       9999  rows processed

 

SQL>

 

SQL> alter table t_test_append1 logging;

 

表已更改。

 

SQL> insert /*+ noappend */ into t_test_append1 select policy_id from t_policy a;

 

已创建9999行。

 

 

统计信息

----------------------------------------------------------

        177  recursive calls

        170  db block gets

         82  consistent gets

          0  physical reads

     147812  redo size

        677  bytes sent via SQL*Net to client

        614  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

          6  sorts (memory)

          0  sorts (disk)

       9999  rows processed

 

SQL>  insert /*+ append */ into t_test_append1 select policy_id from t_policy a;

 

已创建9999行。

 

 

统计信息

----------------------------------------------------------

          1  recursive calls

         27  db block gets

         25  consistent gets

          0  physical reads

     139988  redo size

        661  bytes sent via SQL*Net to client

        613  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

       9999  rows processed

 

SQL>

 

归档模式下：

 

SQL> create table t_test_append2 as select * from all_objects a where 1=2 ;

 

表已创建。

 

SQL> alter table t_test_append2 nologging ;

 

表已更改。

 

SQL> insert into t_test_append2  select * from all_objects a ;

 

已创建59067行。

 

 

统计信息

----------------------------------------------------------

       6929  recursive calls

       7952  db block gets

      89658  consistent gets

          0  physical reads

    6646932  redo size

        673  bytes sent via SQL*Net to client

        592  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

          6  sorts (memory)

          0  sorts (disk)

      59067  rows processed

 

SQL> rollback;

 

回退已完成。

 

SQL> insert into /*+ append */t_test_append2  select * from all_objects a ;

 

已创建59067行。

 

 

统计信息

----------------------------------------------------------

       5058  recursive calls

       6095  db block gets

      89257  consistent gets

          0  physical reads

    6510168  redo size

        676  bytes sent via SQL*Net to client

        608  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

      59067  rows processed

 

SQL> rollback;

 

回退已完成。

 

SQL>

SQL> insert into /*+ noappend */t_test_append2  select * from all_objects a ;

 

已创建59067行。

 

 

统计信息

----------------------------------------------------------

       6693  recursive calls

       6096  db block gets

      90611  consistent gets

          1  physical reads

    6508744  redo size

        675  bytes sent via SQL*Net to client

        610  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

         44  sorts (memory)

          0  sorts (disk)

      59067  rows processed

 

SQL> rollback;

 

回退已完成。

 

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16179598/viewspace-675574/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16179598/viewspace-675574/