insert,update,delete产生redo日志的比较

对于三种操作产生的redo日志，网上找到的信息：update > delete > insert，但是我实验发现，这需要有个前提条件，就是update的字节总和要约等于整条记录的字节总和。如果你使用一个标志位来表示记录删除，那么使用update将会产生更少的redo。

决定update产生redo量的一个因素就是更新的字节数。

实验数据如下：

连接到:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table test

  2  ( 

  3  a1 char(1),

  4  a2 char(10),

  5  a3 char(100),

  6  a4 char(1000)

  7  );

表已创建。

SQL> set autotrace on

SQL> insert into test(a1, a2, a3, a4)

  2  values('1', '1', '1', '1');

已创建 1 行。

执行计划

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

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

| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | INSERT STATEMENT |      |     1 |   100 |     1   (0)| 00:00:01 |

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

统计信息

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

          3  recursive calls

         22  db block gets

          2  consistent gets

          0  physical reads

          0  redo size

        542  bytes sent via SQL*Net to client

        503  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          2  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL> update test set a2='2' where a1='1';

已更新 1 行。

执行计划

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

Plan hash value: 839355234

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | UPDATE STATEMENT   |      |     1 |    15 |     3   (0)| 00:00:01 |

|   1 |  UPDATE            | TEST |       |       |            |          |

|*  2 |   TABLE ACCESS FULL| TEST |     1 |    15 |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - filter("A1"='1')

Note

-----

   - dynamic sampling used for this statement

统计信息

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

         48  recursive calls

          1  db block gets

         19  consistent gets

          0  physical reads

        364  redo size

        541  bytes sent via SQL*Net to client

        479  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL> update test set a3='2' where a1='1';

已更新 1 行。

执行计划

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

Plan hash value: 839355234

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | UPDATE STATEMENT   |      |     1 |   105 |     3   (0)| 00:00:01 |

|   1 |  UPDATE            | TEST |       |       |            |          |

|*  2 |   TABLE ACCESS FULL| TEST |     1 |   105 |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - filter("A1"='1')

Note

-----

   - dynamic sampling used for this statement

统计信息

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

         27  recursive calls

          1  db block gets

         17  consistent gets

          0  physical reads

        548  redo size

        545  bytes sent via SQL*Net to client

        479  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL> update test set a4='2' where a1='1';

已更新 1 行。

执行计划

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

Plan hash value: 839355234

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | UPDATE STATEMENT   |      |     1 |  1005 |     3   (0)| 00:00:01 |

|   1 |  UPDATE            | TEST |       |       |            |          |

|*  2 |   TABLE ACCESS FULL| TEST |     1 |  1005 |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - filter("A1"='1')

Note

-----

   - dynamic sampling used for this statement

统计信息

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

         27  recursive calls

          1  db block gets

         17  consistent gets

          0  physical reads

       2348  redo size

        546  bytes sent via SQL*Net to client

        479  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL> delete test where a1='1';

已删除 1 行。

执行计划

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

Plan hash value: 180605370

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | DELETE STATEMENT   |      |     1 |     3 |     3   (0)| 00:00:01 |

|   1 |  DELETE            | TEST |       |       |            |          |

|*  2 |   TABLE ACCESS FULL| TEST |     1 |     3 |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - filter("A1"='1')

Note

-----

   - dynamic sampling used for this statement

统计信息

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

          4  recursive calls

          2  db block gets

         15  consistent gets

          0  physical reads

       1400  redo size

        547  bytes sent via SQL*Net to client

        468  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL>

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

转载于:http://blog.itpub.net/14683446/viewspace-714492/