【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/，如需轉載，請註明出處，否則將追究法律責任。